public static void Sample14(Northwind db) { // use SubmitChanges() to submit all changes back to the database Customer cust = db.Customers.First(c => c.CustomerID == "ALFKI"); cust.ContactTitle = "Sith Lord"; // other changes ... db.SubmitChanges(); }
public void LinqToSqlOptimistic01() { Console.WriteLine("YOU: ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~"); Product product = db.Products.First(p => p.ProductID == 1); Console.WriteLine("~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~"); Console.WriteLine(); Console.WriteLine("OTHER USER: ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~"); // Open a second connection to the database to simulate another user // who is going to make changes to the Products table Northwind otherUser_db = new Northwind(connString) { Log = db.Log }; Product otherUser_product = otherUser_db.Products.First(p => p.ProductID == 1); otherUser_product.UnitPrice = 999.99M; otherUser_product.UnitsOnOrder = 10; otherUser_db.SubmitChanges(); Console.WriteLine("~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~"); Console.WriteLine("YOU (continued): ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~"); product.UnitPrice = 777.77M; bool conflictOccurred = false; try { db.SubmitChanges(ConflictMode.ContinueOnConflict); } catch (ChangeConflictException) { Console.WriteLine("* * * OPTIMISTIC CONCURRENCY EXCEPTION * * *"); foreach (ObjectChangeConflict aConflict in /*ex.Conflicts*/db.ChangeConflicts) { Product prod = (Product)aConflict.Object; Console.WriteLine("The conflicting product has ProductID {0}", prod.ProductID); Console.WriteLine(); Console.WriteLine("Conflicting members:"); Console.WriteLine(); foreach (MemberChangeConflict memConflict in aConflict.MemberConflicts) { string name = memConflict.Member/*MemberInfo*/.Name; string yourUpdate = memConflict.CurrentValue.ToString(); string original = memConflict.OriginalValue.ToString(); string theirUpdate = memConflict.DatabaseValue.ToString(); if (memConflict.IsModified/*HaveModified*/) { Console.WriteLine("'{0}' was updated from {1} to {2} while you updated it to {3}", name, original, theirUpdate, yourUpdate); } else { Console.WriteLine("'{0}' was updated from {1} to {2}, you did not change it.", name, original, theirUpdate); } } Console.WriteLine(); } conflictOccurred = true; } Console.WriteLine(); if (!conflictOccurred) { Console.WriteLine("* * * COMMIT SUCCESSFUL * * *"); Console.WriteLine("Changes to Product 1 saved."); } Console.WriteLine("~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ "); ResetProducts(); // clean up }
public void LinqToSqlAdo02() { var q = from p in db.Products where p.ProductID == 3 select p; Console.WriteLine("*** BEFORE ***"); ObjectDumper.Write(q); Console.WriteLine(); Console.WriteLine("*** INSERT ***"); // Create a standard ADO.NET connection: SqlConnection nwindConn = new SqlConnection(connString); nwindConn.Open(); // Use pre-existing ADO.NET connection to create DataContext: Northwind interop_db = new Northwind(nwindConn) { Log = db.Log }; SqlTransaction nwindTxn = nwindConn.BeginTransaction(); try { SqlCommand cmd = new SqlCommand("UPDATE Products SET QuantityPerUnit = 'single item' WHERE ProductID = 3"); cmd.Connection = nwindConn; cmd.Transaction = nwindTxn; cmd.ExecuteNonQuery(); // Share pre-existing ADO.NET transaction: //interop_db.LocalTransaction = nwindTxn; interop_db.Transaction = nwindTxn; Product prod1 = interop_db.Products.First(p => p.ProductID == 4); Product prod2 = interop_db.Products.First(p => p.ProductID == 5); prod1.UnitsInStock -= 3; prod2.UnitsInStock -= 5; // ERROR: this will make the units in stock negative interop_db.SubmitChanges(); nwindTxn.Commit(); } catch (Exception e) { // If there is a transaction error, all changes are rolled back, // including any changes made directly through the ADO.NET connection Console.WriteLine(e.Message); Console.WriteLine("Error submitting changes... all changes rolled back."); } nwindConn.Close(); Console.WriteLine(); Console.WriteLine("*** AFTER ***"); ClearDBCache(); ObjectDumper.Write(q); Cleanup112(); // Restore previous database state }
public void LinqToSqlSimultaneous02() { Console.WriteLine("YOU: ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~"); var product = db.Products.First(p => p.ProductID == 1); Console.WriteLine("~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~"); Console.WriteLine(); Console.WriteLine("OTHER USER: ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~"); // Open a second connection to the database to simulate another user // who is going to make changes to the Products table. Northwind otherUser_db = new Northwind(connString) { Log = db.Log }; var otherUser_product = otherUser_db.Products.First(p => p.ProductID == 1); otherUser_product.UnitPrice = 999.99M; otherUser_db.SubmitChanges(); Console.WriteLine("~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~"); Console.WriteLine(); Console.WriteLine("YOU (continued): ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~"); product.UnitPrice = 777.77M; bool conflict = false; try { db.SubmitChanges(); } //OptimisticConcurrencyException catch (ChangeConflictException) { conflict = true; } Console.WriteLine(); if (conflict) { Console.WriteLine("* * * OPTIMISTIC CONCURRENCY EXCEPTION * * *"); Console.WriteLine("Another user has changed Product 1 since it was first requested."); Console.WriteLine("Backing out changes."); } else { Console.WriteLine("* * * COMMIT SUCCESSFUL * * *"); Console.WriteLine("Changes to Product 1 saved."); } Console.WriteLine("~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ "); Cleanup72(); // Restore previous database state }
private void CleanupInsert11() { int[] alfkiOrderIDs = { 10643, 10692, 10702, 10835, 10952, 11011 }; using (Northwind tempdb = new Northwind(connString)) { Customer c1 = tempdb.Customers.Single(c => c.CustomerID == "ALFKI"); c1.Phone = "030-0074321"; Order oa = tempdb.Orders.Single(o => o.OrderID == 10643); oa.ShipCity = "Berlin"; Order ob = tempdb.Orders.Single(o => o.OrderID == 10692); ob.CustomerID = "ALFKI"; foreach (Order o in c1.Orders.Where(p => !alfkiOrderIDs.Contains(p.OrderID))) tempdb.Orders.DeleteOnSubmit(o); tempdb.SubmitChanges(); } }
public static void Sample15(Northwind db) { Console.WriteLine("*** BEFORE ***"); ObjectDumper.Write(from p in db.Products where p.ProductID == 4 select p); ObjectDumper.Write(from p in db.Products where p.ProductID == 5 select p); Console.WriteLine(); Console.WriteLine("*** UPDATE WITH EXPLICIT TRANSACTION ***"); // Explicit use of TransactionScope ensures that // the data will not change in the database between // read and write using (TransactionScope ts = new TransactionScope()) { try { Product prod1 = db.Products.First(p => p.ProductID == 4); Product prod2 = db.Products.First(p => p.ProductID == 5); prod1.UnitsInStock -= 3; prod2.UnitsInStock -= 5; // ERROR: this will make the units in stock negative db.SubmitChanges(); } catch (System.Data.SqlClient.SqlException e) { Console.WriteLine(e.Message); } } Console.WriteLine(); Console.WriteLine("*** AFTER ***"); db = new Northwind(Program.connString); // reset object cache to prove objects are reset ObjectDumper.Write(from p in db.Products where p.ProductID == 4 select p); ObjectDumper.Write(from p in db.Products where p.ProductID == 5 select p); }
static void Main(string[] args) { /* if (args.Length != 4) { Logger.Write("Usage: DbLinq.MySql.Example.exe server user password database"); Logger.Write("Debug arguments can be set on project properties in visual studio."); Logger.Write("Press enter to continue."); Console.ReadLine(); return; } string connStr = String.Format("server={0};user id={1}; password={2}; database={3}", args); insertTest(connStr); return; */ #if false MySqlCommand cmd = new MySqlCommand("select hello(?s)", new MySqlConnection(connStr)); //cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("?s", "xx"); cmd.Parameters[0].Direction = ParameterDirection.Input; //.Value = "xx"; cmd.Connection.Open(); //MySqlDataReader dr = cmd.ExecuteReader(); object obj = cmd.ExecuteScalar(); #endif string dbServer = Environment.GetEnvironmentVariable("DbLinqServer") ?? "localhost"; // BUG: contexts must to be disposable string connStr = String.Format("server={0};user id={1}; password={2}; database={3}" , dbServer, "LinqUser", "linq2", "Northwind"); Northwind db = new Northwind(new MySqlConnection(connStr)); #if USE_STORED_PROCS int is2; object xx = db.sp_selOrders("ZZ", out is2); string reply0 = db.Hello0(); string reply1 = db.Hello1("Pigafetta"); #endif #if USE_AllTypes //Console.Clear(); //Logger.Write("from at in db.Alltypes select at;"); //var q1 = from at in db.Alltypes select at; //foreach (var v in q1) // ObjectDumper.Write(v); //Logger.Write("Press enter to continue."); //Console.ReadLine(); #endif Console.Clear(); Console.WriteLine( "from p in db.Products orderby p.ProductName select p;"); var q2 = from p in db.Products orderby p.ProductName select p; foreach (var v in q2) ObjectDumper.Write(v); Console.WriteLine( "Press enter to continue."); Console.ReadLine(); // BUG: This one throws a null reference for some reason. //Console.Clear(); //var q3 = from c in db.Customers // from o in c.Orders // where c.City == "London" select new { c, o }; //foreach (var v in q3) // ObjectDumper.Write(v); //Console.ReadLine(); Console.Clear(); Console.WriteLine( "from p in db.Products where p.ProductID == 7 select p;"); var q4 = from p in db.Products where p.ProductID == 7 select p; foreach (var v in q4) ObjectDumper.Write(v); Console.WriteLine( "Press enter to continue."); Console.ReadLine(); Console.Clear(); Console.WriteLine( "from c in db.Customers from o in c.Orders where c.City == \"London\" select new { c, o };"); var q5 = from c in db.Customers from o in c.Orders where c.City == "London" select new { c, o }; foreach (var v in q4) ObjectDumper.Write(v); Console.WriteLine( "Press enter to continue."); Console.ReadLine(); Console.Clear(); Console.WriteLine( "from o in db.Orders where o.Customer.City == \"London\" select new { c = o.Customer, o };"); var q6 = from o in db.Orders where o.Customer.City == "London" select new { c = o.Customer, o }; foreach (var v in q4) ObjectDumper.Write(v); Console.WriteLine( "Press enter to continue."); Console.ReadLine(); Console.Clear(); Console.WriteLine( "db.Orders"); foreach (var v in db.Orders) ObjectDumper.Write(v); Console.WriteLine( "Press enter to continue."); Console.ReadLine(); // BUG: This currently will insert 3 rows when it should insert only 2 // SubmitChanges isn't clearing the client side transaction data //Console.Clear(); //Console.WriteLine( "db.Orders.Add(new Order { ProductID = 7, CustomerId = 1, OrderDate = DateTime.Now });"); //db.Orders.InsertOnSubmit(new Order { EmployeeID = 1, CustomerId = "ALFKI", OrderDate = DateTime.Now }); //db.SubmitChanges(); //Console.WriteLine( "db.Orders.Add(new Order { ProductID = 2, CustomerId = 2, OrderDate = DateTime.Now });"); //db.Orders.InsertOnSubmit(new Order { EmployeeID = 1, CustomerId = "ALFKI", OrderDate = DateTime.Now }); //db.SubmitChanges(); //foreach (var v in db.Orders) // ObjectDumper.Write(v); //Console.WriteLine("Press enter to continue."); //Console.ReadLine(); Console.Clear(); Console.WriteLine( "db.Orders.Remove(db.Orders.First());"); db.Orders.DeleteOnSubmit(db.Orders.First()); db.SubmitChanges(); foreach (var v in db.Orders) ObjectDumper.Write(v); Console.WriteLine( "Press enter to continue."); Console.ReadLine(); }
public void LinqToSqlOptimistic05() { Northwind otherUser_db = new Northwind(connString); db.Log = null; Product product = db.Products.First(p => p.ProductID == 1); Console.WriteLine("You retrieve the product 1, it costs {0}", product.UnitPrice); Console.WriteLine("There are {0} units in stock, {1} units on order", product.UnitsInStock, product.UnitsOnOrder); Console.WriteLine(); Console.WriteLine("Another user changes the price to 22.22 and UnitsOnOrder to 2"); Product otherUser_product = otherUser_db.Products.First(p => p.ProductID == 1); otherUser_product.UnitPrice = 22.22M; otherUser_product.UnitsOnOrder = 2; otherUser_db.SubmitChanges(); Console.WriteLine("You set the price of product 1 to 1.01 and UnitsOnOrder to 11"); product.UnitPrice = 1.01M; product.UnitsOnOrder = 11; bool needsSubmit = true; while (needsSubmit) { try { Console.WriteLine("You submit"); Console.WriteLine(); needsSubmit = false; db.SubmitChanges(); } catch (ChangeConflictException) { needsSubmit = true; WriteConflictDetails(db.ChangeConflicts); // write changed objects / members to console Console.WriteLine(); Console.WriteLine("Resolve by higher price / order"); foreach (ObjectChangeConflict conflict in db.ChangeConflicts) { conflict.Resolve(RefreshMode.KeepChanges); foreach (MemberChangeConflict memConflict in conflict.MemberConflicts) { if (memConflict.Member.Name == "UnitPrice") { //always use the highest price decimal theirPrice = (decimal)memConflict.DatabaseValue; decimal yourPrice = (decimal)memConflict.CurrentValue; memConflict.Resolve(Math.Max(theirPrice, yourPrice)); } else if (memConflict.Member.Name == "UnitsOnOrder") { //always use higher order short theirOrder = (short)memConflict.DatabaseValue; short yourOrder = (short)memConflict.CurrentValue; memConflict.Resolve(Math.Max(theirOrder, yourOrder)); } } } } } Northwind dbResult = new Northwind(connString); Product result = dbResult.Products.First(p => p.ProductID == 1); Console.WriteLine("Now product 1 has price={0}, UnitsOnOrder={1}", result.UnitPrice, result.UnitsOnOrder); Console.WriteLine(); ResetProducts(); // clean up }
public void LinqToSqlInsert02() { Northwind db2 = new Northwind(connString); DataLoadOptions ds = new DataLoadOptions(); ds.LoadWith<nwind.Category>(p => p.Products); db2.LoadOptions = ds; var q = ( from c in db2.Categories where c.CategoryName == "Widgets" select c); Console.WriteLine("*** BEFORE ***"); ObjectDumper.Write(q, 1); Console.WriteLine(); Console.WriteLine("*** INSERT ***"); var newCategory = new Category { CategoryName = "Widgets", Description = "Widgets are the customer-facing analogues " + "to sprockets and cogs." }; var newProduct = new Product { ProductName = "Blue Widget", UnitPrice = 34.56M, Category = newCategory }; db2.Categories.InsertOnSubmit(newCategory); db2.SubmitChanges(); Console.WriteLine(); Console.WriteLine("*** AFTER ***"); ObjectDumper.Write(q, 1); Cleanup65(); // Restore previous database state }
static void Main(string[] args) { #if SQLITE string connStr = "Data Source=Northwind.db3;FailIfMissing=false;"; if (args.Length >= 1) { System.Data.SQLite.SQLiteConnection cnn = new SQLiteConnection(connStr); System.Data.SQLite.SQLiteCommand cmd = cnn.CreateCommand(); for (int i = 0; i < args.Length; i++) { cmd.CommandText = System.IO.File.ReadAllText(args[i]); cnn.Open(); cmd.ExecuteNonQuery(); cnn.Close(); } cmd.Dispose(); cnn.Dispose(); } #else if (args.Length != 4) { Console.WriteLine("Usage: DbLinq.MySql.Example.exe server user password database"); Console.WriteLine("Debug arguments can be set on project properties in visual studio."); Console.WriteLine("Press enter to continue."); Console.ReadLine(); return; } string connStr = String.Format("server={0};user id={1}; password={2}; database={3}", args); #endif #if false SQLiteCommand cmd = new SQLiteCommand("select hello(?s)", new SQLiteConnection(connStr)); //cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("?s", "xx"); cmd.Parameters[0].Direction = ParameterDirection.Input; //.Value = "xx"; cmd.Connection.Open(); //SQLiteDataReader dr = cmd.ExecuteReader(); object obj = cmd.ExecuteScalar(); #endif // BUG: contexts must to be disposable Northwind db = new Northwind(new XSqlConnection(connStr)); #if !SQLITE && USE_STORED_PROCS int is2; object xx = db.sp_selOrders("ZZ", out is2); string reply0 = db.hello0(); string reply1 = db.Hello1("Pigafetta"); #endif #if NO Console.Clear(); Console.WriteLine("from at in db.Alltypes select at;"); var q1 = from at in db.Alltypes select at; foreach (var v in q1) ObjectDumper.Write(v); Console.WriteLine("Press enter to continue."); Console.ReadLine(); #endif Console.Clear(); Console.WriteLine("from p in db.Products orderby p.ProductName select p;"); var q2 = from p in db.Products orderby p.ProductName select p; foreach (var v in q2) ObjectDumper.Write(v); Console.WriteLine("Press enter to continue."); Console.ReadLine(); // BUG: This one throws a null reference for some reason. //Console.Clear(); //var q3 = from c in db.Customers // from o in c.Orders // where c.City == "London" select new { c, o }; //foreach (var v in q3) // ObjectDumper.Write(v); //Console.ReadLine(); Console.Clear(); Console.WriteLine("from p in db.Products where p.ProductID == 7 select p;"); var q4 = from p in db.Products where p.ProductID == 7 select p; foreach (var v in q4) ObjectDumper.Write(v); Console.WriteLine("Press enter to continue."); Console.ReadLine(); #if !SQLITE Console.Clear(); Console.WriteLine("from c in db.Customers from o in c.Orders where c.City == \"London\" select new { c, o };"); var q5 = from c in db.Customers from o in c.Orders where c.City == "London" select new { c, o }; foreach (var v in q4) ObjectDumper.Write(v); Console.WriteLine("Press enter to continue."); Console.ReadLine(); #endif #if !SQLITE Console.Clear(); Console.WriteLine("from o in db.Orders where o.Customer.City == \"London\" select new { c = o.Customer, o };"); var q6 = from o in db.Orders where o.Customer.City == "London" select new { c = o.Customer, o }; foreach (var v in q4) ObjectDumper.Write(v); Console.WriteLine("Press enter to continue."); Console.ReadLine(); #endif Console.Clear(); Console.WriteLine("db.Orders"); foreach (var v in db.Orders) ObjectDumper.Write(v); Console.WriteLine("Press enter to continue."); Console.ReadLine(); #if !SQLITE // BUG: This currently will insert 3 rows when it should insert only 2 // SubmitChanges isn't clearing the client side transaction data Console.Clear(); Console.WriteLine("db.Orders.Add(new Order { ProductID = 7, CustomerID = 1, OrderDate = DateTime.Now });"); db.Orders.Add(new Order { EmployeeID = 1, CustomerID = "ALFKI", OrderDate = DateTime.Now }); db.SubmitChanges(); Console.WriteLine("db.Orders.Add(new Order { ProductID = 2, CustomerID = 2, OrderDate = DateTime.Now });"); db.Orders.Add(new Order { EmployeeID = 1, CustomerID = "ALFKI", OrderDate = DateTime.Now }); db.SubmitChanges(); foreach (var v in db.Orders) ObjectDumper.Write(v); Console.WriteLine("Press enter to continue."); Console.ReadLine(); #endif Console.Clear(); Console.WriteLine("db.Orders.Remove(db.Orders.First());"); db.Orders.DeleteOnSubmit(db.Orders.First()); db.SubmitChanges(); foreach (var v in db.Orders) ObjectDumper.Write(v); Console.WriteLine("Press enter to continue."); Console.ReadLine(); }
public void LinqToSqlSimultaneous02() { Console.WriteLine("YOU: ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~"); var product = db.Products.First(p => p.ProductID == 1); Console.WriteLine("~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~"); Console.WriteLine(); Console.WriteLine("OTHER USER: ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~"); // 打开与数据库的第二个连接以模拟 // 将要对 Products 表进行更改的另一个用户。 Northwind otherUser_db = new Northwind(connString) { Log = db.Log }; var otherUser_product = otherUser_db.Products.First(p => p.ProductID == 1); otherUser_product.UnitPrice = 999.99M; otherUser_db.SubmitChanges(); Console.WriteLine("~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~"); Console.WriteLine(); Console.WriteLine("YOU (continued): ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~"); product.UnitPrice = 777.77M; bool conflict = false; try { db.SubmitChanges(); } //OptimisticConcurrencyException catch (ChangeConflictException) { conflict = true; } Console.WriteLine(); if (conflict) { Console.WriteLine("* * * OPTIMISTIC CONCURRENCY EXCEPTION * * *"); Console.WriteLine("Another user has changed Product 1 since it was first requested."); Console.WriteLine("Backing out changes."); } else { Console.WriteLine("* * * COMMIT SUCCESSFUL * * *"); Console.WriteLine("Changes to Product 1 saved."); } Console.WriteLine("~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ "); Cleanup72(); // 还原以前的数据库状态 }
public void LinqToSqlInsert11() { // 通常,通过从其他层反序列化 // XML 获取要附加的实体。 // 此示例使用 LoadWith 在一个查询中预先加载客户和订单, //并禁用延迟加载。 Customer cust = null; using (Northwind tempdb = new Northwind(connString)) { DataLoadOptions shape = new DataLoadOptions(); shape.LoadWith<Customer>(c => c.Orders); // 加载第一个客户实体及其订单。 tempdb.LoadOptions = shape; tempdb.DeferredLoadingEnabled = false; cust = tempdb.Customers.First(x => x.CustomerID == "ALFKI"); } Console.WriteLine("Customer {0}'s original phone number {1}", cust.CustomerID, cust.Phone); Console.WriteLine(); foreach (Order o in cust.Orders) { Console.WriteLine("Customer {0} has order {1} for city {2}", o.CustomerID, o.OrderID, o.ShipCity); } Order orderA = cust.Orders.First(); Order orderB = cust.Orders.First(x => x.OrderID > orderA.OrderID); using (Northwind db2 = new Northwind(connString)) { // 将第一个实体附加到当前数据上下文,以跟踪更改。 db2.Customers.Attach(cust); // 附加相关订单以进行跟踪; 否则将在提交时插入它们。 db2.Orders.AttachAll(cust.Orders.ToList()); // 更新客户。 cust.Phone = "2345 5436"; // 更新第一个订单。 orderA.ShipCity = "Redmond"; // 移除第二个订单。 cust.Orders.Remove(orderB); // 创建一个新订单并将它添加到客户。 Order orderC = new Order() { ShipCity = "New York" }; Console.WriteLine("Adding new order"); cust.Orders.Add(orderC); //现在提交所有更改 db2.SubmitChanges(); } // 验证是否按预期应用了更改。 using (Northwind db3 = new Northwind(connString)) { Customer newCust = db3.Customers.First(x => x.CustomerID == "ALFKI"); Console.WriteLine("Customer {0}'s new phone number {1}", newCust.CustomerID, newCust.Phone); Console.WriteLine(); foreach (Order o in newCust.Orders) { Console.WriteLine("Customer {0} has order {1} for city {2}", o.CustomerID, o.OrderID, o.ShipCity); } } CleanupInsert11(); }
public void LinqToSqlInsert10() { // 通常,通过从其他层反序列化 XML 来获取要附加的实体。 // 不支持将实体从一个 DataContext 附加到另一个 DataContext。 // 因此若要复制反序列化实体的操作,将在此处重新创建这些实体。 Customer c1; List<Order> deserializedOrders = new List<Order>(); Customer deserializedC1; using (Northwind tempdb = new Northwind(connString)) { c1 = tempdb.Customers.Single(c => c.CustomerID == "ALFKI"); Console.WriteLine("Customer {0}'s original address {1}", c1.CustomerID, c1.Address); Console.WriteLine(); deserializedC1 = new Customer { Address = c1.Address, City = c1.City, CompanyName=c1.CompanyName, ContactName=c1.ContactName, ContactTitle=c1.ContactTitle, Country=c1.Country, CustomerID=c1.CustomerID, Fax=c1.Fax, Phone=c1.Phone, PostalCode=c1.PostalCode, Region=c1.Region}; Customer tempcust = tempdb.Customers.Single(c => c.CustomerID == "ANTON"); foreach (Order o in tempcust.Orders) { Console.WriteLine("Order {0} belongs to customer {1}", o.OrderID, o.CustomerID); deserializedOrders.Add(new Order {CustomerID=o.CustomerID, EmployeeID=o.EmployeeID, Freight=o.Freight, OrderDate=o.OrderDate, OrderID=o.OrderID, RequiredDate=o.RequiredDate, ShipAddress=o.ShipAddress, ShipCity=o.ShipCity, ShipName=o.ShipName, ShipCountry=o.ShipCountry, ShippedDate=o.ShippedDate, ShipPostalCode=o.ShipPostalCode, ShipRegion=o.ShipRegion, ShipVia=o.ShipVia}); } Console.WriteLine(); Customer tempcust2 = tempdb.Customers.Single(c => c.CustomerID == "CHOPS"); var c3Orders = tempcust2.Orders.ToList(); foreach (Order o in c3Orders) { Console.WriteLine("Order {0} belongs to customer {1}", o.OrderID, o.CustomerID); } Console.WriteLine(); } using (Northwind db2 = new Northwind(connString)) { // 将第一个实体附加到当前数据上下文,以跟踪更改。 db2.Customers.Attach(deserializedC1); Console.WriteLine("***** Update Customer ALFKI's address ******"); Console.WriteLine(); // 更改所跟踪的实体。 deserializedC1.Address = "123 First Ave"; // 附加订单列表中的所有实体。 db2.Orders.AttachAll(deserializedOrders); // 将订单更新为属于其他客户。 Console.WriteLine("****** Assign all Orders belong to ANTON to CHOPS ******"); Console.WriteLine(); foreach (Order o in deserializedOrders) { o.CustomerID = "CHOPS"; } // 在当前数据上下文中提交更改。 db2.SubmitChanges(); } // 检查是否像预期的那样提交了订单。 using (Northwind db3 = new Northwind(connString)) { Customer dbC1 = db3.Customers.Single(c => c.CustomerID == "ALFKI"); Console.WriteLine("Customer {0}'s new address {1}", dbC1.CustomerID, dbC1.Address); Console.WriteLine(); Customer dbC2 = db3.Customers.Single(c => c.CustomerID == "CHOPS"); foreach (Order o in dbC2.Orders) { Console.WriteLine("Order {0} belongs to customer {1}", o.OrderID, o.CustomerID); } } CleanupInsert10(); }
public void LinqToSqlAdo02() { var q = from p in db.Products where p.ProductID == 3 select p; Console.WriteLine("*** BEFORE ***"); ObjectDumper.Write(q); Console.WriteLine(); Console.WriteLine("*** INSERT ***"); // 创建标准 ADO.NET 连接: SqlConnection nwindConn = new SqlConnection(connString); nwindConn.Open(); // 使用预先存在的 ADO.NET 连接创建 DataContext: Northwind interop_db = new Northwind(nwindConn) { Log = db.Log }; SqlTransaction nwindTxn = nwindConn.BeginTransaction(); try { SqlCommand cmd = new SqlCommand("UPDATE Products SET QuantityPerUnit = 'single item' WHERE ProductID = 3"); cmd.Connection = nwindConn; cmd.Transaction = nwindTxn; cmd.ExecuteNonQuery(); // 共享预先存在的 ADO.NET 事务: //interop_db.LocalTransaction = nwindTxn; interop_db.Transaction = nwindTxn; Product prod1 = interop_db.Products.First(p => p.ProductID == 4); Product prod2 = interop_db.Products.First(p => p.ProductID == 5); prod1.UnitsInStock -= 3; prod2.UnitsInStock -= 5; // 错误: 这将使库存量变为负值 interop_db.SubmitChanges(); nwindTxn.Commit(); } catch (Exception e) { // 如果事务发生错误,则将回滚所有更改, // 包其中包括直接通过 ADO.NET 连接进行的任何更改 Console.WriteLine(e.Message); Console.WriteLine("Error submitting changes... all changes rolled back."); } nwindConn.Close(); Console.WriteLine(); Console.WriteLine("*** AFTER ***"); ClearDBCache(); ObjectDumper.Write(q); Cleanup112(); // 还原以前的数据库状态 }
private void ResetProducts() { ClearDBCache(); Northwind dbClean = new Northwind(connString); Product[] prod = new Product[4]; decimal[] price = new decimal[4] { 18.00M, 19.00M, 10.00M, 22.00M }; for (int i = 0; i < 4; i++) { prod[i] = dbClean.Products.First(p => p.ProductID == i + 1); prod[i].UnitPrice = price[i]; } prod[0].UnitsInStock = 39; prod[0].UnitsOnOrder = 0; dbClean.SubmitChanges(); }
public void LinqToSqlOptimistic04() { Northwind otherUser_db = new Northwind(connString); db.Log = null; Product product = db.Products.First(p => p.ProductID == 1); Console.WriteLine("You retrieve the product 1, it costs {0}", product.UnitPrice); Console.WriteLine("There are {0} units in stock, {1} units on order", product.UnitsInStock, product.UnitsOnOrder); Console.WriteLine(); Console.WriteLine("Another user changes the price to 22.22 and UnitsInStock to 22"); Product otherUser_product = otherUser_db.Products.First(p => p.ProductID == 1); otherUser_product.UnitPrice = 22.22M; otherUser_product.UnitsInStock = 22; otherUser_db.SubmitChanges(); Console.WriteLine("You set the price of product 1 to 1.01 and UnitsOnOrder to 11"); product.UnitPrice = 1.01M; product.UnitsOnOrder = 11; try { Console.WriteLine("You submit"); Console.WriteLine(); db.SubmitChanges(); } catch (ChangeConflictException) { WriteConflictDetails(db.ChangeConflicts); // write changed objects / members to console Console.WriteLine(); Console.WriteLine("Resolve by keeping changes"); db.ChangeConflicts.ResolveAll(RefreshMode.KeepChanges); db.SubmitChanges(); } Console.WriteLine(); Northwind dbResult = new Northwind(connString); Product result = dbResult.Products.First(p => p.ProductID == 1); Console.WriteLine("Now product 1 has price={0}, UnitsInStock={1}, UnitsOnOrder={2}", result.UnitPrice, result.UnitsInStock, result.UnitsOnOrder); Console.WriteLine(); ResetProducts(); // clean up }
public void LinqToSqlInsert10() { // Typically you would get entities to attach from deserializing XML from another tier. // It is not supported to attach entities from one DataContext to another DataContext. // So to duplicate deserializing the entities, the entities will be recreated here. Customer c1; List<Order> deserializedOrders = new List<Order>(); Customer deserializedC1; using (Northwind tempdb = new Northwind(connString)) { c1 = tempdb.Customers.Single(c => c.CustomerID == "ALFKI"); Console.WriteLine("Customer {0}'s original address {1}", c1.CustomerID, c1.Address); Console.WriteLine(); deserializedC1 = new Customer { Address = c1.Address, City = c1.City, CompanyName=c1.CompanyName, ContactName=c1.ContactName, ContactTitle=c1.ContactTitle, Country=c1.Country, CustomerID=c1.CustomerID, Fax=c1.Fax, Phone=c1.Phone, PostalCode=c1.PostalCode, Region=c1.Region}; Customer tempcust = tempdb.Customers.Single(c => c.CustomerID == "ANTON"); foreach (Order o in tempcust.Orders) { Console.WriteLine("Order {0} belongs to customer {1}", o.OrderID, o.CustomerID); deserializedOrders.Add(new Order {CustomerID=o.CustomerID, EmployeeID=o.EmployeeID, Freight=o.Freight, OrderDate=o.OrderDate, OrderID=o.OrderID, RequiredDate=o.RequiredDate, ShipAddress=o.ShipAddress, ShipCity=o.ShipCity, ShipName=o.ShipName, ShipCountry=o.ShipCountry, ShippedDate=o.ShippedDate, ShipPostalCode=o.ShipPostalCode, ShipRegion=o.ShipRegion, ShipVia=o.ShipVia}); } Console.WriteLine(); Customer tempcust2 = tempdb.Customers.Single(c => c.CustomerID == "CHOPS"); var c3Orders = tempcust2.Orders.ToList(); foreach (Order o in c3Orders) { Console.WriteLine("Order {0} belongs to customer {1}", o.OrderID, o.CustomerID); } Console.WriteLine(); } using (Northwind db2 = new Northwind(connString)) { // Attach the first entity to the current data context, to track changes. db2.Customers.Attach(deserializedC1); Console.WriteLine("***** Update Customer ALFKI's address ******"); Console.WriteLine(); // Change the entity that is tracked. deserializedC1.Address = "123 First Ave"; // Attach all entities in the orders list. db2.Orders.AttachAll(deserializedOrders); // Update the orders to belong to another customer. Console.WriteLine("****** Assign all Orders belong to ANTON to CHOPS ******"); Console.WriteLine(); foreach (Order o in deserializedOrders) { o.CustomerID = "CHOPS"; } // Submit the changes in the current data context. db2.SubmitChanges(); } // Check that the orders were submitted as expected. using (Northwind db3 = new Northwind(connString)) { Customer dbC1 = db3.Customers.Single(c => c.CustomerID == "ALFKI"); Console.WriteLine("Customer {0}'s new address {1}", dbC1.CustomerID, dbC1.Address); Console.WriteLine(); Customer dbC2 = db3.Customers.Single(c => c.CustomerID == "CHOPS"); foreach (Order o in dbC2.Orders) { Console.WriteLine("Order {0} belongs to customer {1}", o.OrderID, o.CustomerID); } } CleanupInsert10(); }
public void LinqToSqlOptimistic07() { db.Log = null; Northwind otherUser_db = new Northwind(connString); // you load 3 products Product[] prod = db.Products.OrderBy(p => p.ProductID).Take(3).ToArray(); for (int i = 0; i < 3; i++) { Console.WriteLine("You retrieve the product {0}, it costs {1}", i + 1, prod[i].UnitPrice); } // other user changes these products Product[] otherUserProd = otherUser_db.Products.OrderBy(p => p.ProductID).Take(3).ToArray(); for (int i = 0; i < 3; i++) { decimal otherPrice = (i + 1) * 111.11M; Console.WriteLine("Other user changes the price of product {0} to {1}", i + 1, otherPrice); otherUserProd[i].UnitPrice = otherPrice; } otherUser_db.SubmitChanges(); Console.WriteLine("Other user submitted changes"); // you change your loaded products for (int i = 0; i < 3; i++) { decimal yourPrice = (i + 1) * 1.01M; Console.WriteLine("You set the price of product {0} to {1}", i + 1, yourPrice); prod[i].UnitPrice = yourPrice; } // submit bool needsSubmit = true; while (needsSubmit) { try { Console.WriteLine("======= You submit with ContinueOnConflict ======="); needsSubmit = false; db.SubmitChanges(ConflictMode.ContinueOnConflict); } catch (ChangeConflictException) { foreach (ObjectChangeConflict conflict in db.ChangeConflicts) { DescribeConflict(conflict); // write changes to console Console.WriteLine("Resolve conflict with KeepCurrentValues"); conflict.Resolve(RefreshMode.KeepCurrentValues); } needsSubmit = true; } } Northwind dbResult = new Northwind(connString); for (int i = 0; i < 3; i++) { Product result = dbResult.Products.First(p => p.ProductID == i + 1); Console.WriteLine("Now the product {0} has price {1}", i + 1, result.UnitPrice); } ResetProducts(); // clean up }
private void CleanupInsert10() { int[] c2OrderIDs = { 10365, 10507, 10535, 10573, 10677, 10682, 10856 }; using (Northwind tempdb = new Northwind(connString)) { Customer c1 = tempdb.Customers.Single(c => c.CustomerID == "ALFKI"); c1.Address = "Obere Str. 57"; foreach (Order o in tempdb.Orders.Where(p => c2OrderIDs.Contains(p.OrderID))) o.CustomerID = "ANTON"; tempdb.SubmitChanges(); } }
static void Main(string[] args) { string programFiles = Environment.GetFolderPath(Environment.SpecialFolder.ProgramFiles); string databasePath = Path.Combine(programFiles, @"Firebird\Firebird_2_1\examples\nwind\NORTHWIND.FDB"); string connStr = string.Format("Server={0};Database={1};User={2};Password={3}", "localhost", databasePath, "sysdba", "masterkey"); Northwind db = new Northwind(new FbConnection(connStr)); // BUG: Fixes problem deleting orders below. var x = db.OrderDetails.First(); ObjectDumper.Write(x); Console.Clear(); Console.WriteLine("from p in db.Products orderby p.ProductName select p;"); var q2 = from p in db.Products orderby p.ProductName select p; foreach (var v in q2) ObjectDumper.Write(v); Console.WriteLine("Press enter to continue."); Console.ReadKey(); Console.Clear(); var q3 = from c in db.Customers from o in c.Orders where c.City == "London" select new { c, o }; foreach (var v in q3) ObjectDumper.Write(v); Console.WriteLine("Press enter to continue."); Console.ReadKey(); Console.Clear(); Console.WriteLine("from p in db.Products where p.ProductID == 7 select p;"); var q4 = from p in db.Products where p.ProductID == 7 select p; foreach (var v in q4) ObjectDumper.Write(v); Console.WriteLine("Press enter to continue."); Console.ReadKey(); Console.Clear(); Console.WriteLine("from c in db.Customers from o in c.Orders where c.City == \"London\" select new { c, o };"); var q5 = from c in db.Customers from o in c.Orders where c.City == "London" select new { c, o }; foreach (var v in q4) ObjectDumper.Write(v); Console.WriteLine("Press enter to continue."); Console.ReadKey(); Console.Clear(); Console.WriteLine("from o in db.Orders where o.Customer.City == \"London\" select new { c = o.Customer, o };"); var q6 = from o in db.Orders where o.Customer.City == "London" select new { c = o.Customer, o }; foreach (var v in q4) ObjectDumper.Write(v); Console.WriteLine("Press enter to continue."); Console.ReadKey(); Console.Clear(); Console.WriteLine("db.Orders"); foreach (var v in db.Orders) ObjectDumper.Write(v); Console.WriteLine("Press enter to continue."); Console.ReadKey(); // BUG: auto_increment columns aren't supported on Firebird SQL yet. Console.Clear(); Console.WriteLine("db.Orders.Add(new Order { ProductID = 7, CustomerId = 1, OrderDate = DateTime.Now });"); db.Orders.InsertOnSubmit(new Order { EmployeeID = 1, CustomerID = "ALFKI", OrderDate = DateTime.Now }); db.SubmitChanges(); Console.WriteLine("db.Orders.Add(new Order { ProductID = 2, CustomerId = 2, OrderDate = DateTime.Now });"); db.Orders.InsertOnSubmit(new Order { EmployeeID = 1, CustomerID = "ALFKI", OrderDate = DateTime.Now }); db.SubmitChanges(); foreach (var v in db.Orders) ObjectDumper.Write(v); Console.WriteLine("Press enter to continue."); Console.ReadKey(); Console.Clear(); Console.WriteLine("db.Orders.Remove(db.Orders.First());"); var order = db.Orders.First(); db.Orders.DeleteOnSubmit(order); db.SubmitChanges(); foreach (var v in db.Orders) ObjectDumper.Write(v); Console.WriteLine("Press enter to continue."); Console.ReadKey(); }
public void LinqToSqlInsert11() { // Typically you would get entities to attach from deserializing // XML from another tier. // This sample uses LoadWith to eager load customer and orders // in one query and disable deferred loading. Customer cust = null; using (Northwind tempdb = new Northwind(connString)) { DataLoadOptions shape = new DataLoadOptions(); shape.LoadWith<Customer>(c => c.Orders); // Load the first customer entity and its orders. tempdb.LoadOptions = shape; tempdb.DeferredLoadingEnabled = false; cust = tempdb.Customers.First(x => x.CustomerID == "ALFKI"); } Console.WriteLine("Customer {0}'s original phone number {1}", cust.CustomerID, cust.Phone); Console.WriteLine(); foreach (Order o in cust.Orders) { Console.WriteLine("Customer {0} has order {1} for city {2}", o.CustomerID, o.OrderID, o.ShipCity); } Order orderA = cust.Orders.First(); Order orderB = cust.Orders.First(x => x.OrderID > orderA.OrderID); using (Northwind db2 = new Northwind(connString)) { // Attach the first entity to the current data context, to track changes. db2.Customers.Attach(cust); // Attach the related orders for tracking; otherwise they will be inserted on submit. db2.Orders.AttachAll(cust.Orders.ToList()); // Update the customer. cust.Phone = "2345 5436"; // Update the first order. orderA.ShipCity = "Redmond"; // Remove the second order. cust.Orders.Remove(orderB); // Create a new order and add it to the customer. Order orderC = new Order() { ShipCity = "New York" }; Console.WriteLine("Adding new order"); cust.Orders.Add(orderC); //Now submit the all changes db2.SubmitChanges(); } // Verify that the changes were applied a expected. using (Northwind db3 = new Northwind(connString)) { Customer newCust = db3.Customers.First(x => x.CustomerID == "ALFKI"); Console.WriteLine("Customer {0}'s new phone number {1}", newCust.CustomerID, newCust.Phone); Console.WriteLine(); foreach (Order o in newCust.Orders) { Console.WriteLine("Customer {0} has order {1} for city {2}", o.CustomerID, o.OrderID, o.ShipCity); } } CleanupInsert11(); }