//
// 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
{
// open the connection and setup transaction
dataTier.openConnection();
tx = dataTier.GetDbConnection().BeginTransaction(IsolationLevel.Serializable);
dataTier.setCmdTransaction(tx);
if (GetCustRentStatus(cid.ToString(), false) == true)
{
return(-2); // customer is renting
}
// make sure every selected bike is available
foreach (int bikeId in selectedBikes)
{
bool rented = GetBikeRentStatus(bikeId, false);
// one of bikes is rented
if (rented == true)
{
tx.Rollback();
return(-bikeId);
}
}
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);
}
static void Main(string[] args)
{
Console.WriteLine();
Console.WriteLine("** Create Database Console App **");
Console.WriteLine();
string baseDatabaseName = "Coursemo";
string sql;
try
{
//
// 1. Make a copy of empty MDF file to get us started:
//
Console.WriteLine("Copying empty database to {0}.mdf and {0}_log.ldf...", baseDatabaseName);
CopyEmptyFile("__EmptyDB", baseDatabaseName);
Console.WriteLine();
//
// 2. Now let's make sure we can connect to SQL Server on local machine:
//
DataAccessTier.Data data = new DataAccessTier.Data(baseDatabaseName + ".mdf");
Console.Write("Testing access to database: ");
if (data.TestConnection())
{
Console.WriteLine("success");
}
else
{
Console.WriteLine("failure?!");
}
Console.WriteLine();
//
// 3. Create tables by reading from .sql file and executing DDL queries:
//
Console.WriteLine("Creating tables by executing {0}.sql file...", baseDatabaseName);
string[] lines = System.IO.File.ReadAllLines(baseDatabaseName + ".sql");
sql = "";
for (int i = 0; i < lines.Length; ++i)
{
string next = lines[i];
if (next.Trim() == "") // empty line, ignore...
{
}
else if (next.Contains(";")) // we have found the end of the query:
{
sql = sql + next + System.Environment.NewLine;
Console.WriteLine("** Executing '{0}'...", sql);
data.ExecuteActionQuery(sql);
sql = ""; // reset:
}
else // add to existing query:
{
sql = sql + next + System.Environment.NewLine;
}
}
Console.WriteLine();
//
// 4. Insert data by parsing data from .csv files:
//
Console.WriteLine("Inserting data...");
InsertStudents(data);
//InsertBikes(data);
//InsertCustomers(data);
Console.WriteLine();
//
// Done
//
}
catch (Exception ex)
{
Console.WriteLine("**Exception: '{0}'", ex.Message);
}
Console.WriteLine();
Console.WriteLine("** Done **");
Console.WriteLine();
}//Main
//
// 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);
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);
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);
}
static void Main(string[] args)
{
Console.WriteLine();
Console.WriteLine("** Create Database Console App **");
Console.WriteLine();
string baseDatabaseName = "DDL";
string sql;
try
{
//
// 1. Make a copy of empty MDF file to get us started:
//
Console.WriteLine("Copying empty database to {0}.mdf and {0}_log.ldf...", baseDatabaseName);
CopyEmptyFile("__EmptyDB", baseDatabaseName);
Console.WriteLine();
//
// 2. Now let's make sure we can connect to SQL Server on local machine:
//
DataAccessTier.Data data = new DataAccessTier.Data(baseDatabaseName + ".mdf");
Console.Write("Testing access to database: ");
if (data.TestConnection())
{
Console.WriteLine("success");
}
else
{
Console.WriteLine("failure?!");
}
Console.WriteLine();
//
// 3. Create tables by reading from .sql file and executing DDL queries:
//
Console.WriteLine("Creating tables by executing {0}.sql file...", baseDatabaseName);
string[] lines = System.IO.File.ReadAllLines(baseDatabaseName + ".sql");
sql = "";
for (int i = 0; i < lines.Length; ++i)
{
string next = lines[i];
if (next.Trim() == "") // empty line, ignore...
{
}
else if (next.Contains(";")) // we have found the end of the query:
{
sql = sql + next + System.Environment.NewLine;
Console.WriteLine("** Executing '{0}'...", sql.Substring(0, 32));
data.ExecuteActionQuery(sql);
sql = ""; // reset:
}
else // add to existing query:
{
sql = sql + next + System.Environment.NewLine;
}
}
Console.WriteLine();
//
// 4. Insert data by parsing data from .csv files: (WHAT I ACTUALLY DID)
//
Console.WriteLine("Inserting data...");
// NOTE, i DONT HAVE TO INSERT THE PRIMARY KEY VALUES, IGNORE IT.
//
// first parse the courses
using (var file = new System.IO.StreamReader("courses.csv"))
{
while (!file.EndOfStream)
{
string line = file.ReadLine();
string[] values = line.Split(',');
string Dept = values[0];
string CourseNumber = values[1];
string Semester = values[2];
string Year = values[3];
string CRN = values[4];
string Type = values[5];
string Day = values[6];
string Time = values[7];
string Size = values[8];
sql = string.Format(@"Insert Into
COURSE_INFO(Dept,CourseNumber, Semester, Year, CRN, Type, Day, Time, Size, Enrolled)
Values('{0}',{1}, '{2}', {3}, '{4}', '{5}', '{6}', '{7}', {8}, 0);", Dept, CourseNumber, Semester, Year, CRN, Type, Day, Time, Size);
// letting the database handle CID
data.ExecuteActionQuery(sql);
}
}
// next the students
using (var file = new System.IO.StreamReader("students.csv"))
{
while (!file.EndOfStream)
{
string line = file.ReadLine();
string[] values = line.Split(',');
string LAST = values[0];
string FIRST = values[1];
string NetID = values[2];
LAST = LAST.Replace("'", @"''");
FIRST = FIRST.Replace("'", @"''");
sql = string.Format(@"INSERT INTO
STUDENT(FirstName,LastName, NetID)
Values('{0}', '{1}', '{2}');", FIRST, LAST, NetID);
data.ExecuteActionQuery(sql);
}
}
Console.WriteLine();
//
// Done
//
}
catch (Exception ex)
{
Console.WriteLine("**Exception: '{0}'", ex.Message);
}
Console.WriteLine();
Console.WriteLine("** Done **");
Console.WriteLine();
}//Main
private void StartRental_Click(object sender, EventArgs e)
{
if (!fileExists(this.Filename.Text))
{
return;
}
DataAccessTier.Data data = new DataAccessTier.Data(this.Filename.Text);
//Get Data from Primary Data table
string primeData = this.PrimeData.Text;
var IDs = this.RentalBox.CheckedItems;
string hours = this.RentalDuration.Text;
//Check if Data exists
if (primeData == "" || IDs.Count == 0 || hours == "")
{
MessageBox.Show("One or more fields are blank...");
return;
}
//Parse the data
double expectedHours = Convert.ToDouble(hours);
string[] name = primeData.Split(',');
List <string> BIDs = new List <string>();
foreach (string id in IDs)
{
BIDs.Add(id.Substring(0, 4));
}
//Check if Data is valid
if (expectedHours <= 0)
{
MessageBox.Show("Please enter an expected rental time greater than 0");
return;
}
foreach (string id in BIDs)
{
if (!bikeExists(Int32.Parse(id)))
{
return;
}
}
foreach (string id in BIDs)
{
if (!bikeAvailable(Int32.Parse(id)))
{
return;
}
}
string custIDSql = string.Format(@"
SELECT CID
FROM Customer
WHERE LastName = '{0}' AND FirstName = '{1}';",
name[0], name[1]);
var custIDOBJ = data.ExecuteScalarQuery(custIDSql);
int custID = Int32.Parse(custIDOBJ.ToString());
string custSql = string.Format(@"
SELECT RentingOut
FROM Customer
WHERE CID = {0};",
custID);
var ds = data.ExecuteScalarQuery(custSql);
if (ds.Equals(true))
{
MessageBox.Show("Customer is currently renting out a bike...");
return;
}
List <string> sql = new List <string>();
//Create the Rental entry and modify the Customer and Bike entries
foreach (string id in BIDs)
{
string bikeUpdate = string.Format(@"
UPDATE Bike
SET RentedOut = 1
WHERE BID = {0};",
Int32.Parse(id));
sql.Add(bikeUpdate);
}
string custUpdate = string.Format(@"
UPDATE Customer
SET RentingOut = 1
WHERE CID = {0};",
custID);
sql.Add(custUpdate);
foreach (string id in BIDs)
{
string createRental = string.Format(@"
INSERT INTO
Rentals(ExpectedHours, _Started, CID, BID)
Values({0},GetDate(),{1},{2});",
hours, custID, Int32.Parse(id));
sql.Add(createRental);
}
data.ExecuteActionQuery(sql.ToArray(), "s");
string getRentalID = string.Format(@"
SELECT TOP 1 RID
FROM Rentals
ORDER BY RID DESC;");
var rentID = data.ExecuteScalarQuery(getRentalID);
int i = Int32.Parse(rentID.ToString());
int c = BIDs.Count();
for (int a = i - c + 1; a <= i; a++)
{
MessageBox.Show("Rental ID: " + a.ToString());
}
}
private void ReturnRental_Click(object sender, EventArgs e)
{
if (!fileExists(this.Filename.Text))
{
return;
}
DataAccessTier.Data data = new DataAccessTier.Data(this.Filename.Text);
string primeData = this.PrimeData.Text;
if (primeData.Contains(" ") || primeData == "")
{
return;
}
string[] name = primeData.Split(',');
string custIDSql = string.Format(@"
SELECT CID
FROM Customer
WHERE LastName = '{0}' AND FirstName = '{1}';",
name[0], name[1]);
var custIDOBJ = data.ExecuteScalarQuery(custIDSql);
int custID = Int32.Parse(custIDOBJ.ToString());
string custSql = string.Format(@"
SELECT RentingOut
FROM Customer
WHERE CID = {0};",
custID);
var d = data.ExecuteScalarQuery(custSql);
if (d.Equals(false))
{
MessageBox.Show("Customer is not currently renting out a bike...");
return;
}
List <string> sql = new List <string>();
string getBID = string.Format(@"
SELECT RID, BID
FROM Rentals WITH (INDEX(CID_Index))
WHERE CID = {0} AND Returned IS NULL;",
custID);
DataSet ds = new DataSet();
ds = data.ExecuteNonScalarQuery(getBID);
string rentalUpdate = string.Format(@"
UPDATE Rentals
SET Returned = GetDate()
WHERE CID = {0};",
custID);
string custUpdate = string.Format(@"
UPDATE Customer
SET RentingOut = 0
WHERE CID = {0};",
custID);
sql.Add(rentalUpdate);
sql.Add(custUpdate);
foreach (DataRow row in ds.Tables[0].Rows)
{
string bikeUpdate = string.Format(@"
UPDATE Bike
SET RentedOut = 0
WHERE BID = {0};",
row["BID"]);
sql.Add(bikeUpdate);
}
data.ExecuteActionQuery(sql.ToArray(), "s");
double total = 0.0;
DataSet money = new DataSet();
foreach (DataRow row in ds.Tables[0].Rows)
{
string bikeUpdate = string.Format(@"
SELECT Bike_Type.Price, Rentals._Started, Rentals.Returned
FROM Rentals WITH (INDEX(BID_Index))
INNER JOIN Bike ON Bike.BID = Rentals.BID
INNER JOIN Bike_Type ON Bike.BTID = Bike_Type.BTID
WHERE RID = {0};",
row["RID"]);
money = data.ExecuteNonScalarQuery(bikeUpdate);
double price = Convert.ToDouble(money.Tables[0].Rows[0]["Price"]);
TimeSpan datetime = Convert.ToDateTime(money.Tables[0].Rows[0]["Returned"]).Subtract(Convert.ToDateTime(money.Tables[0].Rows[0]["_Started"]));
double time = datetime.TotalHours;
total = total + (price * time);
}
decimal sum = Convert.ToDecimal(total);
sum = Decimal.Round(sum, 2);
MessageBox.Show("Rental Cost: $" + sum.ToString());
}
private void button5_Click(object sender, EventArgs e)
{
string selectedBikes = null;
List <Bike> selectedBikeList = new List <Bike>();
decimal totalPrice = 0;
foreach (TreeNode node in this.treeView1.Nodes)
{
foreach (TreeNode sub in node.Nodes)
{
if (sub.Checked)
{
int id = Convert.ToInt32(sub.Text);
Bike b = AllBikeList[id - 1001];
totalPrice += b.HourlyPrice;
selectedBikes += b.BID + ", ";
selectedBikeList.Add(b);
}
}
}
if (selectedBikes == null || string.IsNullOrWhiteSpace(textBox8.Text) || containsLetter(textBox8.Text))
{
MessageBox.Show("Please make sure that you enter a numerical value in the expecteed time field!");
}
else
{
selectedBikes = selectedBikes.Remove(selectedBikes.Length - 2);
DialogResult result = MessageBox.Show(string.Format("Are you sure you would like to rent bikes " + selectedBikes + " at an estimated price of ${0:#.00}?", Convert.ToDecimal(totalPrice) * Convert.ToDecimal(textBox8.Text)), "ATTENTION!", MessageBoxButtons.YesNo);
if (result == DialogResult.Yes)
{
Customer c = CustomerList[this.listBox3.SelectedIndex];
ConnectionInfo = String.Format(@"Data Source=(LocalDB)\MSSQLLocalDB;
AttachDbFilename=|DataDirectory|\{0};Integrated Security=True;",
filename);
db = new SqlConnection(ConnectionInfo);
db.Open();
foreach (Bike b in selectedBikeList)
{
string sql = string.Format(@"INSERT INTO
Rentals(CID,BID,StartTime,ExpDuration)
Values((SELECT CID from Customers WHERE CID = '{0}'),
(SELECT BID from Bikes WHERE BID = '{1}'),
'{2}',
'{3}');
UPDATE Bikes
Set Rented = 1
Where BID = '{1}';
", c.CID, b.BID, DateTime.Now, Convert.ToDecimal(textBox8.Text));;
//MessageBox.Show(sql);
data.ExecuteActionQuery(sql);
}
db.Close();
MessageBox.Show("success");
treeView1.Nodes.Clear();
treeView1.Refresh();
textBox8.Clear();
textBox8.Refresh();
}
else
{
}
}
}
static void Main(string[] args)
{
Console.WriteLine();
Console.WriteLine("** Create Database Console App **");
Console.WriteLine();
string baseDatabaseName = "BikeHike";
string sql;
try
{
//
// 1. Make a copy of empty MDF file to get us started:
//
Console.WriteLine("Copying empty database to {0}.mdf and {0}_log.ldf...", baseDatabaseName);
CopyEmptyFile("__EmptyDB", baseDatabaseName);
Console.WriteLine();
//
// 2. Now let's make sure we can connect to SQL Server on local machine:
//
DataAccessTier.Data data = new DataAccessTier.Data(baseDatabaseName + ".mdf");
Console.Write("Testing access to database: ");
if (data.TestConnection())
{
Console.WriteLine("success");
}
else
{
Console.WriteLine("failure?!");
}
Console.WriteLine();
//
// 3. Create tables by reading from .sql file and executing DDL queries:
//
Console.WriteLine("Creating tables by executing {0}.sql file...", baseDatabaseName);
string[] lines = System.IO.File.ReadAllLines(baseDatabaseName + ".sql");
sql = "";
for (int i = 0; i < lines.Length; ++i)
{
string next = lines[i];
if (next.Trim() == "") // empty line, ignore...
{
}
else if (next.Contains(";")) // we have found the end of the query:
{
sql = sql + next + System.Environment.NewLine;
Console.WriteLine("** Executing '{0}'...", sql.Substring(0, 32));
data.ExecuteActionQuery(sql);
sql = ""; // reset:
}
else // add to existing query:
{
sql = sql + next + System.Environment.NewLine;
}
}
Console.WriteLine();
//
// 4. Insert data by parsing data from .csv files:
//
Console.WriteLine("Inserting data...");
//
// TODO...
//
//Console.WriteLine("**TODO**");
//
//parse biketypes.csv
Console.WriteLine("Inserting BikeTypes...");
using (var file = new System.IO.StreamReader("BikeTypes.csv"))
{
while (!file.EndOfStream)
{
string line = file.ReadLine();
string[] values = line.Split(',');
string desc = values[1];
double price = Convert.ToDouble(values[2]);
string BikeTypesSQL = string.Format(@"
Insert Into
Bike_Type(_Description,Price)
Values('{0}',{1});
",
desc, price);
data.ExecuteActionQuery(BikeTypesSQL);
}
}
//parse customers.csv
Console.WriteLine("Inserting Customers...");
using (var file = new System.IO.StreamReader("Customers.csv"))
{
while (!file.EndOfStream)
{
string line = file.ReadLine();
string[] values = line.Split(',');
string first = values[1];
string last = values[2];
string email = values[3];
string CustomerSQL = string.Format(@"
Insert Into
Customer(FirstName,LastName,Email)
Values('{0}','{1}','{2}');
",
first, last, email);
data.ExecuteActionQuery(CustomerSQL);
}
}
//parse bikes.csv
Console.WriteLine("Inserting Bikes...");
using (var file = new System.IO.StreamReader("Bikes.csv"))
{
while (!file.EndOfStream)
{
string line = file.ReadLine();
string[] values = line.Split(',');
int typeid = Convert.ToInt32(values[1]);
string year = values[2];
string BikesSQL = string.Format(@"
Insert Into
Bike(BTID,YearDeployed,RentedOut)
Values({0},'{1}',0);
",
typeid, year);
data.ExecuteActionQuery(BikesSQL);
}
}
Console.WriteLine();
//
// Done
//
}
catch (Exception ex)
{
Console.WriteLine("**Exception: '{0}'", ex.Message);
}
Console.WriteLine();
Console.WriteLine("** Done **");
Console.WriteLine();
}//Main
