Ejemplo n.º 1
0
        //
        // 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;
 }
Ejemplo n.º 3
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
Ejemplo n.º 4
0
        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
Ejemplo n.º 5
0
        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());
                }
            }
        }
Ejemplo n.º 6
0
        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
Ejemplo n.º 7
0
        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());
        }
Ejemplo n.º 8
0
        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);
 }
Ejemplo n.º 10
0
        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);
        }
Ejemplo n.º 12
0
 public BusinessLogic(string dataFile)
 {
     this._DBFile = dataFile;
     dataTier     = new DataAccessTier.Data(this._DBFile);
 }
Ejemplo n.º 13
0
 public BusinessLogic(string dataFile)
 {
     this._DBFile = dataFile;
     dataTier = new DataAccessTier.Data(this._DBFile);
 }
Ejemplo n.º 14
0
        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
Ejemplo n.º 15
0
        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);
            }
        }