Ejemplo n.º 1
0
        public void ContainsJoin1(string context)
        {
            using (var db = new TestDataConnection(context))
            {
                db.Child. Delete(c => c.ParentID >= 1000);
                db.Parent.Delete(c => c.ParentID >= 1000);

                try
                {
                    var id = 1000;

                    db.Insert(new Parent { ParentID = id });

                    for (var i = 0; i < 3; i++)
                        db.Insert(new Child { ParentID = id, ChildID = 1000 + i });

                    var sql1 = ContainsJoin1Impl(db, new [] { 1000, 1001 });
                    var sql2 = ContainsJoin1Impl(db, new [] { 1002       });

                    Assert.That(sql1, Is.Not.EqualTo(sql2));
                }
                finally
                {
                    db.Child. Delete(c => c.ParentID >= 1000);
                    db.Parent.Delete(c => c.ParentID >= 1000);
                }
            }
        }
Ejemplo n.º 2
0
 public void Merge(string context)
 {
     using (var db = new TestDataConnection(context))
     {
         db.Merge(db.Types2);
     }
 }
Ejemplo n.º 3
0
 public void CompiledUpdate()
 {
     using (var ctx = new TestDataConnection())
     {
         _updateQuery(ctx, 12345, "54321");
     }
 }
Ejemplo n.º 4
0
		public void MergeWithEmptySource(string context)
		{
			using (var db = new TestDataConnection(context))
			{
				db.Merge(new Person[] {});
			}
		}
Ejemplo n.º 5
0
		public void AliasTest1()
		{
			using (var db = new TestDataConnection())
			{
				var count = db.GetTable<TestParent>().Count(t => t.ID > 0);
			}
		}
        public void Test()
        {
            using (var db = new TestDataConnection())
            {
                var q = (
                    from t1 in db.GetTable<Table1>()
                    where t1.Field3 != null
                    select new
                    {
                        t1.Ref1.Ref4.Field6, t1.Ref3.Field4,
                        Field1 = t1.Ref2.Ref5.Field8 ?? string.Empty
                    }
                ).Distinct();

                var sql1 = q.ToString();

                var q2 =
                    from t3 in q
                    group t3 by new { t3.Field6, t3.Field4 }
                    into g
                    where g.Count() > 1
                    select new { g.Key.Field6, EngineeringCircuitNumber = g.Key.Field4, Count = g.Count() };

                var sql2 = q2.ToString();

                var idx = sql2.IndexOf("DISTINCT");

                Assert.That(idx, Is.GreaterThanOrEqualTo(0));

                idx = sql2.IndexOf("Field8", idx);

                Assert.That(idx, Is.GreaterThanOrEqualTo(0));
            }
        }
		public void Test()
		{
			using (var db = new TestDataConnection())
			{
				/*
				var query =
					from t3 in db.Parent
					//let t1 = t3.Children.SelectMany(x => x.GrandChildren)
					//let t2 = t3.Table2s.SelectMany(x => x.Table1s)
					select new
					{
						//c2 = t1.Count(),
						c1 = t3.Children.SelectMany(x => x.GrandChildren),
					};
				*/

				var query =
					from t3 in db.GetTable<Table3>()
					let t1 = t3.Children.SelectMany(x => x.GrandChildren)
					//let t2 = t3.Children.SelectMany(x => x.GrandChildren)
					select new
					{
						c2 = t1.Count(),
						c1 = t3.Children.SelectMany(x => x.GrandChildren).Count(),
					};

				query.FirstOrDefault(p => p.c2 > 1);
				query.FirstOrDefault();
			}
		}
Ejemplo n.º 8
0
		public void MergeWithDeletePredicate2(string context)
		{
			using (var db = new TestDataConnection(context))
			{
				db.Merge(db.Types2, t => t.ID > 5);
			}
		}
Ejemplo n.º 9
0
 public void CompiledFunc2([IncludeDataContexts(ProviderName.SqlServer2008)] string context)
 {
     using (var db = new TestDataConnection(context))
     {
         var q = _f2(db, 1);
         q.ToList();
     }
 }
Ejemplo n.º 10
0
		public void MergeWithDeletePredicate3(string context)
		{
			using (var db = new TestDataConnection(context))
			{
				var patient = db.Patient.First();
				db.Merge(db.Person, t => t.Patient == patient);
			}
		}
Ejemplo n.º 11
0
 public void CompiledFunc2(string context)
 {
     using (var db = new TestDataConnection(context))
     {
         var q = _f2(db, 1);
         q.ToList();
     }
 }
Ejemplo n.º 12
0
		public void TestExecute2(string context)
		{
			using (var conn = new TestDataConnection(context))
			{
				var sql = conn.Person.Where(p => p.ID == 1).Select(p => p.Name).Take(1).ToString().Replace("-- Access", "");

				var res = conn.SetCommand(sql).ExecuteAsync<string>().Result;

				Assert.That(res, Is.EqualTo("John"));
			}
		}
Ejemplo n.º 13
0
		public void AliasTest2()
		{
			using (var db = new TestDataConnection())
			{
				db.GetTable<TestParent>()
					.Where(t => t.ID < 0 && t.ID > 0)
					.Update(t => new TestParent
					{
						ID = t.ID - 1
					});
			}
		}
Ejemplo n.º 14
0
		public void SubQueryCount(string context)
		{
			using (var db = new TestDataConnection(context))
			{
				AreEqual(
					from p in Parent
					select Parent.Where(t => t.ParentID == p.ParentID).Count()
					,
					from p in db.Parent
					select Sql.AsSql(db.GetParentByID(p.ParentID).Count()));
			}
		}
Ejemplo n.º 15
0
		public void GeneratePredicate()
		{
			Expression<Func<Person,bool>> a = x => x.FirstName == "John";
			Expression<Func<Person,bool>> b = x => x.LastName  == "Pupkin";

			var bBody     = b.GetBody(a.Parameters[0]);
			var predicate = Expression.Lambda<Func<Person,bool>>(Expression.AndAlso(a.Body, bBody), a.Parameters[0]);

			using (var db = new TestDataConnection())
			{
				var q = db.Person.Where(predicate);
				var p = q.First();
			}
		}
Ejemplo n.º 16
0
		public async void TestQueryToArray(string context)
		{
			using (var conn = new TestDataConnection(context))
			{
				var sql = conn.Person.Where(p => p.ID == 1).Select(p => p.Name).Take(1).ToString().Replace("-- Access", "");

				using (var rd = await conn.SetCommand(sql).ExecuteReaderAsync())
				{
					var list = await rd.QueryToArrayAsync<string>();

					Assert.That(list[0], Is.EqualTo("John"));
				}
			}
		}
Ejemplo n.º 17
0
		public void Test()
		{
			using (var db = new TestDataConnection())
			{
				var q = db.GetTable<CustomTableColumn>()
					.Select(
						x => new
						{
							DataType = Sql.AsSql(ColumnDataType.Unknown),
						});

				var sql = q.ToString();

				Assert.That(sql, Is.Not.Contains("Unknown"));
			}
		}
Ejemplo n.º 18
0
        public void IsDbGeneratedTest()
        {
            using (var db = new TestDataConnection())
            {
                db.BeginTransaction();

                var id = db.InsertWithIdentity(new L2SPersons
                {
                    FirstName = "Test",
                    LastName  = "Test",
                    Gender    = "M"
                });

                db.GetTable<L2SPersons>().Delete(p => p.PersonID == ConvertTo<int>.From(id));
            }
        }
Ejemplo n.º 19
0
        public void LetTest2()
        {
            using (var repository = new TestDataConnection())
            {
                var q =
                    from t1 in repository.GetTable<Table2>()
                    from t2 in
                        from t5 in t1.Ref3.Ref4.Ref1.Ref2
                        let  t3 = t1.Ref3
                        where t3.Ref5 == t5.Ref5
                        from t4 in t5.Ref9
                        select t4
                    select t1;

                var linqResult = q.ToString();
            }
        }
Ejemplo n.º 20
0
        public void MergeChar1(string context)
        {
            using (var db = new TestDataConnection(context))
            {
                var id = ConvertTo<int>.From(db.GetTable<AllType>().InsertWithIdentity(() => new AllType
                {
                    charDataType  = '\x0',
                    ncharDataType = "\x0"
                }));

                try
                {
                    db.Merge(db.GetTable<AllType>().Where(t => t.ID == id));
                }
                finally
                {
                    db.GetTable<AllType>().Delete(t => t.ID == id);
                }
            }
        }
Ejemplo n.º 21
0
		public void NoTransaction(string context)
		{
			using (var db = new TestDataConnection(context))
			{
				var list = new[]
				{
					new Parent { ParentID = 1111, Value1 = 1111 },
					new Parent { ParentID = 2111, Value1 = 2111 },
					new Parent { ParentID = 3111, Value1 = 3111 },
					new Parent { ParentID = 4111, Value1 = 4111 },
				};

				foreach (var parent in list)
					db.Parent.Delete(p => p.ParentID == parent.ParentID);

				db.BulkCopy(list);

				foreach (var parent in list)
					db.Parent.Delete(p => p.ParentID == parent.ParentID);
			}
		}
Ejemplo n.º 22
0
 public void MergeChar2(string context)
 {
     using (var db = new TestDataConnection(context))
     {
         try
         {
             db.Merge(new[]
             {
                 new AllType
                 {
                     ID            = 10,
                     charDataType  = '\x0',
                     ncharDataType = "\x0"
                 }
             });
         }
         finally
         {
             db.GetTable<AllType>().Delete(t => t.ID == 10);
         }
     }
 }
