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();
}
