public void InsertPrimaryKeyAutoIncrement()
{
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.Insert(poco);
Assert.IsTrue(poco.UserId > 0, "POCO failed to insert.");
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);
}
// This will fail using SQLite in-memory as the transaction wraps the whole connection :/ Will probably need
// to switch over to SQLite file based at some point. So that one connection can create the DB and a different connection can
// do the tests.
public void ExternalTransactionDiscarded()
{
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);
}
var count = Database.ExecuteScalar <long>("SELECT COUNT(*) FROM Users");
Assert.AreEqual(count, 15);
}
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);
}
public void SingleOrDefaultInto()
{
var u = new UserDecorated();
var user = Database.SingleOrDefaultInto(u, "select u.* from users u where u.userid = 1");
AssertUserValues(InMemoryUsers[0], u);
Assert.AreEqual(u, user);
}
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, new SqlServer2012DatabaseType());
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);
}
public void UpdateOrInsert()
{
const string dataName = "John Doe";
const int dataAge = 56;
const decimal dataSavings = (decimal)345.23;
var dataDateOfBirth = DateTime.Now;
const string secondName = "John Doe modified";
const int secondAge = 60;
const decimal secondSavings = (decimal)111.11;
var secondDateOfBirth = DateTime.Now.AddDays(1);
var poco = new UserDecorated();
poco.UserId = 9999;
poco.Name = dataName;
poco.Age = dataAge;
poco.Savings = dataSavings;
poco.DateOfBirth = dataDateOfBirth;
Database.UpdateOrInsert(poco);
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);
poco.Name = secondName;
poco.Age = secondAge;
poco.Savings = secondSavings;
poco.DateOfBirth = secondDateOfBirth;
Database.UpdateOrInsert(poco);
verify = Database.SingleOrDefaultById <UserDecorated>(poco.UserId);
Assert.IsNotNull(verify);
Assert.AreEqual(poco.UserId, verify.UserId);
Assert.AreEqual(secondName, verify.Name);
Assert.AreEqual(secondAge, verify.Age);
Assert.AreEqual(secondSavings, verify.Savings);
}
public void SingleIntoWithNoRecord()
{
var u = new UserDecorated();
Assert.Throws<InvalidOperationException>(() => Database.SingleInto(u, "select u.* from users u where u.userid = -1"));
}