Ejemplo n.º 23
0
        public void Transaction([DataContexts(ExcludeLinqService=true)] string context)
        {
            using (var db = new TestDataConnection(context))
            {
                var list = new[]
                {
                    new Parent { ParentID = 1111, Value1 = 1111 },
                    new Parent { ParentID = 2111, Value1 = 2111 },
                    new Parent { ParentID = 3111, Value1 = 3111 },
                    new Parent { ParentID = 4111, Value1 = 4111 },
                };

                foreach (var parent in list)
                    db.Parent.Delete(p => p.ParentID == parent.ParentID);

                db.BeginTransaction();
                db.BulkCopy(list);
                db.CommitTransaction();

                foreach (var parent in list)
                    db.Parent.Delete(p => p.ParentID == parent.ParentID);
            }
        }
Ejemplo n.º 24
0
        public void AutoRollbackTransaction(string context)
        {
            using (var db = new TestDataConnection(context))
            {
                db.Insert(new Parent { ParentID = 1010, Value1 = 1010 });

                try
                {
                    using (db.BeginTransaction())
                    {
                        db.Parent.Update(t => t.ParentID == 1010, t => new Parent { Value1 = 1012 });
                    }

                    var p = db.Parent.First(t => t.ParentID == 1010);

                    Assert.That(p.Value1, Is.Not.EqualTo(1012));
                }
                finally
                {
                    db.Parent.Delete(t => t.ParentID >= 1000);
                }
            }
        }
Ejemplo n.º 25
0
        public void CommitTransaction()
        {
            using (var db = new TestDataConnection())
            {
                db.Insert(new Parent { ParentID = 1010, Value1 = 1010 });

                try
                {
                    using (var tr = db.BeginTransaction())
                    {
                        db.Parent.Update(t => t.ParentID == 1010, t => new Parent { Value1 = 1011 });
                        tr.Commit();
                    }

                    var p = db.Parent.First(t => t.ParentID == 1010);

                    Assert.That(p.Value1, Is.EqualTo(1011));
                }
                finally
                {
                    db.Parent.Delete(t => t.ParentID >= 1000);
                }
            }
        }
Ejemplo n.º 26
0
		public void SelectComplexField()
		{
			using (var db = new TestDataConnection())
			{
				var q =
					from p in db.GetTable<ComplexPerson>()
					select p.Name.LastName;

				var sql = q.ToString();

				Assert.That(sql.IndexOf("First"), Is.LessThan(0));
				Assert.That(sql.IndexOf("LastName"), Is.GreaterThan(0));
			}
		}
Ejemplo n.º 27
0
		public void SelectField()
		{
			using (var db = new TestDataConnection())
			{
				var q =
					from p in db.GetTable<TestParent>()
					select p.Value1_;

				var sql = q.ToString();

				Assert.That(sql.IndexOf("ParentID_"), Is.LessThan(0));
			}
		}
