C# Programming

ConsoleSalesDB/ConsoleSalesDB/ExtractInfo.cs

using System; using System.Data; using System.Data.SqlClient; public class ExtractInfo { public static void Main() { String connect = "Data Source=" + @"OW206_VM\MIS218;" + "Initial Catalog=Sales;Integrated Security=True"; SqlConnection con = new SqlConnection(connect); con.Open(); Console.WriteLine ("Made the connection to the Sales database"); SqlCommand cmd = con.CreateCommand(); cmd.CommandText = "SELECT CustomerName, Address " + "FROM Customer ORDER BY CustomerName"; SqlDataReader reader = cmd.ExecuteReader(); Console.WriteLine(" Names and Addresses of Customers"); Console.WriteLine("Name\t\tAddress"); while (reader.Read()) Console.WriteLine("{0}\t{1}", reader.GetString(0), reader.GetString(1)); reader.Close(); //grab one record cmd.CommandText = "SELECT * FROM OrderItem " + "WHERE ItemNumber = '222222'"; reader = cmd.ExecuteReader(); Console.WriteLine(); Console.WriteLine(" Order items for radios"); Console.WriteLine("OrderNumber\tQuantity\tUnitPrice"); while (reader.Read()) Console.WriteLine("{0}\t\t{1}\t\t{2}", reader.GetString(0), reader.GetInt32(2), reader.GetDecimal(3)); reader.Close(); cmd.CommandText = "SELECT CustomerName FROM Customer, Orders " + "WHERE Customer.CustomerID = Orders.CustomerID " + "AND OrderDate = '3/22/99'"; reader = cmd.ExecuteReader(); Console.WriteLine(); Console.WriteLine (" Customer placing orders on Mar 22, 1999"); while (reader.Read()) Console.WriteLine(reader.GetString(0)); reader.Close(); cmd.CommandText = "SELECT DISTINCT CustomerName " + "FROM Customer, Item, Orders, OrderItem " + "WHERE Customer.CustomerID = Orders.CustomerID " + "AND Orders.OrderNumber = OrderItem.OrderNumber " + "AND OrderItem.ItemNumber = Item.ItemNumber " + "AND Description = 'computer'"; reader = cmd.ExecuteReader(); Console.WriteLine(); Console.WriteLine(" Customers ordering computers"); while (reader.Read()) Console.WriteLine(reader.GetString(0)); reader.Close(); cmd.CommandText = "SELECT OrderNumber FROM Orders " + "WHERE OrderDate BETWEEN '4/1/99' AND '4/30/99'"; reader = cmd.ExecuteReader(); Console.WriteLine(); Console.WriteLine (" Order numbers of orders from 4/1/99 to 4/30/99"); while (reader.Read()) Console.WriteLine(reader.GetString(0)); reader.Close(); //Aggregates cmd.CommandText = "SELECT SUM(Balancedue) FROM Customer"; Console.WriteLine("Sum of balances: {0:C}", (decimal)cmd.ExecuteScalar()); cmd.CommandText = "SELECT COUNT(*) FROM Customer"; Console.WriteLine("Number of Customer rows {0}", (int)cmd.ExecuteScalar()); cmd.CommandText = "SELECT AVG(Balancedue) FROM Customer"; Console.WriteLine("Average of balances: {0:C}", (decimal)cmd.ExecuteScalar()); cmd.CommandText = "SELECT MAX(Balancedue) FROM Customer"; Console.WriteLine("MAX of balances: {0:C}", (decimal)cmd.ExecuteScalar()); cmd.CommandText = "INSERT INTO Item VALUES ('555555','CD player',10)"; cmd.ExecuteNonQuery(); cmd.CommandText = "UPDATE Item SET Quantity = 12 " + "WHERE Description = 'CD player'"; cmd.ExecuteNonQuery(); Console.WriteLine(); Console.WriteLine(" Added and updated a new item"); Console.WriteLine("Description"); cmd.CommandText = "SELECT Description FROM Item"; reader = cmd.ExecuteReader(); while (reader.Read()) Console.WriteLine(reader.GetString(0)); reader.Close(); cmd.CommandText = "DELETE FROM Item WHERE Description = 'CD player'"; cmd.ExecuteNonQuery(); cmd.CommandText = "SELECT Description FROM Item"; reader = cmd.ExecuteReader(); Console.WriteLine(); Console.WriteLine(" Deleted the new item"); Console.WriteLine("Description"); while (reader.Read()) Console.WriteLine(reader.GetString(0)); reader.Close(); con.Close(); } }