//
// 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);
}
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);
}
}