Ejemplo n.º 28
0
        static void RunScript(string configString, string divider, string name, Action <IDbConnection> action = null)
        {
            Console.WriteLine("=== " + name + " === \n");

            var text = File.ReadAllText(@"..\..\..\..\Data\Create Scripts\" + name + ".sql");

            while (true)
            {
                var idx = text.IndexOf("SKIP " + configString + " BEGIN");

                if (idx >= 0)
                {
                    text = text.Substring(0, idx) + text.Substring(text.IndexOf("SKIP " + configString + " END", idx));
                }
                else
                {
                    break;
                }
            }

            var cmds = text.Replace("\r", "").Replace(divider, "\x1").Split('\x1');

            Exception exception = null;

            using (var db = new TestDataConnection(configString))
            {
                foreach (var cmd in cmds)
                {
                    var command = cmd.Trim();

                    if (command.Length == 0)
                    {
                        continue;
                    }

                    try
                    {
                        Console.WriteLine(command);
                        db.Execute(command);
                        Console.WriteLine("\nOK\n");
                    }
                    catch (Exception ex)
                    {
                        if (command.TrimStart().StartsWith("DROP"))
                        {
                            Console.WriteLine("\nnot too OK\n");
                        }
                        else
                        {
                            Console.WriteLine(ex.Message);
                            Console.WriteLine("\nFAILED\n");

                            if (exception == null)
                            {
                                exception = ex;
                            }
                        }
                    }
                }

                if (exception != null)
                {
                    throw exception;
                }

                Console.WriteLine("\nBulkCopy LinqDataTypes\n");

                db.BulkCopy(
                    new LinqDataTypes {
                    ID = 1, MoneyValue = 1.11m, DateTimeValue = new DateTime(2001, 1, 11, 1, 11, 21, 100), BoolValue = true, GuidValue = new Guid("ef129165-6ffe-4df9-bb6b-bb16e413c883"), SmallIntValue = 1
                },
                    new LinqDataTypes {
                    ID = 2, MoneyValue = 2.49m, DateTimeValue = new DateTime(2005, 5, 15, 5, 15, 25, 500), BoolValue = false, GuidValue = new Guid("bc663a61-7b40-4681-ac38-f9aaf55b706b"), SmallIntValue = 2
                },
                    new LinqDataTypes {
                    ID = 3, MoneyValue = 3.99m, DateTimeValue = new DateTime(2009, 9, 19, 9, 19, 29, 90), BoolValue = true, GuidValue = new Guid("d2f970c0-35ac-4987-9cd5-5badb1757436"), SmallIntValue = 3
                },
                    new LinqDataTypes {
                    ID = 4, MoneyValue = 4.50m, DateTimeValue = new DateTime(2009, 9, 20, 9, 19, 29, 90), BoolValue = false, GuidValue = new Guid("40932fdb-1543-4e4a-ac2c-ca371604fb4b"), SmallIntValue = 4
                },
                    new LinqDataTypes {
                    ID = 5, MoneyValue = 5.50m, DateTimeValue = new DateTime(2009, 9, 21, 9, 19, 29, 90), BoolValue = true, GuidValue = new Guid("febe3eca-cb5f-40b2-ad39-2979d312afca"), SmallIntValue = 5
                },
                    new LinqDataTypes {
                    ID = 6, MoneyValue = 6.55m, DateTimeValue = new DateTime(2009, 9, 22, 9, 19, 29, 90), BoolValue = false, GuidValue = new Guid("8d3c5d1d-47db-4730-9fe7-968f6228a4c0"), SmallIntValue = 6
                },
                    new LinqDataTypes {
                    ID = 7, MoneyValue = 7.00m, DateTimeValue = new DateTime(2009, 9, 23, 9, 19, 29, 90), BoolValue = true, GuidValue = new Guid("48094115-83af-46dd-a906-bff26ee21ee2"), SmallIntValue = 7
                },
                    new LinqDataTypes {
                    ID = 8, MoneyValue = 8.99m, DateTimeValue = new DateTime(2009, 9, 24, 9, 19, 29, 90), BoolValue = false, GuidValue = new Guid("c1139f1f-1335-4cd4-937e-92602f732dd3"), SmallIntValue = 8
                },
                    new LinqDataTypes {
                    ID = 9, MoneyValue = 9.63m, DateTimeValue = new DateTime(2009, 9, 25, 9, 19, 29, 90), BoolValue = true, GuidValue = new Guid("46c5c512-3d4b-4cf7-b4e7-1de080789e5d"), SmallIntValue = 9
                },
                    new LinqDataTypes {
                    ID = 10, MoneyValue = 10.77m, DateTimeValue = new DateTime(2009, 9, 26, 9, 19, 29, 90), BoolValue = false, GuidValue = new Guid("61b2bc55-147f-4b40-93ed-a4aa83602fee"), SmallIntValue = 10
                },
                    new LinqDataTypes {
                    ID = 11, MoneyValue = 11.45m, DateTimeValue = new DateTime(2009, 9, 27, 9, 19, 29, 90), BoolValue = true, GuidValue = new Guid("d3021d18-97f0-4dc0-98d0-f0c7df4a1230"), SmallIntValue = 11
                },
                    new LinqDataTypes {
                    ID = 12, MoneyValue = 11.45m, DateTimeValue = new DateTime(2012, 11, 7, 19, 19, 29, 90), BoolValue = true, GuidValue = new Guid("03021d18-97f0-4dc0-98d0-f0c7df4a1230"), SmallIntValue = 12
                });

                Console.WriteLine("\nBulkCopy Parent\n");

                db.BulkCopy(
                    new Parent {
                    ParentID = 1, Value1 = 1
                },
                    new Parent {
                    ParentID = 2, Value1 = null
                },
                    new Parent {
                    ParentID = 3, Value1 = 3
                },
                    new Parent {
                    ParentID = 4, Value1 = null
                },
                    new Parent {
                    ParentID = 5, Value1 = 5
                },
                    new Parent {
                    ParentID = 6, Value1 = 6
                },
                    new Parent {
                    ParentID = 7, Value1 = 1
                });

                Console.WriteLine("\nBulkCopy Child\n");

                db.BulkCopy(
                    new Child {
                    ParentID = 1, ChildID = 11
                },
                    new Child {
                    ParentID = 2, ChildID = 21
                },
                    new Child {
                    ParentID = 2, ChildID = 22
                },
                    new Child {
                    ParentID = 3, ChildID = 31
                },
                    new Child {
                    ParentID = 3, ChildID = 32
                },
                    new Child {
                    ParentID = 3, ChildID = 33
                },
                    new Child {
                    ParentID = 4, ChildID = 41
                },
                    new Child {
                    ParentID = 4, ChildID = 42
                },
                    new Child {
                    ParentID = 4, ChildID = 43
                },
                    new Child {
                    ParentID = 4, ChildID = 44
                },
                    new Child {
                    ParentID = 6, ChildID = 61
                },
                    new Child {
                    ParentID = 6, ChildID = 62
                },
                    new Child {
                    ParentID = 6, ChildID = 63
                },
                    new Child {
                    ParentID = 6, ChildID = 64
                },
                    new Child {
                    ParentID = 6, ChildID = 65
                },
                    new Child {
                    ParentID = 6, ChildID = 66
                },
                    new Child {
                    ParentID = 7, ChildID = 77
                });

                Console.WriteLine("\nBulkCopy GrandChild\n");

                db.BulkCopy(
                    new GrandChild {
                    ParentID = 1, ChildID = 11, GrandChildID = 111
                },
                    new GrandChild {
                    ParentID = 2, ChildID = 21, GrandChildID = 211
                },
                    new GrandChild {
                    ParentID = 2, ChildID = 21, GrandChildID = 212
                },
                    new GrandChild {
                    ParentID = 2, ChildID = 22, GrandChildID = 221
                },
                    new GrandChild {
                    ParentID = 2, ChildID = 22, GrandChildID = 222
                },
                    new GrandChild {
                    ParentID = 3, ChildID = 31, GrandChildID = 311
                },
                    new GrandChild {
                    ParentID = 3, ChildID = 31, GrandChildID = 312
                },
                    new GrandChild {
                    ParentID = 3, ChildID = 31, GrandChildID = 313
                },
                    new GrandChild {
                    ParentID = 3, ChildID = 32, GrandChildID = 321
                },
                    new GrandChild {
                    ParentID = 3, ChildID = 32, GrandChildID = 322
                },
                    new GrandChild {
                    ParentID = 3, ChildID = 32, GrandChildID = 323
                },
                    new GrandChild {
                    ParentID = 3, ChildID = 33, GrandChildID = 331
                },
                    new GrandChild {
                    ParentID = 3, ChildID = 33, GrandChildID = 332
                },
                    new GrandChild {
                    ParentID = 3, ChildID = 33, GrandChildID = 333
                },
                    new GrandChild {
                    ParentID = 4, ChildID = 41, GrandChildID = 411
                },
                    new GrandChild {
                    ParentID = 4, ChildID = 41, GrandChildID = 412
                },
                    new GrandChild {
                    ParentID = 4, ChildID = 41, GrandChildID = 413
                },
                    new GrandChild {
                    ParentID = 4, ChildID = 41, GrandChildID = 414
                },
                    new GrandChild {
                    ParentID = 4, ChildID = 42, GrandChildID = 421
                },
                    new GrandChild {
                    ParentID = 4, ChildID = 42, GrandChildID = 422
                },
                    new GrandChild {
                    ParentID = 4, ChildID = 42, GrandChildID = 423
                },
                    new GrandChild {
                    ParentID = 4, ChildID = 42, GrandChildID = 424
                });

                if (action != null)
                {
                    action(db.Connection);
                }
            }
        }
Ejemplo n.º 29
0
 public ITable <Person> People2(TestDataConnection db)
 {
     return(db.GetTable <Person>());
 }
        public void TestMaterializedViewSchema([IncludeDataSources(TestProvName.AllPostgreSQL)] string context)
        {
            using (var db = new TestDataConnection(context))
            {
                var schema = db.DataProvider.GetSchemaProvider().GetSchema(db);

                var view = schema.Tables.FirstOrDefault(t => t.TableName == "Issue2023");

                if (context.Contains("9.2"))
                {
                    // test that schema load is not broken by materialized view support for old versions
                    Assert.IsNull(view);
                    return;
                }

                Assert.IsNotNull(view);

                Assert.That(view.ID, Is.EqualTo(view.CatalogName + ".public.Issue2023"));
                Assert.IsNotNull(view.CatalogName);
                Assert.AreEqual("public", view.SchemaName);
                Assert.AreEqual("Issue2023", view.TableName);
                Assert.AreEqual("This is the Issue2023 matview", view.Description);
                Assert.AreEqual(true, view.IsDefaultSchema);
                Assert.AreEqual(true, view.IsView);
                Assert.AreEqual(false, view.IsProcedureResult);
                Assert.AreEqual("Issue2023", view.TypeName);
                Assert.AreEqual(false, view.IsProviderSpecific);
                Assert.AreEqual(0, view.ForeignKeys.Count);
                Assert.AreEqual(5, view.Columns.Count);

                Assert.AreEqual("PersonID", view.Columns[0].ColumnName);
                Assert.AreEqual("integer", view.Columns[0].ColumnType);
                Assert.AreEqual(true, view.Columns[0].IsNullable);
                Assert.AreEqual(false, view.Columns[0].IsIdentity);
                Assert.AreEqual(false, view.Columns[0].IsPrimaryKey);
                Assert.AreEqual(-1, view.Columns[0].PrimaryKeyOrder);
                Assert.AreEqual("This is the Issue2023.PersonID column", view.Columns[0].Description);
                Assert.AreEqual("PersonID", view.Columns[0].MemberName);
                Assert.AreEqual("int?", view.Columns[0].MemberType);
                Assert.AreEqual(null, view.Columns[0].ProviderSpecificType);
                Assert.AreEqual(typeof(int), view.Columns[0].SystemType);
                Assert.AreEqual(DataType.Int32, view.Columns[0].DataType);
                Assert.AreEqual(true, view.Columns[0].SkipOnInsert);
                Assert.AreEqual(true, view.Columns[0].SkipOnUpdate);
                Assert.AreEqual(null, view.Columns[0].Length);
                // TODO: maybe we should fix it?
                Assert.AreEqual(32, view.Columns[0].Precision);
                Assert.AreEqual(0, view.Columns[0].Scale);
                Assert.AreEqual(view, view.Columns[0].Table);

                Assert.AreEqual("FirstName", view.Columns[1].ColumnName);
                Assert.AreEqual("character varying(50)", view.Columns[1].ColumnType);
                Assert.AreEqual(true, view.Columns[1].IsNullable);
                Assert.AreEqual(false, view.Columns[1].IsIdentity);
                Assert.AreEqual(false, view.Columns[1].IsPrimaryKey);
                Assert.AreEqual(-1, view.Columns[1].PrimaryKeyOrder);
                Assert.IsNull(view.Columns[1].Description);
                Assert.AreEqual("FirstName", view.Columns[1].MemberName);
                Assert.AreEqual("string", view.Columns[1].MemberType);
                Assert.AreEqual(null, view.Columns[1].ProviderSpecificType);
                Assert.AreEqual(typeof(string), view.Columns[1].SystemType);
                Assert.AreEqual(DataType.NVarChar, view.Columns[1].DataType);
                Assert.AreEqual(true, view.Columns[1].SkipOnInsert);
                Assert.AreEqual(true, view.Columns[1].SkipOnUpdate);
                Assert.AreEqual(50, view.Columns[1].Length);
                Assert.AreEqual(null, view.Columns[1].Precision);
                Assert.AreEqual(null, view.Columns[1].Scale);
                Assert.AreEqual(view, view.Columns[1].Table);

                Assert.AreEqual("LastName", view.Columns[2].ColumnName);
                Assert.AreEqual("character varying(50)", view.Columns[2].ColumnType);
                Assert.AreEqual(true, view.Columns[2].IsNullable);
                Assert.AreEqual(false, view.Columns[2].IsIdentity);
                Assert.AreEqual(false, view.Columns[2].IsPrimaryKey);
                Assert.AreEqual(-1, view.Columns[2].PrimaryKeyOrder);
                Assert.IsNull(view.Columns[2].Description);
                Assert.AreEqual("LastName", view.Columns[2].MemberName);
                Assert.AreEqual("string", view.Columns[2].MemberType);
                Assert.AreEqual(null, view.Columns[2].ProviderSpecificType);
                Assert.AreEqual(typeof(string), view.Columns[2].SystemType);
                Assert.AreEqual(DataType.NVarChar, view.Columns[2].DataType);
                Assert.AreEqual(true, view.Columns[2].SkipOnInsert);
                Assert.AreEqual(true, view.Columns[2].SkipOnUpdate);
                Assert.AreEqual(50, view.Columns[2].Length);
                Assert.AreEqual(null, view.Columns[2].Precision);
                Assert.AreEqual(null, view.Columns[2].Scale);
                Assert.AreEqual(view, view.Columns[2].Table);

                Assert.AreEqual("MiddleName", view.Columns[3].ColumnName);
                Assert.AreEqual("character varying(50)", view.Columns[3].ColumnType);
                Assert.AreEqual(true, view.Columns[3].IsNullable);
                Assert.AreEqual(false, view.Columns[3].IsIdentity);
                Assert.AreEqual(false, view.Columns[3].IsPrimaryKey);
                Assert.AreEqual(-1, view.Columns[3].PrimaryKeyOrder);
                Assert.IsNull(view.Columns[3].Description);
                Assert.AreEqual("MiddleName", view.Columns[3].MemberName);
                Assert.AreEqual("string", view.Columns[3].MemberType);
                Assert.AreEqual(null, view.Columns[3].ProviderSpecificType);
                Assert.AreEqual(typeof(string), view.Columns[3].SystemType);
                Assert.AreEqual(DataType.NVarChar, view.Columns[3].DataType);
                Assert.AreEqual(true, view.Columns[3].SkipOnInsert);
                Assert.AreEqual(true, view.Columns[3].SkipOnUpdate);
                Assert.AreEqual(50, view.Columns[3].Length);
                Assert.AreEqual(null, view.Columns[3].Precision);
                Assert.AreEqual(null, view.Columns[3].Scale);
                Assert.AreEqual(view, view.Columns[3].Table);

                Assert.AreEqual("Gender", view.Columns[4].ColumnName);
                Assert.AreEqual("character(1)", view.Columns[4].ColumnType);
                Assert.AreEqual(true, view.Columns[4].IsNullable);
                Assert.AreEqual(false, view.Columns[4].IsIdentity);
                Assert.AreEqual(false, view.Columns[4].IsPrimaryKey);
                Assert.AreEqual(-1, view.Columns[4].PrimaryKeyOrder);
                Assert.IsNull(view.Columns[4].Description);
                Assert.AreEqual("Gender", view.Columns[4].MemberName);
                Assert.AreEqual("char?", view.Columns[4].MemberType);
                Assert.AreEqual(null, view.Columns[4].ProviderSpecificType);
                Assert.AreEqual(typeof(char), view.Columns[4].SystemType);
                Assert.AreEqual(DataType.NChar, view.Columns[4].DataType);
                Assert.AreEqual(true, view.Columns[4].SkipOnInsert);
                Assert.AreEqual(true, view.Columns[4].SkipOnUpdate);
                Assert.AreEqual(1, view.Columns[4].Length);
                Assert.AreEqual(null, view.Columns[4].Precision);
                Assert.AreEqual(null, view.Columns[4].Scale);
                Assert.AreEqual(view, view.Columns[4].Table);
            }
        }
Ejemplo n.º 31
0
		public void InsertFromSelectManySourceQuery([MergeDataContextSource(false)] string context)
		{
			using (var db = new TestDataConnection(context))
			using (db.BeginTransaction())
			{
				// prepare test data
				db.GetTable<CrossJoinLeft>().Delete();
				db.GetTable<CrossJoinRight>().Delete();
				db.GetTable<CrossJoinResult>().Delete();

				db.Insert(new CrossJoinLeft()   { Id = 1 });
				db.Insert(new CrossJoinLeft()   { Id = 2 });
				db.Insert(new CrossJoinRight()  { Id = 10 });
				db.Insert(new CrossJoinRight()  { Id = 20 });
				db.Insert(new CrossJoinResult() { Id = 11, LeftId = 100, RightId = 200 });

				var source = db.GetTable<CrossJoinLeft>()
					.SelectMany(
						r => db.GetTable<CrossJoinRight>(),
						(t1, t2) =>
						 new
							 {
								 LeftId   = t1.Id,
								 RightId  = t2.Id,
								 ResultId = t1.Id + t2.Id
							 });

				var rows = db.GetTable<CrossJoinResult>()
					.Merge()
					.Using(source)
					.On((t, s) => t.Id == s.ResultId)
					.InsertWhenNotMatched(s => new CrossJoinResult()
					{
						Id      = s.ResultId,
						LeftId  = s.LeftId,
						RightId = s.RightId
					})
					.Merge();

				var result = db.GetTable<CrossJoinResult>().OrderBy(_ => _.Id).ToList();

				AssertRowCount(3, rows, context);

				Assert.AreEqual(4, result.Count);

				Assert.AreEqual(11, result[0].Id);
				Assert.AreEqual(100, result[0].LeftId);
				Assert.AreEqual(200, result[0].RightId);

				Assert.AreEqual(12, result[1].Id);
				Assert.AreEqual(2, result[1].LeftId);
				Assert.AreEqual(10, result[1].RightId);

				Assert.AreEqual(21, result[2].Id);
				Assert.AreEqual(1, result[2].LeftId);
				Assert.AreEqual(20, result[2].RightId);

				Assert.AreEqual(22, result[3].Id);
				Assert.AreEqual(2, result[3].LeftId);
				Assert.AreEqual(20, result[3].RightId);
			}
		}
Ejemplo n.º 32
0
 public void TestMono03(string context)
 {
     using (var db = new TestDataConnection(context))
         Assert.That(new GenericConcatQuery(db, new object[] { "A", 1 }).Query().ToList(), Is.Not.Null);
 }
Ejemplo n.º 33
0
        public void KeepIdentity_SkipOnInsertTrue(
            [DataSources(false)] string context,
            [Values(null, true, false)] bool?keepIdentity,
            [Values] BulkCopyType copyType)
        {
            // don't use transactions as some providers will fallback to non-provider-specific implementation then
            using (var db = new TestDataConnection(context))
                using (db.BeginTransaction())
                {
                    var lastId = db.InsertWithInt32Identity(new TestTable2());
                    try
                    {
                        var options = new BulkCopyOptions()
                        {
                            KeepIdentity = keepIdentity,
                            BulkCopyType = copyType
                        };

                        if (!Execute(context, perform, keepIdentity, copyType))
                        {
                            return;
                        }

                        var data = db.GetTable <TestTable2>().Where(_ => _.ID > lastId).OrderBy(_ => _.ID).ToArray();

                        Assert.AreEqual(2, data.Length);

                        // oracle supports identity insert only starting from version 12c, which is not used yet for tests
                        var useGenerated = keepIdentity != true ||
                                           context == ProviderName.Oracle ||
                                           context == ProviderName.OracleNative ||
                                           context == ProviderName.OracleManaged;

                        Assert.AreEqual(lastId + (!useGenerated ? 10 : 1), data[0].ID);
                        Assert.AreEqual(200, data[0].Value);
                        Assert.AreEqual(lastId + (!useGenerated ? 20 : 2), data[1].ID);
                        Assert.AreEqual(300, data[1].Value);

                        void perform()
                        {
                            db.BulkCopy(
                                options,
                                new[]
                            {
                                new TestTable2()
                                {
                                    ID    = lastId + 10,
                                    Value = 200
                                },
                                new TestTable2()
                                {
                                    ID    = lastId + 20,
                                    Value = 300
                                }
                            });
                        }
                    }
                    finally
                    {
                        // cleanup
                        db.GetTable <TestTable2>().Delete(_ => _.ID >= lastId);
                    }
                }
        }
Ejemplo n.º 34
0
        static void RunScript(string configString, string divider, string name, Action <IDbConnection> action = null)
        {
            Console.WriteLine("=== " + name + " === \n");

            var gas   = configString.Contains(".GAS");
            var ver73 = configString.Contains(".73");

            name += gas ? "GAS" : "";

            //var text = File.ReadAllText(@"..\..\..\..\Data\Create Scripts\" + name + (gas ? "GAS" : "") + ".sql");

            var scriptFolder = Path.Combine(Path.GetFullPath("."), "Database", "Create Scripts");

            Console.WriteLine("Script folder exists: {1}; {0}", scriptFolder, Directory.Exists(scriptFolder));

            var sqlFileName = Path.GetFullPath(Path.Combine(scriptFolder, Path.ChangeExtension(name, "sql")));

            Console.WriteLine("Sql file exists: {1}; {0}", sqlFileName, File.Exists(sqlFileName));

            var text = File.ReadAllText(sqlFileName);

            while (true)
            {
                var idx = text.IndexOf("SKIP " + configString + " BEGIN");

                if (idx >= 0)
                {
                    text = text.Substring(0, idx) + text.Substring(text.IndexOf("SKIP " + configString + " END", idx));
                }
                else
                {
                    break;
                }
            }

            var cmds = text.Replace("\r", "").Replace(divider, "\x1").Split('\x1');

            Exception exception = null;

            using (var db = new TestDataConnection(configString))
            {
                //db.CommandTimeout = 20;

                foreach (var cmd in cmds)
                {
                    var command = cmd.Trim();

                    if (command.Length == 0)
                    {
                        continue;
                    }

                    try
                    {
                        Console.WriteLine(command);
                        db.Execute(command);
                        Console.WriteLine("\nOK\n");
                    }
                    catch (Exception ex)
                    {
                        var actualCommand = command.Split('\n').FirstOrDefault(c => !string.IsNullOrWhiteSpace(c) && !c.TrimStart().StartsWith("--", StringComparison.CurrentCulture));
                        if (actualCommand.TrimStart().StartsWith("DROP", StringComparison.CurrentCultureIgnoreCase))
                        {
                            Console.WriteLine("\nnot too OK\n");
                        }
                        else
                        {
                            Console.WriteLine(ex.Message);
                            Console.WriteLine("\nFAILED\n");

                            if (exception == null)
                            {
                                exception = ex;
                            }
                        }
                    }
                }

                if (exception != null)
                {
                    throw exception;
                }

                Console.WriteLine("\nBulkCopy LinqDataTypes\n");

                var options = new BulkCopyOptions
                {
#if MONO
                    BulkCopyType = BulkCopyType.MultipleRows
#endif
                };


                db.BulkCopy(
                    options,
                    new[]
                {
                    new LinqDataTypes {
                        ID = 1, MoneyValue = 1.11m, DateTimeValue = new DateTime(2001, 1, 11, 1, 11, 21, 100), BoolValue = true, GuidValue = new Guid("ef129165-6ffe-4df9-bb6b-bb16e413c883"), SmallIntValue = 1
                    },
                    new LinqDataTypes {
                        ID = 2, MoneyValue = 2.49m, DateTimeValue = new DateTime(2005, 5, 15, 5, 15, 25, 500), BoolValue = false, GuidValue = new Guid("bc663a61-7b40-4681-ac38-f9aaf55b706b"), SmallIntValue = 2
                    },
                    new LinqDataTypes {
                        ID = 3, MoneyValue = 3.99m, DateTimeValue = new DateTime(2009, 9, 19, 9, 19, 29, 90), BoolValue = true, GuidValue = new Guid("d2f970c0-35ac-4987-9cd5-5badb1757436"), SmallIntValue = 3
                    },
                    new LinqDataTypes {
                        ID = 4, MoneyValue = 4.50m, DateTimeValue = new DateTime(2009, 9, 20, 9, 19, 29, 90), BoolValue = false, GuidValue = new Guid("40932fdb-1543-4e4a-ac2c-ca371604fb4b"), SmallIntValue = 4
                    },
                    new LinqDataTypes {
                        ID = 5, MoneyValue = 5.50m, DateTimeValue = new DateTime(2009, 9, 21, 9, 19, 29, 90), BoolValue = true, GuidValue = new Guid("febe3eca-cb5f-40b2-ad39-2979d312afca"), SmallIntValue = 5
                    },
                    new LinqDataTypes {
                        ID = 6, MoneyValue = 6.55m, DateTimeValue = new DateTime(2009, 9, 22, 9, 19, 29, 90), BoolValue = false, GuidValue = new Guid("8d3c5d1d-47db-4730-9fe7-968f6228a4c0"), SmallIntValue = 6
                    },
                    new LinqDataTypes {
                        ID = 7, MoneyValue = 7.00m, DateTimeValue = new DateTime(2009, 9, 23, 9, 19, 29, 90), BoolValue = true, GuidValue = new Guid("48094115-83af-46dd-a906-bff26ee21ee2"), SmallIntValue = 7
                    },
                    new LinqDataTypes {
                        ID = 8, MoneyValue = 8.99m, DateTimeValue = new DateTime(2009, 9, 24, 9, 19, 29, 90), BoolValue = false, GuidValue = new Guid("c1139f1f-1335-4cd4-937e-92602f732dd3"), SmallIntValue = 8
                    },
                    new LinqDataTypes {
                        ID = 9, MoneyValue = 9.63m, DateTimeValue = new DateTime(2009, 9, 25, 9, 19, 29, 90), BoolValue = true, GuidValue = new Guid("46c5c512-3d4b-4cf7-b4e7-1de080789e5d"), SmallIntValue = 9
                    },
                    new LinqDataTypes {
                        ID = 10, MoneyValue = 10.77m, DateTimeValue = new DateTime(2009, 9, 26, 9, 19, 29, 90), BoolValue = false, GuidValue = new Guid("61b2bc55-147f-4b40-93ed-a4aa83602fee"), SmallIntValue = 10
                    },
                    new LinqDataTypes {
                        ID = 11, MoneyValue = 11.45m, DateTimeValue = new DateTime(2009, 9, 27, 9, 19, 29, 90), BoolValue = true, GuidValue = new Guid("d3021d18-97f0-4dc0-98d0-f0c7df4a1230"), SmallIntValue = 11
                    },
                    new LinqDataTypes {
                        ID = 12, MoneyValue = 11.45m, DateTimeValue = new DateTime(2012, 11, 7, 19, 19, 29, 90), BoolValue = true, GuidValue = new Guid("03021d18-97f0-4dc0-98d0-f0c7df4a1230"), SmallIntValue = 12
                    }
                });

                Console.WriteLine("\nBulkCopy Parent\n");

                db.BulkCopy(
                    options,
                    new[]
                {
                    new Parent {
                        ParentID = 1, Value1 = 1
                    },
                    new Parent {
                        ParentID = 2, Value1 = null
                    },
                    new Parent {
                        ParentID = 3, Value1 = 3
                    },
                    new Parent {
                        ParentID = 4, Value1 = null
                    },
                    new Parent {
                        ParentID = 5, Value1 = 5
                    },
                    new Parent {
                        ParentID = 6, Value1 = 6
                    },
                    new Parent {
                        ParentID = 7, Value1 = 1
                    }
                });

                Console.WriteLine("\nBulkCopy Child\n");

                db.BulkCopy(
                    options,
                    new[]
                {
                    new Child {
                        ParentID = 1, ChildID = 11
                    },
                    new Child {
                        ParentID = 2, ChildID = 21
                    },
                    new Child {
                        ParentID = 2, ChildID = 22
                    },
                    new Child {
                        ParentID = 3, ChildID = 31
                    },
                    new Child {
                        ParentID = 3, ChildID = 32
                    },
                    new Child {
                        ParentID = 3, ChildID = 33
                    },
                    new Child {
                        ParentID = 4, ChildID = 41
                    },
                    new Child {
                        ParentID = 4, ChildID = 42
                    },
                    new Child {
                        ParentID = 4, ChildID = 43
                    },
                    new Child {
                        ParentID = 4, ChildID = 44
                    },
                    new Child {
                        ParentID = 6, ChildID = 61
                    },
                    new Child {
                        ParentID = 6, ChildID = 62
                    },
                    new Child {
                        ParentID = 6, ChildID = 63
                    },
                    new Child {
                        ParentID = 6, ChildID = 64
                    },
                    new Child {
                        ParentID = 6, ChildID = 65
                    },
                    new Child {
                        ParentID = 6, ChildID = 66
                    },
                    new Child {
                        ParentID = 7, ChildID = 77
                    }
                });

                Console.WriteLine("\nBulkCopy GrandChild\n");

                db.BulkCopy(
                    options,
                    new[]
                {
                    new GrandChild {
                        ParentID = 1, ChildID = 11, GrandChildID = 111
                    },
                    new GrandChild {
                        ParentID = 2, ChildID = 21, GrandChildID = 211
                    },
                    new GrandChild {
                        ParentID = 2, ChildID = 21, GrandChildID = 212
                    },
                    new GrandChild {
                        ParentID = 2, ChildID = 22, GrandChildID = 221
                    },
                    new GrandChild {
                        ParentID = 2, ChildID = 22, GrandChildID = 222
                    },
                    new GrandChild {
                        ParentID = 3, ChildID = 31, GrandChildID = 311
                    },
                    new GrandChild {
                        ParentID = 3, ChildID = 31, GrandChildID = 312
                    },
                    new GrandChild {
                        ParentID = 3, ChildID = 31, GrandChildID = 313
                    },
                    new GrandChild {
                        ParentID = 3, ChildID = 32, GrandChildID = 321
                    },
                    new GrandChild {
                        ParentID = 3, ChildID = 32, GrandChildID = 322
                    },
                    new GrandChild {
                        ParentID = 3, ChildID = 32, GrandChildID = 323
                    },
                    new GrandChild {
                        ParentID = 3, ChildID = 33, GrandChildID = 331
                    },
                    new GrandChild {
                        ParentID = 3, ChildID = 33, GrandChildID = 332
                    },
                    new GrandChild {
                        ParentID = 3, ChildID = 33, GrandChildID = 333
                    },
                    new GrandChild {
                        ParentID = 4, ChildID = 41, GrandChildID = 411
                    },
                    new GrandChild {
                        ParentID = 4, ChildID = 41, GrandChildID = 412
                    },
                    new GrandChild {
                        ParentID = 4, ChildID = 41, GrandChildID = 413
                    },
                    new GrandChild {
                        ParentID = 4, ChildID = 41, GrandChildID = 414
                    },
                    new GrandChild {
                        ParentID = 4, ChildID = 42, GrandChildID = 421
                    },
                    new GrandChild {
                        ParentID = 4, ChildID = 42, GrandChildID = 422
                    },
                    new GrandChild {
                        ParentID = 4, ChildID = 42, GrandChildID = 423
                    },
                    new GrandChild {
                        ParentID = 4, ChildID = 42, GrandChildID = 424
                    }
                });

                if (action != null)
                {
                    action(db.Connection);
                }
            }
        }
Ejemplo n.º 35
0
        static void RunScript(string configString, string divider, string name, Action <IDbConnection> action = null, string database = null)
        {
            Console.WriteLine("=== " + name + " === \n");

            var scriptFolder = Path.Combine(Path.GetFullPath("."), "Database", "Create Scripts");

            Console.WriteLine("Script folder exists: {1}; {0}", scriptFolder, Directory.Exists(scriptFolder));

            var sqlFileName = Path.GetFullPath(Path.Combine(scriptFolder, Path.ChangeExtension(name, "sql")));

            Console.WriteLine("Sql file exists: {1}; {0}", sqlFileName, File.Exists(sqlFileName));

            var text = File.ReadAllText(sqlFileName);

            while (true)
            {
                var idx = text.IndexOf("SKIP " + configString + " BEGIN");

                if (idx >= 0)
                {
                    text = text.Substring(0, idx) + text.Substring(text.IndexOf("SKIP " + configString + " END", idx));
                }
                else
                {
                    break;
                }
            }

            var cmds = text
                       .Replace("{DBNAME}", database)
                       .Replace("\r", "")
                       .Replace(divider, "\x1")
                       .Split('\x1')
                       .Select(c => c.Trim())
                       .Where(c => !string.IsNullOrEmpty(c))
                       .ToArray();

            if (DataConnection.TraceSwitch.TraceInfo)
            {
                Console.WriteLine("Commands count: {0}", cmds.Length);
            }

            Exception exception = null;

            using (var db = new TestDataConnection(configString))
            {
                //db.CommandTimeout = 20;

                foreach (var command in cmds)
                {
                    try
                    {
                        if (DataConnection.TraceSwitch.TraceInfo)
                        {
                            Console.WriteLine(command);
                        }

                        db.Execute(command);

                        if (DataConnection.TraceSwitch.TraceInfo)
                        {
                            Console.WriteLine("\nOK\n");
                        }
                    }
                    catch (Exception ex)
                    {
                        if (DataConnection.TraceSwitch.TraceError)
                        {
                            if (!DataConnection.TraceSwitch.TraceInfo)
                            {
                                Console.WriteLine(command);
                            }

                            var isDrop =
                                command.TrimStart().StartsWith("DROP") ||
                                command.TrimStart().StartsWith("CALL DROP");

#if APPVEYOR
                            if (!isDrop)
#endif
                            Console.WriteLine(ex.Message);

                            if (isDrop)
                            {
#if !APPVEYOR
                                Console.WriteLine("\nnot too OK\n");
#endif
                            }
                            else
                            {
                                Console.WriteLine("\nFAILED\n");

                                if (exception == null)
                                {
                                    exception = ex;
                                }
                            }
                        }
                    }
                }

                if (exception != null)
                {
                    throw exception;
                }

                if (DataConnection.TraceSwitch.TraceInfo)
                {
                    Console.WriteLine("\nBulkCopy LinqDataTypes\n");
                }

                var options = new BulkCopyOptions();

                db.BulkCopy(
                    options,
                    new []
                {
                    new LinqDataTypes2 {
                        ID = 1, MoneyValue = 1.11m, DateTimeValue = new DateTime(2001, 1, 11, 1, 11, 21, 100), BoolValue = true, GuidValue = new Guid("ef129165-6ffe-4df9-bb6b-bb16e413c883"), SmallIntValue = 1, StringValue = null, BigIntValue = 1
                    },
                    new LinqDataTypes2 {
                        ID = 2, MoneyValue = 2.49m, DateTimeValue = new DateTime(2005, 5, 15, 5, 15, 25, 500), BoolValue = false, GuidValue = new Guid("bc663a61-7b40-4681-ac38-f9aaf55b706b"), SmallIntValue = 2, StringValue = "", BigIntValue = 2
                    },
                    new LinqDataTypes2 {
                        ID = 3, MoneyValue = 3.99m, DateTimeValue = new DateTime(2009, 9, 19, 9, 19, 29, 90), BoolValue = true, GuidValue = new Guid("d2f970c0-35ac-4987-9cd5-5badb1757436"), SmallIntValue = 3, StringValue = "1"
                    },
                    new LinqDataTypes2 {
                        ID = 4, MoneyValue = 4.50m, DateTimeValue = new DateTime(2009, 9, 20, 9, 19, 29, 90), BoolValue = false, GuidValue = new Guid("40932fdb-1543-4e4a-ac2c-ca371604fb4b"), SmallIntValue = 4, StringValue = "2"
                    },
                    new LinqDataTypes2 {
                        ID = 5, MoneyValue = 5.50m, DateTimeValue = new DateTime(2009, 9, 20, 9, 19, 29, 90), BoolValue = true, GuidValue = new Guid("febe3eca-cb5f-40b2-ad39-2979d312afca"), SmallIntValue = 5, StringValue = "3"
                    },
                    new LinqDataTypes2 {
                        ID = 6, MoneyValue = 6.55m, DateTimeValue = new DateTime(2009, 9, 22, 9, 19, 29, 90), BoolValue = false, GuidValue = new Guid("8d3c5d1d-47db-4730-9fe7-968f6228a4c0"), SmallIntValue = 6, StringValue = "4"
                    },
                    new LinqDataTypes2 {
                        ID = 7, MoneyValue = 7.00m, DateTimeValue = new DateTime(2009, 9, 23, 9, 19, 29, 90), BoolValue = true, GuidValue = new Guid("48094115-83af-46dd-a906-bff26ee21ee2"), SmallIntValue = 7, StringValue = "5"
                    },
                    new LinqDataTypes2 {
                        ID = 8, MoneyValue = 8.99m, DateTimeValue = new DateTime(2009, 9, 24, 9, 19, 29, 90), BoolValue = false, GuidValue = new Guid("c1139f1f-1335-4cd4-937e-92602f732dd3"), SmallIntValue = 8, StringValue = "6"
                    },
                    new LinqDataTypes2 {
                        ID = 9, MoneyValue = 9.63m, DateTimeValue = new DateTime(2009, 9, 25, 9, 19, 29, 90), BoolValue = true, GuidValue = new Guid("46c5c512-3d4b-4cf7-b4e7-1de080789e5d"), SmallIntValue = 9, StringValue = "7"
                    },
                    new LinqDataTypes2 {
                        ID = 10, MoneyValue = 10.77m, DateTimeValue = new DateTime(2009, 9, 26, 9, 19, 29, 90), BoolValue = false, GuidValue = new Guid("61b2bc55-147f-4b40-93ed-a4aa83602fee"), SmallIntValue = 10, StringValue = "8"
                    },
                    new LinqDataTypes2 {
                        ID = 11, MoneyValue = 11.45m, DateTimeValue = new DateTime(2009, 9, 27, 0, 0, 0, 0), BoolValue = true, GuidValue = new Guid("d3021d18-97f0-4dc0-98d0-f0c7df4a1230"), SmallIntValue = 11, StringValue = "9"
                    },
                    new LinqDataTypes2 {
                        ID = 12, MoneyValue = 11.45m, DateTimeValue = new DateTime(2012, 11, 7, 19, 19, 29, 90), BoolValue = true, GuidValue = new Guid("03021d18-97f0-4dc0-98d0-f0c7df4a1230"), SmallIntValue = 12, StringValue = "0"
                    }
                });

                if (DataConnection.TraceSwitch.TraceInfo)
                {
                    Console.WriteLine("\nBulkCopy Parent\n");
                }

                db.BulkCopy(
                    options,
                    new []
                {
                    new Parent {
                        ParentID = 1, Value1 = 1
                    },
                    new Parent {
                        ParentID = 2, Value1 = null
                    },
                    new Parent {
                        ParentID = 3, Value1 = 3
                    },
                    new Parent {
                        ParentID = 4, Value1 = null
                    },
                    new Parent {
                        ParentID = 5, Value1 = 5
                    },
                    new Parent {
                        ParentID = 6, Value1 = 6
                    },
                    new Parent {
                        ParentID = 7, Value1 = 1
                    }
                });

                if (DataConnection.TraceSwitch.TraceInfo)
                {
                    Console.WriteLine("\nBulkCopy Child\n");
                }

                db.BulkCopy(
                    options,
                    new []
                {
                    new Child {
                        ParentID = 1, ChildID = 11
                    },
                    new Child {
                        ParentID = 2, ChildID = 21, IsActive = true
                    },
                    new Child {
                        ParentID = 2, ChildID = 22
                    },
                    new Child {
                        ParentID = 3, ChildID = 31, IsActive = true
                    },
                    new Child {
                        ParentID = 3, ChildID = 32, IsActive = true
                    },
                    new Child {
                        ParentID = 3, ChildID = 33
                    },
                    new Child {
                        ParentID = 4, ChildID = 41
                    },
                    new Child {
                        ParentID = 4, ChildID = 42
                    },
                    new Child {
                        ParentID = 4, ChildID = 43, IsActive = true
                    },
                    new Child {
                        ParentID = 4, ChildID = 44, IsActive = true
                    },
                    new Child {
                        ParentID = 6, ChildID = 61
                    },
                    new Child {
                        ParentID = 6, ChildID = 62
                    },
                    new Child {
                        ParentID = 6, ChildID = 63, IsActive = true
                    },
                    new Child {
                        ParentID = 6, ChildID = 64, IsActive = true
                    },
                    new Child {
                        ParentID = 6, ChildID = 65, IsActive = true
                    },
                    new Child {
                        ParentID = 6, ChildID = 66, IsActive = true
                    },
                    new Child {
                        ParentID = 7, ChildID = 77, IsActive = true
                    }
                });

                if (DataConnection.TraceSwitch.TraceInfo)
                {
                    Console.WriteLine("\nBulkCopy GrandChild\n");
                }

                db.BulkCopy(
                    options,
                    new []
                {
                    new GrandChild {
                        ParentID = 1, ChildID = 11, GrandChildID = 111
                    },
                    new GrandChild {
                        ParentID = 2, ChildID = 21, GrandChildID = 211
                    },
                    new GrandChild {
                        ParentID = 2, ChildID = 21, GrandChildID = 212
                    },
                    new GrandChild {
                        ParentID = 2, ChildID = 22, GrandChildID = 221
                    },
                    new GrandChild {
                        ParentID = 2, ChildID = 22, GrandChildID = 222
                    },
                    new GrandChild {
                        ParentID = 3, ChildID = 31, GrandChildID = 311
                    },
                    new GrandChild {
                        ParentID = 3, ChildID = 31, GrandChildID = 312
                    },
                    new GrandChild {
                        ParentID = 3, ChildID = 31, GrandChildID = 313
                    },
                    new GrandChild {
                        ParentID = 3, ChildID = 32, GrandChildID = 321
                    },
                    new GrandChild {
                        ParentID = 3, ChildID = 32, GrandChildID = 322
                    },
                    new GrandChild {
                        ParentID = 3, ChildID = 32, GrandChildID = 323
                    },
                    new GrandChild {
                        ParentID = 3, ChildID = 33, GrandChildID = 331
                    },
                    new GrandChild {
                        ParentID = 3, ChildID = 33, GrandChildID = 332
                    },
                    new GrandChild {
                        ParentID = 3, ChildID = 33, GrandChildID = 333
                    },
                    new GrandChild {
                        ParentID = 4, ChildID = 41, GrandChildID = 411
                    },
                    new GrandChild {
                        ParentID = 4, ChildID = 41, GrandChildID = 412
                    },
                    new GrandChild {
                        ParentID = 4, ChildID = 41, GrandChildID = 413
                    },
                    new GrandChild {
                        ParentID = 4, ChildID = 41, GrandChildID = 414
                    },
                    new GrandChild {
                        ParentID = 4, ChildID = 42, GrandChildID = 421
                    },
                    new GrandChild {
                        ParentID = 4, ChildID = 42, GrandChildID = 422
                    },
                    new GrandChild {
                        ParentID = 4, ChildID = 42, GrandChildID = 423
                    },
                    new GrandChild {
                        ParentID = 4, ChildID = 42, GrandChildID = 424
                    }
                });


                db.BulkCopy(
                    options,
                    new[]
                {
                    new InheritanceParent2 {
                        InheritanceParentId = 1, TypeDiscriminator = null, Name = null
                    },
                    new InheritanceParent2 {
                        InheritanceParentId = 2, TypeDiscriminator = 1, Name = null
                    },
                    new InheritanceParent2 {
                        InheritanceParentId = 3, TypeDiscriminator = 2, Name = "InheritanceParent2"
                    }
                });

                db.BulkCopy(
                    options,
                    new[]
                {
                    new InheritanceChild2()
                    {
                        InheritanceChildId = 1, TypeDiscriminator = null, InheritanceParentId = 1, Name = null
                    },
                    new InheritanceChild2()
                    {
                        InheritanceChildId = 2, TypeDiscriminator = 1, InheritanceParentId = 2, Name = null
                    },
                    new InheritanceChild2()
                    {
                        InheritanceChildId = 3, TypeDiscriminator = 2, InheritanceParentId = 3, Name = "InheritanceParent2"
                    }
                });

                action?.Invoke(db.Connection);
            }
        }
        public void TestParameters3(string context)
        {
            using (var db = new TestDataConnection(context))
            {
                PrepareData(db);

                var parameterValues = new
                {
                    Val1 = 1,
                    Val2 = 2,
                    Val3 = 3,
                    Val4 = 4,
                    Val5 = 5
                };

                var table = GetTarget(db);

                table
                .Merge()
                .Using(GetSource2(db)
                       .Where(_ => _.OtherId != parameterValues.Val5)
                       .Select(_ => new
                {
                    Id     = _.OtherId,
                    Field1 = _.OtherField1,
                    Field2 = _.OtherField2,
                    Field3 = _.OtherField3,
                    Field4 = _.OtherField4,
                    Field5 = _.OtherField5,
                    Field7 = parameterValues.Val2
                }))
                .On((t, s) => t.Id == s.Id)
                .InsertWhenNotMatchedAnd(
                    s => s.Field7 == parameterValues.Val1 + s.Id,
                    s => new TestMapping1()
                {
                    Id     = s.Id + parameterValues.Val5,
                    Field1 = s.Field1
                })
                .UpdateWhenMatchedAnd(
                    (t, s) => s.Id == parameterValues.Val3,
                    (t, s) => new TestMapping1()
                {
                    Field4 = parameterValues.Val5
                })
                .DeleteWhenMatchedAnd((t, s) => t.Field3 != parameterValues.Val2)
                .Merge();

                var parametersCount = 7;

                if (context == ProviderName.DB2)
                {
                    parametersCount = 1;
                }
                else if (context == ProviderName.Firebird || context == TestProvName.Firebird3)
                {
                    parametersCount = 3;
                }

                Assert.AreEqual(parametersCount, db.LastQuery.Count(_ => _ == GetParameterToken(context)));
            }
        }
Ejemplo n.º 37
0
        public void Issue200InUpdate(string context)
        {
            using (var db = new TestDataConnection(context))
                using (db.BeginTransaction())
                {
                    db.GetTable <AllTypes2>().Delete();

                    var dt  = DateTime.Now;
                    var dto = DateTimeOffset.Now;

                    var testData = new[]
                    {
                        new AllTypes2()
                        {
                            ID = 1,
                            datetimeoffsetDataType = dto,
                            datetime2DataType      = dt
                        },
                        new AllTypes2()
                        {
                            ID = 2,
                            datetimeoffsetDataType = dto.AddTicks(1),
                            datetime2DataType      = dt.AddTicks(1)
                        }
                    };

                    db.GetTable <AllTypes2>()
                    .Merge()
                    .Using(testData)
                    .OnTargetKey()
                    .InsertWhenNotMatched()
                    .Merge();

                    var dt2  = dt.AddTicks(3);
                    var dto2 = dto.AddTicks(3);
                    var cnt  = db.GetTable <AllTypes2>()
                               .Merge()
                               .Using(testData)
                               .On((t, s) => t.datetime2DataType == s.datetime2DataType &&
                                   t.datetimeoffsetDataType == s.datetimeoffsetDataType &&
                                   t.datetime2DataType == testData[0].datetime2DataType &&
                                   t.datetimeoffsetDataType == testData[0].datetimeoffsetDataType)
                               .UpdateWhenMatched((t, s) => new AllTypes2()
                    {
                        datetimeoffsetDataType = dto2,
                        datetime2DataType      = dt2
                    })
                               .Merge();

                    var result = db.GetTable <AllTypes2>().OrderBy(_ => _.ID).ToArray();

                    Assert.AreEqual(1, cnt);
                    Assert.AreEqual(2, result.Length);

                    Assert.AreEqual(testData[0].ID, result[0].ID);
                    Assert.AreEqual(dt2, result[0].datetime2DataType);
                    Assert.AreEqual(dto2, result[0].datetimeoffsetDataType);

                    Assert.AreEqual(testData[1].ID, result[1].ID);
                    Assert.AreEqual(testData[1].datetime2DataType, result[1].datetime2DataType);
                    Assert.AreEqual(testData[1].datetimeoffsetDataType, result[1].datetimeoffsetDataType);
                }
        }
Ejemplo n.º 38
0
		static void RunScript(string configString, string divider, string name, Action<IDbConnection> action = null)
		{
			Console.WriteLine("=== " + name + " === \n");

			var text = File.ReadAllText(@"Database\Create Scripts\" + name + ".sql");

			while (true)
			{
				var idx = text.IndexOf("SKIP " + configString + " BEGIN");

				if (idx >= 0)
					text = text.Substring(0, idx) + text.Substring(text.IndexOf("SKIP " + configString + " END", idx));
				else
					break;
			}

			var cmds = text.Replace("\r", "").Replace(divider, "\x1").Split('\x1');

			Exception exception = null;

			using (var db = new TestDataConnection(configString))
			{
				//db.CommandTimeout = 20;

				foreach (var cmd in cmds)
				{
					var command = cmd.Trim();

					if (command.Length == 0)
						continue;

					try 
					{
						Console.WriteLine(command);
						db.Execute(command);
						Console.WriteLine("\nOK\n");
					}
					catch (Exception ex)
					{
						if (command.TrimStart().StartsWith("DROP"))
							Console.WriteLine("\nnot too OK\n");
						else
						{
							Console.WriteLine(ex.Message);
							Console.WriteLine("\nFAILED\n");

							if (exception == null)
								exception = ex;
						}
					}
				}

				if (exception != null)
					throw exception;

				Console.WriteLine("\nBulkCopy LinqDataTypes\n");

				var options = new BulkCopyOptions
				{
#if MONO
					BulkCopyType = BulkCopyType.MultipleRows
#endif						
				};

				db.BulkCopy(
					options,
					new []
					{
						new LinqDataTypes { ID =  1, MoneyValue =  1.11m, DateTimeValue = new DateTime(2001,  1,  11,  1, 11, 21, 100), BoolValue = true,  GuidValue = new Guid("ef129165-6ffe-4df9-bb6b-bb16e413c883"), SmallIntValue =  1 },
						new LinqDataTypes { ID =  2, MoneyValue =  2.49m, DateTimeValue = new DateTime(2005,  5,  15,  5, 15, 25, 500), BoolValue = false, GuidValue = new Guid("bc663a61-7b40-4681-ac38-f9aaf55b706b"), SmallIntValue =  2 },
						new LinqDataTypes { ID =  3, MoneyValue =  3.99m, DateTimeValue = new DateTime(2009,  9,  19,  9, 19, 29,  90), BoolValue = true,  GuidValue = new Guid("d2f970c0-35ac-4987-9cd5-5badb1757436"), SmallIntValue =  3 },
						new LinqDataTypes { ID =  4, MoneyValue =  4.50m, DateTimeValue = new DateTime(2009,  9,  20,  9, 19, 29,  90), BoolValue = false, GuidValue = new Guid("40932fdb-1543-4e4a-ac2c-ca371604fb4b"), SmallIntValue =  4 },
						new LinqDataTypes { ID =  5, MoneyValue =  5.50m, DateTimeValue = new DateTime(2009,  9,  20,  9, 19, 29,  90), BoolValue = true,  GuidValue = new Guid("febe3eca-cb5f-40b2-ad39-2979d312afca"), SmallIntValue =  5 },
						new LinqDataTypes { ID =  6, MoneyValue =  6.55m, DateTimeValue = new DateTime(2009,  9,  22,  9, 19, 29,  90), BoolValue = false, GuidValue = new Guid("8d3c5d1d-47db-4730-9fe7-968f6228a4c0"), SmallIntValue =  6 },
						new LinqDataTypes { ID =  7, MoneyValue =  7.00m, DateTimeValue = new DateTime(2009,  9,  23,  9, 19, 29,  90), BoolValue = true,  GuidValue = new Guid("48094115-83af-46dd-a906-bff26ee21ee2"), SmallIntValue =  7 },
						new LinqDataTypes { ID =  8, MoneyValue =  8.99m, DateTimeValue = new DateTime(2009,  9,  24,  9, 19, 29,  90), BoolValue = false, GuidValue = new Guid("c1139f1f-1335-4cd4-937e-92602f732dd3"), SmallIntValue =  8 },
						new LinqDataTypes { ID =  9, MoneyValue =  9.63m, DateTimeValue = new DateTime(2009,  9,  25,  9, 19, 29,  90), BoolValue = true,  GuidValue = new Guid("46c5c512-3d4b-4cf7-b4e7-1de080789e5d"), SmallIntValue =  9 },
						new LinqDataTypes { ID = 10, MoneyValue = 10.77m, DateTimeValue = new DateTime(2009,  9,  26,  9, 19, 29,  90), BoolValue = false, GuidValue = new Guid("61b2bc55-147f-4b40-93ed-a4aa83602fee"), SmallIntValue = 10 },
						new LinqDataTypes { ID = 11, MoneyValue = 11.45m, DateTimeValue = new DateTime(2009,  9,  27,  0,  0,  0,   0), BoolValue = true,  GuidValue = new Guid("d3021d18-97f0-4dc0-98d0-f0c7df4a1230"), SmallIntValue = 11 },
						new LinqDataTypes { ID = 12, MoneyValue = 11.45m, DateTimeValue = new DateTime(2012, 11,   7, 19, 19, 29,  90), BoolValue = true,  GuidValue = new Guid("03021d18-97f0-4dc0-98d0-f0c7df4a1230"), SmallIntValue = 12 }
					});

				Console.WriteLine("\nBulkCopy Parent\n");

				db.BulkCopy(
					options,
					new []
					{
						new Parent { ParentID = 1, Value1 = 1    },
						new Parent { ParentID = 2, Value1 = null },
						new Parent { ParentID = 3, Value1 = 3    },
						new Parent { ParentID = 4, Value1 = null },
						new Parent { ParentID = 5, Value1 = 5    },
						new Parent { ParentID = 6, Value1 = 6    },
						new Parent { ParentID = 7, Value1 = 1    }
					});

				Console.WriteLine("\nBulkCopy Child\n");

				db.BulkCopy(
					options,
					new []
					{
						new Child { ParentID = 1, ChildID = 11 },
						new Child { ParentID = 2, ChildID = 21 },
						new Child { ParentID = 2, ChildID = 22 },
						new Child { ParentID = 3, ChildID = 31 },
						new Child { ParentID = 3, ChildID = 32 },
						new Child { ParentID = 3, ChildID = 33 },
						new Child { ParentID = 4, ChildID = 41 },
						new Child { ParentID = 4, ChildID = 42 },
						new Child { ParentID = 4, ChildID = 43 },
						new Child { ParentID = 4, ChildID = 44 },
						new Child { ParentID = 6, ChildID = 61 },
						new Child { ParentID = 6, ChildID = 62 },
						new Child { ParentID = 6, ChildID = 63 },
						new Child { ParentID = 6, ChildID = 64 },
						new Child { ParentID = 6, ChildID = 65 },
						new Child { ParentID = 6, ChildID = 66 },
						new Child { ParentID = 7, ChildID = 77 }
					});

				Console.WriteLine("\nBulkCopy GrandChild\n");

				db.BulkCopy(
					options,
					new []
					{
						new GrandChild { ParentID = 1, ChildID = 11, GrandChildID = 111 },
						new GrandChild { ParentID = 2, ChildID = 21, GrandChildID = 211 },
						new GrandChild { ParentID = 2, ChildID = 21, GrandChildID = 212 },
						new GrandChild { ParentID = 2, ChildID = 22, GrandChildID = 221 },
						new GrandChild { ParentID = 2, ChildID = 22, GrandChildID = 222 },
						new GrandChild { ParentID = 3, ChildID = 31, GrandChildID = 311 },
						new GrandChild { ParentID = 3, ChildID = 31, GrandChildID = 312 },
						new GrandChild { ParentID = 3, ChildID = 31, GrandChildID = 313 },
						new GrandChild { ParentID = 3, ChildID = 32, GrandChildID = 321 },
						new GrandChild { ParentID = 3, ChildID = 32, GrandChildID = 322 },
						new GrandChild { ParentID = 3, ChildID = 32, GrandChildID = 323 },
						new GrandChild { ParentID = 3, ChildID = 33, GrandChildID = 331 },
						new GrandChild { ParentID = 3, ChildID = 33, GrandChildID = 332 },
						new GrandChild { ParentID = 3, ChildID = 33, GrandChildID = 333 },
						new GrandChild { ParentID = 4, ChildID = 41, GrandChildID = 411 },
						new GrandChild { ParentID = 4, ChildID = 41, GrandChildID = 412 },
						new GrandChild { ParentID = 4, ChildID = 41, GrandChildID = 413 },
						new GrandChild { ParentID = 4, ChildID = 41, GrandChildID = 414 },
						new GrandChild { ParentID = 4, ChildID = 42, GrandChildID = 421 },
						new GrandChild { ParentID = 4, ChildID = 42, GrandChildID = 422 },
						new GrandChild { ParentID = 4, ChildID = 42, GrandChildID = 423 },
						new GrandChild { ParentID = 4, ChildID = 42, GrandChildID = 424 }
					});

				if (action != null)
					action(db.Connection);
			}
		}
Ejemplo n.º 39
0
        string ContainsJoin1Impl(TestDataConnection db, int[] arr)
        {
            var id = 1000;

            (
                from p in db.Parent
                join c in db.Child on p.ParentID equals c.ParentID
                where c.ParentID == id && !arr.Contains(c.ChildID)
                select p
            ).Delete();

            return db.LastQuery;
        }
Ejemplo n.º 40
0
 public void SimplTest()
 {
     using (var db = new TestDataConnection())
         Assert.AreEqual(1, QueryTable <PersonEx>(db).Where(_ => _.FirstName == "John").Select(_ => _.ID).Single());
 }
Ejemplo n.º 41
0
		public void CrossJoinedSourceWithSingleFieldSelection([MergeDataContextSource(false)] string context)
		{
			using (var db = new TestDataConnection(context))
			using (db.BeginTransaction())
			{
				// prepare test data
				db.GetTable<CrossJoinLeft>().Delete();
				db.GetTable<CrossJoinRight>().Delete();
				db.GetTable<CrossJoinResult>().Delete();

				db.Insert(new CrossJoinLeft() { Id = 1 });
				db.Insert(new CrossJoinLeft() { Id = 2 });
				db.Insert(new CrossJoinRight() { Id = 10 });
				db.Insert(new CrossJoinRight() { Id = 20 });
				db.Insert(new CrossJoinResult() { Id = 11, LeftId = 100, RightId = 200 });

				var source = from t1 in db.GetTable<CrossJoinLeft>()
							 from t2 in db.GetTable<CrossJoinRight>()
							 select new
							 {
								 RightId = t2.Id
							 };

				var rows = db.GetTable<CrossJoinResult>()
					.Merge()
					.Using(source)
					.On((t, s) => t.Id == s.RightId)
					.InsertWhenNotMatched(s => new CrossJoinResult()
					{
						RightId = s.RightId
					})
					.Merge();

				// sort on client, see SortedMergeResultsIssue test for details
				var result = db.GetTable<CrossJoinResult>().AsEnumerable().OrderBy(_ => _.Id).ThenBy(_ => _.RightId).ToList();

				AssertRowCount(4, rows, context);

				Assert.AreEqual(5, result.Count);

				Assert.AreEqual(0, result[0].Id);
				Assert.AreEqual(0, result[0].LeftId);
				Assert.AreEqual(10, result[0].RightId);

				Assert.AreEqual(0, result[1].Id);
				Assert.AreEqual(0, result[1].LeftId);
				Assert.AreEqual(10, result[1].RightId);

				Assert.AreEqual(0, result[2].Id);
				Assert.AreEqual(0, result[2].LeftId);
				Assert.AreEqual(20, result[2].RightId);

				Assert.AreEqual(0, result[3].Id);
				Assert.AreEqual(0, result[3].LeftId);
				Assert.AreEqual(20, result[3].RightId);

				Assert.AreEqual(11, result[4].Id);
				Assert.AreEqual(100, result[4].LeftId);
				Assert.AreEqual(200, result[4].RightId);
			}
		}
Ejemplo n.º 42
0
		public void TestMethod()
		{
			using (var db = new TestDataConnection())
			{
				IQueryable<IDocument> query = db.GetTable<Document>();
				var idsQuery = query.Select(s => s.Id);
				var str = idsQuery.ToString(); // Exception
				Assert.IsNotNull(str);
			}
		}