PageRenderTime 20ms CodeModel.GetById 2ms app.highlight 14ms RepoModel.GetById 1ms app.codeStats 0ms

/mcs/class/System.Data.Linq/src/DbLinq/Test/Providers/Linq_101_Samples/Insert_Update_Delete.cs

https://github.com/ztfuqingvip/mono
C# | 341 lines | 266 code | 62 blank | 13 comment | 43 complexity | 910b4bc60147cf081fb9ed8baf79ffd8 MD5 | raw file
  1using System;
  2using System.Collections.Generic;
  3using System.Linq;
  4using NUnit.Framework;
  5using Test_NUnit;
  6using Test_NUnit.Linq_101_Samples;
  7
  8#if MONO_STRICT
  9using System.Data.Linq;
 10#else
 11using DbLinq.Data.Linq;
 12#endif
 13
 14using nwind;
 15
 16// test ns Linq_101_Samples
 17#if MYSQL
 18    namespace Test_NUnit_MySql.Linq_101_Samples
 19#elif ORACLE && ODP
 20    namespace Test_NUnit_OracleODP.Linq_101_Samples
 21#elif ORACLE
 22    namespace Test_NUnit_Oracle.Linq_101_Samples
 23#elif POSTGRES
 24    namespace Test_NUnit_PostgreSql.Linq_101_Samples
 25#elif SQLITE
 26    namespace Test_NUnit_Sqlite.Linq_101_Samples
 27#elif INGRES
 28    namespace Test_NUnit_Ingres.Linq_101_Samples
 29#elif MSSQL && L2SQL
 30    namespace Test_NUnit_MsSql_Strict.Linq_101_Samples
 31#elif MSSQL
 32    namespace Test_NUnit_MsSql.Linq_101_Samples
 33#elif FIREBIRD
 34    namespace Test_NUnit_Firebird.Linq_101_Samples
 35#endif
 36{
 37    [TestFixture]
 38    public class Insert_Update_Delete : TestBase
 39    {
 40        [Linq101SamplesModified("Console and ObjectDummper references deleted")]
 41        [Test(Description = "Insert - Simple. This sample uses the Add method to add a new Customer to the Customers Table object. The call to SubmitChanges persists this new Customer to the database.")]
 42        public void LinqToSqlInsert01()
 43        {
 44            Northwind db = CreateDB();
 45
 46            Customer cust = db.Customers.FirstOrDefault(c => c.CustomerID == "MCSFT");
 47            if (cust != null)
 48            {
 49                try
 50                {
 51                    db.Customers.DeleteOnSubmit(cust);
 52                    db.SubmitChanges();
 53                }
 54                catch
 55                {
 56                    Assert.Ignore("Inconclusive: the object already exist. And the couldn't be removed");
 57                }
 58            }
 59
 60            var q = from c in db.Customers
 61                    where c.Region == "WA"
 62                    select c;
 63
 64            var newCustomer = new Customer
 65            {
 66                CustomerID = "MCSFT",
 67                CompanyName = "Microsoft",
 68                ContactName = "John Doe",
 69                ContactTitle = "Sales Manager",
 70                Address = "1 Microsoft Way",
 71                City = "Redmond",
 72                Region = "WA",
 73                PostalCode = "98052",
 74                Country = "USA",
 75                Phone = "(425) 555-1234",
 76                Fax = null
 77            };
 78
 79            db.Customers.InsertOnSubmit(newCustomer);
 80            db.SubmitChanges();
 81
 82            var reloadedCustomer = db.Customers.First(c => c.CustomerID == newCustomer.CustomerID);
 83
 84            Assert.AreEqual(reloadedCustomer.CompanyName, newCustomer.CompanyName);
 85            Assert.AreEqual(reloadedCustomer.ContactName, newCustomer.ContactName);
 86            Assert.AreEqual(reloadedCustomer.ContactTitle, newCustomer.ContactTitle);
 87            Assert.AreEqual(reloadedCustomer.Address, newCustomer.Address);
 88            Assert.AreEqual(reloadedCustomer.City, newCustomer.City);
 89            Assert.AreEqual(reloadedCustomer.Region, newCustomer.Region);
 90            Assert.AreEqual(reloadedCustomer.PostalCode, newCustomer.PostalCode);
 91            Assert.AreEqual(reloadedCustomer.Country, newCustomer.Country);
 92            Assert.AreEqual(reloadedCustomer.Phone, newCustomer.Phone);
 93            Assert.AreEqual(reloadedCustomer.Fax, newCustomer.Fax);
 94
 95            db.Customers.DeleteOnSubmit(reloadedCustomer);
 96            db.SubmitChanges();
 97        }
 98
 99#if !SQLITE
100#if !DEBUG && (POSTGRES || (MSSQL && !L2SQL))
101        [Explicit]
102#endif
103        [Linq101SamplesModified("Console and ObjectDummper references deleted")]
104        [Linq101SamplesModified("The original sample didn't compile, db2 Northwind context was used for nothing")]
105        [Test(Description = "Insert - 1-to-Many. This sample uses the Add method to add a new Category to the Categories table object, and a new Product to the Products Table object with a foreign key relationship to the new Category. The call to SubmitChanges persists these new objects and their relationships to the database.")]
106        public void LinqToSqlInsert02()
107        {
108            Northwind db = CreateDB();
109
110            var ds = new DataLoadOptions();
111
112            ds.LoadWith<Category>(c => c.Products);
113            db.LoadOptions = ds;
114
115            var q = from c in db.Categories
116                    where c.CategoryName == "Temp Widgets"
117                    select c;
118
119            var newCategory = new Category
120                                {
121                                    CategoryName = "Temp Widgets",
122                                    Description = "Widgets are the customer-facing analogues to sprockets and cogs."
123                                };
124
125            var newProduct = new Product
126            {
127                ProductName = "temp Blue Widget",
128                UnitPrice = 34.56m,
129                Category = newCategory
130            };
131            db.Categories.InsertOnSubmit(newCategory);
132            db.SubmitChanges();
133
134            var reloadedProduct = db.Products.First(p => p.ProductID == newProduct.ProductID);
135
136            Assert.AreEqual(reloadedProduct.ProductName, newProduct.ProductName);
137            Assert.AreEqual(reloadedProduct.UnitPrice, newProduct.UnitPrice);
138            Assert.AreEqual(reloadedProduct.Category.CategoryID, newProduct.CategoryID);
139
140            var reloadedCategory = reloadedProduct.Category;
141
142            Assert.AreEqual(reloadedCategory.CategoryName, newCategory.CategoryName);
143            Assert.AreEqual(reloadedCategory.Description, reloadedCategory.Description);
144
145            db.Products.DeleteOnSubmit(newProduct);
146            db.Categories.DeleteOnSubmit(newCategory);
147            db.SubmitChanges();
148        }
149#endif
150
151#if !DEBUG && (SQLITE || POSTGRES || (MSSQL && !L2SQL))
152        [Explicit]
153#endif
154        [Linq101SamplesModified("Console and ObjectDummper references deleted")]
155        [Linq101SamplesModified("The original sample didn't compile, db2 Northwind context was used for nothing")]
156        [Test(Description = "Insert - Many-to-Many. This sample uses the Add method to add a new Employee to the Employees table object, a new Territory to the Territories table object, and a new EmployeeTerritory to the EmployeeTerritories table object with foreign key relationships to the new Employee and Territory. The call to SubmitChanges persists these new objects and their relationships to the database.")]
157        public void LinqToSqlInsert03()
158        {
159            Northwind db = CreateDB();
160
161            var ds = new DataLoadOptions();
162            ds.LoadWith<Employee>(p => p.EmployeeTerritories);
163            ds.LoadWith<EmployeeTerritory>(p => p.Territory);
164
165            db.LoadOptions = ds;
166            var q = from e in db.Employees where e.FirstName == "Nancy" select e;
167
168
169            if (db.Employees.Any(e => e.FirstName == "Test Kira" && e.LastName == "Test Smith"))
170                Assert.Ignore();
171
172
173            var newEmployee = new Employee { FirstName = "Test Kira", LastName = "Test Smith" };
174            var newTerritory = new Territory
175            {
176                TerritoryID = "12345",
177                TerritoryDescription = "Test Anytown",
178                Region = db.Regions.First()
179            };
180
181            var newEmployeeTerritory = new EmployeeTerritory { Employee = newEmployee, Territory = newTerritory };
182            db.Employees.InsertOnSubmit(newEmployee);
183            db.Territories.InsertOnSubmit(newTerritory);
184            db.EmployeeTerritories.InsertOnSubmit(newEmployeeTerritory);
185            db.SubmitChanges();
186
187            // cleanup
188            db.EmployeeTerritories.DeleteOnSubmit(newEmployeeTerritory);
189            db.Territories.DeleteOnSubmit(newTerritory);
190            db.Employees.DeleteOnSubmit(newEmployee);
191            db.SubmitChanges();
192        }
193
194        [Linq101SamplesModified("Console and ObjectDummper references deleted")]
195        [Test(Description = "Update - Simple. This sample uses SubmitChanges to persist an update made to a retrieved Customer object back to the database.")]
196        public void LinqToSqlInsert04()
197        {
198            Northwind db = CreateDB();
199
200            var q = from c in db.Customers
201                    where c.CustomerID == "ALFKI"
202                    select c;
203
204            Customer cust = (from c in db.Customers
205                             where c.CustomerID == "ALFKI"
206                             select c).First();
207
208            var oldContactTitle = cust.ContactTitle;
209            cust.ContactTitle = "Vice President";
210            db.SubmitChanges();
211
212            Customer reloadedCustomer = db.Customers.First(c => c.CustomerID == cust.CustomerID);
213            Assert.AreEqual(reloadedCustomer.ContactTitle, cust.ContactTitle);
214
215            // undo
216            reloadedCustomer.ContactTitle = oldContactTitle;
217            db.SubmitChanges();
218        }
219
220        [Linq101SamplesModified("Console and ObjectDummper references deleted")]
221        [Test(Description = "Update - Multiple. This sample uses SubmitChanges to persist updates made to multiple retrieved Product objects back to the database.")]
222        public void LinqToSqlInsert05()
223        {
224            Northwind db = CreateDB();
225
226            var q = from p in db.Products
227                    where p.CategoryID.Value == 1
228                    select p;
229
230            foreach (var p in q)
231                p.UnitPrice += 1.0m;
232
233            db.SubmitChanges();
234
235
236            var reloadedProducts = db.Products.Where(p => p.CategoryID.Value == 1);
237            IEnumerator<Product> original = q.GetEnumerator();
238            IEnumerator<Product> reloaded = reloadedProducts.GetEnumerator();
239
240            while (original.MoveNext() && reloaded.MoveNext())
241                Assert.AreEqual(original.Current.UnitPrice, reloaded.Current.UnitPrice);
242
243            Assert.AreEqual(original.MoveNext(), reloaded.MoveNext());
244
245            // undo
246            foreach (var p in q)
247                p.UnitPrice -= 1.0m;
248            db.SubmitChanges();
249        }
250
251
252        [Linq101SamplesModified("Console and ObjectDummper references deleted")]
253        [Test(Description = "Delete - Simple. This sample uses the Remove method to delete an OrderDetail from the OrderDetails Table object. The call to SubmitChanges persists this deletion to the database.")]
254        public void LinqToSqlInsert06()
255        {
256            Northwind db = CreateDB();
257
258            db.Connection.Open();
259            db.Transaction = db.Connection.BeginTransaction();
260            try
261            {
262                OrderDetail ode = db.OrderDetails.First();
263                decimal orderID = ode.OrderID;
264                decimal productID = ode.ProductID;
265
266
267                OrderDetail order = (from c in db.OrderDetails
268                                     where c.OrderID == orderID && c.ProductID == productID
269                                     select c).First();
270
271                //what happened to Table.Remove()?
272                //The Add and AddAll methods are now InsertOnSubmit and InsertAllOnSubmit. The Remove and RemoveAll are now DeleteOnSubmit and DeleteAllOnSubmit.
273                //http://blogs.vertigo.com/personal/petar/Blog/Lists/Posts/Post.aspx?List=9441ab3e%2Df290%2D4a5b%2Da591%2D49a8226de525&ID=3
274
275                db.OrderDetails.DeleteOnSubmit(order); //formerly Remove(order);
276                db.SubmitChanges();
277
278                Assert.IsFalse(db.OrderDetails.Any(od => od.OrderID == orderID && od.ProductID == productID));
279            }
280            finally
281            {
282                db.Transaction.Rollback();
283                db.Transaction = null;
284            }
285        }
286
287        [SetUp]
288        public void Setup_LinqToSqlInsert07()
289        {
290            //Northwind db = CreateDB();
291            //var o = new Order { CustomerID = "WARTH", Employee = db.Employees.First() };
292            //o.OrderDetails.Add(new OrderDetail { Discount = 0.1f, Quantity = 1, Product = db.Products.First(p => p.Discontinued) });
293            //o.OrderDetails.Add(new OrderDetail { Discount = 0.2f, Quantity = 1, Product = db.Products.First(p => !p.Discontinued) });
294            //db.Orders.InsertOnSubmit(o);
295            //db.SubmitChanges();
296        }
297
298        [Linq101SamplesModified("Console and ObjectDummper references deleted")]
299        [Test(Description = "Delete - One-to-Many. This sample uses the Remove method to delete an Order and Order Detail from the Order Details and Orders tables. First deleting Order Details and then deleting from Orders. The call to SubmitChanges persists this deletion to the database.")]
300        public void LinqToSqlInsert07()
301        {
302            Northwind db = CreateDB();
303
304            db.Connection.Open();
305            db.Transaction = db.Connection.BeginTransaction();
306            try
307            {
308                var orderDetails =
309                    from o in db.OrderDetails
310                    where o.Order.CustomerID == "WARTH"
311                    select o;
312
313                var order =
314                    (from o in db.Orders
315                     where o.CustomerID == "WARTH"
316                     select o).FirstOrDefault();
317
318                if (!orderDetails.Any() || order == null)
319                    Assert.Ignore("Preconditions");
320
321
322                foreach (var od in orderDetails)
323                {
324                    db.OrderDetails.DeleteOnSubmit(od); //formerly Remove(od);
325                }
326
327                db.Orders.DeleteOnSubmit(order); //formerly Remove(order);
328                db.SubmitChanges();
329
330                Assert.IsFalse(
331                    db.OrderDetails.Any(od => od.Order.Customer.CustomerID == "WARTH" && od.Order.EmployeeID == 3));
332                Assert.IsFalse(db.Orders.Any(ord => ord.OrderID == order.OrderID));
333            }
334            finally
335            {
336                db.Transaction.Rollback();
337                db.Transaction = null;
338            }
339        }
340    }
341}