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]")); }