public void SetConnectionStringSettings_ChangeQueryService()
{
var db = new EasyDb();
//Assert.IsNull(QueryServiceFactory.Current);
// My SQL
db.SetConnectionStringSettings(@"server=localhost;database=demo;uid=root", "MySql.Data.MySqlClient");
Assert.AreEqual(typeof(MySqlQueryService), QueryServiceFactory.Current.GetType());
db.SetConnectionStringSettings(@"Data Source=(LocalDb)\MSSQLLocalDB;Initial Catalog=testdb;Integrated Security=True", "System.Data.SqlClient");
Assert.AreEqual(typeof(SqlQueryService), QueryServiceFactory.Current.GetType());
}
public void GetQuery_WithMappingAndNoColumnToSet_ThrowException()
{
var service = this.GetService();
var db = new EasyDb();
db.SetConnectionStringSettings(@"Data Source=(LocalDb)\MSSQLLocalDB;Initial Catalog=testdb;Integrated Security=True", "System.Data.SqlClient");
db.SetTable <User>("User")
.SetPrimaryKeyColumn("Id", p => p.Id)
.SetColumn("UserName", p => p.UserName, true);
service.SetDb(db);
var model = new User {
};
bool failed = false;
try
{
var result = service.GetCommand <User>(model, Check.Op("Id", 10).And(Check.Op("RoleId", 5)));
}
catch (System.Exception)
{
failed = true;
}
Assert.IsTrue(failed);
}
public void GetCommand_WithMultipleCondition()
{
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 = 1
};
db.DefaultMappingBehavior = DefaultMappingBehavior.CreateEmptyTable;
var result = service.GetCommand <User>(model, Check.Op("Id", 10).And(Check.Op("RoleId", 5)));
Assert.AreEqual("update [User] set [UserName]=@username where [Id]=@id and [RoleId]=@roleid", result.CommandText);
Assert.AreEqual(CommandType.Text, result.CommandType);
Assert.AreEqual(3, result.Parameters.Count);
Assert.AreEqual("@username", result.Parameters[0].ParameterName);
Assert.AreEqual("Marie", result.Parameters[0].Value);
Assert.AreEqual("@id", result.Parameters[1].ParameterName);
Assert.AreEqual(10, result.Parameters[1].Value);
Assert.AreEqual("@roleid", result.Parameters[2].ParameterName);
Assert.AreEqual(5, result.Parameters[2].Value);
}
public async Task SelectAll_WithSelectionAllCommandFactory_And_DefaultModelFactory_And_Criteria()
{
var db = new EasyDb();
db.SetConnectionStringSettings(DbConstants.SqlDb1, DbConstants.SqlProviderName);
db.DefaultMappingBehavior = DefaultMappingBehavior.CreateEmptyTable;
var result = await db.SelectAllAsync <Post, int>(new PostSelectionAllFactory(), 2);
Assert.AreEqual(3, result.Count);
Assert.AreEqual(2, result[0].Id);
Assert.AreEqual("Post 2", result[0].Title);
Assert.AreEqual("Content 2", result[0].Content);
Assert.AreEqual(2, result[0].UserId);
Assert.AreEqual(3, result[1].Id);
Assert.AreEqual("Post 3", result[1].Title);
Assert.AreEqual("Content 3", result[1].Content);
Assert.AreEqual(2, result[1].UserId);
Assert.AreEqual(4, result[2].Id);
Assert.AreEqual("Post 4", result[2].Title);
Assert.AreEqual("Content 4", result[2].Content);
Assert.AreEqual(2, result[2].UserId);
}
public EasyDb GetService()
{
var db = new EasyDb();
db.SetConnectionStringSettings(DbConstants.SqlDb1, DbConstants.SqlProviderName);
return(db);
}
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 PendingTransaction()
{
var db = new EasyDb();
db.SetConnectionStringSettings(DbConstants.SqlDb1, DbConstants.SqlProviderName);
db.DefaultMappingBehavior = DefaultMappingBehavior.CreateEmptyTable;
db.AddPendingOperation(() => db.InsertAsync <User>(new User {
UserName = "******"
}));
db.AddPendingOperation(() => db.InsertAsync <User>(new User {
UserName = "******"
}));
db.AddPendingOperation(() => db.InsertAsync <User>(new User {
UserName = "******"
}));
Assert.AreEqual(3, db.PendingOperations.Count);
var result = await db.ExecutePendingOperationsAsync();
Assert.IsTrue(result);
Assert.AreEqual(0, db.PendingOperations.Count);
}
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 model = new User
{
UserName = "******",
RoleId = 10
};
var table = new Table <User>("users")
.SetPrimaryKeyColumn("Id", p => p.Id);
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 [users] ([UserName],[RoleId]) output inserted.id values (@username,@roleid)", result);
}
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 model = new User
{
UserName = "******",
RoleId = 10
};
db.DefaultMappingBehavior = DefaultMappingBehavior.CreateEmptyTable;
var result = service.GetCommand <User>(model);
Assert.AreEqual("insert into [User] ([UserName],[RoleId]) output inserted.id values (@username,@roleid)", result.CommandText);
Assert.AreEqual(CommandType.Text, result.CommandType);
Assert.AreEqual(2, result.Parameters.Count);
Assert.AreEqual("@username", result.Parameters[0].ParameterName);
Assert.AreEqual("Marie", result.Parameters[0].Value);
Assert.AreEqual("@roleid", result.Parameters[1].ParameterName);
Assert.AreEqual(10, result.Parameters[1].Value);
}
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 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 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_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 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_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 EasyDb GetDb()
{
var db = new EasyDb();
db.SetConnectionStringSettings(@"server=localhost;database=testdb;uid=root", "MySql.Data.MySqlClient");
db.DefaultMappingBehavior = DefaultMappingBehavior.Reflection;
return(db);
}
public EasyDb GetService()
{
var db = new EasyDb();
db.SetConnectionStringSettings(DbConstants.OleDbConnectionString, DbConstants.OleDbProviderName);
var table = db.SetTable <OleDbCategory>("Categories")
.SetPrimaryKeyColumn("CategoryID", p => p.CategoryID, false);
return(db);
}
public async Task PendingTransaction_Fail()
{
var db = new EasyDb();
db.SetConnectionStringSettings(DbConstants.SqlDb1, DbConstants.SqlProviderName);
db.DefaultMappingBehavior = DefaultMappingBehavior.CreateEmptyTable;
var result = await db.ExecuteTransactionFactoryAsync(new MyTransactionOperations2());
Assert.IsFalse(result);
}
public void GetQuery_WithLimit()
{
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.GetQuery <User>(10, null, null);
Assert.AreEqual("select top 10 [Id],[UserName],[RoleId] from [User]", result);
}
public void GetQuery_WithSortsMySQL()
{
var service = this.GetService();
var db = new EasyDb();
db.SetConnectionStringSettings(@"server=localhost;database=demo;uid=root", "MySql.Data.MySqlClient");
service.SetDb(db);
var result = service.GetQuery <User>(null, null, new string[] { "Id", "RoleId DESC" });
Assert.AreEqual("select `Id`,`UserName`,`RoleId` from `User` order by `Id`,`RoleId` DESC", result);
}
public void GetQuery_WithLimitMySQL()
{
var service = this.GetService();
var db = new EasyDb();
db.SetConnectionStringSettings(@"server=localhost;database=demo;uid=root", "MySql.Data.MySqlClient");
service.SetDb(db);
var result = service.GetQuery <User>(10, null, null);
Assert.AreEqual("select `Id`,`UserName`,`RoleId` from `User` limit 10", result);
}
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 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 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 void GetQuery_WithMultipleCondition()
{
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 result = service.GetQuery <User>(condition);
Assert.AreEqual("delete from [User] where [Id]=@id and [RoleId]=@roleid", result);
}
public void GetQuery_WithLimitAndConditionAndSorts()
{
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);
var result = service.GetQuery <User>(10, condition, new string[] { "Id", "RoleId DESC" });
Assert.AreEqual("select top 10 [Id],[UserName],[RoleId] from [User] where [Id]=@id order by [Id],[RoleId] DESC", 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 void GetQuery_WithMySQL()
{
var service = this.GetService();
var db = new EasyDb();
db.SetConnectionStringSettings(@"server=localhost;database=demo;uid=root", "MySql.Data.MySqlClient");
service.SetDb(db);
var condition = Check.Op("Id", 10).And(Check.Op("RoleId", 5));
var result = service.GetQuery <User>(condition);
Assert.AreEqual("delete from `User` where `Id`=@id and `RoleId`=@roleid", result);
}
public void GetQuery_WithIsNotNull()
{
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.IsNotNull("Id");
var result = service.GetQuery <User>(condition);
Assert.AreEqual("delete from [User] where [Id] is not null", result);
}