public void Delete_All_Test()
{
var query = _DapperContext.DbSet <SysUser>().Delete();
var sqlBuilder = query.SqlBuilder;
var sql = sqlBuilder.GetSql();
Assert.Equal("DELETE [SysUser];", sql);
}
public void Calculate_Groupby_Test()
{
var query = _DapperContext.DbSet <SysUser>().Count(m => m.Id).GroupBy(m => m.IsAdmin);
var sqlBuilder = query.SqlBuilder;
var sql = sqlBuilder.GetSql();
Assert.Equal("SELECT COUNT([Id]),[IsAdmin] FROM [SysUser] GROUP BY [IsAdmin];", sql);
}
public void Select_MultiTable_WithJoin_Test()
{
var query = _DapperContext.DbSet <SysUser>()
.Select <SysRole, SysUserRole>((user, role, userRole) => new { Id = user.Id, Account = user.Account, UserRoleName = role.RoleName })
.InnerJoin <SysUserRole>((u, r) => u.Id == r.UserId)
.InnerJoin <SysUserRole, SysRole>((ur, r) => ur.RoleId == r.Id);
var sqlBuilder = query.SqlBuilder;
var sql = sqlBuilder.GetSql();
Assert.Equal("SELECT a.[Id] [Id],a.[Account] [Account],b.[RoleName] [UserRoleName] FROM [SysUser] a INNER JOIN [SysUserRole] c ON a.[Id] = c.[UserId] INNER JOIN [SysRole] b ON c.[RoleId] = b.[Id];", sql);
}
public void Select_Count_Test()
{
var query = _DapperContext.DbSet <SysUser>().Count();
var sqlBuilder = query.SqlBuilder;
var sql = sqlBuilder.GetSql();
Assert.Equal("SELECT COUNT(*) FROM [SysUser];", sql);
}
public void Insert_New_Test()
{
var query = _DapperContext.DbSet <SysUser>().Insert(() => new SysUser()
{
Account = "admin", Password = "******", IsAdmin = false, UserRoleName = "Test", CreateTime = Convert.ToDateTime("1987-01-28")
});
var sqlBuilder = query.SqlBuilder;
var sql = sqlBuilder.GetSql();
var paramVals = sqlBuilder.GetSqlParams();
Assert.NotEmpty(sql);
Assert.Equal("INSERT INTO [SysUser] ([Account],[Password],[IsAdmin],[CreateTime]) VALUES (@param0,@param1,@param2,@param3);", sql);
Assert.Equal(4, paramVals.Count);
}
public async Task <IActionResult> Login(LoginViewModel model, string returnUrl = null)
{
ViewData["ReturnUrl"] = returnUrl;
if (!ModelState.IsValid)
{
return(View(model));
}
try
{
var users = await _DapperContext.DbSet <SysUser>()
.Select <SysUserRole>((u, r) => new SysUser()
{
Id = u.Id, IsAdmin = u.IsAdmin, LoginName = u.LoginName, LoginPwd = u.LoginPwd, CreateTime = u.CreateTime, UserName = u.UserName, UserRoleNo = u.UserRoleNo, RoleName = r.Name
})
.Join <SysUserRole>((u, r) => u.UserRoleNo == r.No)
.Where(m => m.LoginName == model.Account && m.LoginPwd == model.Password)
.ToListAsync <SysUser>();
if (users != null && users.First().LoginName == model.Account)
{
return(RedirectToAction("Index", "Home"));
}
}
catch (Exception ex)
{
}
return(RedirectToAction("Index"));
}
public void Update_All_Test()
{
var query = _DapperContext.DbSet <SysUser>().Update(() => new SysUser()
{
Account = "AllUpdate", Password = "******", IsAdmin = true
});
var sqlBuilder = query.SqlBuilder;
var sql = sqlBuilder.GetSql();
var sqlParams = sqlBuilder.GetSqlParams();
Assert.NotEmpty(sql);
Assert.Equal("UPDATE [SysUser] SET [Account] = @param0,[Password] = @param1,[IsAdmin] = @param2;", sql);
Assert.NotNull(sqlParams);
Assert.Equal(3, sqlParams.Count);
Assert.Equal(1, sqlParams["@param2"]);
}
public void Select_Orderby_Test()
{
var query = _DapperContext.DbSet <SysUser>().Select().OrderBy(m => m.CreateTime);
var sqlBuilder = query.SqlBuilder;
var sql = sqlBuilder.GetSql();
Assert.Equal("SELECT * FROM [SysUser] a ORDER BY a.[CreateTime] ASC;", sql);
}
public void Select_InNewList_Test()
{
var query = _DapperContext.DbSet <SysUser>().Select().Where(m => m.Id.In(new List <int> {
1, 2, 3
}));
var sqlBuilder = query.SqlBuilder;
var sql = sqlBuilder.GetSql();
Assert.Equal("SELECT * FROM [SysUser] a WHERE a.[Id] IN (1,2,3);", sql);
}
public void Select_Sample_Test()
{
var query = _DapperContext.DbSet <SysUser>().Select();
var sqlBuilder = query.SqlBuilder;
var sql = sqlBuilder.GetSql();
Assert.Equal("SELECT * FROM [SysUser] a;", sql);
}