public static void AddOrderInTransactionScope() { Console.WriteLine("Adding two orders using TransactionScope..."); using (NorthwindEntities northwindEntities = new NorthwindEntities()) { Order firstOrder = new Order(); firstOrder.CustomerID = "TOMSP"; firstOrder.ShipName = "First Order Ship Name"; Order secondOrder = new Order(); secondOrder.CustomerID = "OCEAN"; secondOrder.ShipName = "Second Order Ship Name"; using (System.Transactions.TransactionScope scope = new System.Transactions.TransactionScope()) { northwindEntities.Orders.Add(firstOrder); northwindEntities.SaveChanges(); northwindEntities.Orders.Add(secondOrder); northwindEntities.SaveChanges(); scope.Complete(); } firstOrder = northwindEntities.Orders.Find(firstOrder.OrderID); secondOrder = northwindEntities.Orders.Find(secondOrder.OrderID); Console.WriteLine("FirstOrder.ShipName={0}, SecondOrder.ShipName={1}", firstOrder.ShipName, secondOrder.ShipName); } }
public static void CopyDatabaseSchema() { Console.WriteLine("Creating copy of Northwind database in the MS SQL server...(wait a moment)"); IObjectContextAdapter initialDBSchema = new NorthwindEntities(); string dbScript = initialDBSchema.ObjectContext.CreateDatabaseScript(); DbContext newDBSchema = new DbContext("Server=localhost;Database=NorthwindTwin;Integrated Security=True;"); newDBSchema.Database.CreateIfNotExists(); (newDBSchema as IObjectContextAdapter).ObjectContext.ExecuteStoreCommand(dbScript); }
public static void CalculateTotalIncomes() { Console.WriteLine("Calculates total incomes of Pavlova, Ltd. for year 1997..."); DateTime startDate = new DateTime(1997, 1, 1); DateTime endDate = new DateTime(1998, 1, 1); string supplierName = "Pavlova, Ltd."; using (var northwindEntities = new NorthwindEntities()) { var totalIncomes = northwindEntities.TotalIncomesForGivenSupplier(supplierName, startDate, endDate).ToList(); Console.WriteLine("Incomes: {0:N2}", totalIncomes[0]); } }
public static bool Delete(string customerID) { using (var ctx = new NorthwindEntities()) { var theCustomer = ctx.Customers.Find(customerID); if (theCustomer == null) return false; ctx.Customers.Remove(theCustomer); if (ctx.SaveChanges() == 0) return false; } return true; }
public static Customer Insert(string companyName, string contactName = null, string contactTitle = null, string address = null, string city = null, string region = null, string postalCode = null, string country = null, string phone = null, string fax = null) { var newCustomer = new Customer(); newCustomer.CompanyName = companyName; newCustomer.ContactName = contactName; newCustomer.ContactTitle = contactTitle; newCustomer.Address = address; newCustomer.City = city; newCustomer.Region = region; newCustomer.PostalCode = postalCode; newCustomer.Country = country; newCustomer.Phone = phone; newCustomer.Fax = fax; string[] getWords = companyName.Split(new char[] { ' ' }, StringSplitOptions.RemoveEmptyEntries); newCustomer.CustomerID = getWords[0].Substring(0, 3) + ((getWords.Length > 1) ? getWords[1].Substring(0, 2) : getWords[0].Substring(0, 2)); newCustomer.CustomerID = newCustomer.CustomerID.ToUpperInvariant(); using (var ctx = new NorthwindEntities()) { while (ctx.Customers.Find(newCustomer.CustomerID) != null && newCustomer.CustomerID != LastPossibleCustomerId) { for (int i = newCustomer.CustomerID.Length-1; i >= 0 ; i--) { if (newCustomer.CustomerID[i] < 'Z') { char[] letters = newCustomer.CustomerID.ToCharArray(); letters[i]++; newCustomer.CustomerID = string.Join("", letters); break; } } } ctx.Customers.Add(newCustomer); ctx.SaveChanges(); } return newCustomer; }
public static void ConcurentChanges() { using (var ctx1 = new NorthwindEntities()) { Employee employeeFromCtx1 = ctx1.Employees.First(e => e.EmployeeID == 1); employeeFromCtx1.FirstName = "Pesho"; // Modify and Save the same employee in another context // i.e. mimicking concurrent access. using (var ctx2 = new NorthwindEntities()) { Employee employeeFromCtx2 = ctx2.Employees.First(e => e.EmployeeID == 1); employeeFromCtx2.FirstName = "Gosho"; ctx2.SaveChanges(); } // Console.ReadLine(); // Save the changes... This should result in an Exception, but NOT due to EF 5 Optimistic Concurrency ctx1.SaveChanges(); } }
public static Customer Modify(string customerID, string companyName = null, string contactName = null, string contactTitle = null, string address = null, string city = null, string region = null, string postalCode = null, string country = null, string phone = null, string fax = null) { using (var ctx = new NorthwindEntities()) { var theCustomer = ctx.Customers.Find(customerID); if (theCustomer == null) return null; if (companyName != null) theCustomer.CompanyName = companyName; if (contactName != null) theCustomer.ContactName = contactName; if (contactTitle != null) theCustomer.ContactTitle = contactTitle; if (address != null) theCustomer.Address = address; if (city != null) theCustomer.City = city; if (region != null) theCustomer.Region = region; if (postalCode != null) theCustomer.PostalCode = postalCode; if (country != null) theCustomer.Country = country; if (phone != null) theCustomer.Phone = phone; if (fax != null) theCustomer.Fax = fax; ctx.SaveChanges(); return theCustomer; } }
public static void CreateUserInGroup() { Console.WriteLine("Adding new user 'mancho' and attach it to the 'Admins' group..."); string username = "******"; using (var northwindEntities = new NorthwindEntities()) { using (var scope = new System.Transactions.TransactionScope()) { User someUser = new User(); someUser.Username = username; someUser.FullName = "Chicho Mancho"; someUser.Password = "******"; string adminGroup = "Admins"; var group = northwindEntities.Groups.Where(a => a.Name == adminGroup).ToList(); if (group.Count == 0) { Group newGroup = new Group(); newGroup.Name = adminGroup; northwindEntities.Groups.Add(newGroup); northwindEntities.SaveChanges(); group.Add(newGroup); } someUser.GroupId = group[0].GroupId; northwindEntities.Users.Add(someUser); northwindEntities.SaveChanges(); scope.Complete(); } } }
public static void ShowOrdersByRegionAndPeriod(string region, DateTime startDate, DateTime endDate) { var ctx = new NorthwindEntities(); var query = from ordersInPeriod in ctx.Sales_by_Year(startDate, endDate) join orders in ctx.Orders on ordersInPeriod.OrderID equals orders.OrderID where (orders.ShipRegion == region) select orders; int count = 1; foreach (var item in query) { Console.WriteLine("Order: {0}, Date: {1:dd/MM}, To: {2}, Price: {3:N2}", item.OrderID, item.ShippedDate, item.ShipName, item.Freight); count++; } Console.WriteLine("\nTotal {0} records selected.\n", count - 1); }
public static void ShowFirstEmployeeWithTerritories() { Console.WriteLine("Showing Employee[1] with its territories using EntitySet"); using (var ctx = new NorthwindEntities()) { var employee = ctx.Employees.First(e => e.EmployeeID == 1); Console.WriteLine("Employee[1] name=" + employee.FirstName + " " + employee.LastName); foreach (var item in employee.EntitySetTerritories) { Console.WriteLine("Territory: " + item.TerritoryDescription); } } }
public static void ShowCustomersByOrderYearAndCountrySQL(int year, string country) { var sqlCommand = "SELECT * FROM Customers AS c" + " JOIN Orders AS o" + " ON c.CustomerID = o.CustomerID " + "WHERE YEAR(o.OrderDate) = {0} AND o.ShipCountry = {1}"; var ctx = new NorthwindEntities(); var query = ctx.Database.SqlQuery<Customer>(sqlCommand, year, country); int count = 1; foreach (var item in query) { Console.WriteLine("{0} - CustomerID: {1}, Company Name: {2}", count++, item.CustomerID, item.CompanyName); } Console.WriteLine("\nTotal {0} records selected.\n", count - 1); }
public static void ShowCustomersByOrderYearAndCountry(int year, string country) { var ctx = new NorthwindEntities(); var query = from customer in ctx.Customers join order in ctx.Orders on customer.CustomerID equals order.CustomerID where (order.OrderDate.Value.Year == year && order.ShipCountry == country) select customer; int count = 1; foreach (var item in query) { Console.WriteLine("{0} - CustomerID: {1}, Company Name: {2}", count++, item.CustomerID, item.CompanyName); } Console.WriteLine("\nTotal {0} records selected.\n", count - 1); }