// // GetCustomers(): // // Returns all the customers from Db as a read only list. // public IReadOnlyList <Customer> GetCustomers() { List <Customer> customers = new List <Customer>(); try { // query DataSet ds = dataTier.ExecuteNonScalarQuery(string.Format(@" SELECT * FROM Customer 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) { throw new ApplicationException(string.Format ("Error in Business.GetCustomers(): '{0}'", e.Message)); } return(customers); }
// // List all the bikes: // private void cmdLoadBikes_Click(object sender, EventArgs e) { this.lstBikes.Items.Clear(); ClearBikeInfo(); // ADDED INDEX BikeTypes_PricePerHour FOR FASTER EXECUTION string sql = string.Format(@" Select BID, Year, Description, PricePerHour From Bikes Inner Join BikeTypes WITH (INDEX(BikeTypes_PricePerHour)) On Bikes.TID = BikeTypes.TID Order by BID; "); //MessageBox.Show(sql); DataAccessTier.Data datatier = new DataAccessTier.Data(dbfilename); try { DataSet ds = datatier.ExecuteNonScalarQuery(sql); foreach (DataRow row in ds.Tables["TABLE"].Rows) { Bike b = new Bike( Convert.ToInt32(row["BID"]), Convert.ToInt32(row["Year"]), row["Description"].ToString(), Convert.ToDecimal(row["PricePerHour"])); this.lstBikes.Items.Add(b); } } catch (Exception ex) { MessageBox.Show(ex.Message); } }
// // GetUser // // Retrieves User from the database // public StockUser GetUser(string userName, string password) { string sql = string.Format(Records.LoginVerificationSQL, userName, password); DataSet ds = datatier.ExecuteNonScalarQuery(sql); DataTable dt = ds.Tables["UserDetails"]; int userID = 0; string fName = null; string lName = null; string email = null; double funds = 0.0; foreach (DataRow row in dt.Rows) { userID = Convert.ToInt32(row["UserID"].ToString()); fName = row["FirstName"].ToString(); lName = row["LastName"].ToString(); email = row["Email"].ToString(); funds = Convert.ToDouble(row["FUNDS"].ToString()); } return(new StockUser(userID, fName, lName, email, funds)); }
// //Lines list // public List <String> getLines(string stopName) { List <String> lines = new List <string>(); DataAccessTier.Data dataTier = new DataAccessTier.Data(_DBFile); //query to get lines... string sql = string.Format(@"Select Color from Lines Inner Join StopDetails On StopDetails.LineID = Lines.LineID Inner Join Stops On Stops.StopID = StopDetails.StopID Where Stops.Name = '{0}'", stopName); DataSet ds = dataTier.ExecuteNonScalarQuery(sql); foreach (DataRow row in ds.Tables["TABLE"].Rows) { string ld = Convert.ToString(row["Color"]); lines.Add(ld); } return(lines); }
/// /// <summary> /// Returns the CTA Stops associated with a given station, /// ordered by name. /// </summary> /// <returns>Read-only list of CTAStop objects</returns> /// public IReadOnlyList <CTAStop> GetStops(int stationID) { List <CTAStop> stops = new List <CTAStop>(); DataSet result; try { // // TODO! // DataAccessTier.Data dataTier = new DataAccessTier.Data(_DBFile); string sql = string.Format(@"SELECT * FROM Stops WHERE Stops.StationID = '{0}'; ", stationID); result = dataTier.ExecuteNonScalarQuery(sql); foreach (DataRow row in result.Tables["TABLE"].Rows) { int Stationid = Convert.ToInt32(row["StationID"]); int Stopid = Convert.ToInt32(row["StopID"]); string name = string.Format("{0}", Convert.ToString(row["Name"])); string direction = string.Format("{0}", Convert.ToString(row["Direction"])); bool ada = Convert.ToBoolean(row["ADA"]); double lat = Convert.ToDouble(row["Latitude"]); double lon = Convert.ToDouble(row["Longitude"]); var s = new BusinessTier.CTAStop(Stopid, name, Stationid, direction, ada, lat, lon); stops.Add(s); } } catch (Exception ex) { string msg = string.Format("Error in Business.GetStops: '{0}'", ex.Message); throw new ApplicationException(msg); } return(stops); }
// // GetNamedUser: // // Retrieves User object based on USER NAME; returns null if user is not // found. // // NOTE: there are "named" users from the Users table, and anonymous users // that only exist in the Reviews table. This function only looks up "named" // users from the Users table. // public User GetNamedUser(string UserName) { // Handle special character ' UserName = UserName.Replace("'", "''"); // SQL string string SQL = string.Format(@" SELECT UserID, Occupation FROM Users WHERE UserName = '******'"); // Execute SQL DataSet ds = dataTier.ExecuteNonScalarQuery(SQL); DataTable dt = ds.Tables["TABLE"]; // Create user object UserName = UserName.Replace("''", "'"); int UserID = Convert.ToInt32(dt.Rows[0]["UserID"]); string Occupation = Convert.ToString(dt.Rows[0]["Occupation"]); User u = new User(UserID, UserName, Occupation); // return User return(u); }
// // GetNamedUser: // // Retrieves User object based on USER NAME; returns null if user is not // found. // // NOTE: there are "named" users from the Users table, and anonymous users // that only exist in the Reviews table. This function only looks up "named" // users from the Users table. // public User GetNamedUser(string UserName) { UserName = UserName.Replace("'", "''"); string sql = String.Format(@"SELECT UserID, Occupation from Users where UserName = '******'", UserName); var user = dataTier.ExecuteNonScalarQuery(sql); User users = null; if (user != null) { foreach (DataRow row in user.Tables["TABLE"].Rows) { users = new User(Convert.ToInt32(row["UserID"]), UserName, Convert.ToString(row["Occupation"])); } return(users); } else { return(null); } }
/// /// <summary> /// Returns all the CTA Stations, ordered by name. /// </summary> /// <returns>Read-only list of CTAStation objects</returns> /// public IReadOnlyList <CTAStation> GetStations() { List <CTAStation> stations = new List <CTAStation>(); try { string sql = "Select StationID , Name from Stations order by Name asc "; DataSet result = dataTier.ExecuteNonScalarQuery(sql); foreach (DataRow row in result.Tables["TABLE"].Rows) { stations.Add(new CTAStation(Convert.ToInt32(row["StationID"]), Convert.ToString(row["Name"]))); } } catch (Exception ex) { string msg = string.Format("Error in Business.GetStations: '{0}'", ex.Message); throw new ApplicationException(msg); } return(stations); }
} // done public Reviews GetReviews(int MovieID) { Reviews reviews = new Reviews(); string sql = string.Format(@"SELECT UserID, Rating FROM Reviews WHERE MovieID={0} ORDER BY Rating Desc, UserID ASC;", MovieID); DataSet ds = datatier.ExecuteNonScalarQuery(sql); DataTable dt = ds.Tables["TABLE"]; if (dt.Rows.Count == 0) { return(null); } else { foreach (DataRow row in dt.Rows) { reviews.Add(new Review(Convert.ToInt32(row["UserID"]), MovieID, Convert.ToInt32(row["Rating"]))); } return(reviews); } } //done
// // GetUser: // // Retrieves User object based on USER ID; returns null if user is not // found. // // NOTE: if the user exists in the Users table, then a meaningful name and // occupation are returned in the User object. If the user does not exist // in the Users table, then the user id has to be looked up in the Reviews // table to see if he/she has submitted 1 or more reviews as an "anonymous" // user. If the id is found in the Reviews table, then the user is an // "anonymous" user, so a User object with name = "<UserID>" and no occupation // ("") is returned. In other words, name = the user’s id surrounded by < >. // public User GetUser(int UserID) { try { string sql = string.Format(@"Select UserName, Occupation from Users where UserID = '{0}';", UserID); DataSet ds = dataTier.ExecuteNonScalarQuery(sql); if (ds.Tables["TABLE"].Rows.Count == 0) { return(null); // check if the user exists } DataRow row = ds.Tables["TABLE"].Rows[0]; User newObj = new User(UserID, Convert.ToString(row["UserName"]), Convert.ToString(row["Occupation"])); return(newObj); } catch (Exception ex) { string msg = string.Format("Error in Business.GetUser: '******'", ex.Message); throw new ApplicationException(msg); } }
// // User has clicked button indicating that selected customer is returning // from their rental. // private void cmdReturn_Click(object sender, EventArgs e) { string version = "MSSQLLocalDB"; // for VS 2015 and newer: string DBFile = "BikeHike.mdf"; string DBConnectionInfo = String.Format(@"Data Source=(LocalDB)\{0};AttachDbFilename=|DataDirectory|\{1};Integrated Security=True;", version, DBFile); // Set up connection SqlConnection db; db = new SqlConnection(DBConnectionInfo); db.Open(); // Initialize transaction (serializable) SqlTransaction tx = db.BeginTransaction(IsolationLevel.Serializable); // is a customer selected? Customer c = (Customer)this.lstCustomers.SelectedItem; if (c == null) { MessageBox.Show("Please select a customer..."); return; } int RID, N; DateTime start; double expectedDuration; if (!c.OnRental(out RID, out start, out expectedDuration, out N)) { MessageBox.Show("This customer has no bikes to return..."); tx.Rollback(); db.Close(); return; } // // Compute duration of rental: // TimeSpan duration = DateTime.Now.Subtract(start); double hours = (duration.Days * 24) + duration.Hours + (duration.Minutes / 60.0) + (duration.Seconds / 3600.00); // Used for deadlock int retries = 0; //MessageBox.Show("duration: " + hours.ToString()); while (retries < 3) { // // now lookup the bikes involved in rental, and compute // total price: // try { // ADDED INDEX BikeTypes_PricePerHour FOR FASTER EXECUTION string sql = string.Format(@" SELECT Bikes.BID, PricePerHour FROM RentalDetails INNER JOIN Bikes ON RentalDetails.BID = Bikes.BID INNER JOIN BikeTypes WITH (INDEX(BikeTypes_PricePerHour)) ON Bikes.TID = BikeTypes.TID WHERE RID = {0}; ", RID); //MessageBox.Show(sql); DataAccessTier.Data datatier = new DataAccessTier.Data(dbfilename); double totalprice = 0.0; List <int> bids = new List <int>(); DataSet ds = datatier.ExecuteNonScalarQuery(sql); foreach (DataRow row in ds.Tables["Table"].Rows) { bids.Add(Convert.ToInt32(row["BID"])); totalprice += (hours * Convert.ToDouble(row["PricePerHour"])); } // // we have what we need to update the rental record: // sql = string.Format(@" UPDATE Rentals SET ActDuration = {0}, TotalPrice = {1} WHERE RID = {2}; ", hours, totalprice, RID); //MessageBox.Show(sql); SqlCommand cmd = new SqlCommand(); cmd.Connection = db; cmd.CommandText = sql; cmd.Transaction = tx; object result = cmd.ExecuteNonQuery(); // Rollback if (System.Int32.Parse(result.ToString()) != 1) { tx.Rollback(); break; } // // Finally, update each of the bikes' Rented flag so // they can be rented again: // bool first = true; sql = ""; foreach (int bid in bids) { if (first) //create base query: { first = false; sql = string.Format(@" UPDATE Bikes SET Rented = 0 WHERE BID = {0} ", bid); } else // append to base query for next update: { sql = string.Format(@" {0} OR BID = {1} ", sql, bid); } }//foreach sql = sql + ";"; cmd.CommandText = sql; cmd.Transaction = tx; object result2 = cmd.ExecuteNonQuery(); // Delay int timeInMS; if (System.Int32.TryParse(this.textBox1.Text, out timeInMS) == true) { ; } else { timeInMS = 0; // no delay: } System.Threading.Thread.Sleep(timeInMS); // Rollback if (bids.Count != N) // database correctness check: { MessageBox.Show("**Sanity check: # of bikes in rentals table doesn't agree with # of bikes in details table?!"); tx.Rollback(); break; } // Rollback if (System.Int32.Parse(result2.ToString()) != N) { tx.Rollback(); break; } // // Success, rental updated and all bikes returned: // string msg = string.Format("Return complete, total cost ${0:0.00}", totalprice); MessageBox.Show(msg); tx.Commit(); // // Update selected customer since no longer renting: // int index = this.lstCustomers.SelectedIndex; if (index >= 0) { this.lstCustomers.SelectedIndex = -1; this.lstCustomers.SelectedIndex = index; } // // Likewise, update selected bike (if any): // index = this.lstBikes.SelectedIndex; if (index >= 0) { this.lstBikes.SelectedIndex = -1; this.lstBikes.SelectedIndex = index; } // // Finally, update the list of available bikes: // cmdForRent_Click(sender, e); break; } // Deadlock catch (SqlException ex) { if (ex.Number == 1205) { retries++; } else { break; } } // Rollback catch (Exception ex) { tx.Rollback(); MessageBox.Show(ex.Message); break; } finally { db.Close(); } } }
public bool OnRental(out DateTime dueback) { dueback = DateTime.Now; // // bike is on rental based on Rented attribute: // string sql = string.Format(@" Select Rented From Bikes Where BID = {0}; ", BID); DataAccessTier.Data datatier = new DataAccessTier.Data("BikeHike.mdf"); try { object result = datatier.ExecuteScalarQuery(sql); if (result == null) // something is wrong, bike not found... { System.Diagnostics.Debug.WriteLine("**Internal error: bike not found?!"); return(false); } int rented = Convert.ToInt32(result); if (rented == 0) // not rented: { return(false); } // // Since the bike is rented, display expected return date and time... // We have the Bike ID, so we have to join the rental details with the // Rentals table to get the rental info --- in particular start time and // expected duration. Note that this bike has probably been rented // before, so to find the correct rental, we want the MOST RECENT rental // for this bike. So we order the rentals by details ID in descending // order, and take the first record (top 1). // sql = string.Format(@" SELECT Top 1 StartTime, ExpDuration FROM Rentals INNER JOIN RentalDetails ON Rentals.RID = RentalDetails.RID WHERE BID = {0} ORDER BY RDID DESC; ", BID); DataSet ds = datatier.ExecuteNonScalarQuery(sql); if (ds.Tables["TABLE"].Rows.Count == 0) // not found?! { System.Diagnostics.Debug.WriteLine("**Internal error, rental record not found?!"); return(false); } DataRow row = ds.Tables["TABLE"].Rows[0]; DateTime start = Convert.ToDateTime(row["StartTime"]); double duration = Convert.ToDouble(row["ExpDuration"]); dueback = start.AddHours(duration); return(true); // on rental: } catch (Exception ex) { System.Diagnostics.Debug.WriteLine(ex.Message); return(false); } }
// // GetUser: // // Retrieves User object based on USER ID; returns null if user is not // found. // // NOTE: if the user exists in the Users table, then a meaningful name and // occupation are returned in the User object. If the user does not exist // in the Users table, then the user id has to be looked up in the Reviews // table to see if he/she has submitted 1 or more reviews as an "anonymous" // user. If the id is found in the Reviews table, then the user is an // "anonymous" user, so a User object with name = "<UserID>" and no occupation // ("") is returned. In other words, name = the user’s id surrounded by < >. // public User GetUser(int UserID) { string sql = string.Format("Select * From Users Where UserID = {0}", UserID); DataSet userData = dataTier.ExecuteNonScalarQuery(sql); DataTable dt = userData.Tables["TABLE"]; if (dt.Rows.Count == 0) // not in users table { string sql2 = string.Format("Select * From Reviews Where UserID = {0}", UserID); DataSet userData2 = dataTier.ExecuteNonScalarQuery(sql2); DataTable dt2 = userData2.Tables["Table"]; if (dt2.Rows.Count == 0) // also not int reviews table { return(null); } else { string userName = string.Format("<anonymous>"); User s = new User(UserID, userName, null); return(s); } } else // in users table { foreach (DataRow row in dt.Rows) { string userName = row["UserName"].ToString(); string occupation = row["Occupation"].ToString(); User s = new User(UserID, userName, occupation); return(s); } } return(null); }
private void PrimeData_SelectedIndexChanged(object sender, EventArgs e) { if (!fileExists(this.Filename.Text)) { return; } DataAccessTier.Data data = new DataAccessTier.Data(this.Filename.Text); string listData = this.PrimeData.Text; //Check to see whether Primary Data Table contains Bikes or Customers or is empty if (listData == "") { return; } if (listData.Contains("BID: ")) { //Data is a Bike so get Bike data from Bike table SecData.Items.Clear(); listData = listData.Substring(5); string dispData = string.Format(@" SELECT Bike_Type._Description, Bike.YearDeployed, Bike_Type.Price, Bike.RentedOut FROM Bike INNER JOIN Bike_Type ON Bike_Type.BTID = Bike.BTID WHERE BID = {0};", listData); DataSet ds = new DataSet(); ds = data.ExecuteNonScalarQuery(dispData); SecData.Items.Add("Type: " + ds.Tables[0].Rows[0]["_Description"].ToString()); SecData.Items.Add("Year: " + ds.Tables[0].Rows[0]["YearDeployed"].ToString()); SecData.Items.Add("Price: $" + ds.Tables[0].Rows[0]["Price"].ToString()); if (ds.Tables[0].Rows[0]["RentedOut"].ToString() == "False") { SecData.Items.Add("Status: Available"); } else { SecData.Items.Add("Status: Unavailable"); string rentData = string.Format(@" SELECT _Started, ExpectedHours FROM Rentals WHERE BID = {0} AND Returned IS NULL;", listData); DataSet rds = new DataSet(); rds = data.ExecuteNonScalarQuery(rentData); DateTime start = Convert.ToDateTime(rds.Tables[0].Rows[0]["_Started"]); DateTime end = start.AddHours(Convert.ToDouble(rds.Tables[0].Rows[0]["ExpectedHours"])); SecData.Items.Add("Start: " + start.ToString()); SecData.Items.Add("Expected End: " + end.ToString()); } } else if (listData.Contains(" ")) { return; } else { //Data is a Customer so get Customer data from Customer table SecData.Items.Clear(); string[] names = listData.Split(','); string last = names[0]; string first = names[1]; string dispData = string.Format(@" SELECT CID, Email, RentingOut FROM Customer WHERE FirstName = '{0}' AND LastName = '{1}';", first, last); DataSet ds = new DataSet(); ds = data.ExecuteNonScalarQuery(dispData); int CID = Int32.Parse(ds.Tables[0].Rows[0]["CID"].ToString()); SecData.Items.Add("ID: " + ds.Tables[0].Rows[0]["CID"].ToString()); SecData.Items.Add("Email: " + ds.Tables[0].Rows[0]["Email"].ToString()); if (ds.Tables[0].Rows[0]["RentingOut"].ToString() == "False") { SecData.Items.Add("Status: Available"); } else { SecData.Items.Add("Status: Unavailable"); string rentData = string.Format(@" SELECT RID, _Started, ExpectedHours FROM Rentals WHERE CID = {0} AND Returned IS NULL;", CID); DataSet rds = new DataSet(); rds = data.ExecuteNonScalarQuery(rentData); DateTime start = Convert.ToDateTime(rds.Tables[0].Rows[0]["_Started"]); DateTime end = start.AddHours(Convert.ToDouble(rds.Tables[0].Rows[0]["ExpectedHours"])); SecData.Items.Add("Bikes Rented: " + rds.Tables[0].Rows.Count.ToString()); SecData.Items.Add("Start: " + start.ToString()); SecData.Items.Add("Expected End: " + end.ToString()); } } }
/// /// <summary> /// Returns overall stats about crimes in Chicago. /// </summary> /// <returns>CrimeStats object</returns> /// public CrimeStats GetOverallCrimeStats() { CrimeStats cs; Business bus = new Business("C:\\Users\\Jacky\\Downloads\\CrimeDB.mdf"); string sql = @" Select Min(Year) As MinYear, Max(Year) As MaxYear, Count(*) As Total From Crimes; "; DataSet result = dataTier.ExecuteNonScalarQuery(sql); DataRow row = result.Tables["TABLE"].Rows[0]; int minYear = System.Convert.ToInt32(row["MinYear"]); int maxYear = System.Convert.ToInt32(row["MaxYear"]); long total = System.Convert.ToInt64(row["Total"]); // // TODO! // cs = new CrimeStats(total, minYear, maxYear); return(cs); }
public bool validateUser(User usr) { if (usr.userName == string.Empty || usr.password == string.Empty) { throw new System.ArgumentException("UserName or password can't be empty"); } else { string sql = string.Format(@" SELECT ID, FirstName, LastName, Email, Balance FROM Users WHERE UserName='******' AND UserPassword='******'; ", usr.userName, usr.password); DataSet ds = dataTier.ExecuteNonScalarQuery(sql); if (ds.Tables[0].Rows.Count == 0) { return(false); } DataTable dt = ds.Tables["TABLE"]; foreach (DataRow r in dt.Rows) { usr.id = System.Convert.ToInt32(r["ID"]); usr.firstName = System.Convert.ToString(r["FirstName"]); usr.lastName = System.Convert.ToString(r["LastName"]); usr.email = System.Convert.ToString(r["Email"]); usr.balance = System.Convert.ToDouble(r["Balance"]); return(true); } } return(false); }
// // GetUser: // // Retrieves User object based on USER ID; returns null if user is not // found. // // NOTE: if the user exists in the Users table, then a meaningful name and // occupation are returned in the User object. If the user does not exist // in the Users table, then the user id has to be looked up in the Reviews // table to see if he/she has submitted 1 or more reviews as an "anonymous" // user. If the id is found in the Reviews table, then the user is an // "anonymous" user, so a User object with name = "<UserID>" and no occupation // ("") is returned. In other words, name = the user’s id surrounded by < >. // public User GetUser(int UserID) { // // TODO! // string sql = string.Format("Select * From Users Where UserID = {0}", UserID); DataSet ds = dataTier.ExecuteNonScalarQuery(sql); //check if the user was found in the user file ... if found cerat new user object and return; if (ds.Tables["TABLE"].Rows.Count != 0) { object ID = ds.Tables["TABLE"].Rows[0]["UserID"]; int uID = Convert.ToInt32(ID); string name = Convert.ToString(ds.Tables["TABLE"].Rows[0]["UserName"]); string Occupation = Convert.ToString(ds.Tables["TABLE"].Rows[0]["Occupation"]); User u = new User(uID, name, Occupation); return(u); } // not found now look in the review... else { sql = string.Format(@"Select UserID From Reviews Where UserID = '{0}' Group by UserID", UserID); object result = dataTier.ExecuteScalarQuery(sql); if (result != null) { string name = string.Format("<{0}>", UserID); User u = new User(UserID, name, "Unknown"); return(u); } } return(null); }
/// /// <summary> /// Returns all the CTA Stations, ordered by name. /// </summary> /// <returns>Read-only list of CTAStation objects</returns> /// public IReadOnlyList <CTAStation> GetStations() { List <CTAStation> stations = new List <CTAStation>(); try { // // TODO! // string sql = string.Format(@" SELECT Name FROM Stations ORDER BY Name ASC;"); DataSet ds = dataTier.ExecuteNonScalarQuery(sql); int stationID = 0; string stationName; foreach (DataRow row in ds.Tables["TABLE"].Rows) { stationName = Convert.ToString(row["Name"]); stations.Add(new CTAStation(stationID, stationName)); } } catch (Exception ex) { string msg = string.Format("Error in Business.GetStations: '{0}'", ex.Message); throw new ApplicationException(msg); } return(stations); }
// // User has clicked button indicating that selected customer is returning // from their rental. // private void cmdReturn_Click(object sender, EventArgs e) { // is a customer selected? Customer c = (Customer)this.lstCustomers.SelectedItem; if (c == null) { MessageBox.Show("Please select a customer..."); return; } int RID, N; DateTime start; double expectedDuration; if (!c.OnRental(out RID, out start, out expectedDuration, out N)) { MessageBox.Show("This customer has no bikes to return..."); return; } // // Compute duration of rental: // TimeSpan duration = DateTime.Now.Subtract(start); double hours = (duration.Days * 24) + duration.Hours + (duration.Minutes / 60.0) + (duration.Seconds / 3600.00); //MessageBox.Show("duration: " + hours.ToString()); // // now lookup the bikes involved in rental, and compute // total price: // try { string sql = string.Format(@" SELECT Bikes.BID, PricePerHour FROM RentalDetails INNER JOIN Bikes ON RentalDetails.BID = Bikes.BID INNER JOIN BikeTypes ON Bikes.TID = BikeTypes.TID WHERE RID = {0}; ", RID); //MessageBox.Show(sql); DataAccessTier.Data datatier = new DataAccessTier.Data(dbfilename); double totalprice = 0.0; List <int> bids = new List <int>(); DataSet ds = datatier.ExecuteNonScalarQuery(sql); foreach (DataRow row in ds.Tables["Table"].Rows) { bids.Add(Convert.ToInt32(row["BID"])); totalprice += (hours * Convert.ToDouble(row["PricePerHour"])); } // // we have what we need to update the rental record: // sql = string.Format(@" UPDATE Rentals SET ActDuration = {0}, TotalPrice = {1} WHERE RID = {2}; ", hours, totalprice, RID); //MessageBox.Show(sql); int rowsModified = datatier.ExecuteActionQuery(sql); if (rowsModified != 1) { MessageBox.Show("**Internal error, update of rental record failed?!"); return; } // // Finally, update each of the bikes' Rented flag so // they can be rented again: // bool first = true; sql = ""; foreach (int bid in bids) { if (first) //create base query: { first = false; sql = string.Format(@" UPDATE Bikes SET Rented = 0 WHERE BID = {0} ", bid); } else // append to base query for next update: { sql = string.Format(@" {0} OR BID = {1} ", sql, bid); } }//foreach sql = sql + ";"; //MessageBox.Show(sql); if (bids.Count != N) // database correctness check: { MessageBox.Show("**Sanity check: # of bikes in rentals table doesn't agree with # of bikes in details table?!"); } rowsModified = datatier.ExecuteActionQuery(sql); if (rowsModified != N) { MessageBox.Show("**Internal error, update of bike record(s) failed?!"); return; } // // Success, rental updated and all bikes returned: // string msg = string.Format("Return complete, total cost ${0:0.00}", totalprice); MessageBox.Show(msg); // // Update selected customer since no longer renting: // int index = this.lstCustomers.SelectedIndex; if (index >= 0) { this.lstCustomers.SelectedIndex = -1; this.lstCustomers.SelectedIndex = index; } // // Likewise, update selected bike (if any): // index = this.lstBikes.SelectedIndex; if (index >= 0) { this.lstBikes.SelectedIndex = -1; this.lstBikes.SelectedIndex = index; } // // Finally, update the list of available bikes: // cmdForRent_Click(sender, e); } catch (Exception ex) { MessageBox.Show(ex.Message); } }
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; } string getBID = string.Format(@" SELECT RID, BID FROM Rentals 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); data.ExecuteActionQuery(rentalUpdate); string custUpdate = string.Format(@" UPDATE Customer SET RentingOut = 0 WHERE CID = {0};", custID); data.ExecuteActionQuery(custUpdate); foreach (DataRow row in ds.Tables[0].Rows) { string bikeUpdate = string.Format(@" UPDATE Bike SET RentedOut = 0 WHERE BID = {0};", row["BID"]); data.ExecuteActionQuery(bikeUpdate); } 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 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()); }