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);
}
}
}
public void Merge(string context)
{
using (var db = new TestDataConnection(context))
{
db.Merge(db.Types2);
}
}
public void CompiledUpdate()
{
using (var ctx = new TestDataConnection())
{
_updateQuery(ctx, 12345, "54321");
}
}
public void MergeWithEmptySource(string context)
{
using (var db = new TestDataConnection(context))
{
db.Merge(new Person[] {});
}
}
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();
}
}
public void MergeWithDeletePredicate2(string context)
{
using (var db = new TestDataConnection(context))
{
db.Merge(db.Types2, t => t.ID > 5);
}
}
public void CompiledFunc2([IncludeDataContexts(ProviderName.SqlServer2008)] string context)
{
using (var db = new TestDataConnection(context))
{
var q = _f2(db, 1);
q.ToList();
}
}
public void MergeWithDeletePredicate3(string context)
{
using (var db = new TestDataConnection(context))
{
var patient = db.Patient.First();
db.Merge(db.Person, t => t.Patient == patient);
}
}
public void CompiledFunc2(string context)
{
using (var db = new TestDataConnection(context))
{
var q = _f2(db, 1);
q.ToList();
}
}
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"));
}
}
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
});
}
}
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()));
}
}
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();
}
}
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"));
}
}
}
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"));
}
}
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));
}
}
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();
}
}
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);
}
}
}
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);
}
}
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);
}
}
}
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);
}
}
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);
}
}
}
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);
}
}
}
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));
}
}
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));
}
}
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);
}
}
}
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);
}
}
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);
}
}
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);
}
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);
}
}
}
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);
}
}
}
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)));
}
}
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);
}
}
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);
}
}
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;
}
public void SimplTest()
{
using (var db = new TestDataConnection())
Assert.AreEqual(1, QueryTable <PersonEx>(db).Where(_ => _.FirstName == "John").Select(_ => _.ID).Single());
}
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);
}
}
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);
}
}
