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

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