PageRenderTime 58ms CodeModel.GetById 20ms RepoModel.GetById 1ms app.codeStats 0ms

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

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