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); } }