//
// 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);
}
//
// 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;
while (retries < 3)
{
try
{
dataTier.openConnection();
tx = dataTier.GetDbConnection().BeginTransaction(IsolationLevel.Serializable);
dataTier.setCmdTransaction(tx);
// 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)
{
if (exc.Number == 1205)
retries++;
}
catch (Exception exc)
{
MessageBox.Show("Error in RentBikes(): " + exc.Message);
}
finally
{
dataTier.closeConnection();
}
}
return rid;
}
