コード例 #1
0
 //
 // TestConnection():
 //
 public bool TestConnection()
 {
     return(dataTier.TestConnection());
 }
コード例 #2
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...");

                //
                //Insering student data to database
                //
                Console.WriteLine("**Insert Students...");

                using (var file = new System.IO.StreamReader("students.csv"))
                {
                    while (!file.EndOfStream)
                    {
                        string   line          = file.ReadLine();
                        string[] values        = line.Split(',');
                        string   lname         = values[0];
                        string   replace_lname = lname.Replace("'", "''");
                        string   fname         = values[1];
                        string   netid         = values[2];

                        string studentSQL = string.Format(@"
INSERT INTO Students(lname, fname, netid) 
Values('{0}', '{1}', '{2}');
",
                                                          replace_lname, fname, netid);

                        data.ExecuteActionQuery(studentSQL);
                        Console.WriteLine(studentSQL);
                    }
                }//end students


                Console.WriteLine("**Insert courses...");

                using (var file = new System.IO.StreamReader("courses.csv"))
                {
                    while (!file.EndOfStream)
                    {
                        string   line    = file.ReadLine();
                        string[] values  = line.Split(',');
                        string   DID     = values[0];
                        int      CID     = Convert.ToInt32(values[1]);
                        string   SID     = values[2];
                        int      Year    = Convert.ToInt32(values[3]);
                        int      CRN     = Convert.ToInt32(values[4]);
                        string   TID     = values[5];
                        string   Day     = values[6];
                        string   Time    = values[7];
                        int      tot_cap = Convert.ToInt32(values[8]);


                        string coursesSQL = string.Format(@"
                                    INSERT INTO Courses(DID, CID, SID, Year_, CRN, TID, Day_, Time_, Cap, Registered)
                                    Values('{0}', {1}, '{2}', {3}, {4}, '{5}', '{6}', '{7}', {8}, 0);
                                    ", DID, CID, SID, Year, CRN, TID, Day, Time, tot_cap);

                        data.ExecuteActionQuery(coursesSQL);
                        Console.WriteLine(coursesSQL);
                    }
                }//end courses



                //
                // Done
                //
            }
            catch (Exception ex)
            {
                Console.WriteLine("**Exception: '{0}'", ex.Message);
            }

            Console.WriteLine();
            Console.WriteLine("** All Data Successfully Entered. Done **");
            Console.WriteLine();
        }//Main
コード例 #3
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();
                InsertCourses();

                Console.WriteLine();

                //
                // Done
                //
            }
            catch (Exception ex)
            {
                Console.WriteLine("**Exception: '{0}'", ex.Message);
            }

            Console.WriteLine();
            Console.WriteLine("** Done **");
            Console.WriteLine();
        }//Main
