public void TestBuildComposedDisjunctionWithSimpleExpressionCorrectly()
{
var findByParameters = Parameters(
KeyValuePair.Create <string, object>("0", "Fake First Name"),
KeyValuePair.Create <string, object>("1", "Fake Last Name")
);
var findByParametersExpected = Parameters(findByParameters);
FormattableString firstExpression = $"{Sql.Column("FirstName")} = @{"0"}";
FormattableString firstJunctionExpression = $"({firstExpression})";
FormattableString secondExpression = $"{Sql.Column("LastName")} = @{"1"}";
FormattableString secondJunctionExpression = $"({secondExpression})";
FormattableString expressionCommand = $"{firstJunctionExpression} AND {secondJunctionExpression}";
DapperCommand expectedCommand = Command(expressionCommand, findByParametersExpected);
var firstNameCriterion = Restrictions.Equal("FirstName", findByParameters["0"]);
var lastNameCriterion = Restrictions.Equal("LastName", findByParameters["1"]);
var junction1 = Restrictions
.Disjunction()
.Add(firstNameCriterion);
var junction2 = Restrictions
.Disjunction()
.Add(lastNameCriterion);
commandBuilder
.AddExpression(junction1.GetExpressionBuilder())
.AddExpression(junction2.GetExpressionBuilder());
AssertCommand(expectedCommand, commandBuilder.Build());
}
public static T QueryFirstOrDefault <T>(this IDbConnection dbConnection, DapperCommand command)
{
CheckDapperCommand(command);
CommandDefinition commandDefinition = CreateCommandDefine(command);
return(dbConnection.QueryFirstOrDefault <T>(commandDefinition));
}
public static T ExecuteScalar <T>(this IDbConnection dbConnection, DapperCommand command)
{
CheckDapperCommand(command);
CommandDefinition commandDefinition = CreateCommandDefine(command);
return(dbConnection.ExecuteScalar <T>(commandDefinition));
}
public void Conn()
{
//string sql = "insert into tuser values (@a,@b)";
//PgSqlHelper.ExecuteNonQuery(sql, new NpgsqlParameter("@a",1), new NpgsqlParameter("@b",1 ));
var list = DapperCommand <TUser> .GetList("select id,name from tuser");
DapperCommand <TUser> .Update("");
}
/// <summary>
/// 创建CommandDefinition实例
/// </summary>
/// <param name="dapperCommand"></param>
/// <returns></returns>
public static CommandDefinition CreateCommandDefine(DapperCommand dapperCommand)
{
return(new CommandDefinition(
dapperCommand.CommandText,
dapperCommand.Parameters,
dapperCommand.Transaction,
dapperCommand.CommandTimeout,
dapperCommand.CommandType,
dapperCommand.Flags,
dapperCommand.CancellationToken));
}
private static void CheckDapperCommand(DapperCommand command)
{
if (command == null)
{
throw new ArgumentNullException("command");
}
if (string.IsNullOrEmpty(command.CommandText))
{
throw new ArgumentNullException("command.CommandText");
}
}
/// <summary>
/// 登录
/// </summary>
/// <returns></returns>
public ActionResult AjaxLogin(string UserName, string PassWord)
{
AjaxResult json = new AjaxResult();
PassWord = PassWord.ToMD5();
Sys_UserAccount SystemUser = DapperCommand.SelectSingle <Sys_UserAccount>(new { UserName, PassWord }, " UserName=@UserName and PassWord=@PassWord");
if (SystemUser == null)
{
json.Message = "用户名或密码不正确！";
json.Code = 1;
json.Result = false;
}
else if (SystemUser.Status == 1)
{
json.Message = "账户已锁定，请与管理员联系！";
json.Code = 1;
json.Result = false;
}
else
{
FormsAuthen(SystemUser, SystemUser.UserName);
SystemUser.LastTime = DateTime.Now;
SystemUser.Update();
json.Message = "登录成功！";
json.Code = 0;
json.Result = true;
try
{
UserPermisstionsOperate userPermisstionsOperate = new UserPermisstionsOperate(SystemUser.GUID);
userPermisstionsOperate.StoragePermissions();
if (SystemUser.IsAdmin != 1)
{
//判断是否有权限
if (userPermisstionsOperate.HasRightList().Count == 0)
{
json.Message = "此账号未分配权限！请与管理员联系!";
json.Code = 1;
json.Result = false;
}
}
}
catch (Exception)
{
json.Message = "此账号未分配权限！请与管理员联系!";
json.Code = 1;
json.Result = false;
}
}
return(Content(json.ToJson()));
}
public void TestBuildSimpleExpressionCorrectly()
{
var findByParameters = Parameters(KeyValuePair.Create <string, object>("0", "fake name"));
var findByParametersExpected = Parameters(findByParameters);
FormattableString expression = $"{Sql.Column("FirstName")} = @{"0"}";
FormattableString expressionCommand = $"{expression}";
DapperCommand expectedCommand = Command(expressionCommand, findByParametersExpected);
var firstNameCriterion = Restrictions.Equal("FirstName", findByParameters["0"]);
commandBuilder.AddExpression(firstNameCriterion.GetExpressionBuilder());
AssertCommand(expectedCommand, commandBuilder.Build());
}
private static void PreparePageCommand(IDbConnection connection, DapperCommand command, int startRowIndex, int maximumRows)
{
if (startRowIndex == 0 && maximumRows == int.MaxValue)
{
return;
}
switch (connection)
{
case MySql.Data.MySqlClient.MySqlConnection conn:
PrepareMySqlPageCommand(command, startRowIndex, maximumRows);
break;
default:
throw new System.NotSupportedException($"not supported database type : {connection.GetType().FullName}");
}
}
private static void PrepareMySqlPageCommand(DapperCommand command, int startRowIndex, int maximumRows)
{
command.CommandText = $"{command.CommandText} LIMIT {startRowIndex},{maximumRows}";
}
public static IEnumerable <TReturn> Query <TFirst, TSecond, TThird, TFourth, TReturn>(this IDbConnection connection, DapperCommand command, Func <TFirst, TSecond, TThird, TFourth, TReturn> map, string splitOn, int startRowIndex = 0, int maximumRows = int.MaxValue)
{
CheckDapperCommand(command);
PreparePageCommand(connection, command, startRowIndex, maximumRows);
bool buffered = (command.Flags & CommandFlags.Buffered) != 0;
return(connection.Query(
sql: command.CommandText,
map: map,
param: command.Parameters,
transaction: command.Transaction,
buffered: buffered,
splitOn: splitOn,
commandTimeout: command.CommandTimeout,
commandType: command.CommandType));
}
public static IEnumerable <TReturn> Query <TReturn>(this IDbConnection connection, DapperCommand command, int startRowIndex = 0, int maximumRows = int.MaxValue)
{
CheckDapperCommand(command);
PreparePageCommand(connection, command, startRowIndex, maximumRows);
CommandDefinition commandDefinition = CreateCommandDefine(command);
return(connection.Query <TReturn>(commandDefinition));
}
public static IEnumerable <TReturn> Query <TReturn>(this IDbConnection connection, DapperCommand command)
{
CheckDapperCommand(command);
CommandDefinition commandDefinition = CreateCommandDefine(command);
return(connection.Query <TReturn>(commandDefinition));
}
/// <summary>
/// 缓存权限
/// </summary>
public void StoragePermissions()
{
#region 用户缓存
//获取用户 Model
Sys_UserAccount admin = DapperCommand.SelectSingle <Sys_UserAccount>(new { GUID }, " GUID=@GUID ");
Cache.AddCache(GUID.ToString() + "-Admin", admin, SystemExtends.CacheExpiredTime);
#endregion
if (GetAdmin.IsAdmin == 0)
{
#region 角色缓存
//获取用户角色集合
List <Sys_UserRole> listUserRole = DapperCommand.Select <Sys_UserRole>(new { UserAccId = GUID }, " UserAccId=@UserAccId ").ToList();
//获取角色OID集合
List <string> listRoleOID = new List <string>();
listUserRole.ForEach(x =>
{
if (x.RoleID != null && x.RoleID != "")
{
string item = "'" + x.RoleID + "'";
if (!listRoleOID.Contains(item))
{
listRoleOID.Add(item);
}
}
});
//获取角色集合 保存至 缓存
List <Sys_Role> listRoles = DapperCommand.Select <Sys_Role>(new { GUID = listRoleOID.ToArray() }, " GUID in ({0}) and Deleted = 0 ").ToList();
Cache.AddCache(GUID.ToString() + "-Roles", listRoles, SystemExtends.CacheExpiredTime);
#endregion
#region 权限缓存
//获取角色权限集合
List <Sys_RoleRight> listRoleRight = DapperCommand.Select <Sys_RoleRight>(new { RoleID = listRoleOID.ToArray() }, " RoleID in ({0}) Deleted = 0 ").ToList();
//获取权限OID集合
List <string> listRightOID = new List <string>();
listRoleRight.ForEach(x =>
{
if (x.RightID != null && x.RightID != "")
{
string item = "'" + x.RightID + "'";
if (!listRightOID.Contains(item))
{
listRightOID.Add(item);
}
}
});
//获取权限集合 保存至缓存
List <Sys_Right> listRights = DapperCommand.Select <Sys_Right>(new { GUID = listRightOID.ToArray() }, " GUID in ({0}) Deleted = 0 ").ToList();
Cache.AddCache(GUID.ToString() + "-Rights", listRights, SystemExtends.CacheExpiredTime);
#endregion
#region 模块缓存
//获取模块OID
List <string> listModulesOID = new List <string>();
listRights.ForEach(x =>
{
if (x.ForModuleID != null && x.ForModuleID != "")
{
string item = "'" + x.ForModuleID + "'";
if (!listModulesOID.Contains(item))
{
listModulesOID.Add(item);
}
}
});
//获取模块集合 保存至缓存
List <Sys_Module> listModule = DapperCommand.Select <Sys_Module>(new { GUID = listModulesOID.ToArray() }, " GUID in ({0}) Deleted = 0 ").ToList();
Cache.AddCache(GUID.ToString() + "-Module", listModule, SystemExtends.CacheExpiredTime);
#endregion
}
}
protected void AssertCommand(DapperCommand expected, DapperCommand actual)
{
AssertExpression(expected.Command, actual.Command);
AssertObject(expected.Values, actual.Values);
}
