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 SelectAll_WithCommandAndFactory()
{
var db = new EasyDb();
db.SetConnectionStringSettings(DbConstants.SqlDb1, DbConstants.SqlProviderName);
db.DefaultMappingBehavior = DefaultMappingBehavior.CreateEmptyTable;
List <User> result = null;
using (var command = db.CreateSqlCommand("select * from [User]"))
{
result = await db.SelectAllAsync <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(4, result.Count);
Assert.AreEqual("Marie", result[0].UserName);
Assert.AreEqual(null, result[0].Age);
Assert.AreEqual("*****@*****.**", result[0].Email);
Assert.AreEqual("Pat", result[1].UserName);
Assert.AreEqual(30, result[1].Age);
Assert.AreEqual(null, result[1].Email);
}
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 SelectAll_WithCommandAndDefaultFactory()
{
var db = new EasyDb();
db.SetConnectionStringSettings(DbConstants.SqlDb1, DbConstants.SqlProviderName);
db.DefaultMappingBehavior = DefaultMappingBehavior.CreateEmptyTable;
List <User> result = null;
using (var command = db.CreateSqlCommand("select * from [User]"))
{
result = await db.SelectAllAsync <User>(command);
}
Assert.AreEqual(4, result.Count);
Assert.AreEqual("Marie", result[0].UserName);
Assert.AreEqual(null, result[0].Age);
Assert.AreEqual("*****@*****.**", result[0].Email);
Assert.AreEqual("Pat", result[1].UserName);
Assert.AreEqual(30, result[1].Age);
Assert.AreEqual(null, result[1].Email);
}
public async Task SelectAll_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;
List <User> result = null;
using (var command = db.CreateSqlCommand("select * from [users]"))
{
result = await db.SelectAllAsync <User>(command);
}
Assert.AreEqual(4, result.Count);
Assert.AreEqual("Marie", result[0].UserName);
Assert.AreEqual(null, result[0].Age);
Assert.AreEqual("*****@*****.**", result[0].Email);
Assert.AreEqual("Pat", result[1].UserName);
Assert.AreEqual(30, result[1].Age);
Assert.AreEqual(null, result[1].Email);
}
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 DbCommand CreateCommand(EasyDb db, int id)
{
return(db.CreateSqlCommand("select * from [User] where Id=@id")
.AddInParameter("@id", id));
}
public DbCommand CreateCommand(EasyDb db, User model)
{
return(db.CreateSqlCommand("insert into [User](UserName) output inserted.id values(@username)")
.AddInParameter("@username", model.UserName));
}
public DbCommand CreateCommand(EasyDb db, int criteria)
{
return(db.CreateSqlCommand("select * from [Post] where [UserId]=@userid").AddInParameter("@userid", criteria));
}
public DbCommand CreateCommand(EasyDb db, NullCriteria criteria)
{
return(db.CreateSqlCommand("select * from [User]"));
}
public DbCommand CreateCommand(EasyDb db, User model)
{
return(db.CreateSqlCommand("update [User] set UserName=@username where Id=@id")
.AddInParameter("@username", model.UserName)
.AddInParameter("@id", model.Id));
}
public DbCommand CreateCommand(EasyDb db, Post model)
{
return(db.CreateSqlCommand("delete from [Post] where Id=@id")
.AddInParameter("@id", model.Id));
}