private static void CreatingNewDataBase(northwindEntities context) { SqlConnection myConn = new SqlConnection("Server=localhost;Integrated security=SSPI;database=master"); string nativeSQLQuery = "CREATE DATABASE MyDatabase ON PRIMARY " + "(NAME = MyDatabase_Data, " + "FILENAME = 'C:\\MyDatabaseData.mdf', " + "SIZE = 2MB, MAXSIZE = 10MB, FILEGROWTH = 10%) " + "LOG ON (NAME = MyDatabase_Log, " + "FILENAME = 'C:\\MyDatabaseLog.ldf', " + "SIZE = 1MB, " + "MAXSIZE = 5MB, " + "FILEGROWTH = 10%)"; context.Database.SqlQuery<string>(nativeSQLQuery); //SqlCommand myCommand = new SqlCommand(nativeSQLQuery, myConn); //try //{ // myConn.Open(); // myCommand.ExecuteNonQuery(); //} //finally //{ // if (myConn.State == ConnectionState.Open) // { // myConn.Close(); // } //} }
static void Main() { northwindEntities context = new northwindEntities(); InsertCustomer(context); UpdateCustomer(context); DeleteCustomer(context); }
static void DeleteCustomer(northwindEntities context) { Console.WriteLine("Enter Customer ID"); string id = Console.ReadLine(); Customer customer = context.Customers.First(x => x.CustomerID == id); context.Customers.Remove(customer); context.SaveChanges(); }
static void Main() { northwindEntities context = new northwindEntities(); using (context) { CreatingNewDataBase(context); } }
static void Main() { northwindEntities context = new northwindEntities(); using (context) { var supplierIncome = findSupplierIncome(context); Console.WriteLine("Supplier {0}'s income is {1}", "ss", supplierIncome); } }
static void Main() { northwindEntities context = new northwindEntities(); using (context) { foreach (var territory in context.Territories) { Console.WriteLine(territory.TerritoryDescription); } } }
static void UpdateCustomer(northwindEntities context) { Console.WriteLine("Enter Customer ID"); string id = Console.ReadLine(); Customer customer = context.Customers.First(x => x.CustomerID == id); Console.WriteLine("Enter Column name"); string column = Console.ReadLine(); Console.WriteLine("Enter Column value"); string value = Console.ReadLine(); switch (column) { case "CompanyName": customer.CompanyName = value; break; case "ContactName": customer.ContactName = value; break; case "ContactTitle": customer.ContactTitle = value; break; case "Address": customer.Address = value; break; case "City": customer.City = value; break; case "Region": customer.Region = value; break; case "PostalCode": customer.PostalCode = value; break; case "Country": customer.Country = value; break; case "Phone": customer.Phone = value; break; case "Fax": customer.Fax = value; break; } context.SaveChanges(); }
static void Main() { using (northwindEntities firstContext = new northwindEntities()) { using (northwindEntities secondContext = new northwindEntities()) { Customer customerFirstContext = firstContext.Customers.Find("CHOPS"); customerFirstContext.Region = "SW"; Customer customerSecondContext = secondContext.Customers.Find("CHOPS"); customerSecondContext.Region = "SSWW"; firstContext.SaveChanges(); secondContext.SaveChanges(); } } Console.WriteLine("Changes successfully made!"); }
static void Main() { northwindEntities context = new northwindEntities(); using (context) { string nativeSQLQuery = "SELECT DISTINCT c.CompanyName " + "FROM dbo.Customers c " + "JOIN dbo.Orders o " + "ON c.CustomerID = o.CustomerID " + "WHERE YEAR(o.OrderDate) = 1997 AND o.ShipCountry = 'Canada'"; var companies = context.Database.SqlQuery<string>(nativeSQLQuery); foreach (var company in companies) { Console.WriteLine(company); } } }
static void Main() { northwindEntities context = new northwindEntities(); using (context) { Console.WriteLine("Enter Region name"); string region = Console.ReadLine(); Console.WriteLine("Enter Start date in format dd.MM.yyyy"); DateTime startDate = DateTime.ParseExact(Console.ReadLine(), "dd.MM.yyyy", new CultureInfo("en-US")); Console.WriteLine("Enter End date in format dd.MM.yyyy"); DateTime endDate = DateTime.ParseExact(Console.ReadLine(), "dd.MM.yyyy", new CultureInfo("en-US")); var sales = context.Orders.Select(x => x). Where(x => x.ShipRegion == region && x.OrderDate > startDate && x.OrderDate < endDate); Console.WriteLine(sales.Count()); } }
static void InsertCustomer(northwindEntities context) { using (context) { Console.WriteLine("Enter Company name"); string company = Console.ReadLine(); Console.WriteLine("Enter Contact name"); string contact = Console.ReadLine(); Console.WriteLine("Enter Contact title"); string title = Console.ReadLine(); Console.WriteLine("Enter Address"); string address = Console.ReadLine(); Console.WriteLine("Enter City"); string city = Console.ReadLine(); Console.WriteLine("Enter Region"); string region = Console.ReadLine(); Console.WriteLine("Enter PostalCode"); string code = Console.ReadLine(); Console.WriteLine("Enter Country"); string country = Console.ReadLine(); Console.WriteLine("Enter Phone"); string phone = Console.ReadLine(); Console.WriteLine("Enter Fax"); string fax = Console.ReadLine(); Customer customer = new Customer { CompanyName = company, ContactName = contact, ContactTitle = title, Address = address, City = city, Region = region, PostalCode = code, Country = country, Phone = phone, Fax = fax, }; context.Customers.Add(customer); context.SaveChanges(); } }
static void Main() { northwindEntities context = new northwindEntities(); using (context) { var orders = context.Customers. Join(context.Orders, (c => c.CustomerID), (o => o.CustomerID), (c, o) => new { Customer = c.CompanyName, OrderDate = o.OrderDate, Country = o.ShipCountry }). Select(x => x). Where(x => x.OrderDate.Value.Year == 1997 && x.Country == "Canada"); var companies = orders.Select(x => x.Customer).Distinct(); foreach (var company in companies) { Console.WriteLine(company); } } }
static IEnumerable<int> findSupplierIncome(northwindEntities context) { Console.WriteLine("Enter Company Name"); string name = Console.ReadLine(); Console.WriteLine("Enter Start Date in format dd.MM.yyyy"); DateTime startDate = DateTime.ParseExact(Console.ReadLine(), "dd.MM.yyyy", new CultureInfo("en-US")); Console.WriteLine("Enter End Date in format dd.MM.yyyy"); DateTime endDate = DateTime.ParseExact(Console.ReadLine(), "dd.MM.yyyy", new CultureInfo("en-US")); string nativeSQLQuery = string.Format( "select sum(od.UnitPrice * Quantity) as SupplierIncome " + "from dbo.Suppliers as s " + "inner join dbo.Products as p " + "on p.SupplierID = s.SupplierID " + "inner join dbo.[Order Details] as od " + "on od.ProductID = p.ProductID " + "inner join dbo.Orders as o " + "on o.OrderID = od.OrderID " + "where s.CompanyName = {0} and o.OrderDate > {1} and o.OrderDate > {2}", name, startDate, endDate); var income = context.Database.SqlQuery<int>(nativeSQLQuery); return income; }
static void Main() { TransactionScope scope = new TransactionScope(); using (scope) { northwindEntities context = new northwindEntities(); using (context) { Console.WriteLine("Enter Customer ID"); string customer = Console.ReadLine(); Console.WriteLine("Enter Employee ID"); int employee = int.Parse(Console.ReadLine()); Console.WriteLine("Enter Order Date in format dd.MM.yyyy"); DateTime orderDate = DateTime.ParseExact(Console.ReadLine(), "dd.MM.yyyy", new CultureInfo("en-US")); Console.WriteLine("Enter Required Date in format dd.MM.yyyy"); DateTime requiredDate = DateTime.ParseExact(Console.ReadLine(), "dd.MM.yyyy", new CultureInfo("en-US")); Console.WriteLine("Enter Shipped Date in format dd.MM.yyyy"); DateTime shippedDate = DateTime.ParseExact(Console.ReadLine(), "dd.MM.yyyy", new CultureInfo("en-US")); Console.WriteLine("Enter Sip Via"); int via = int.Parse(Console.ReadLine()); Console.WriteLine("Enter Freight"); decimal freight = decimal.Parse(Console.ReadLine()); Console.WriteLine("Enter Ship Name"); string name = Console.ReadLine(); Console.WriteLine("Enter Ship Address"); string address = Console.ReadLine(); Console.WriteLine("Enter Ship City"); string city = Console.ReadLine(); Console.WriteLine("Enter Ship Region"); string region = Console.ReadLine(); Console.WriteLine("Enter Ship Postal Code"); string code = Console.ReadLine(); Console.WriteLine("Enter Ship Country"); string country = Console.ReadLine(); Order order = new Order { CustomerID = customer, EmployeeID = employee, OrderDate = orderDate, RequiredDate = requiredDate, ShippedDate = shippedDate, ShipVia = via, Freight = freight, ShipName = name, ShipAddress = address, ShipCity = city, ShipRegion = region, ShipPostalCode = code, ShipCountry = country }; context.Orders.Add(order); context.SaveChanges(); } scope.Complete(); } }