Esempio n. 1
0
        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);
        }
Esempio n. 2
0
        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();

        }
Esempio n. 3
0
 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);
 }
Esempio n. 4
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();
 }
Esempio n. 5
0
 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);
 }
Esempio n. 6
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);
 }
Esempio n. 7
0
        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 };
        }
Esempio n. 9
0
        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();
        }
Esempio n. 10
0
        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)
     {
     }
 }
Esempio n. 12
0
        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 );
                    }
                }
            }
        }
Esempio n. 13
0
        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);
                }
            }
        }
Esempio n. 14
0
        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();
        }
Esempio n. 15
0
        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
        }
Esempio n. 16
0
        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();

        }
Esempio n. 17
0
        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
        }
Esempio n. 18
0
        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
        }
Esempio n. 19
0
        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
        }
Esempio n. 20
0
 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();
     }
 }
Esempio n. 21
0
 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();
 }
Esempio n. 22
0
        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();
        }
Esempio n. 23
0
        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
        }
Esempio n. 24
0
        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();
            }
        }
Esempio n. 25
0
        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);

             
        }
Esempio n. 26
0
        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
        }
Esempio n. 27
0
 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;
 }
Esempio n. 28
0
        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);
                }
            }
        }
Esempio n. 29
0
        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();
        }
Esempio n. 30
0
        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);
                    }
                }
            }
        }