public void ExternalTransactionComplete()
{
using (var scope = Database.GetTransaction())
{
var user = new UserDecorated
{
Name = "Name" + 16,
Age = 20 + 16,
DateOfBirth = new DateTime(1970, 1, 1).AddYears(16),
Savings = 50.00m + (1.01m * 16)
};
InMemoryUsers.Add(user);
Database.Insert(user);
var extra = new ExtraUserInfoDecorated
{
UserId = user.UserId,
Email = "email" + 16 + "@email.com",
Children = 16
};
InMemoryExtraUserInfos.Add(extra);
Database.Insert(extra);
scope.Complete();
}
var count = Database.ExecuteScalar<long>("SELECT COUNT(*) FROM Users");
Assert.AreEqual(count, 16);
}
protected void AssertUserValues(UserDecorated expected, UserDecorated actual)
{
Assert.AreEqual(expected.UserId, actual.UserId);
Assert.AreEqual(expected.Name, actual.Name);
Assert.AreEqual(expected.Age, actual.Age);
Assert.AreEqual(expected.DateOfBirth, actual.DateOfBirth);
Assert.AreEqual(expected.Savings, actual.Savings);
}
protected void AssertUserValues(UserDecorated expected, UserDecorated actual)
{
Assert.AreEqual(expected.UserId, actual.UserId);
Assert.AreEqual(expected.Name, actual.Name);
Assert.AreEqual(expected.Age, actual.Age);
Assert.AreEqual(expected.DateOfBirth, actual.DateOfBirth);
Assert.AreEqual(expected.Savings, actual.Savings);
}
public void SingleInto()
{
var u = new UserDecorated();
var user = Database.SingleInto(u, "select u.* from users u where u.userid = 1");
AssertUserValues(InMemoryUsers[0], user);
Assert.AreEqual(u, user);
}
public void SingleOrDefaultIntoWithNoRecord()
{
var u = new UserDecorated();
var user = Database.SingleOrDefaultInto(u, "select u.* from users u where u.userid = -1");
AssertUserValues(u, new UserDecorated());
Assert.Null(user);
}
protected void InsertData()
{
InMemoryUsers = new List <UserDecorated>();
InMemoryExtraUserInfos = new List <ExtraUserInfoDecorated>();
InMemoryCompositeObjects = new List <CompositeObjectDecorated>();
for (var i = 0; i < 15; i++)
{
var pos = i + 1;
var user = new UserDecorated
{
Name = "Name" + (i + 1),
Age = 20 + (i + 1),
DateOfBirth = new DateTime(1970, 1, 1).AddYears(i + 1),
Savings = 50.00m + (1.01m * (i + 1)),
IsMale = (i % 2 == 0)
};
Database.Insert(user);
InMemoryUsers.Add(user);
var extra = new ExtraUserInfoDecorated
{
UserId = user.UserId,
Email = "email" + (i + 1) + "@email.com",
Children = (i + 1)
};
Database.Insert(extra);
InMemoryExtraUserInfos.Add(extra);
var composite = new CompositeObjectDecorated
{
Key1ID = pos,
Key2ID = i + 2,
Key3ID = i + 4,
TextData = "This is some text data.",
DateEntered = DateTime.Now
};
Database.Insert(composite);
InMemoryCompositeObjects.Add(composite);
}
// Verify DB record counts
var userCount = Database.ExecuteScalar <int>("SELECT COUNT(UserId) FROM Users");
Assert.AreEqual(InMemoryUsers.Count, userCount, "Test User Data not in sync db has " + userCount + " records, but the in memory copy has only " + InMemoryUsers.Count + " records.");
System.Diagnostics.Debug.WriteLine("Created " + userCount + " test users for the unit tests.");
var userExtraInfoCount = Database.ExecuteScalar <int>("SELECT COUNT(ExtraUserInfoId) FROM ExtraUserInfos");
Assert.AreEqual(InMemoryExtraUserInfos.Count, userExtraInfoCount, "Test User Extra Info Data not in sync db has " + userExtraInfoCount + " records, but the in memory copy has only " + InMemoryExtraUserInfos.Count + " records.");
System.Diagnostics.Debug.WriteLine("Created " + userExtraInfoCount + " test extra user info records for the unit tests.");
var compositeObjectCount = Database.ExecuteScalar <int>("SELECT COUNT(Key1ID) FROM CompositeObjects");
Assert.AreEqual(InMemoryCompositeObjects.Count, compositeObjectCount, "Test Composite Object Data not in sync db has " + compositeObjectCount + " records, but the in memory copy has only " + InMemoryCompositeObjects.Count + " records.");
System.Diagnostics.Debug.WriteLine("Created " + compositeObjectCount + " test composite PK objects for the unit tests.");
}
protected void InsertData()
{
InMemoryUsers = new List<UserDecorated>();
InMemoryExtraUserInfos = new List<ExtraUserInfoDecorated>();
InMemoryCompositeObjects = new List<CompositeObjectDecorated>();
for (var i = 0; i < 15; i++)
{
var pos = i + 1;
var user = new UserDecorated
{
Name = "Name" + (i + 1),
Age = 20 + (i + 1),
DateOfBirth = new DateTime(1970, 1, 1).AddYears(i + 1),
Savings = 50.00m + (1.01m * (i + 1)),
IsMale = (i%2==0)
};
Database.Insert(user);
InMemoryUsers.Add(user);
var extra = new ExtraUserInfoDecorated
{
UserId = user.UserId,
Email = "email" + (i + 1) + "@email.com",
Children = (i + 1)
};
Database.Insert(extra);
InMemoryExtraUserInfos.Add(extra);
var composite = new CompositeObjectDecorated
{
Key1ID = pos,
Key2ID = i + 2,
Key3ID = i + 4,
TextData = "This is some text data.",
DateEntered = DateTime.Now
};
Database.Insert(composite);
InMemoryCompositeObjects.Add(composite);
}
// Verify DB record counts
var userCount = Database.ExecuteScalar<int>("SELECT COUNT(UserId) FROM Users");
Assert.AreEqual(InMemoryUsers.Count, userCount, "Test User Data not in sync db has " + userCount + " records, but the in memory copy has only " + InMemoryUsers.Count + " records.");
System.Diagnostics.Debug.WriteLine("Created " + userCount + " test users for the unit tests.");
var userExtraInfoCount = Database.ExecuteScalar<int>("SELECT COUNT(ExtraUserInfoId) FROM ExtraUserInfos");
Assert.AreEqual(InMemoryExtraUserInfos.Count, userExtraInfoCount, "Test User Extra Info Data not in sync db has " + userExtraInfoCount + " records, but the in memory copy has only " + InMemoryExtraUserInfos.Count + " records.");
System.Diagnostics.Debug.WriteLine("Created " + userExtraInfoCount + " test extra user info records for the unit tests.");
var compositeObjectCount = Database.ExecuteScalar<int>("SELECT COUNT(Key1ID) FROM CompositeObjects");
Assert.AreEqual(InMemoryCompositeObjects.Count, compositeObjectCount, "Test Composite Object Data not in sync db has " + compositeObjectCount + " records, but the in memory copy has only " + InMemoryCompositeObjects.Count + " records.");
System.Diagnostics.Debug.WriteLine("Created " + compositeObjectCount + " test composite PK objects for the unit tests.");
}
public void SavePrimaryKeyAutoIncrementNew()
{
const string dataName = "John Doe";
const int dataAge = 56;
const decimal dataSavings = (decimal)345.23;
var dataDateOfBirth = DateTime.Now;
var poco = new UserDecorated();
poco.Name = dataName;
poco.Age = dataAge;
poco.Savings = dataSavings;
poco.DateOfBirth = dataDateOfBirth;
Database.Save<UserDecorated>(poco);
Assert.IsTrue(poco.UserId > 0, "POCO failed to Save.");
var verify = Database.SingleOrDefaultById<UserDecorated>(poco.UserId);
Assert.IsNotNull(verify);
Assert.AreEqual(poco.UserId, verify.UserId);
Assert.AreEqual(dataName, verify.Name);
Assert.AreEqual(dataAge, verify.Age);
Assert.AreEqual(dataSavings, verify.Savings);
}
public void SingleIntoWithNoRecord()
{
var u = new UserDecorated();
Assert.Throws<InvalidOperationException>(() => Database.SingleInto(u, "select u.* from users u where u.userid = -1"));
}
protected void InsertData()
{
InMemoryUsers = new List <UserDecorated>();
InMemoryExtraUserInfos = new List <ExtraUserInfoDecorated>();
InMemoryCompositeObjects = new List <CompositeObjectDecorated>();
InMemoryHouses = new List <HouseDecorated>();
for (var i = 0; i < 5; i++)
{
var house = new HouseDecorated()
{
Address = i + " Road Street, Suburb"
};
Database.Insert(house);
InMemoryHouses.Add(house);
}
for (var i = 0; i < 15; i++)
{
var pos = i + 1;
var user = new UserDecorated
{
Name = "Name" + (i + 1),
Age = 20 + (i + 1),
DateOfBirth = new DateTime(1970, 1, 1).AddYears(i + 1),
Savings = 50.00m + (1.01m * (i + 1)),
IsMale = (i % 2 == 0),
HouseId = i % 2 == 0 ? (int?)null : InMemoryHouses[i % 5].HouseId
};
Database.Insert(user);
InMemoryUsers.Add(user);
var extra = new ExtraUserInfoDecorated
{
UserId = user.UserId,
Email = "email" + (i + 1) + "@email.com",
Children = (i + 1)
};
Database.Insert(extra);
InMemoryExtraUserInfos.Add(extra);
var composite = new CompositeObjectDecorated
{
Key1ID = pos,
Key2ID = i + 2,
Key3ID = i + 4,
TextData = "This is some text data.",
DateEntered = DateTime.Now
};
Database.Insert(composite);
InMemoryCompositeObjects.Add(composite);
var recursionUser = new RecursionUser
{
Name = "Name" + (i + 1),
CreatedBy = new RecursionUser()
{
Id = 1
},
Supervisor = new RecursionUser()
{
Id = 2
}
};
Database.Insert(recursionUser);
var one = new One()
{
Name = "Name" + (i + 1),
};
Database.Insert(one);
for (int j = 0; j < (i % 3); j++)
{
var many = new Many()
{
OneId = one.OneId,
Currency = "Cur" + (i + j + 1),
AValue = (i + j + 1)
};
Database.Insert(many);
}
var userWithAddress = new UserWithAddress()
{
Name = "Name" + (i + 1),
Address = new UserWithAddress.MyAddress()
{
StreetNo = i + 1,
StreetName = "Street" + (i + 1),
MovedInOn = new DateTime(1970, 1, 1).AddYears(i + 1),
AddressFurtherInfo = new UserWithAddress.MyAddress.AddressInfo()
{
PostCode = "99999"
}
}
};
Database.Insert(userWithAddress);
}
// Verify DB record counts
var userCount = Database.ExecuteScalar <int>("SELECT COUNT(UserId) FROM Users");
Assert.AreEqual(InMemoryUsers.Count, userCount, "Test User Data not in sync db has " + userCount + " records, but the in memory copy has only " + InMemoryUsers.Count + " records.");
System.Diagnostics.Debug.WriteLine("Created " + userCount + " test users for the unit tests.");
var userExtraInfoCount = Database.ExecuteScalar <int>("SELECT COUNT(ExtraUserInfoId) FROM ExtraUserInfos");
Assert.AreEqual(InMemoryExtraUserInfos.Count, userExtraInfoCount, "Test User Extra Info Data not in sync db has " + userExtraInfoCount + " records, but the in memory copy has only " + InMemoryExtraUserInfos.Count + " records.");
System.Diagnostics.Debug.WriteLine("Created " + userExtraInfoCount + " test extra user info records for the unit tests.");
var compositeObjectCount = Database.ExecuteScalar <int>("SELECT COUNT(Key1_ID) FROM CompositeObjects");
Assert.AreEqual(InMemoryCompositeObjects.Count, compositeObjectCount, "Test Composite Object Data not in sync db has " + compositeObjectCount + " records, but the in memory copy has only " + InMemoryCompositeObjects.Count + " records.");
System.Diagnostics.Debug.WriteLine("Created " + compositeObjectCount + " test composite PK objects for the unit tests.");
}
public void NestedTransactionThatFailsAbortsWholeUsingBeginAbort()
{
Database.BeginTransaction();
Database.BeginTransaction();
var user1 = new UserDecorated
{
Name = "Name" + 16,
Age = 20 + 16,
DateOfBirth = new DateTime(1970, 1, 1).AddYears(16),
Savings = 50.00m + (1.01m * 16)
};
InMemoryUsers.Add(user1);
Database.Insert(user1);
var extra1 = new ExtraUserInfoDecorated
{
UserId = user1.UserId,
Email = "email" + 16 + "@email.com",
Children = 16
};
InMemoryExtraUserInfos.Add(extra1);
Database.Insert(extra1);
Database.AbortTransaction();
var user = new UserDecorated
{
Name = "Name" + 16,
Age = 20 + 16,
DateOfBirth = new DateTime(1970, 1, 1).AddYears(16),
Savings = 50.00m + (1.01m * 16)
};
InMemoryUsers.Add(user);
Database.Insert(user);
var extra = new ExtraUserInfoDecorated
{
UserId = user.UserId,
Email = "email" + 16 + "@email.com",
Children = 16
};
InMemoryExtraUserInfos.Add(extra);
Database.Insert(extra);
Database.CompleteTransaction();
var count = Database.ExecuteScalar<long>("SELECT COUNT(*) FROM Users");
Assert.AreEqual(15, count);
}
public void TransactionSettingsDontCauseLocksAndTransationRollback()
{
var nameInsert = "Name" + 16;
var ageInsert = 20 + 16;
var nameUpdate = "Name" + 99;
var ageUpdate = 20 + 99;
var user = new UserDecorated
{
Name = nameInsert,
Age = ageInsert,
DateOfBirth = new DateTime(1970, 1, 1).AddYears(16),
Savings = 50.00m + (1.01m * 16)
};
Database.Insert(user);
var userAfterCreate = Database.SingleOrDefault<UserDecorated>("WHERE UserID = @0", user.UserId);
Assert.IsNotNull(userAfterCreate);
Assert.AreEqual(userAfterCreate.Name, nameInsert);
var dbTrans = new Database(TestDatabase.ConnectionString, TestDatabase.DbType);
dbTrans.BeginTransaction();
user.Name = nameUpdate;
user.Age = ageUpdate;
dbTrans.Update(user);
// Verify inside of transaction
var userPreCommitInside = dbTrans.SingleOrDefault<UserDecorated>("WHERE UserID = @0", user.UserId);
Assert.IsNotNull(userPreCommitInside);
Assert.AreEqual(nameUpdate, userPreCommitInside.Name);
Assert.AreEqual(ageUpdate, userPreCommitInside.Age);
dbTrans.AbortTransaction();
dbTrans.Dispose();
var userPostCommit = Database.SingleOrDefault<UserDecorated>("WHERE UserID = @0", user.UserId);
Assert.IsNotNull(userPostCommit);
Assert.AreEqual(nameInsert, userPostCommit.Name);
Assert.AreEqual(ageInsert, userPostCommit.Age);
}
protected void InsertData()
{
InMemoryUsers = new List<UserDecorated>();
InMemoryExtraUserInfos = new List<ExtraUserInfoDecorated>();
InMemoryCompositeObjects = new List<CompositeObjectDecorated>();
InMemoryHouses = new List<HouseDecorated>();
for (var i = 0; i < 5; i++)
{
var house = new HouseDecorated()
{
Address = i + " Road Street, Suburb"
};
Database.Insert(house);
InMemoryHouses.Add(house);
}
for (var i = 0; i < 15; i++)
{
var pos = i + 1;
var user = new UserDecorated
{
Name = "Name" + (i + 1),
Age = 20 + (i + 1),
DateOfBirth = new DateTime(1970, 1, 1).AddYears(i + 1),
Savings = 50.00m + (1.01m * (i + 1)),
IsMale = (i%2==0),
HouseId = i % 2 == 0 ? (int?)null : InMemoryHouses[i % 5].HouseId
};
Database.Insert(user);
InMemoryUsers.Add(user);
var extra = new ExtraUserInfoDecorated
{
UserId = user.UserId,
Email = "email" + (i + 1) + "@email.com",
Children = (i + 1)
};
Database.Insert(extra);
InMemoryExtraUserInfos.Add(extra);
var composite = new CompositeObjectDecorated
{
Key1ID = pos,
Key2ID = i + 2,
Key3ID = i + 4,
TextData = "This is some text data.",
DateEntered = DateTime.Now
};
Database.Insert(composite);
InMemoryCompositeObjects.Add(composite);
var recursionUser = new RecursionUser
{
Name = "Name" + (i + 1),
CreatedBy = new RecursionUser() {Id = 1},
Supervisor = new RecursionUser() {Id = 2}
};
Database.Insert(recursionUser);
var one = new One()
{
Name = "Name" + (i + 1),
};
Database.Insert(one);
for (int j = 0; j < (i%3); j++)
{
var many = new Many()
{
OneId = one.OneId,
Currency = "Cur" + (i + j + 1),
Value = (i + j + 1)
};
Database.Insert(many);
}
var userWithAddress = new UserWithAddress()
{
Name = "Name" + (i + 1),
Address = new UserWithAddress.MyAddress()
{
StreetNo = i + 1,
StreetName = "Street" + (i + 1),
MovedInOn = new DateTime(1970, 1, 1).AddYears(i + 1),
AddressFurtherInfo = new UserWithAddress.MyAddress.AddressInfo()
{
PostCode = "99999"
}
}
};
Database.Insert(userWithAddress);
}
// Verify DB record counts
var userCount = Database.ExecuteScalar<int>("SELECT COUNT(UserId) FROM Users");
Assert.AreEqual(InMemoryUsers.Count, userCount, "Test User Data not in sync db has " + userCount + " records, but the in memory copy has only " + InMemoryUsers.Count + " records.");
System.Diagnostics.Debug.WriteLine("Created " + userCount + " test users for the unit tests.");
var userExtraInfoCount = Database.ExecuteScalar<int>("SELECT COUNT(ExtraUserInfoId) FROM ExtraUserInfos");
Assert.AreEqual(InMemoryExtraUserInfos.Count, userExtraInfoCount, "Test User Extra Info Data not in sync db has " + userExtraInfoCount + " records, but the in memory copy has only " + InMemoryExtraUserInfos.Count + " records.");
System.Diagnostics.Debug.WriteLine("Created " + userExtraInfoCount + " test extra user info records for the unit tests.");
var compositeObjectCount = Database.ExecuteScalar<int>("SELECT COUNT(Key1_ID) FROM CompositeObjects");
Assert.AreEqual(InMemoryCompositeObjects.Count, compositeObjectCount, "Test Composite Object Data not in sync db has " + compositeObjectCount + " records, but the in memory copy has only " + InMemoryCompositeObjects.Count + " records.");
System.Diagnostics.Debug.WriteLine("Created " + compositeObjectCount + " test composite PK objects for the unit tests.");
}
