public void GetCommand()
{
var service = this.GetService();
var db = new EasyDb();
db.SetConnectionStringSettings(@"Data Source=(LocalDb)\MSSQLLocalDB;Initial Catalog=testdb;Integrated Security=True", "System.Data.SqlClient");
service.SetDb(db);
var result = service.GetCommand <User>(Check.Op("Id", 10));
Assert.AreEqual("select [Id],[UserName],[RoleId] from [User] where [Id]=@id", result.CommandText);
Assert.AreEqual(CommandType.Text, result.CommandType);
Assert.AreEqual(1, result.Parameters.Count);
Assert.AreEqual("@id", result.Parameters[0].ParameterName);
Assert.AreEqual(10, result.Parameters[0].Value);
}
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 Insert_WithFactory()
{
var db = new EasyDb();
db.SetConnectionStringSettings(DbConstants.SqlDb1, DbConstants.SqlProviderName);
db.DefaultMappingBehavior = DefaultMappingBehavior.CreateEmptyTable;
var user = new User
{
UserName = "******"
};
var result = await db.InsertAsync <User>(new UserInsertFactory(), user);
Assert.AreEqual(6, result);
Assert.AreEqual(6, user.Id);
}
public void GetQuery_WithNoMappingAndNoCondition()
{
var service = this.GetService();
var db = new EasyDb();
db.SetConnectionStringSettings(@"Data Source=(LocalDb)\MSSQLLocalDB;Initial Catalog=testdb;Integrated Security=True", "System.Data.SqlClient");
service.SetDb(db);
var model = new User {
};
var columnValues = DbHelper.GetUpdateColumnValues <User>(model);
var columns = new List <string>(columnValues.Keys);
var result = service.GetQuery <User>(columns, null, null);
Assert.AreEqual("update [User] set [Id]=@id,[UserName]=@username,[RoleId]=@roleid", 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 void GetQuery_WithMapping()
{
var service = this.GetService();
var table = new Table <User>("users")
.SetColumn("Name", p => p.UserName)
.SetColumn("RoleId", p => p.RoleId, false, true);
var db = new EasyDb();
db.SetConnectionStringSettings(@"Data Source=(LocalDb)\MSSQLLocalDB;Initial Catalog=testdb;Integrated Security=True", "System.Data.SqlClient");
service.SetDb(db);
var condition = Check.Op("Id", 10);
var result = service.GetQuery <User>(null, condition, null, table);
Assert.AreEqual("select [Id],[Name] from [users] where [Id]=@id", result);
}
public async Task SelectAll_WithLimit()
{
var db = new EasyDb();
db.SetConnectionStringSettings(DbConstants.SqlDb1, DbConstants.SqlProviderName);
db.DefaultMappingBehavior = DefaultMappingBehavior.CreateEmptyTable;
var result = await db.SelectAllAsync <User>(2, null, null); // var result = await db.SelectAllAsync<User>(2, null, new string[] { "UserName" });
Assert.AreEqual(2, 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_WithSelectionAllCommandFactory_And_ModelFactory_And_NullCriteria()
{
var db = new EasyDb();
db.SetConnectionStringSettings(DbConstants.SqlDb1, DbConstants.SqlProviderName);
db.DefaultMappingBehavior = DefaultMappingBehavior.CreateEmptyTable;
var result = await db.SelectAllAsync <User>(new UserSelectionAllFactory(), new UserModelFactory());
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_WithSorts()
{
var db = new EasyDb();
db.SetConnectionStringSettings(DbConstants.SqlDb1, DbConstants.SqlProviderName);
db.DefaultMappingBehavior = DefaultMappingBehavior.CreateEmptyTable;
var result = await db.SelectAllAsync <User>(null, null, new string[] { "UserName" });
Assert.AreEqual(4, result.Count);
Assert.AreEqual("Deb", result[0].UserName);
Assert.AreEqual(null, result[0].Age);
Assert.AreEqual("*****@*****.**", result[0].Email);
Assert.AreEqual("Ken", result[1].UserName);
Assert.AreEqual(25, result[1].Age);
Assert.AreEqual(null, result[1].Email);
}
public void CreateModel_ConvertStringToInt()
{
var service = this.GetService();
var db = new EasyDb();
var data = new Dictionary <string, FakeContainerItem>
{
["id"] = new FakeContainerItem {
IsDBNull = false, Name = "id", Value = "10"
},
["first"] = new FakeContainerItem {
IsDBNull = false, Name = "first", Value = "Marie"
}
};
var reader = new MyReaderContainer(data);
var result = (FakeModel)service.CreateModel <FakeModel>(reader, db);
Assert.AreEqual(10, result.id);
}
public void SetTable_WithColumn()
{
var db = new EasyDb();
db
.SetTable <User>("users")
.SetColumn("username", p => p.UserName);
var table = db.GetTable <User>();
var result = table.MappingByColumnName["username"] as Column;
Assert.AreEqual(typeof(User), table.ModelType);
Assert.AreEqual("users", table.TableName);
Assert.AreEqual(typeof(User), result.ModelType);
Assert.AreEqual("users", result.TableName);
Assert.AreEqual(typeof(Column), result.GetType());
Assert.AreEqual("username", result.ColumnName);
Assert.AreEqual("UserName", result.Property.Name);
Assert.AreEqual(typeof(User), result.ModelType);
Assert.AreEqual(false, result.IsDatabaseGenerated);
Assert.AreEqual(false, result.IsIgnored);
}
public void GetQuery_WithMapping()
{
var service = this.GetService();
var db = new EasyDb();
db.SetConnectionStringSettings(@"Data Source=(LocalDb)\MSSQLLocalDB;Initial Catalog=testdb;Integrated Security=True", "System.Data.SqlClient");
service.SetDb(db);
var condition = Check.Op("Id", 10).And(Check.Op("RoleId", 5));
var table = new Table <User>("users")
.SetPrimaryKeyColumn("Id", p => p.Id)
.SetColumn("Name", p => p.UserName);
var model = new User {
};
var columnValues = DbHelper.GetUpdateColumnValues <User>(model, table, condition);
var columns = new List <string>(columnValues.Keys);
var result = service.GetQuery <User>(columns, condition, table);
Assert.AreEqual("update [users] set [Name]=@name where [Id]=@id and [RoleId]=@roleid", result);
}
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);
}
public void GetQuery_WithNoMapping_NoIgnoreKeyResult()
{
var service = this.GetService();
var db = new EasyDb();
db.SetConnectionStringSettings(@"Data Source=(LocalDb)\MSSQLLocalDB;Initial Catalog=testdb;Integrated Security=True", "System.Data.SqlClient");
service.SetDb(db);
var model = new User
{
UserName = "******",
RoleId = 10
};
var table = db.TryGetTable <User>();
var columnValues = DbHelper.GetInsertColumnValues <User>(model, table);
var columns = new List <string>(columnValues.Keys);
var result = service.GetQuery <User>(columns, table);
Assert.AreEqual("insert into [User] ([Id],[UserName],[RoleId]) values (@id,@username,@roleid)", result);
}
public async Task SelectOne_WithoutCompleteMappingAndeIgnoreCase_Fail()
{
var db = new EasyDb();
db.SetConnectionStringSettings(DbConstants.SqlDbLikeMySql, DbConstants.SqlProviderName);
var table = db.SetTable <User>("users")
.SetPrimaryKeyColumn("id", p => p.Id);
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(null, result.UserName);
Assert.AreEqual(null, result.Age);
Assert.AreEqual(null, result.Email);
}
public DbCommand CreateCommand(EasyDb db, int criteria)
{
return(db.CreateSqlCommand("select * from [Post] where [UserId]=@userid").AddInParameter("@userid", criteria));
}
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 void DiscoverTable()
{
var db = new EasyDb();
var result = db.DiscoverMappingFor <UserWithAttributes>();
Assert.AreEqual("Users", result.TableName);
Assert.AreEqual(typeof(UserWithAttributes), result.ModelType);
Assert.AreEqual(5, result.MappingByColumnName.Count);
// Id
var idColumn = result.MappingByColumnName["Id"];
Assert.AreEqual(typeof(PrimaryKeyColumn), idColumn.GetType());
Assert.AreEqual("Id", idColumn.ColumnName);
Assert.AreEqual("Id", idColumn.Property.Name);
Assert.AreEqual(typeof(UserWithAttributes), idColumn.ModelType);
Assert.AreEqual("Users", idColumn.TableName);
Assert.AreEqual(true, idColumn.IsDatabaseGenerated);
Assert.AreEqual(false, idColumn.IsIgnored);
// UserName
var userNameColumn = result.MappingByColumnName["UserName"];
Assert.AreEqual(typeof(Column), userNameColumn.GetType());
Assert.AreEqual("UserName", userNameColumn.ColumnName);
Assert.AreEqual("UserName", userNameColumn.Property.Name);
Assert.AreEqual(typeof(UserWithAttributes), userNameColumn.ModelType);
Assert.AreEqual("Users", userNameColumn.TableName);
Assert.AreEqual(false, userNameColumn.IsDatabaseGenerated);
Assert.AreEqual(false, userNameColumn.IsIgnored);
// RowVersion
var rowVersionColumn = result.MappingByColumnName["RowVersion"];
Assert.AreEqual(typeof(Column), rowVersionColumn.GetType());
Assert.AreEqual("RowVersion", rowVersionColumn.ColumnName);
Assert.AreEqual("RowVersion", rowVersionColumn.Property.Name);
Assert.AreEqual(typeof(UserWithAttributes), rowVersionColumn.ModelType);
Assert.AreEqual("Users", rowVersionColumn.TableName);
Assert.AreEqual(true, rowVersionColumn.IsDatabaseGenerated);
Assert.AreEqual(false, userNameColumn.IsIgnored);
// role id
var roleIdColumn = result.MappingByColumnName["role_id"];
Assert.AreEqual(typeof(Column), roleIdColumn.GetType());
Assert.AreEqual("role_id", roleIdColumn.ColumnName);
Assert.AreEqual("RoleId", roleIdColumn.Property.Name);
Assert.AreEqual(typeof(UserWithAttributes), roleIdColumn.ModelType);
Assert.AreEqual("Users", roleIdColumn.TableName);
Assert.AreEqual(false, roleIdColumn.IsDatabaseGenerated);
Assert.AreEqual(false, roleIdColumn.IsIgnored);
// Age
var ageColumn = result.MappingByColumnName["Age"];
Assert.AreEqual(typeof(Column), ageColumn.GetType());
Assert.AreEqual("Age", ageColumn.ColumnName);
Assert.AreEqual("Age", ageColumn.Property.Name);
Assert.AreEqual(typeof(UserWithAttributes), ageColumn.ModelType);
Assert.AreEqual("Users", ageColumn.TableName);
Assert.AreEqual(false, ageColumn.IsDatabaseGenerated);
Assert.AreEqual(true, ageColumn.IsIgnored);
}
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));
}
public DbCommand CreateCommand(EasyDb db, NullCriteria criteria)
{
return(db.CreateSqlCommand("select * from [User]"));
}
