public async Task SelectOne_WithCommand_And_ModelFactory()
{
var db = new EasyDb();
db.SetConnectionStringSettings(DbConstants.SqlDb1, DbConstants.SqlProviderName);
db.DefaultMappingBehavior = DefaultMappingBehavior.CreateEmptyTable;
User result = null;
using (var command = db.CreateSqlCommand("select * from [User] where [Id]=@id").AddInParameter("@id", 1))
{
result = await db.SelectOneAsync <User>(command, (reader, idb) =>
{
return(new User
{
Id = (int)reader["Id"],
UserName = ((string)reader["UserName"]).Trim(),
Email = idb.CheckDBNullAndConvertTo <string>(reader["Email"])?.Trim(),
Age = idb.CheckDBNullAndConvertTo <int?>(reader["Age"])
});
});
}
Assert.AreEqual("Marie", result.UserName);
Assert.AreEqual(null, result.Age);
Assert.AreEqual("*****@*****.**", result.Email);
}
public async Task SelectOne_WithModelFactory()
{
var db = new EasyDb();
db.SetConnectionStringSettings(DbConstants.SqlDb1, DbConstants.SqlProviderName);
db.DefaultMappingBehavior = DefaultMappingBehavior.CreateEmptyTable;
Post result = null;
using (var command = db.CreateSqlCommand("select a.Title, a.Content, a.UserId, b.UserName from [Post] as a,[User] as b where a.UserId=b.Id and a.Id=@id").AddInParameter("@id", 2))
{
result = await db.SelectOneAsync <Post>(command, (reader, idb) =>
{
return(new Post
{
Title = ((string)reader["Title"]).Trim(),
Content = ((string)reader["Content"]).Trim(),
UserId = (int)reader["UserId"],
User = new User
{
UserName = ((string)reader["UserName"]).Trim()
}
});
});
}
Assert.AreEqual("Post 2", result.Title);
Assert.AreEqual("Content 2", result.Content);
Assert.AreEqual("Pat", result.User.UserName);
}
public async Task PendingTransaction_Fail()
{
var db = new EasyDb();
db.SetConnectionStringSettings(DbConstants.SqlDb1, DbConstants.SqlProviderName);
db.DefaultMappingBehavior = DefaultMappingBehavior.CreateEmptyTable;
db.AddPendingOperation(() => db.InsertAsync <User>(new User {
UserName = "******"
}));
// constraint on user permission
var user = await db.SelectOneAsync <User>(Check.Op("Id", 1));
db.AddPendingOperation(() => db.DeleteAsync <User>(user, Check.Op("Id", 1)));
Assert.AreEqual(2, db.PendingOperations.Count);
var result = await db.ExecutePendingOperationsAsync();
Assert.IsFalse(result);
Assert.AreEqual(2, db.PendingOperations.Count);
}
public async Task SelectOne_WithCompleteMappingAndeIgnoreCase_Success()
{
var db = new EasyDb();
db.SetConnectionStringSettings(DbConstants.SqlDbLikeMySql, DbConstants.SqlProviderName);
var table = db.SetTable <User>("users")
.SetPrimaryKeyColumn("id", p => p.Id)
.SetColumn("username", p => p.UserName)
.SetColumn("age", p => p.Age)
.SetColumn("email", p => p.Email);
table.IgnoreCase = false;
User result = null;
using (var command = db.CreateSqlCommand("select * from [users] where [id]=@id").AddInParameter("@id", 2))
{
result = await db.SelectOneAsync <User>(command);
}
Assert.AreEqual("Pat", result.UserName);
Assert.AreEqual(30, result.Age);
Assert.AreEqual(null, result.Email);
}
public async Task ConcurrencyException()
{
var db = new EasyDb();
db.SetConnectionStringSettings(@"Data Source=(LocalDb)\MSSQLLocalDB;Initial Catalog=MyDb;Integrated Security=True", DbConstants.SqlProviderName);
db.AddInterceptor(new ConcurrencyInterceptor());
db.SetTable <UserWithRowVersion>("User")
.SetPrimaryKeyColumn("Id", p => p.Id)
.SetColumn("Version", p => p.Version, true);
var model = await db.SelectOneAsync <UserWithRowVersion>(Check.Op("Id", 2));
model.UserName += " updated";
model.Version[4] = 2;
bool failed = false;
try
{
int result = await db.UpdateAsync <UserWithRowVersion>(model, Check.Op("Id", model.Id).And(Check.Op("Version", model.Version)));
}
catch (OptimisticConcurrencyException ex)
{
failed = true;
}
Assert.IsTrue(failed);
}
public async Task SelectOne_WithSelectionOneCommandFactory_And_DefaultModelFactory()
{
var db = new EasyDb();
db.SetConnectionStringSettings(DbConstants.SqlDb1, DbConstants.SqlProviderName);
db.DefaultMappingBehavior = DefaultMappingBehavior.CreateEmptyTable;
var result = await db.SelectOneAsync <User, int>(new UserSelectionOneFactory(), 2);
Assert.AreEqual("Pat", result.UserName);
Assert.AreEqual(30, result.Age);
Assert.AreEqual(null, result.Email);
}
public async Task SelectOne_WithCondition_And_DefaultModelFactory()
{
var db = new EasyDb();
db.SetConnectionStringSettings(DbConstants.SqlDb1, DbConstants.SqlProviderName);
db.DefaultMappingBehavior = DefaultMappingBehavior.CreateEmptyTable;
var result = await db.SelectOneAsync <User>(Check.Op("Age", ">", 26));
Assert.AreEqual("Pat", result.UserName);
Assert.AreEqual(30, result.Age);
Assert.AreEqual(null, result.Email);
}
public async Task Delete()
{
var db = new EasyDb();
db.SetConnectionStringSettings(DbConstants.SqlDb1, DbConstants.SqlProviderName);
db.DefaultMappingBehavior = DefaultMappingBehavior.CreateEmptyTable;
var model = await db.SelectOneAsync <User>(Check.Op("Id", 3));
var result = await db.DeleteAsync <User>(model, Check.Op("Id", 3));
Assert.AreEqual(1, result);
}
public async Task Update_WithFactory()
{
var db = new EasyDb();
db.SetConnectionStringSettings(DbConstants.SqlDb1, DbConstants.SqlProviderName);
db.DefaultMappingBehavior = DefaultMappingBehavior.CreateEmptyTable;
var model = await db.SelectOneAsync <User>(Check.Op("Id", 4));
model.UserName += " updated";
int result = await db.UpdateAsync <User>(new UserUdpateFactory(), model);
Assert.AreEqual(1, result);
}
public override async Task ExecuteAsync(EasyDb db)
{
try
{
await db.InsertAsync <User>(new User { UserName = "******" });
var user = await db.SelectOneAsync <User>(Check.Op("Id", 1));
// constraint on user permission
await db.DeleteAsync <User>(user, Check.Op("Id", 1));
}
catch (System.Exception ex)
{
throw ex;
}
}
public async Task Update()
{
var db = new EasyDb();
db.DefaultMappingBehavior = DefaultMappingBehavior.CreateEmptyTable;
db.SetTable <User>("User")
.SetPrimaryKeyColumn("Id", p => p.Id);
db.SetConnectionStringSettings(DbConstants.SqlDb1, DbConstants.SqlProviderName);
var model = await db.SelectOneAsync <User>(Check.Op("Id", 2));
model.UserName += " updated";
var result = await db.UpdateAsync <User>(model, Check.Op("Id", model.Id));
Assert.AreEqual(1, result);
}
public async Task SelectOne_WithCommand_And_DefaultModelFactory()
{
var db = new EasyDb();
db.SetConnectionStringSettings(DbConstants.SqlDb1, DbConstants.SqlProviderName);
db.DefaultMappingBehavior = DefaultMappingBehavior.CreateEmptyTable;
User result = null;
using (var command = db.CreateSqlCommand("select * from [User] where [Id]=@id").AddInParameter("@id", 1))
{
result = await db.SelectOneAsync <User>(command);
}
Assert.AreEqual("Marie", result.UserName);
Assert.AreEqual(null, result.Age);
Assert.AreEqual("*****@*****.**", result.Email);
}
public async Task SelectOne_WithCondition_And_ModelFactory()
{
var db = new EasyDb();
db.SetConnectionStringSettings(DbConstants.SqlDb1, DbConstants.SqlProviderName);
db.DefaultMappingBehavior = DefaultMappingBehavior.CreateEmptyTable;
var result = await db.SelectOneAsync <User>(Check.Op("Age", ">", 26), (reader, idb) =>
{
return(new User
{
Id = (int)reader["Id"],
UserName = ((string)reader["UserName"]).Trim(),
Email = idb.CheckDBNullAndConvertTo <string>(reader["Email"])?.Trim(),
Age = idb.CheckDBNullAndConvertTo <int?>(reader["Age"])
});
});
Assert.AreEqual("Pat", result.UserName);
Assert.AreEqual(30, result.Age);
Assert.AreEqual(null, result.Email);
}
