public void MergeWithDelete(string context)
{
using (var db = new TestDataConnection(context))
using (db.BeginTransaction())
{
db.GetTable <LinqDataTypes2>()
.Merge()
.Using(db.Types2)
.OnTargetKey()
.UpdateWhenMatched()
.InsertWhenNotMatched()
.DeleteWhenNotMatchedBySource()
.Merge();
}
}
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 Issue200InSource([IncludeDataSources(
ProviderName.SqlServer2008, ProviderName.SqlServer2012, ProviderName.SqlServer2014)]
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)
}
};
var cnt = db.GetTable <AllTypes2>()
.Merge()
.Using(testData)
.OnTargetKey()
.InsertWhenNotMatched()
.Merge();
var result = db.GetTable <AllTypes2>().OrderBy(_ => _.ID).ToArray();
Assert.AreEqual(2, cnt);
Assert.AreEqual(2, result.Length);
Assert.AreEqual(testData[0].datetime2DataType, result[0].datetime2DataType);
Assert.AreEqual(testData[0].datetimeoffsetDataType, result[0].datetimeoffsetDataType);
Assert.AreEqual(testData[1].datetime2DataType, result[1].datetime2DataType);
Assert.AreEqual(testData[1].datetimeoffsetDataType, result[1].datetimeoffsetDataType);
}
}
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 Issue200InPredicate([IncludeDataSources(TestProvName.AllSqlServer2008Plus)] string context)
{
using (var db = new TestDataConnection(context))
using (db.BeginTransaction())
{
db.GetTable <AllTypes2>().Delete();
var testData = new[]
{
new AllTypes2()
{
datetimeoffsetDataType = TestData.DateTimeOffset,
datetime2DataType = TestData.DateTime
},
new AllTypes2()
{
datetimeoffsetDataType = TestData.DateTimeOffset.AddTicks(1),
datetime2DataType = TestData.DateTime.AddTicks(1)
}
};
var cnt = db.GetTable <AllTypes2>()
.Merge()
.Using(testData)
.On((t, s) => s.datetime2DataType == testData[0].datetime2DataType && s.datetimeoffsetDataType == testData[0].datetimeoffsetDataType)
.InsertWhenNotMatched()
.Merge();
var result = db.GetTable <AllTypes2>().OrderBy(_ => _.ID).ToArray();
Assert.AreEqual(2, cnt);
Assert.AreEqual(2, result.Length);
Assert.AreEqual(testData[0].datetime2DataType, result[0].datetime2DataType);
Assert.AreEqual(testData[0].datetimeoffsetDataType, result[0].datetimeoffsetDataType);
Assert.AreEqual(testData[1].datetime2DataType, result[1].datetime2DataType);
Assert.AreEqual(testData[1].datetimeoffsetDataType, result[1].datetimeoffsetDataType);
}
}
public void SameSourceAssociationInUpdateBySourcePredicate(
[IncludeDataSources(TestProvName.AllSqlServer2008Plus)] string context)
{
using (var db = new TestDataConnection(context))
using (db.BeginTransaction())
{
PrepareAssociationsData(db);
var rows = db.Person
.Merge()
.Using(db.Person)
.On((t, s) => t.ID == s.ID + 10)
.UpdateWhenNotMatchedBySourceAnd(
t => t.Patient.Diagnosis.Contains("very"),
t => new Model.Person()
{
FirstName = "Updated"
})
.Merge();
var result = db.Person.OrderBy(_ => _.ID).ToList();
Assert.AreEqual(1, rows);
Assert.AreEqual(6, result.Count);
AssertPerson(AssociationPersons[0], result[0]);
AssertPerson(AssociationPersons[1], result[1]);
AssertPerson(AssociationPersons[2], result[2]);
Assert.AreEqual(AssociationPersons[3].ID, result[3].ID);
Assert.AreEqual(AssociationPersons[3].Gender, result[3].Gender);
Assert.AreEqual("Updated", result[3].FirstName);
Assert.AreEqual(AssociationPersons[3].LastName, result[3].LastName);
Assert.AreEqual(AssociationPersons[3].MiddleName, result[3].MiddleName);
AssertPerson(AssociationPersons[4], result[4]);
AssertPerson(AssociationPersons[5], result[5]);
}
}
public void Transaction(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);
}
}
}