コード例 #4
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...");

                //
                // DONE
                //

                Console.WriteLine("Inserting Bike Types");
                using (var file = new System.IO.StreamReader("BikeTypes.csv"))
                {
                    while (!file.EndOfStream)
                    {
                        //Parse each line from the given file
                        string   line   = file.ReadLine();
                        string[] values = line.Split(',');

                        int    typeid       = Convert.ToInt32(values[0]);
                        string description  = values[1];
                        double priceperhour = Convert.ToDouble(values[2]);

                        //Build SQL Query for BikeTypes
                        string bikeTypeSQL = string.Format(@"
                                        set identity_insert Bike_Type ON
                                        Insert into Bike_Type(Bike_Type_ID, Description, Price_hour) 
                                        values({0}, '{1}', {2});
                                        ",
                                                           typeid, description, priceperhour);

                        //Execute the SQL query
                        data.ExecuteActionQuery(bikeTypeSQL);

                        //Check to see format
                        Console.WriteLine(bikeTypeSQL);
                    }
                }


                Console.WriteLine("Inserting Bikes");
                using (var file = new System.IO.StreamReader("Bikes.csv"))
                {
                    while (!file.EndOfStream)
                    {
                        string   line   = file.ReadLine();
                        string[] values = line.Split(',');

                        int typeid       = Convert.ToInt32(values[0]);
                        int bikeTypeID   = Convert.ToInt32(values[1]);
                        int year_service = Convert.ToInt32(values[2]);

                        //Build SQL Query for inserting bikes
                        string BikesSQL = string.Format(@"
                                        set identity_insert Bike ON
                                        Insert into Bike(BID, Bike_Type_ID, Year_In_Service)
                                        values({0}, {1}, {2});
                                        ",
                                                        typeid, bikeTypeID, year_service);

                        //Execute the SQL Query for inserting bikes
                        data.ExecuteActionQuery(BikesSQL);

                        //Check to see if correct format added
                        Console.WriteLine(BikesSQL);
                    }
                }

                Console.WriteLine("Inserting Customers");
                using (var file = new System.IO.StreamReader("Customers.csv"))
                {
                    while (!file.EndOfStream)
                    {
                        string   line   = file.ReadLine();
                        string[] values = line.Split(',');

                        long   typeid    = Convert.ToInt64(values[0]);
                        string firstName = values[1];
                        string lastName  = values[2];
                        string email     = values[3];

                        string customerSQL = string.Format(@"
                                             set identity_insert Customer ON
                                             insert into Customer( CID, First_name, Last_name, email_address)
                                             values({0}, '{1}', '{2}', '{3}'); 
                                             ", typeid, firstName, lastName, email);

                        //Execute the SQL Query
                        data.ExecuteActionQuery(customerSQL);

                        //Write to Screen to check formatting
                        Console.WriteLine(customerSQL);
                    }
                }

                Console.WriteLine();

                //
                // Done
                //
            }
            catch (Exception ex)
            {
                Console.WriteLine("**Exception: '{0}'", ex.Message);
            }

            Console.WriteLine();
            Console.WriteLine("** Project 1 Complete! Now exiting... **");
            Console.WriteLine();
        }//Main
コード例 #5
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...");

                //
                // TODO...
                //
                //Console.WriteLine("**TODO**");
                //
                //parse biketypes.csv
                Console.WriteLine("Inserting BikeTypes...");
                using (var file = new System.IO.StreamReader("BikeTypes.csv"))
                {
                    while (!file.EndOfStream)
                    {
                        string line = file.ReadLine();

                        string[] values = line.Split(',');

                        string desc  = values[1];
                        double price = Convert.ToDouble(values[2]);

                        string BikeTypesSQL = string.Format(@"
                        Insert Into
                            Bike_Type(_Description,Price)
                            Values('{0}',{1});
                        ",
                                                            desc, price);

                        data.ExecuteActionQuery(BikeTypesSQL);
                    }
                }

                //parse customers.csv
                Console.WriteLine("Inserting Customers...");
                using (var file = new System.IO.StreamReader("Customers.csv"))
                {
                    while (!file.EndOfStream)
                    {
                        string line = file.ReadLine();

                        string[] values = line.Split(',');

                        string first = values[1];
                        string last  = values[2];
                        string email = values[3];

                        string CustomerSQL = string.Format(@"
                        Insert Into
                            Customer(FirstName,LastName,Email, RentingOut)
                            Values('{0}','{1}','{2}', 0);
                        ",
                                                           first, last, email);

                        data.ExecuteActionQuery(CustomerSQL);
                    }
                }

                //parse bikes.csv
                Console.WriteLine("Inserting Bikes...");
                using (var file = new System.IO.StreamReader("Bikes.csv"))
                {
                    while (!file.EndOfStream)
                    {
                        string line = file.ReadLine();

                        string[] values = line.Split(',');

                        int    typeid = Convert.ToInt32(values[1]);
                        string year   = values[2];

                        string BikesSQL = string.Format(@"
                        Insert Into
                            Bike(BTID,YearDeployed,RentedOut)
                            Values({0},'{1}',0);
                        ",
                                                        typeid, year);

                        data.ExecuteActionQuery(BikesSQL);
                    }
                }
                Console.WriteLine();

                //
                // Done
                //
            }
            catch (Exception ex)
            {
                Console.WriteLine("**Exception: '{0}'", ex.Message);
            }

            Console.WriteLine();
            Console.WriteLine("** Done **");
            Console.WriteLine();
        }//Main
コード例 #6
0
ファイル: Program.cs プロジェクト: ADLJR/CourseMoApp
        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
コード例 #7
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
コード例 #8
0
ファイル: Program.cs プロジェクト: maziesmith/Coursemo
        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