C# Programming

CreateDB/CreateDB/Program.cs

using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Data; using System.Data.SqlClient; namespace CreateDB { class Program { static void Main(string[] args) { //Establish the syntax for the connection string String connect = "Data Source=" + @"OW206_VM\MIS218;" + "Initial Catalog=Sales;Integrated Security=True"; //establish the variable con as a new connection object for the sqlconnection class using the connect string defined above. SqlConnection con = new SqlConnection(connect); con.Open(); Console.WriteLine ("Made the connection to the Sales database"); SqlCommand cmd = con.CreateCommand(); // clear out existing datbase tables, comment out during the first execution //cmd.CommandText = "DROP TABLE Customer"; //cmd.ExecuteNonQuery(); //cmd.CommandText = "DROP TABLE Salesperson"; //cmd.ExecuteNonQuery(); //cmd.CommandText = "DROP TABLE Item"; //cmd.ExecuteNonQuery(); //cmd.CommandText = "DROP TABLE Orders"; //cmd.ExecuteNonQuery(); //cmd.CommandText = "DROP TABLE OrderItem"; //cmd.ExecuteNonQuery(); cmd.CommandText = "CREATE TABLE Customer (CustomerID " + "CHAR(4), CustomerName VARCHAR(25), Address " + "VARCHAR(25), BalanceDue DECIMAL)"; cmd.ExecuteNonQuery(); cmd.CommandText = "INSERT INTO Customer VALUES (1234,'Fred " + "Flynn','22 First St.',1667.00)"; cmd.ExecuteNonQuery(); cmd.CommandText = "INSERT INTO Customer VALUES " + "(5678,'Darnell Davis','33 Second St.',130.95)"; cmd.ExecuteNonQuery(); cmd.CommandText = "INSERT INTO Customer VALUES (4321,'Marla " + "Martinez','44 Third St.',0)"; cmd.ExecuteNonQuery(); cmd.CommandText = "INSERT INTO Customer VALUES (8765,'Carla " + "Kahn','55 Fourth St.', 0)"; cmd.ExecuteNonQuery(); cmd.CommandText = "CREATE TABLE Salesperson (SalespersonID CHAR(2), SalespersonName VARCHAR(25), " + "Address VARCHAR(25))"; cmd.ExecuteNonQuery(); cmd.CommandText = "INSERT INTO Salesperson VALUES " + "(12,'Peter Patterson','66 Fifth St.')"; cmd.ExecuteNonQuery(); cmd.CommandText = "INSERT INTO Salesperson VALUES " + "(98,'Donna Dubarian','77 Sixth St.')"; cmd.ExecuteNonQuery(); cmd.CommandText = "CREATE TABLE Item (ItemNumber CHAR(6)," + "Description VARCHAR(20), Quantity INTEGER)"; cmd.ExecuteNonQuery(); cmd.CommandText = "INSERT INTO Item VALUES (222222,'radio',32)"; cmd.ExecuteNonQuery(); cmd.CommandText = "INSERT INTO Item VALUES (333333,'television',14)"; cmd.ExecuteNonQuery(); cmd.CommandText = "INSERT INTO Item VALUES (444444,'computer',9)"; cmd.ExecuteNonQuery(); cmd.CommandText = "CREATE TABLE Orders (OrderNumber VARCHAR(4), CustomerID CHAR(4), SalespersonID CHAR(2), OrderDate DATE)"; cmd.ExecuteNonQuery(); cmd.CommandText = "INSERT INTO Orders VALUES (1,1234,12,'Apr 3, 1999')"; cmd.ExecuteNonQuery(); cmd.CommandText = "INSERT INTO Orders VALUES (2,5678,12,'Mar 22, 1999')"; cmd.ExecuteNonQuery(); cmd.CommandText = "INSERT INTO Orders VALUES (3,8765,98,'Feb 19, 1999')"; cmd.ExecuteNonQuery(); cmd.CommandText = "INSERT INTO Orders VALUES (4,1234,12,'Apr 5, 1999')"; cmd.ExecuteNonQuery(); cmd.CommandText = "INSERT INTO Orders VALUES (5,8765,98,'Feb 28, 1999')"; cmd.ExecuteNonQuery(); cmd.CommandText = "CREATE TABLE OrderItem (OrderNumber " + "CHAR(4), ItemNumber CHAR(6), Quantity " + "INTEGER, UnitPrice DECIMAL)"; cmd.ExecuteNonQuery(); cmd.CommandText = "INSERT INTO OrderItem VALUES (1,222222,4,27.00)"; cmd.ExecuteNonQuery(); cmd.CommandText = "INSERT INTO OrderItem VALUES (1,333333,2,210.50)"; cmd.ExecuteNonQuery(); cmd.CommandText = "INSERT INTO OrderItem VALUES (1,444444,1,569.00)"; cmd.ExecuteNonQuery(); cmd.CommandText = "INSERT INTO OrderItem VALUES (2,333333,2,230.95)"; cmd.ExecuteNonQuery(); cmd.CommandText = "INSERT INTO OrderItem VALUES (3,222222,3,27.00)"; cmd.ExecuteNonQuery(); cmd.CommandText = "INSERT INTO OrderItem VALUES (3,333333,1,230.95)"; cmd.ExecuteNonQuery(); cmd.CommandText = "INSERT INTO OrderItem VALUES (4,444444,1,569.00)"; cmd.ExecuteNonQuery(); cmd.CommandText = "INSERT INTO OrderItem VALUES (5,222222,2,27.00)"; cmd.ExecuteNonQuery(); cmd.CommandText = "INSERT INTO OrderItem VALUES (5,444444,1,725.00)"; cmd.ExecuteNonQuery(); con.Close(); } } }