// // Constructor: // public Business(string DatabaseFilename) { _DBFile = DatabaseFilename; dataTier = new DataAccessTier.Data(DatabaseFilename); }
// // Business(): // public Business(string dbFilename) { _DBFile = dbFilename; dataTier = new DataAccessTier.Data(dbFilename); delay = 0; }
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..."); // // 4 a. Inserting data for biketypes // Console.WriteLine("**Insert bike types..."); using (var file = new System.IO.StreamReader("biketypes.csv")) { while (!file.EndOfStream) { string line = file.ReadLine(); string[] values = line.Split(','); string description = values[1]; double priceperhour = Convert.ToDouble(values[2]); string sqlq = string.Format(@" INSERT INTO BikeTypes(Description, PricePerHour) Values('{0}', {1}); ", description, priceperhour); data.ExecuteActionQuery(sqlq); } } // // 4 b. Inserting data for bikes // Console.WriteLine("**Insert bikes..."); using (var file = new System.IO.StreamReader("bikes.csv")) { while (!file.EndOfStream) { string line = file.ReadLine(); string[] values = line.Split(','); int btypeid = Convert.ToInt32(values[1]); int pyear = Convert.ToInt32(values[2]); string sqlq = string.Format(@" INSERT INTO Bikes(TID, Year, Rented) Values({0}, {1}, 0); ", btypeid, pyear); data.ExecuteActionQuery(sqlq); } } // // 4 c. Inserting data for customers // Console.WriteLine("**Insert customers..."); using (var file = new System.IO.StreamReader("customers.csv")) { while (!file.EndOfStream) { string line = file.ReadLine(); string[] values = line.Split(','); string fname = values[1]; string lname = values[2]; string cemail = values[3]; string sqlq = string.Format(@" INSERT INTO Customers(FirstName, LastName, Email) Values('{0}', '{1}', '{2}'); ", fname, lname, cemail); data.ExecuteActionQuery(sqlq); } } // // Done // } catch (Exception ex) { Console.WriteLine("**Exception: '{0}'", ex.Message); } Console.WriteLine(); Console.WriteLine("** Done **"); Console.WriteLine(); }//Main
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
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()); } } }
static void Main(string[] args) { Console.WriteLine(); Console.WriteLine("** Create Database Console App **"); Console.WriteLine(); string baseDatabaseName = "RegisterDB"; 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..."); // Inserting into Students table Console.WriteLine("**Inserting Students**"); using (var file = new System.IO.StreamReader("students.csv")) { while (!file.EndOfStream) { string line = file.ReadLine(); string[] values = line.Split(','); string netid = values[2]; string lastName = values[0]; lastName = lastName.Replace("'", "''"); string firstName = values[1]; firstName = firstName.Replace("'", "''"); string sqlS = string.Format(@" INSERT INTO Students(NetID, LastName, FirstName) Values('{0}', '{1}', '{2}'); ", netid, lastName, firstName); data.ExecuteActionQuery(sqlS); } } Console.WriteLine(); // Inserting into Courses table Console.WriteLine("**Inserting Courses**"); using (var file = new System.IO.StreamReader("courses.csv")) { while (!file.EndOfStream) { string line = file.ReadLine(); string[] values = line.Split(','); int crn = Convert.ToInt32(values[4]); string abbr = values[0]; int cid = Convert.ToInt32(values[1]); string semester = values[2]; int cYear = Convert.ToInt32(values[3]); string cType = values[5]; string cDay = values[6]; string cTime = values[7]; int cSize = Convert.ToInt32(values[8]); string sqlC = string.Format(@" INSERT INTO Courses( CRN, Abbrev, CID, Semester, Term, CType, CDay, CTime, CSize) Values({0}, '{1}', {2}, '{3}', {4}, '{5}', '{6}', '{7}', {8}); ", crn, abbr, cid, semester, cYear, cType, cDay, cTime, cSize); data.ExecuteActionQuery(sqlC); } } Console.WriteLine(); // // Done // } catch (Exception ex) { Console.WriteLine("**Exception: '{0}'", ex.Message); } Console.WriteLine(); Console.WriteLine("** Done **"); Console.WriteLine(); }//Main
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()); }
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; string IDs = this.BikeIDs.Text; string hours = this.RentalDuration.Text; //Check if Data exists if (primeData == "" || IDs == "" || hours == "") { MessageBox.Show("One or more fields are blank..."); return; } //Parse the data double expectedHours = Convert.ToDouble(hours); string[] BIDs = IDs.Split(','); string[] name = primeData.Split(','); //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; } //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)); data.ExecuteActionQuery(bikeUpdate); } string custUpdate = string.Format(@" UPDATE Customer SET RentingOut = 1 WHERE CID = {0};", custID); data.ExecuteActionQuery(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)); data.ExecuteActionQuery(createRental); string getRentalID = string.Format(@" SELECT TOP 1 RID FROM Rentals ORDER BY RID DESC;"); var rentID = data.ExecuteScalarQuery(getRentalID); MessageBox.Show("Rental ID: " + rentID.ToString()); } }
public Business() { datatier = new DataAccessTier.Data(ConnectionString.ConnString); }
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.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..."); // // execute BikeType .csv // using stmt will close file when scope is exited: // //using (var file = new System.IO.StreamReader("biketypes.csv")) //{ // while (!file.EndOfStream) // { // string line = file.ReadLine(); // string[] values = line.Split(','); // int typeid = Convert.ToInt32(values[0]); // string description = values[1]; // double priceperhour = Convert.ToDouble(values[2]); // // // // create query and execute // // // sql = string.Format(@"Insert Into BikeTypes(Price, info) // Values({1},'{2}');", typeid, priceperhour, description); // data.ExecuteActionQuery(sql); // }//while //}//using // // execute student.csv // using (var file = new System.IO.StreamReader("students.csv")) { while (!file.EndOfStream) { string line = file.ReadLine(); string[] values = line.Split(','); string first_name = values[1]; string last_name = values[0]; string email = values[2]; if (last_name.Contains("'")) { last_name = last_name.Replace("'", "\""); Console.WriteLine("Replacing Single Quotes in Last Name"); } if (first_name.Contains("'")) { first_name = first_name.Replace("'", "\""); Console.WriteLine("Replacing Single Qoutes in First Name"); } // // create query and execute // sql = string.Format(@"insert into Students (FirstName, Lastname, NetID) values ('{0}', '{1}', '{2}');", first_name, last_name, email); data.ExecuteActionQuery(sql); } //while } //using // // execute bike.csv // using (var file = new System.IO.StreamReader("courses.csv")) { ArrayList departmets = new ArrayList(); ArrayList semesters = new ArrayList(); ArrayList classtypes = new ArrayList(); while (!file.EndOfStream) { //CS,251,fall,2018,37159,lab,R,1100-1150,8 string line = file.ReadLine(); string[] values = line.Split(','); string cs = values[0]; int classNum = Convert.ToInt32(values[1]); string semester = values[2]; int year = Convert.ToInt32(values[3]); int crn = Convert.ToInt32(values[4]); string lecture = values[5]; string day = values[6]; string time = values[7]; int capacity = Convert.ToInt32(values[8]); //check if contains cs in the table if (!departmets.Contains(cs)) { departmets.Add(cs); sql = string.Format(@"INSERT INTO Departments(Department) VALUES ('{0}')", cs); data.ExecuteActionQuery(sql); } //check if contains semester if (!semesters.Contains(semester)) { semesters.Add(semester); sql = string.Format(@"INSERT INTO Semesters(Semester) VALUES ('{0}')", semester); data.ExecuteActionQuery(sql); } //check if classtype lab lecture etc if (!classtypes.Contains(lecture)) { classtypes.Add(lecture); sql = string.Format(@"INSERT INTO ClassTypes(ClassType) VALUES ('{0}')", lecture); data.ExecuteActionQuery(sql); } int csindex = departmets.IndexOf(cs) + 1; int semesterindex = semesters.IndexOf(semester) + 1; int ctype = classtypes.IndexOf(lecture) + 101; sql = string.Format(@"INSERT INTO Classes(DID, ClassNum, Semester, Years,CRN, CTID, WeekDays, Times, Capcity, CurrentTotal) VALUES ( {0}, -- DID - int {1}, -- ClassNum - int {2}, -- Semester - int {3}, -- Years - int {4}, -- CRN - int {5}, -- CTID - int N'{6}', -- WeekDays - nvarchar(128) N'{7}', -- Times - nvarchar(128) {8} , -- Capcity - int 0 )", csindex, classNum, semesterindex, year, crn, ctype, day, time, capacity); data.ExecuteActionQuery(sql); // // create query and execute // } //while } //using Console.WriteLine(); // // Done // } catch (Exception ex) { Console.WriteLine(" * *Exception: '{0}'", ex.Message); } Console.WriteLine(); Console.WriteLine("** Done **"); Console.WriteLine(); }//Main
// // Constructor: // public Business(string connectionString) { //_DBFile = connectionString; datatier = new DataAccessTier.Data(connectionString); }
public BusinessLogic(string dataFile) { this._DBFile = dataFile; dataTier = new DataAccessTier.Data(this._DBFile); }
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
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); } }