public void InsertFromCrossJoinedSourceQuery([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 { 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 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 MergeWithDeletePredicate3([MergeBySourceDataContextSource] string context) { using (var db = new TestDataConnection(context)) using (db.BeginTransaction()) { db.Insert(new Person() { FirstName = "Не ASCII", Gender = Gender.Unknown, LastName = "Last Name", MiddleName = "Mid" }); var person = db.Person.First(); db.Insert(new Patient() { PersonID = person.ID, Diagnosis = "Negative" }); var patient = db.Patient.Where(_ => _.PersonID == person.ID).First(); db.GetTable <Person>() .Merge() .Using(db.Person.Where(t => t.Patient == patient)) .OnTargetKey() .UpdateWhenMatched() .InsertWhenNotMatched() .DeleteWhenNotMatchedBySourceAnd(t => t.Patient == patient) .Merge(); } }
private void PrepareAssociationsData(TestDataConnection db) { using (new DisableLogging()) { db.Patient.Delete(); db.Doctor.Delete(); db.Person.Delete(); var id = 1; foreach (var person in AssociationPersons) { person.ID = id++; person.ID = Convert.ToInt32(db.InsertWithIdentity(person)); } AssociationDoctors[0].PersonID = AssociationPersons[4].ID; AssociationDoctors[1].PersonID = AssociationPersons[5].ID; foreach (var doctor in AssociationDoctors) { db.Insert(doctor); } AssociationPatients[0].PersonID = AssociationPersons[2].ID; AssociationPatients[1].PersonID = AssociationPersons[3].ID; foreach (var patient in AssociationPatients) { db.Insert(patient); } } }
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 MergeWithDeletePredicate4([IncludeDataSources(TestProvName.AllSqlServer2008Plus)] string context) { using (var db = new TestDataConnection(context)) using (db.BeginTransaction()) { db.Insert(new Person() { FirstName = "Не ASCII", Gender = Gender.Unknown, LastName = "Last Name", MiddleName = "Mid" }); var person = db.Person.First(); db.InsertOrReplace(new Patient() { PersonID = person.ID, Diagnosis = "Negative" }); var patient = person.ID; var merge = db.GetTable <Person>() .Merge() .Using(db.Person.Where(t => t.Patient !.PersonID == patient)) .OnTargetKey() .UpdateWhenMatched() .InsertWhenNotMatched() .DeleteWhenNotMatchedBySourceAnd(t => t.Patient !.PersonID == patient); merge.Merge(); patient++; merge.Merge(); } }
public void Issue1007OnOldAPIv1(string context) { using (var db = new TestDataConnection(context)) using (db.BeginTransaction()) { var table = db.GetTable <TestMappingWithIdentity>(); table.Delete(); db.Insert(new TestMappingWithIdentity()); var lastId = table.Select(_ => _.Id).Max(); var source = new[] { new TestMappingWithIdentity() { Id = lastId, Field = 10 } }; var rows = db.Merge(source); var result = table.OrderBy(_ => _.Id).ToList(); AssertRowCount(1, rows, context); Assert.AreEqual(1, result.Count); var newRecord = new TestMapping1(); Assert.AreEqual(lastId, result[0].Id); Assert.AreEqual(10, result[0].Field); } }
public void CommitTransaction(string context) { using (var db = new TestDataConnection(context)) { 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 TestBinaryLengthTranslation([IncludeDataSources(TestProvName.AllSQLite)] string context) { using (var db = new TestDataConnection(context)) using (var table = db.CreateLocalTable <IssueClass>()) { db.Insert(new IssueClass() { Id = 99, Data = new byte[] { 1, 2, 3, 4, 5 }, DataB = new Binary(new byte[] { 1, 2, 3, 4, 5, 6 }) }); var qry1 = table.Select(x => Sql.Length(x.Data)).ToList(); Assert.That(db.LastQuery !.ToLower().Contains("length(")); var qry2 = table.Select(x => x.Data !.Length).ToList(); Assert.That(db.LastQuery !.ToLower().Contains("length(")); var qry3 = db.GetTable <IssueClass>().Select(x => Sql.Length(x.DataB)).ToList(); Assert.That(db.LastQuery !.ToLower().Contains("length(")); var qry4 = db.GetTable <IssueClass>().Select(x => x.DataB !.Length).ToList(); Assert.That(db.LastQuery !.ToLower().Contains("length(")); Assert.AreEqual(5, qry1[0]); Assert.AreEqual(5, qry2[0]); Assert.AreEqual(6, qry3[0]); Assert.AreEqual(6, qry4[0]); } }
public void AutoRollbackTransaction() { using (var db = new TestDataConnection()) { 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 RollbackTransaction([DataSources(false)] string context) { using (var db = new TestDataConnection(context)) { 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 = 1012 }); tr.Rollback(); } 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); } } }
private void Update(string context, Data data, int iteration) { using (var db = new TestDataConnection(context)) { foreach (var record in data.Updates) { db.Insert(record); } db.GetTable <Animal>().Merge() .Using(db.GetTable <AnimalUpdate>().Where(_ => _.Iteration == iteration)) .On((target, source) => target.Name == source.Name) .UpdateWhenMatched( (target, source) => new Animal() { Color = data.Color, Length = source.Length }) .InsertWhenNotMatched( source => new Animal() { Name = source.Name, Color = data.Color, Length = source.Length }) .Merge(); } }
public void Test([DataSources(false)] string context) { using (var db = new TestDataConnection(context)) using (db.CreateLocalTable <Parent564>()) { db.Insert(new Child564A() { StringValue = "SomeValue" }); db.Insert(new Child564B() { IntValue = 911 }); Assert.AreEqual(2, db.GetTable <Parent564>().Count()); } }
public void Issue1007OnOldAPIv2([IdentityInsertMergeDataContextSource( ProviderName.Sybase, ProviderName.SybaseManaged)] string context) { using (var db = new TestDataConnection(context)) using (db.BeginTransaction()) { db.Doctor.Delete(); db.Patient.Delete(); var table = db.GetTable <Person1007>(); table.Delete(); db.Insert(new Person1007() { FirstName = "first name", LastName = "last name", Gender = Gender.Female }); var lastId = table.Select(_ => _.ID).Max(); var source = new[] { new Person1007() { FirstName = "first name", LastName = "updated", Gender = Gender.Male, ID = 10 } }; var rows = db.Merge(source); var result = table.OrderBy(_ => _.ID).ToList(); AssertRowCount(1, rows, context); Assert.AreEqual(1, result.Count); var newRecord = new TestMapping1(); Assert.AreEqual(lastId, result[0].ID); Assert.AreEqual("first name", result[0].FirstName); Assert.AreEqual("updated", result[0].LastName); Assert.AreEqual(Gender.Male, result[0].Gender); } }
public void ImplicitInsertIdentityWithSkipOnInsert( [IdentityInsertMergeDataContextSource] string context) { using (var db = new TestDataConnection(context)) { var table = db.GetTable <TestMappingWithIdentity>(); table.Delete(); db.Insert(new TestMappingWithIdentity()); var lastId = table.Select(_ => _.Id).Max(); var source = new[] { new TestMappingWithIdentity() { Field = 22, }, new TestMappingWithIdentity() { Field = 23 } }; var rows = table .Merge() .Using(source) .On((s, t) => s.Field == t.Field) .InsertWhenNotMatched() .Merge(); var result = table.OrderBy(_ => _.Id).ToList(); AssertRowCount(2, rows, context); Assert.AreEqual(3, result.Count); var newRecord = new TestMapping1(); Assert.AreEqual(lastId, result[0].Id); Assert.AreEqual(null, result[0].Field); Assert.AreEqual(lastId + 1, result[1].Id); Assert.AreEqual(22, result[1].Field); Assert.AreEqual(lastId + 2, result[2].Id); Assert.AreEqual(23, result[2].Field); } }
public void MyType3() { using (var db = new TestDataConnection().AddMappingSchema(_myMappingSchema) as TestDataConnection) { try { db.Insert(new MyParent { ParentID = new MyInt { MyValue = 1001 }, Value1 = 1001 }); } finally { db.Parent.Delete(p => p.ParentID >= 1000); } } }
public void SequenceNameTest([IncludeDataSources(false, TestProvName.AllOracle)] string context) { using (var db = new TestDataConnection(context)) { db.BeginTransaction(); var user = new User { Name = "user" }; user.Id = Convert.ToInt64(db.InsertWithIdentity(user)); Assert.True(db.LastQuery?.Contains("\"c##sequence_schema\".\"sq_test_user\".nextval")); db.Insert(new Contract { UserId = user.Id, ContractNo = 1, Name = "contract1" }); Assert.True(db.LastQuery?.Contains("\t\"sq_test_user_contract\".nextval")); } }
public void Issue1007OnNewAPI(string context) { using (var db = new TestDataConnection(context)) using (db.BeginTransaction()) { var table = db.GetTable <TestMappingWithIdentity>(); table.Delete(); db.Insert(new TestMappingWithIdentity()); var lastId = table.Select(_ => _.Id).Max(); var source = new[] { new TestMappingWithIdentity() { Field = 10 } }; var rows = table .Merge() .Using(source) .On((s, t) => s.Field == null) .InsertWhenNotMatched() .UpdateWhenMatched() .Merge(); var result = table.OrderBy(_ => _.Id).ToList(); AssertRowCount(1, rows, context); Assert.AreEqual(1, result.Count); var newRecord = new TestMapping1(); Assert.AreEqual(lastId, result[0].Id); Assert.AreEqual(10, result[0].Field); } }
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 MyType3() { using (var db = new TestDataConnection().AddMappingSchema(_myMappingSchema) as TestDataConnection) { try { db.Insert(new MyParent { ParentID = new MyInt { MyValue = 1001 }, Value1 = 1001 }); } finally { db.Parent.Delete(p => p.ParentID >= 1000); } } }
public void KeepIdentity_SkipOnInsertFalse( [DataSources(false)] string context, [Values(null, true, false)] bool?keepIdentity, [Values] BulkCopyType copyType) { List <TestTable1> list = null; // 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 TestTable1()); try { list = db.GetTable <TestTable1>().ToList(); db.GetTable <TestTable1>().Delete(); var options = new BulkCopyOptions() { KeepIdentity = keepIdentity, BulkCopyType = copyType }; if (!Execute(context, perform, keepIdentity, copyType)) { return; } var data = db.GetTable <TestTable1>().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 TestTable1() { ID = lastId + 10, Value = 200 }, new TestTable1() { ID = lastId + 20, Value = 300 } }); } } finally { // cleanup db.GetTable <TestTable2>().Delete(_ => _.ID >= lastId); if (list != null) { foreach (var item in list) { db.Insert(item); } } } } }
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); } }