//
// 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);
}
