// // RentBikes(): // // Handles nessesary Db updates for the rental. // public int RentBikes(List <int> selectedBikes, int cid, decimal expDur) { int rid = -1; SqlTransaction tx = null; int retries = 0; try { dataTier.openConnection(); tx = dataTier.GetDbConnection().BeginTransaction(IsolationLevel.Serializable); // add rental table entry int rowsModified = dataTier.ExecuteActionQuery(string.Format(@" INSERT INTO Rental (CID, StartTime, ExpDuration, NumBikes) VALUES ({0}, GETDATE(), {1}, {2}); ", cid, expDur, selectedBikes.Count)); // insert failed if (rowsModified != 1) { tx.Rollback(); dataTier.closeConnection(); MessageBox.Show("RentBikes(): INSERT failed") } rid = GetLastRentalId(false); // update bike table and add rental details foreach (int bid in selectedBikes) { dataTier.ExecuteActionQuery(string.Format(@" INSERT INTO RentalDetail(RID, BID) VALUES ({0}, {1}); ", rid, bid)); dataTier.ExecuteActionQuery(string.Format(@" UPDATE Bike SET Rented = 1 WHERE BID = {0}; ", bid)); } } catch (SqlException exc) { if (exc.Number == 1205) { retries++; } } catch (Exception exc) { MessageBox.Show("Error in RentBikes(): " + exc.Message); } finally { dataTier.closeConnection(); } return(rid); }
// // GetCustomers(): // // Returns all the customers from Db as a read only list. // public IReadOnlyList <Customer> GetCustomers() { List <Customer> customers = new List <Customer>(); SqlTransaction tx = null; int retries = 0; while (retries < 3) { try { dataTier.openConnection(); tx = dataTier.GetDbConnection().BeginTransaction(); // query DataSet ds = dataTier.ExecuteNonScalarQuery(string.Format(@" SELECT * FROM Customer WITH(INDEX(CustomerLastName_Index)) ORDER BY LastName ASC, FirstName ASC; " )); // build list foreach (DataRow row in ds.Tables["TABLE"].Rows) { customers.Add( new Customer( Convert.ToInt32(row["CID"].ToString()), row["FirstName"].ToString(), row["LastName"].ToString(), row["Email"].ToString() ) ); } } catch (Exception e) { MessageBox.Show("GetCustomers(): " + e.Message); throw new ApplicationException(string.Format ("Error in Business.GetCustomers(): '{0}'", e.Message)); } finally { dataTier.closeConnection(); } } return(customers); }
// // RentBikes(): // // Handles nessesary Db updates for the rental. // public int RentBikes(List <int> selectedBikes, int cid, decimal expDur) { try { dataTier.openConnection(); tx = dataTier.GetDbConnection().BeginTransaction(IsolationLevel.Serializable); // add rental table entry dataTier.ExecuteActionQuery(string.Format(@" INSERT INTO Rental (CID, StartTime, ExpDuration, NumBikes) VALUES ({0}, GETDATE(), {1}, {2}); ", cid, expDur, selectedBikes.Count)); int rid = GetLastRentalId(false); // update bike table and add rental details foreach (int bid in selectedBikes) { dataTier.ExecuteActionQuery(string.Format(@" INSERT INTO RentalDetail(RID, BID) VALUES ({0}, {1}); ", rid, bid)); dataTier.ExecuteActionQuery(string.Format(@" UPDATE Bike SET Rented = 1 WHERE BID = {0}; ", bid)); } return(rid); } catch (Exception exc) { MessageBox.Show("Error in RentBikes(): " + exc.Message); } finally { dataTier.closeConnection(); } return(-1); }
// // RentBikes(): // // Handles nessesary Db updates for the rental. // Return value: // -1 - query error // -2 - customer already on the rent // <= -1000 - negative id of bike rented // > 0 - rental id // public int RentBikes(List <int> selectedBikes, int cid, decimal expDur) { int rid = -1; SqlTransaction tx = null; int retries = 0; while (retries < 3) { try { dataTier.openConnection(); tx = dataTier.GetDbConnection().BeginTransaction(IsolationLevel.Serializable); dataTier.setCmdTransaction(tx); if (GetCustRentStatus(cid.ToString(), false) == true) { return(-2); // customer is not renting } foreach (int bikeId in selectedBikes) { bool rented = GetBikeRentStatus(bikeId, false); // one of bikes is rented if (rented == true) { return(-bikeId); } } SQLDebugging.Log("Before sleep"); System.Threading.Thread.Sleep(delay); // add rental table entry int rowsModified = dataTier.ExecuteActionQuery(string.Format(@" INSERT INTO Rental (CID, StartTime, ExpDuration, NumBikes) VALUES ({0}, GETDATE(), {1}, {2}); ", cid, expDur, selectedBikes.Count)); // insert failed if (rowsModified != 1) { HandleInsertFailure(tx, "RentBikes()"); return(-1); } rid = GetLastRentalId(false); if (rid == -1) { tx.Rollback(); dataTier.CloseConnection(); MessageBox.Show("RentBikes(): Retrieving RID failed"); return(-1); } // update bike table and add rental details foreach (int bid in selectedBikes) { rowsModified = dataTier.ExecuteActionQuery(string.Format(@" INSERT INTO RentalDetail(RID, BID) VALUES ({0}, {1}); ", rid, bid)); if (rowsModified != 1) { HandleInsertFailure(tx, "RentBikes():"); return(-1); } rowsModified = dataTier.ExecuteActionQuery(string.Format(@" UPDATE Bike SET Rented = 1 WHERE BID = {0}; ", bid)); if (rowsModified != 1) { tx.Rollback(); dataTier.CloseConnection(); MessageBox.Show("RentBikes(): UPDATE failed"); return(-1); } } tx.Commit(); retries = 4; } catch (SqlException exc) { // deadlock if (exc.Number == 1205) { retries++; } } catch (Exception exc) { MessageBox.Show("Error in RentBikes(): " + exc.Message); } finally { dataTier.CloseConnection(); } } return(rid); }