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)
{
string connStr = "Data Source=.\\SQLExpress;Integrated Security=True;Initial Catalog=Northwind";
Northwind db = new Northwind(new SqlConnection(connStr));
var res = from cust in db.Customers
select cust.CompanyName;
foreach (var r in res.ToList())
Console.WriteLine(r);
//var regions = db.Regions.ToList();
//Vendor.UseBulkInsert[db.Regions] = true;
//db.Regions.Add(new Region(-1, "tmp_region1"));
//db.Regions.Add(new Region(-2, "tmp_region2"));
//DbLinq.vendor.mssql.VendorMssql.UseBulkInsert[db.Shippers] = true;
//db.Shippers.Add(new Shippers(-1, "UPS", "800-800-8888"));
//db.Shippers.Add(new Shippers(-1, "Fedex", "900-900-9999"));
//db.SubmitChanges();
}
public static void Sample11(Northwind db)
{
// use Any() to determine if a collection has at least one element, or at least one element matches a condition
var q = from c in db.Customers
where c.Orders.Any()
select c;
ObjectDumper.Write(q, 0);
}
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 static void Sample12(Northwind db)
{
// use All() to determine if all elements of a collection match a condition (or that the collection is empty!)
var q = from c in db.Customers
where c.Orders.All(o => o.ShipCity == c.City)
select c;
ObjectDumper.Write(q, 0);
}
public static void Sample10(Northwind db)
{
// use group-by and aggregates like Min()/Max() to compute values over group partitions
var q = from p in db.Products
group p by p.CategoryID into g
select new {
Category = g.Key,
MinPrice = g.Min(p => p.UnitPrice),
MaxPrice = g.Max(p => p.UnitPrice)
};
ObjectDumper.Write(q, 1);
}
public void ClearDBCache()
{
// 创建一个新的 Northwind 对象，以空对象缓存从头开始
// 新 Northwind 对象将重复利用活动的 ADO.NET 连接
TextWriter oldLog;
if (db == null)
oldLog = null;
else
oldLog = db.Log;
db = new Northwind(connString) { Log = oldLog };
}
public void ClearDBCache()
{
// Creates a new Northwind object to start fresh with an empty object cache
// Active ADO.NET connection will be reused by new Northwind object
TextWriter oldLog;
if (db == null)
oldLog = null;
else
oldLog = db.Log;
db = new Northwind(connString) { Log = oldLog };
}
static void Main()
{
// The following assumes that:
// 1. SQL Server 2005 Express is installed on your machine
// 2. You install the Data Sample directory that contains Northwind.
// Or, if you have installed Northwind, you will need to alter the connection string to
// Northwind db = new Northwind("Server=.\\SQLExpress;Database=Northwind;Trusted_Connection=True");
// You must edit the path to point to the mdf file on your machine
// Northwind db = new Northwind("c:\\northwind\\northwnd.mdf");
Northwind db = new Northwind(connString);
db.Log = Console.Out;
Samples.Sample15(db);
Console.ReadLine();
}
static void Main()
{
// 编写以下代码时假定:
// 1. 计算机中已安装了 SQL Server 2005 Express
// 2. 安装含有 Northwind 的 Data Sample 目录。
// 或者，如果已安装 Northwind，则需要将连接字符串改为
// Northwind db = new Northwind("Server=.\\SQLExpress;Database=Northwind;Trusted_Connection=True");
// 必须编辑路径，使其指向您计算机上的 mdf 文件
// Northwind db = new Northwind("c:\\northwind\\northwnd.mdf");
string dbPath = Path.GetFullPath(Path.Combine(Application.StartupPath, @"..\..\..\Data\NORTHWND.MDF"));
string sqlServerInstance = @".\SQLEXPRESS";
string connString = "AttachDBFileName='" + dbPath + "';Server='" + sqlServerInstance + "';user instance=true;Integrated Security=SSPI; Pooling=false; Connection Timeout=60";
Northwind db = new Northwind(connString);
db.Log = Console.Out;
Samples.Sample1(db);
Console.ReadLine();
}
protected void cleanup(Northwind db)
{
try
{
// Get the name of the Order Details table properly evaluating the Annotation
string tableName = null;// db.Vendor.GetSqlFieldSafeName("order details"); //eg. "[Order Details]"
foreach (object obj in typeof(OrderDetail).GetCustomAttributes(true))
{
if (obj is System.Data.Linq.Mapping.TableAttribute)
{
tableName = ((System.Data.Linq.Mapping.TableAttribute)obj).Name;
}
}
string sql = string.Format("DELETE FROM {0} WHERE Quantity={1}", tableName, TestQuantity);
db.ExecuteCommand(sql);
}
catch (Exception)
{
}
}
public void LinqToSqlObject04() {
Northwind db2 = new Northwind(connString);
db2.Log = this.OutputStreamWriter;
DataLoadOptions ds = new DataLoadOptions();
ds.LoadWith<Customer>(p => p.Orders);
ds.LoadWith<Order>(p => p.OrderDetails);
ds.AssociateWith<Order>(p=>p.OrderDetails.OrderBy(o=>o.Quantity));
db2.LoadOptions = ds;
var custs = (
from c in db2.Customers
where c.City == "London"
select c );
foreach (var cust in custs) {
foreach (var ord in cust.Orders) {
foreach (var orderDetail in ord.OrderDetails) {
Console.WriteLine("CustomerID {0} has an OrderID {1} with ProductID {2} that has Quantity {3}.",
cust.CustomerID, ord.OrderID, orderDetail.ProductID, orderDetail.Quantity );
}
}
}
}
public void LinqToSqlObject07()
{
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.CategoryID < 3
select c);
foreach (var cat in q)
{
foreach (var prod in cat.Products)
{
Console.WriteLine("Category {0} has a ProductID {1} that Discontined = {2}.", cat.CategoryID, prod.ProductID, prod.Discontinued);
}
}
}
public void LinqToSqlAdo01() {
// Create a standard ADO.NET connection:
SqlConnection nwindConn = new SqlConnection(connString);
nwindConn.Open();
// ... other ADO.NET database access code ... //
// Use pre-existing ADO.NET connection to create DataContext:
Northwind interop_db = new Northwind(nwindConn) { Log = db.Log };
var orders =
from o in interop_db.Orders
where o.Freight > 500.00M
select o;
ObjectDumper.Write(orders);
nwindConn.Close();
}
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
}
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 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
}
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 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
}
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();
}
}
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 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();
}
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
}
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 void LinqToSqlExtensibility01()
{
var order = (from o in db.Orders
where o.OrderID == 10355
select o).First();
ObjectDumper.Write(order);
Console.WriteLine();
Console.WriteLine("***** Update Order to set ShipVia to 120 and submit changes ******");
Console.WriteLine();
order.ShipVia = 120;
try
{
db.SubmitChanges();
}
catch (Exception e)
{
Console.WriteLine("****** Catch exception throw by OnValidate() ******");
Console.WriteLine(e.Message);
}
Console.WriteLine();
Console.WriteLine("****** verify that order's ShipVia didn't get changed in db. ******");
Northwind db2 = new Northwind(connString);
var order2 = (from o in db2.Orders
where o.OrderID == 10355
select o).First();
ObjectDumper.Write(order2);
}
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
}
public Northwind CreateDB(System.Data.ConnectionState state)
{
CheckRecreateSqlite();
var conn = CreateConnection(connStr);
if (state == System.Data.ConnectionState.Open)
conn.Open();
var db = new Northwind(conn) { Log = Console.Out };
return db;
}
public void LinqToSqlObject02() {
Northwind db2 = new Northwind(connString);
db2.Log = this.OutputStreamWriter;
DataLoadOptions ds = new DataLoadOptions();
ds.LoadWith<nwind.Customer>(p => p.Orders);
db2.LoadOptions = ds;
var custs = (
from c in db2.Customers
where c.City == "Sao Paulo"
select c);
foreach (var cust in custs) {
foreach (var ord in cust.Orders) {
Console.WriteLine("CustomerID {0} has an OrderID {1}.", cust.CustomerID, ord.OrderID);
}
}
}
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 LinqToSqlObject03() {
Northwind db2 = new Northwind(connString);
db2.Log = this.OutputStreamWriter;
DataLoadOptions ds = new DataLoadOptions();
ds.AssociateWith<nwind.Customer>(p => p.Orders.Where(o=>o.ShipVia > 1));
db2.LoadOptions = ds;
var custs =
from c in db2.Customers
where c.City == "London"
select c;
foreach (var cust in custs) {
foreach (var ord in cust.Orders) {
foreach (var orderDetail in ord.OrderDetails) {
Console.WriteLine("CustomerID {0} has an OrderID {1} that ShipVia is {2} with ProductID {3} that has name {4}.",
cust.CustomerID, ord.OrderID, ord.ShipVia, orderDetail.ProductID, orderDetail.Product.ProductName);
}
}
}
}
