// // 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