|
0
|
1 using System;
|
|
|
2 using System.Linq;
|
|
|
3
|
|
|
4 using BLToolkit.Data;
|
|
|
5 using BLToolkit.Data.DataProvider;
|
|
|
6 using BLToolkit.Data.Linq;
|
|
|
7 using BLToolkit.Data.Sql.SqlProvider;
|
|
|
8 using BLToolkit.DataAccess;
|
|
|
9 using BLToolkit.Mapping;
|
|
|
10
|
|
|
11 using NUnit.Framework;
|
|
|
12
|
|
|
13 using Data.Linq;
|
|
|
14 using Data.Linq.Model;
|
|
|
15
|
|
|
16 #region ReSharper disable
|
|
|
17 // ReSharper disable ConvertToConstant.Local
|
|
|
18 #endregion
|
|
|
19
|
|
|
20 namespace Update
|
|
|
21 {
|
|
|
22 [TestFixture]
|
|
|
23 public class InsertTest : TestBase
|
|
|
24 {
|
|
|
25 [Test]
|
|
|
26 public void DistinctInsert1()
|
|
|
27 {
|
|
|
28 ForEachProvider(new[] { ProviderName.DB2, ProviderName.Informix, ProviderName.PostgreSQL, ProviderName.SQLite, ProviderName.Access }, db =>
|
|
|
29 {
|
|
|
30 try
|
|
|
31 {
|
|
|
32 db.Types.Delete(c => c.ID > 1000);
|
|
|
33
|
|
|
34 Assert.AreEqual(
|
|
|
35 Types.Select(_ => _.ID / 3).Distinct().Count(),
|
|
|
36 db
|
|
|
37 .Types
|
|
|
38 .Select(_ => Math.Floor(_.ID / 3.0))
|
|
|
39 .Distinct()
|
|
|
40 .Insert(db.Types, _ => new LinqDataTypes
|
|
|
41 {
|
|
|
42 ID = (int)(_ + 1001),
|
|
|
43 GuidValue = Sql.NewGuid(),
|
|
|
44 BoolValue = true
|
|
|
45 }));
|
|
|
46 }
|
|
|
47 finally
|
|
|
48 {
|
|
|
49 db.Types.Delete(c => c.ID > 1000);
|
|
|
50 }
|
|
|
51 });
|
|
|
52 }
|
|
|
53
|
|
|
54 [Test]
|
|
|
55 public void DistinctInsert2()
|
|
|
56 {
|
|
|
57 ForEachProvider(new[] { ProviderName.DB2, ProviderName.Informix, ProviderName.PostgreSQL, ProviderName.SQLite, ProviderName.Access }, db =>
|
|
|
58 {
|
|
|
59 try
|
|
|
60 {
|
|
|
61 db.Types.Delete(c => c.ID > 1000);
|
|
|
62
|
|
|
63 Assert.AreEqual(
|
|
|
64 Types.Select(_ => _.ID / 3).Distinct().Count(),
|
|
|
65 db.Types
|
|
|
66 .Select(_ => Math.Floor(_.ID / 3.0))
|
|
|
67 .Distinct()
|
|
|
68 .Into(db.Types)
|
|
|
69 .Value(t => t.ID, t => (int)(t + 1001))
|
|
|
70 .Value(t => t.GuidValue, t => Sql.NewGuid())
|
|
|
71 .Value(t => t.BoolValue, t => true)
|
|
|
72 .Insert());
|
|
|
73 }
|
|
|
74 finally
|
|
|
75 {
|
|
|
76 db.Types.Delete(c => c.ID > 1000);
|
|
|
77 }
|
|
|
78 });
|
|
|
79 }
|
|
|
80
|
|
|
81 [Test]
|
|
|
82 public void Insert1()
|
|
|
83 {
|
|
|
84 ForEachProvider(db =>
|
|
|
85 {
|
|
|
86 try
|
|
|
87 {
|
|
|
88 var id = 1001;
|
|
|
89
|
|
|
90 db.Child.Delete(c => c.ChildID > 1000);
|
|
|
91
|
|
|
92 Assert.AreEqual(1,
|
|
|
93 db.Child
|
|
|
94 .Insert(() => new Child
|
|
|
95 {
|
|
|
96 ParentID = 1,
|
|
|
97 ChildID = id
|
|
|
98 }));
|
|
|
99
|
|
|
100 Assert.AreEqual(1, db.Child.Count(c => c.ChildID == id));
|
|
|
101 }
|
|
|
102 finally
|
|
|
103 {
|
|
|
104 db.Child.Delete(c => c.ChildID > 1000);
|
|
|
105 }
|
|
|
106 });
|
|
|
107 }
|
|
|
108
|
|
|
109 [Test]
|
|
|
110 public void Insert2()
|
|
|
111 {
|
|
|
112 ForEachProvider(db =>
|
|
|
113 {
|
|
|
114 try
|
|
|
115 {
|
|
|
116 var id = 1001;
|
|
|
117
|
|
|
118 db.Child.Delete(c => c.ChildID > 1000);
|
|
|
119
|
|
|
120 Assert.AreEqual(1,
|
|
|
121 db
|
|
|
122 .Into(db.Child)
|
|
|
123 .Value(c => c.ParentID, () => 1)
|
|
|
124 .Value(c => c.ChildID, () => id)
|
|
|
125 .Insert());
|
|
|
126 Assert.AreEqual(1, db.Child.Count(c => c.ChildID == id));
|
|
|
127 }
|
|
|
128 finally
|
|
|
129 {
|
|
|
130 db.Child.Delete(c => c.ChildID > 1000);
|
|
|
131 }
|
|
|
132 });
|
|
|
133 }
|
|
|
134
|
|
|
135 [Test]
|
|
|
136 public void Insert3()
|
|
|
137 {
|
|
|
138 ForEachProvider(db =>
|
|
|
139 {
|
|
|
140 try
|
|
|
141 {
|
|
|
142 var id = 1001;
|
|
|
143
|
|
|
144 db.Child.Delete(c => c.ChildID > 1000);
|
|
|
145
|
|
|
146 Assert.AreEqual(1,
|
|
|
147 db.Child
|
|
|
148 .Where(c => c.ChildID == 11)
|
|
|
149 .Insert(db.Child, c => new Child
|
|
|
150 {
|
|
|
151 ParentID = c.ParentID,
|
|
|
152 ChildID = id
|
|
|
153 }));
|
|
|
154 Assert.AreEqual(1, db.Child.Count(c => c.ChildID == id));
|
|
|
155 }
|
|
|
156 finally
|
|
|
157 {
|
|
|
158 db.Child.Delete(c => c.ChildID > 1000);
|
|
|
159 }
|
|
|
160 });
|
|
|
161 }
|
|
|
162
|
|
|
163 [Test]
|
|
|
164 public void Insert31()
|
|
|
165 {
|
|
|
166 ForEachProvider(db =>
|
|
|
167 {
|
|
|
168 try
|
|
|
169 {
|
|
|
170 var id = 1001;
|
|
|
171
|
|
|
172 db.Child.Delete(c => c.ChildID > 1000);
|
|
|
173
|
|
|
174 Assert.AreEqual(1,
|
|
|
175 db.Child
|
|
|
176 .Where(c => c.ChildID == 11)
|
|
|
177 .Select(c => new Child
|
|
|
178 {
|
|
|
179 ParentID = c.ParentID,
|
|
|
180 ChildID = id
|
|
|
181 })
|
|
|
182 .Insert(db.Child, c => c));
|
|
|
183 Assert.AreEqual(1, db.Child.Count(c => c.ChildID == id));
|
|
|
184 }
|
|
|
185 finally
|
|
|
186 {
|
|
|
187 db.Child.Delete(c => c.ChildID > 1000);
|
|
|
188 }
|
|
|
189 });
|
|
|
190 }
|
|
|
191
|
|
|
192 [Test]
|
|
|
193 public void Insert4()
|
|
|
194 {
|
|
|
195 ForEachProvider(db =>
|
|
|
196 {
|
|
|
197 try
|
|
|
198 {
|
|
|
199 var id = 1001;
|
|
|
200
|
|
|
201 db.Child.Delete(c => c.ChildID > 1000);
|
|
|
202
|
|
|
203 Assert.AreEqual(1,
|
|
|
204 db.Child
|
|
|
205 .Where(c => c.ChildID == 11)
|
|
|
206 .Into(db.Child)
|
|
|
207 .Value(c => c.ParentID, c => c.ParentID)
|
|
|
208 .Value(c => c.ChildID, () => id)
|
|
|
209 .Insert());
|
|
|
210 Assert.AreEqual(1, db.Child.Count(c => c.ChildID == id));
|
|
|
211 }
|
|
|
212 finally
|
|
|
213 {
|
|
|
214 db.Child.Delete(c => c.ChildID > 1000);
|
|
|
215 }
|
|
|
216 });
|
|
|
217 }
|
|
|
218
|
|
|
219 [Test]
|
|
|
220 public void Insert5()
|
|
|
221 {
|
|
|
222 ForEachProvider(db =>
|
|
|
223 {
|
|
|
224 try
|
|
|
225 {
|
|
|
226 var id = 1001;
|
|
|
227
|
|
|
228 db.Child.Delete(c => c.ChildID > 1000);
|
|
|
229
|
|
|
230 Assert.AreEqual(1,
|
|
|
231 db.Child
|
|
|
232 .Where(c => c.ChildID == 11)
|
|
|
233 .Into(db.Child)
|
|
|
234 .Value(c => c.ParentID, c => c.ParentID)
|
|
|
235 .Value(c => c.ChildID, id)
|
|
|
236 .Insert());
|
|
|
237 Assert.AreEqual(1, db.Child.Count(c => c.ChildID == id));
|
|
|
238 }
|
|
|
239 finally
|
|
|
240 {
|
|
|
241 db.Child.Delete(c => c.ChildID > 1000);
|
|
|
242 }
|
|
|
243 });
|
|
|
244 }
|
|
|
245
|
|
|
246 [Test]
|
|
|
247 public void Insert6()
|
|
|
248 {
|
|
|
249 ForEachProvider(db =>
|
|
|
250 {
|
|
|
251 try
|
|
|
252 {
|
|
|
253 db.Parent.Delete(p => p.Value1 == 11);
|
|
|
254
|
|
|
255 Assert.AreEqual(1,
|
|
|
256 db.Child
|
|
|
257 .Where(c => c.ChildID == 11)
|
|
|
258 .Into(db.Parent)
|
|
|
259 .Value(p => p.ParentID, c => c.ParentID)
|
|
|
260 .Value(p => p.Value1, c => (int?)c.ChildID)
|
|
|
261 .Insert());
|
|
|
262 Assert.AreEqual(1, db.Parent.Count(p => p.Value1 == 11));
|
|
|
263 }
|
|
|
264 finally
|
|
|
265 {
|
|
|
266 db.Parent.Delete(p => p.Value1 == 11);
|
|
|
267 }
|
|
|
268 });
|
|
|
269 }
|
|
|
270
|
|
|
271 [Test]
|
|
|
272 public void Insert7()
|
|
|
273 {
|
|
|
274 ForEachProvider(db =>
|
|
|
275 {
|
|
|
276 try
|
|
|
277 {
|
|
|
278 var id = 1001;
|
|
|
279
|
|
|
280 db.Child.Delete(c => c.ChildID > 1000);
|
|
|
281
|
|
|
282 Assert.AreEqual(1,
|
|
|
283 db
|
|
|
284 .Child
|
|
|
285 .Value(c => c.ChildID, () => id)
|
|
|
286 .Value(c => c.ParentID, 1)
|
|
|
287 .Insert());
|
|
|
288 Assert.AreEqual(1, db.Child.Count(c => c.ChildID == id));
|
|
|
289 }
|
|
|
290 finally
|
|
|
291 {
|
|
|
292 db.Child.Delete(c => c.ChildID > 1000);
|
|
|
293 }
|
|
|
294 });
|
|
|
295 }
|
|
|
296
|
|
|
297 [Test]
|
|
|
298 public void Insert8()
|
|
|
299 {
|
|
|
300 ForEachProvider(db =>
|
|
|
301 {
|
|
|
302 try
|
|
|
303 {
|
|
|
304 var id = 1001;
|
|
|
305
|
|
|
306 db.Child.Delete(c => c.ChildID > 1000);
|
|
|
307
|
|
|
308 Assert.AreEqual(1,
|
|
|
309 db
|
|
|
310 .Child
|
|
|
311 .Value(c => c.ParentID, 1)
|
|
|
312 .Value(c => c.ChildID, () => id)
|
|
|
313 .Insert());
|
|
|
314 Assert.AreEqual(1, db.Child.Count(c => c.ChildID == id));
|
|
|
315 }
|
|
|
316 finally
|
|
|
317 {
|
|
|
318 db.Child.Delete(c => c.ChildID > 1000);
|
|
|
319 }
|
|
|
320 });
|
|
|
321 }
|
|
|
322
|
|
|
323 [Test]
|
|
|
324 public void Insert9()
|
|
|
325 {
|
|
|
326 ForEachProvider(db =>
|
|
|
327 {
|
|
|
328 try
|
|
|
329 {
|
|
|
330 var id = 1001;
|
|
|
331
|
|
|
332 db.Child. Delete(c => c.ParentID > 1000);
|
|
|
333 db.Parent.Delete(p => p.ParentID > 1000);
|
|
|
334
|
|
|
335 db.Insert(new Parent { ParentID = id, Value1 = id });
|
|
|
336
|
|
|
337 Assert.AreEqual(1,
|
|
|
338 db.Parent
|
|
|
339 .Where(p => p.ParentID == id)
|
|
|
340 .Insert(db.Child, p => new Child
|
|
|
341 {
|
|
|
342 ParentID = p.ParentID,
|
|
|
343 ChildID = p.ParentID,
|
|
|
344 }));
|
|
|
345 Assert.AreEqual(1, db.Child.Count(c => c.ParentID == id));
|
|
|
346 }
|
|
|
347 finally
|
|
|
348 {
|
|
|
349 db.Child. Delete(c => c.ParentID > 1000);
|
|
|
350 db.Parent.Delete(p => p.ParentID > 1000);
|
|
|
351 }
|
|
|
352 });
|
|
|
353 }
|
|
|
354
|
|
|
355 [TableName("LinqDataTypes")]
|
|
|
356 public class LinqDataTypesArrayTest
|
|
|
357 {
|
|
|
358 public int ID;
|
|
|
359 public decimal MoneyValue;
|
|
|
360 public DateTime DateTimeValue;
|
|
|
361 public bool BoolValue;
|
|
|
362 public Guid GuidValue;
|
|
|
363 public byte[] BinaryValue;
|
|
|
364 public short SmallIntValue;
|
|
|
365 }
|
|
|
366
|
|
|
367 [Test]
|
|
|
368 public void InsertArray1()
|
|
|
369 {
|
|
|
370 ForEachProvider(db =>
|
|
|
371 {
|
|
|
372 try
|
|
|
373 {
|
|
|
374 var types = db.GetTable<LinqDataTypesArrayTest>();
|
|
|
375
|
|
|
376 types.Delete(t => t.ID > 1000);
|
|
|
377 types.Insert(() => new LinqDataTypesArrayTest { ID = 1001, BoolValue = true, BinaryValue = null });
|
|
|
378
|
|
|
379 Assert.IsNull(types.Single(t => t.ID == 1001).BinaryValue);
|
|
|
380 }
|
|
|
381 finally
|
|
|
382 {
|
|
|
383 db.GetTable<LinqDataTypesArrayTest>().Delete(t => t.ID > 1000);
|
|
|
384 }
|
|
|
385 });
|
|
|
386 }
|
|
|
387
|
|
|
388 [Test]
|
|
|
389 public void InsertArray2()
|
|
|
390 {
|
|
|
391 ForEachProvider(db =>
|
|
|
392 {
|
|
|
393 try
|
|
|
394 {
|
|
|
395 var types = db.GetTable<LinqDataTypesArrayTest>();
|
|
|
396
|
|
|
397 types.Delete(t => t.ID > 1000);
|
|
|
398
|
|
|
399 byte[] arr = null;
|
|
|
400
|
|
|
401 types.Insert(() => new LinqDataTypesArrayTest { ID = 1001, BoolValue = true, BinaryValue = arr });
|
|
|
402
|
|
|
403 var res = types.Single(t => t.ID == 1001).BinaryValue;
|
|
|
404
|
|
|
405 Assert.IsNull(res);
|
|
|
406 }
|
|
|
407 finally
|
|
|
408 {
|
|
|
409 db.GetTable<LinqDataTypesArrayTest>().Delete(t => t.ID > 1000);
|
|
|
410 }
|
|
|
411 });
|
|
|
412 }
|
|
|
413
|
|
|
414 [Test]
|
|
|
415 public void InsertUnion1()
|
|
|
416 {
|
|
|
417 Child.Count();
|
|
|
418
|
|
|
419 ForEachProvider(
|
|
|
420 db =>
|
|
|
421 {
|
|
|
422 db.Parent.Delete(p => p.ParentID > 1000);
|
|
|
423
|
|
|
424 try
|
|
|
425 {
|
|
|
426 var q =
|
|
|
427 db.Child. Select(c => new Parent { ParentID = c.ParentID, Value1 = (int) Math.Floor(c.ChildID / 10.0) }).Union(
|
|
|
428 db.GrandChild.Select(c => new Parent { ParentID = c.ParentID ?? 0, Value1 = (int?)Math.Floor((c.GrandChildID ?? 0) / 100.0) }));
|
|
|
429
|
|
|
430 q.Insert(db.Parent, p => new Parent
|
|
|
431 {
|
|
|
432 ParentID = p.ParentID + 1000,
|
|
|
433 Value1 = p.Value1
|
|
|
434 });
|
|
|
435
|
|
|
436 Assert.AreEqual(
|
|
|
437 Child. Select(c => new { ParentID = c.ParentID }).Union(
|
|
|
438 GrandChild.Select(c => new { ParentID = c.ParentID ?? 0 })).Count(),
|
|
|
439 db.Parent.Count(c => c.ParentID > 1000));
|
|
|
440 }
|
|
|
441 finally
|
|
|
442 {
|
|
|
443 db.Parent.Delete(p => p.ParentID > 1000);
|
|
|
444 }
|
|
|
445 });
|
|
|
446 }
|
|
|
447
|
|
|
448 [Test]
|
|
|
449 public void InsertEnum1()
|
|
|
450 {
|
|
|
451 ForEachProvider(db =>
|
|
|
452 {
|
|
|
453 try
|
|
|
454 {
|
|
|
455 var id = 1001;
|
|
|
456
|
|
|
457 db.Parent4.Delete(_ => _.ParentID > 1000);
|
|
|
458
|
|
|
459 var p = new Parent4
|
|
|
460 {
|
|
|
461 ParentID = id,
|
|
|
462 Value1 = TypeValue.Value2
|
|
|
463 };
|
|
|
464
|
|
|
465 Assert.AreEqual(1,
|
|
|
466 db.Parent4
|
|
|
467 .Insert(() => new Parent4
|
|
|
468 {
|
|
|
469 ParentID = 1001,
|
|
|
470 Value1 = p.Value1
|
|
|
471 }));
|
|
|
472
|
|
|
473 Assert.AreEqual(1, db.Parent4.Count(_ => _.ParentID == id && _.Value1 == p.Value1));
|
|
|
474 }
|
|
|
475 finally
|
|
|
476 {
|
|
|
477 db.Parent4.Delete(_ => _.ParentID > 1000);
|
|
|
478 }
|
|
|
479 });
|
|
|
480 }
|
|
|
481
|
|
|
482 [Test]
|
|
|
483 public void InsertEnum2()
|
|
|
484 {
|
|
|
485 ForEachProvider(db =>
|
|
|
486 {
|
|
|
487 try
|
|
|
488 {
|
|
|
489 var id = 1001;
|
|
|
490
|
|
|
491 db.Parent4.Delete(_ => _.ParentID > 1000);
|
|
|
492
|
|
|
493 Assert.AreEqual(1,
|
|
|
494 db.Parent4
|
|
|
495 .Value(_ => _.ParentID, id)
|
|
|
496 .Value(_ => _.Value1, TypeValue.Value1)
|
|
|
497 .Insert());
|
|
|
498
|
|
|
499 Assert.AreEqual(1, db.Parent4.Count(_ => _.ParentID == id));
|
|
|
500 }
|
|
|
501 finally
|
|
|
502 {
|
|
|
503 db.Parent4.Delete(_ => _.ParentID > 1000);
|
|
|
504 }
|
|
|
505 });
|
|
|
506 }
|
|
|
507
|
|
|
508 [Test]
|
|
|
509 public void InsertEnum3()
|
|
|
510 {
|
|
|
511 ForEachProvider(db =>
|
|
|
512 {
|
|
|
513 try
|
|
|
514 {
|
|
|
515 var id = 1001;
|
|
|
516
|
|
|
517 db.Parent4.Delete(_ => _.ParentID > 1000);
|
|
|
518
|
|
|
519 Assert.AreEqual(1,
|
|
|
520 db.Parent4
|
|
|
521 .Value(_ => _.ParentID, id)
|
|
|
522 .Value(_ => _.Value1, () => TypeValue.Value1)
|
|
|
523 .Insert());
|
|
|
524
|
|
|
525 Assert.AreEqual(1, db.Parent4.Count(_ => _.ParentID == id));
|
|
|
526 }
|
|
|
527 finally
|
|
|
528 {
|
|
|
529 db.Parent4.Delete(_ => _.ParentID > 1000);
|
|
|
530 }
|
|
|
531 });
|
|
|
532 }
|
|
|
533
|
|
|
534 [Test]
|
|
|
535 public void InsertNull()
|
|
|
536 {
|
|
|
537 ForEachProvider(db =>
|
|
|
538 {
|
|
|
539 try
|
|
|
540 {
|
|
|
541 db.Parent.Delete(p => p.ParentID == 1001);
|
|
|
542
|
|
|
543 Assert.AreEqual(1,
|
|
|
544 db
|
|
|
545 .Into(db.Parent)
|
|
|
546 .Value(p => p.ParentID, 1001)
|
|
|
547 .Value(p => p.Value1, (int?)null)
|
|
|
548 .Insert());
|
|
|
549 Assert.AreEqual(1, db.Parent.Count(p => p.ParentID == 1001));
|
|
|
550 }
|
|
|
551 finally
|
|
|
552 {
|
|
|
553 db.Parent.Delete(p => p.Value1 == 1001);
|
|
|
554 }
|
|
|
555 });
|
|
|
556 }
|
|
|
557
|
|
|
558 [Test]
|
|
|
559 public void InsertWithIdentity1()
|
|
|
560 {
|
|
|
561 ForEachProvider(db =>
|
|
|
562 {
|
|
|
563 try
|
|
|
564 {
|
|
|
565 db.Person.Delete(p => p.ID > 2);
|
|
|
566
|
|
|
567 var id =
|
|
|
568 db.Person
|
|
|
569 .InsertWithIdentity(() => new Person
|
|
|
570 {
|
|
|
571 FirstName = "John",
|
|
|
572 LastName = "Shepard",
|
|
|
573 Gender = Gender.Male
|
|
|
574 });
|
|
|
575
|
|
|
576 Assert.NotNull(id);
|
|
|
577
|
|
|
578 var john = db.Person.Single(p => p.FirstName == "John" && p.LastName == "Shepard");
|
|
|
579
|
|
|
580 Assert.NotNull (john);
|
|
|
581 Assert.AreEqual(id, john.ID);
|
|
|
582 }
|
|
|
583 finally
|
|
|
584 {
|
|
|
585 db.Person.Delete(p => p.ID > 2);
|
|
|
586 }
|
|
|
587 });
|
|
|
588 }
|
|
|
589
|
|
|
590 [Test]
|
|
|
591 public void InsertWithIdentity2()
|
|
|
592 {
|
|
|
593 ForEachProvider(db =>
|
|
|
594 {
|
|
|
595 try
|
|
|
596 {
|
|
|
597 db.Person.Delete(p => p.ID > 2);
|
|
|
598
|
|
|
599 var id = db
|
|
|
600 .Into(db.Person)
|
|
|
601 .Value(p => p.FirstName, () => "John")
|
|
|
602 .Value(p => p.LastName, () => "Shepard")
|
|
|
603 .Value(p => p.Gender, () => Gender.Male)
|
|
|
604 .InsertWithIdentity();
|
|
|
605
|
|
|
606 Assert.NotNull(id);
|
|
|
607
|
|
|
608 var john = db.Person.Single(p => p.FirstName == "John" && p.LastName == "Shepard");
|
|
|
609
|
|
|
610 Assert.NotNull (john);
|
|
|
611 Assert.AreEqual(id, john.ID);
|
|
|
612 }
|
|
|
613 finally
|
|
|
614 {
|
|
|
615 db.Person.Delete(p => p.ID > 2);
|
|
|
616 }
|
|
|
617 });
|
|
|
618 }
|
|
|
619
|
|
|
620 [Test]
|
|
|
621 public void InsertWithIdentity3()
|
|
|
622 {
|
|
|
623 ForEachProvider(db =>
|
|
|
624 {
|
|
|
625 try
|
|
|
626 {
|
|
|
627 db.Person.Delete(p => p.ID > 2);
|
|
|
628
|
|
|
629 var id = db
|
|
|
630 .Into(db.Person)
|
|
|
631 .Value(p => p.FirstName, "John")
|
|
|
632 .Value(p => p.LastName, "Shepard")
|
|
|
633 .Value(p => p.Gender, Gender.Male)
|
|
|
634 .InsertWithIdentity();
|
|
|
635
|
|
|
636 Assert.NotNull(id);
|
|
|
637
|
|
|
638 var john = db.Person.Single(p => p.FirstName == "John" && p.LastName == "Shepard");
|
|
|
639
|
|
|
640 Assert.NotNull (john);
|
|
|
641 Assert.AreEqual(id, john.ID);
|
|
|
642 }
|
|
|
643 finally
|
|
|
644 {
|
|
|
645 db.Person.Delete(p => p.ID > 2);
|
|
|
646 }
|
|
|
647 });
|
|
|
648 }
|
|
|
649
|
|
|
650 [Test]
|
|
|
651 public void InsertWithIdentity4()
|
|
|
652 {
|
|
|
653 ForEachProvider(db =>
|
|
|
654 {
|
|
|
655 try
|
|
|
656 {
|
|
|
657 for (var i = 0; i < 2; i++)
|
|
|
658 {
|
|
|
659 db.Person.Delete(p => p.ID > 2);
|
|
|
660
|
|
|
661 var id = db.InsertWithIdentity(
|
|
|
662 new Person
|
|
|
663 {
|
|
|
664 FirstName = "John" + i,
|
|
|
665 LastName = "Shepard",
|
|
|
666 Gender = Gender.Male
|
|
|
667 });
|
|
|
668
|
|
|
669 Assert.NotNull(id);
|
|
|
670
|
|
|
671 var john = db.Person.Single(p => p.FirstName == "John" + i && p.LastName == "Shepard");
|
|
|
672
|
|
|
673 Assert.NotNull (john);
|
|
|
674 Assert.AreEqual(id, john.ID);
|
|
|
675 }
|
|
|
676 }
|
|
|
677 finally
|
|
|
678 {
|
|
|
679 db.Person.Delete(p => p.ID > 2);
|
|
|
680 }
|
|
|
681 });
|
|
|
682 }
|
|
|
683
|
|
|
684 [Test]
|
|
|
685 public void InsertWithIdentity5()
|
|
|
686 {
|
|
|
687 ForEachProvider(db =>
|
|
|
688 {
|
|
|
689 try
|
|
|
690 {
|
|
|
691 for (var i = 0; i < 2; i++)
|
|
|
692 {
|
|
|
693 db.Person.Delete(p => p.ID > 2);
|
|
|
694
|
|
|
695 var person = new Person
|
|
|
696 {
|
|
|
697 FirstName = "John" + i,
|
|
|
698 LastName = "Shepard",
|
|
|
699 Gender = Gender.Male
|
|
|
700 };
|
|
|
701
|
|
|
702 var id = db.InsertWithIdentity(person);
|
|
|
703
|
|
|
704 Assert.NotNull(id);
|
|
|
705
|
|
|
706 var john = db.Person.Single(p => p.FirstName == "John" + i && p.LastName == "Shepard");
|
|
|
707
|
|
|
708 Assert.NotNull (john);
|
|
|
709 Assert.AreEqual(id, john.ID);
|
|
|
710 }
|
|
|
711 }
|
|
|
712 finally
|
|
|
713 {
|
|
|
714 db.Person.Delete(p => p.ID > 2);
|
|
|
715 }
|
|
|
716 });
|
|
|
717 }
|
|
|
718
|
|
|
719 [Test]
|
|
|
720 public void InsertOrUpdate1()
|
|
|
721 {
|
|
|
722 ForEachProvider(db =>
|
|
|
723 {
|
|
|
724 var id = 0;
|
|
|
725
|
|
|
726 try
|
|
|
727 {
|
|
|
728 id = Convert.ToInt32(db.Person.InsertWithIdentity(() => new Person
|
|
|
729 {
|
|
|
730 FirstName = "John",
|
|
|
731 LastName = "Shepard",
|
|
|
732 Gender = Gender.Male
|
|
|
733 }));
|
|
|
734
|
|
|
735 for (var i = 0; i < 3; i++)
|
|
|
736 {
|
|
|
737 db.Patient.InsertOrUpdate(
|
|
|
738 () => new Patient
|
|
|
739 {
|
|
|
740 PersonID = id,
|
|
|
741 Diagnosis = "abc",
|
|
|
742 },
|
|
|
743 p => new Patient
|
|
|
744 {
|
|
|
745 Diagnosis = (p.Diagnosis.Length + i).ToString(),
|
|
|
746 });
|
|
|
747 }
|
|
|
748
|
|
|
749 Assert.AreEqual("3", db.Patient.Single(p => p.PersonID == id).Diagnosis);
|
|
|
750 }
|
|
|
751 finally
|
|
|
752 {
|
|
|
753 db.Patient.Delete(p => p.PersonID == id);
|
|
|
754 db.Person. Delete(p => p.ID == id);
|
|
|
755 }
|
|
|
756 });
|
|
|
757 }
|
|
|
758
|
|
|
759 [Test]
|
|
|
760 public void InsertOrReplace1()
|
|
|
761 {
|
|
|
762 ForEachProvider(db =>
|
|
|
763 {
|
|
|
764 var id = 0;
|
|
|
765
|
|
|
766 try
|
|
|
767 {
|
|
|
768 id = Convert.ToInt32(db.Person.InsertWithIdentity(() => new Person
|
|
|
769 {
|
|
|
770 FirstName = "John",
|
|
|
771 LastName = "Shepard",
|
|
|
772 Gender = Gender.Male
|
|
|
773 }));
|
|
|
774
|
|
|
775 for (var i = 0; i < 3; i++)
|
|
|
776 {
|
|
|
777 db.InsertOrReplace(new Patient
|
|
|
778 {
|
|
|
779 PersonID = id,
|
|
|
780 Diagnosis = ("abc" + i).ToString(),
|
|
|
781 });
|
|
|
782 }
|
|
|
783
|
|
|
784 Assert.AreEqual("abc2", db.Patient.Single(p => p.PersonID == id).Diagnosis);
|
|
|
785 }
|
|
|
786 finally
|
|
|
787 {
|
|
|
788 db.Patient.Delete(p => p.PersonID == id);
|
|
|
789 db.Person. Delete(p => p.ID == id);
|
|
|
790 }
|
|
|
791 });
|
|
|
792 }
|
|
|
793
|
|
|
794 [Test]
|
|
|
795 public void InsertOrUpdate3()
|
|
|
796 {
|
|
|
797 ForEachProvider(db =>
|
|
|
798 {
|
|
|
799 var id = 0;
|
|
|
800
|
|
|
801 try
|
|
|
802 {
|
|
|
803 id = Convert.ToInt32(db.Person.InsertWithIdentity(() => new Person
|
|
|
804 {
|
|
|
805 FirstName = "John",
|
|
|
806 LastName = "Shepard",
|
|
|
807 Gender = Gender.Male
|
|
|
808 }));
|
|
|
809
|
|
|
810 var diagnosis = "abc";
|
|
|
811
|
|
|
812 for (var i = 0; i < 3; i++)
|
|
|
813 {
|
|
|
814 db.Patient.InsertOrUpdate(
|
|
|
815 () => new Patient
|
|
|
816 {
|
|
|
817 PersonID = id,
|
|
|
818 Diagnosis = "abc",
|
|
|
819 },
|
|
|
820 p => new Patient
|
|
|
821 {
|
|
|
822 Diagnosis = (p.Diagnosis.Length + i).ToString(),
|
|
|
823 },
|
|
|
824 () => new Patient
|
|
|
825 {
|
|
|
826 PersonID = id,
|
|
|
827 //Diagnosis = diagnosis,
|
|
|
828 });
|
|
|
829
|
|
|
830 diagnosis = (diagnosis.Length + i).ToString();
|
|
|
831 }
|
|
|
832
|
|
|
833 Assert.AreEqual("3", db.Patient.Single(p => p.PersonID == id).Diagnosis);
|
|
|
834 }
|
|
|
835 finally
|
|
|
836 {
|
|
|
837 db.Patient.Delete(p => p.PersonID == id);
|
|
|
838 db.Person. Delete(p => p.ID == id);
|
|
|
839 }
|
|
|
840 });
|
|
|
841 }
|
|
|
842
|
|
|
843 [Test]
|
|
|
844 public void InsertBatch1()
|
|
|
845 {
|
|
|
846 ForEachProvider(new[] { ProviderName.PostgreSQL }, db =>
|
|
|
847 {
|
|
|
848 if (db is DbManager && ((DbManager)db).ConfigurationString == "Oracle")
|
|
|
849 {
|
|
|
850 db.Types2.Delete(_ => _.ID > 1000);
|
|
|
851
|
|
|
852 ((DbManager)db).InsertBatch(1, new[]
|
|
|
853 {
|
|
|
854 new LinqDataTypes2 { ID = 1003, MoneyValue = 0m, DateTimeValue = null, BoolValue = true, GuidValue = new Guid("ef129165-6ffe-4df9-bb6b-bb16e413c883"), SmallIntValue = null, IntValue = null },
|
|
|
855 new LinqDataTypes2 { ID = 1004, MoneyValue = 0m, DateTimeValue = DateTime.Now, BoolValue = false, GuidValue = null, SmallIntValue = 2, IntValue = 1532334 },
|
|
|
856 });
|
|
|
857
|
|
|
858 db.Types2.Delete(_ => _.ID > 1000);
|
|
|
859 }
|
|
|
860 });
|
|
|
861 }
|
|
|
862
|
|
|
863 [Test]
|
|
|
864 public void InsertBatch2([IncludeDataContexts("Sql2008", "Sql2012")] string context)
|
|
|
865 {
|
|
|
866 using (var db = new TestDbManager(context))
|
|
|
867 {
|
|
|
868 db.Types2.Delete(_ => _.ID > 1000);
|
|
|
869
|
|
|
870 db.InsertBatch(100, new[]
|
|
|
871 {
|
|
|
872 new LinqDataTypes2 { ID = 1003, MoneyValue = 0m, DateTimeValue = null, BoolValue = true, GuidValue = new Guid("ef129165-6ffe-4df9-bb6b-bb16e413c883"), SmallIntValue = null, IntValue = null },
|
|
|
873 new LinqDataTypes2 { ID = 1004, MoneyValue = 0m, DateTimeValue = DateTime.Now, BoolValue = false, GuidValue = null, SmallIntValue = 2, IntValue = 1532334 },
|
|
|
874 });
|
|
|
875
|
|
|
876 db.Types2.Delete(_ => _.ID > 1000);
|
|
|
877 }
|
|
|
878 }
|
|
|
879
|
|
|
880 [TableName("Parent")]
|
|
|
881 public class NullableFieldTestObject
|
|
|
882 {
|
|
|
883 public int ParentID;
|
|
|
884 [BLToolkit.Mapping.Nullable] public int Value1;
|
|
|
885 }
|
|
|
886
|
|
|
887 [Test]
|
|
|
888 public void NullableFieldTest()
|
|
|
889 {
|
|
|
890 ForEachProvider(db =>
|
|
|
891 {
|
|
|
892 db.Parent.Delete(p => p.ParentID == 1100);
|
|
|
893
|
|
|
894 db.Insert(new NullableFieldTestObject { ParentID = 1100 });
|
|
|
895
|
|
|
896 var parent = db.Parent.Single(p => p.ParentID == 1100);
|
|
|
897
|
|
|
898 Assert.IsNull(parent.Value1);
|
|
|
899 });
|
|
|
900 }
|
|
|
901
|
|
|
902 public class FullName
|
|
|
903 {
|
|
|
904 public string FirstName { get; set; }
|
|
|
905 public string LastName;
|
|
|
906 [Nullable] public string MiddleName;
|
|
|
907 }
|
|
|
908
|
|
|
909 [TableName("Person")]
|
|
|
910 [MapField("FirstName", "Name.FirstName")]
|
|
|
911 [MapField("LastName", "Name.LastName")]
|
|
|
912 [MapField("MiddleName", "Name.MiddleName")]
|
|
|
913 public class TestPerson1
|
|
|
914 {
|
|
|
915 [Identity, PrimaryKey]
|
|
|
916 //[SequenceName("PostgreSQL", "Seq")]
|
|
|
917 [SequenceName("Firebird", "PersonID")]
|
|
|
918 [MapField("PersonID")]
|
|
|
919 public int ID;
|
|
|
920
|
|
|
921 public string Gender;
|
|
|
922
|
|
|
923 public FullName Name;
|
|
|
924 }
|
|
|
925
|
|
|
926 [Test]
|
|
|
927 public void Insert11()
|
|
|
928 {
|
|
|
929 var p = new TestPerson1 { Name = new FullName { FirstName = "fn", LastName = "ln" }, Gender = "M" };
|
|
|
930
|
|
|
931 ForEachProvider(db => db.Insert(p));
|
|
|
932 }
|
|
|
933
|
|
|
934 [Test]
|
|
|
935 public void Insert12()
|
|
|
936 {
|
|
|
937 ForEachProvider(db => db
|
|
|
938 .Into(db.GetTable<TestPerson1>())
|
|
|
939 .Value(_ => _.Name.FirstName, "FirstName")
|
|
|
940 .Value(_ => _.Name.LastName, () => "LastName")
|
|
|
941 .Value(_ => _.Gender, "F")
|
|
|
942 .Insert());
|
|
|
943
|
|
|
944 }
|
|
|
945
|
|
|
946 [Test]
|
|
|
947 public void Insert13()
|
|
|
948 {
|
|
|
949 ForEachProvider(db => db.GetTable<TestPerson1>()
|
|
|
950 .Insert(() => new TestPerson1
|
|
|
951 {
|
|
|
952 Name = new FullName
|
|
|
953 {
|
|
|
954 FirstName = "FirstName",
|
|
|
955 LastName = "LastName"
|
|
|
956 },
|
|
|
957 Gender = "M",
|
|
|
958 }));
|
|
|
959 }
|
|
|
960
|
|
|
961 [Test]
|
|
|
962 public void Insert14()
|
|
|
963 {
|
|
|
964 ForEachProvider(
|
|
|
965 new [] { ProviderName.SqlCe, ProviderName.Access, "Sql2000", "Sql2005", ProviderName.Sybase },
|
|
|
966 db =>
|
|
|
967 {
|
|
|
968 try
|
|
|
969 {
|
|
|
970 db.Person.Delete(p => p.FirstName.StartsWith("Insert14"));
|
|
|
971
|
|
|
972 Assert.AreEqual(1,
|
|
|
973 db.Person
|
|
|
974 .Insert(() => new Person
|
|
|
975 {
|
|
|
976 FirstName = "Insert14" + db.Person.Where(p => p.ID == 1).Select(p => p.FirstName).FirstOrDefault(),
|
|
|
977 LastName = "Shepard",
|
|
|
978 Gender = Gender.Male
|
|
|
979 }));
|
|
|
980
|
|
|
981 Assert.AreEqual(1, db.Person.Count(p => p.FirstName.StartsWith("Insert14")));
|
|
|
982 }
|
|
|
983 finally
|
|
|
984 {
|
|
|
985 db.Person.Delete(p => p.FirstName.StartsWith("Insert14"));
|
|
|
986 }
|
|
|
987 });
|
|
|
988 }
|
|
|
989
|
|
|
990 [Test]
|
|
|
991 public void InsertSingleIdentity()
|
|
|
992 {
|
|
|
993 ForEachProvider(
|
|
|
994 new [] { ProviderName.Informix, ProviderName.SqlCe },
|
|
|
995 db =>
|
|
|
996 {
|
|
|
997 try
|
|
|
998 {
|
|
|
999 db.TestIdentity.Delete();
|
|
|
1000
|
|
|
1001 var id = db.TestIdentity.InsertWithIdentity(() => new TestIdentity {});
|
|
|
1002
|
|
|
1003 Assert.NotNull(id);
|
|
|
1004 }
|
|
|
1005 finally
|
|
|
1006 {
|
|
|
1007 db.TestIdentity.Delete();
|
|
|
1008 }
|
|
|
1009 });
|
|
|
1010 }
|
|
|
1011 }
|
|
|
1012 }
|
