public static PageDataView <T> GetPageData <T>(PageCriteria criteria, object param = null, string connectionString = null)
{
using (var conn = DataBaseConfig.GetSqlConnection(connectionString))
{
var p = new DynamicParameters();
string proName = "ProcGetPageData";
p.Add("@TableName", criteria.TableName);
p.Add("@PrimaryKey", criteria.PrimaryKey);
p.Add("@Fields", criteria.Fields);
p.Add("@Condition", criteria.Condition);
p.Add("@CurrentPage", criteria.CurrentPage);
p.Add("@PageSize", criteria.PageSize);
p.Add("@Sort", criteria.Sort);
p.Add("@RecordCount", dbType: DbType.Int32, direction: ParameterDirection.Output);
var pageData = new PageDataView <T>();
pageData.Items = conn.Query <T>(proName, p, commandType: CommandType.StoredProcedure).ToList();
pageData.TotalNum = p.Get <int>("@RecordCount");
pageData.TotalPageCount = Convert.ToInt32(Math.Ceiling(pageData.TotalNum * 1.0 / criteria.PageSize));
pageData.CurrentPage = criteria.CurrentPage > pageData.TotalPageCount ? pageData.TotalPageCount : criteria.CurrentPage;
return(pageData);
}
}
/// <summary>
/// 分页查询
/// </summary>
/// <param name="startPage">页码</param>
/// <param name="pageSize">单页数据数</param>
/// <param name="rowCount">行数</param>
/// <param name="where">条件</param>
/// <param name="order">排序</param>
/// <returns></returns>
public IEnumerable <Menu> LoadPageList(int parentId, int startPage, int pageSize, out int rowCount, string connectionString = null)
{
rowCount = 1;
using (IDbConnection conn = DataBaseConfig.GetSqlConnection(connectionString))
{
return(conn.Query <Menu>("sp_GetMenus", new { ParentId = parentId }, commandType: CommandType.StoredProcedure));
}
}
/// <summary>
/// 获取所有用户角色
/// </summary>
/// <param name="connectionString">链接字符串</param>
/// <returns></returns>
public IEnumerable <Controllers> RetriveAllEntity(string connectionString = null)
{
using (IDbConnection conn = DataBaseConfig.GetSqlConnection(connectionString))
{
string querySql = @"SELECT [Id],[Name],[Description],(select count(*) as ActionNum FROM [dbo].[Action] where ControllerId=c.Id) as ActionNum
FROM [dbo].[Controller] as c";
return(conn.Query <Controllers>(querySql));
}
}
/// <summary>
/// 获取所有用户
/// </summary>
/// <param name="connectionString">链接字符串</param>
/// <returns></returns>
public IEnumerable <User> RetriveAllEntity(string connectionString = null)
{
using (IDbConnection conn = DataBaseConfig.GetSqlConnection(connectionString))
{
string querySql = @"SELECT [Id],[UserName],[Password],[Name],[EMail],[MobileNumber],[LastLoginTime],[LoginTimes],[CreateDate],[IsDeleted],[RoleIds]
FROM [dbo].[User]";
return(conn.Query <User>(querySql));
}
}
/// <summary>
/// 获取所有用户
/// </summary>
/// <param name="connectionString">链接字符串</param>
/// <summary>
/// 获取所有用户
/// </summary>
/// <param name="connectionString">链接字符串</param>
/// <returns></returns>
/// <returns></returns>
public IEnumerable <Menu> RetriveAllEntity(string connectionString = null)
{
using (IDbConnection conn = DataBaseConfig.GetSqlConnection(connectionString))
{
string querySql = @"SELECT [Id],[ParentId],[SortNum],[DepthNum],[Name],[Code],[Type],[Url],[Icon],[Description]
FROM [dbo].[Menu] ORDER BY SortNum ASC";
return(conn.Query <Menu>(querySql));
}
}
/// <summary>
/// 修改一个用户角色
/// </summary>
/// <param name="entity">要修改的用户角色/param>
/// <param name="connectionString">链接字符串</param>
/// <returns></returns>
public bool UpdateEntity(Role entity, string connectionString = null)
{
using (IDbConnection conn = DataBaseConfig.GetSqlConnection(connectionString))
{
string updateSql = @"UPDATE [dbo].[Role] SET [Name] = @Name,Description=@Description
WHERE Id = @Id";
return(conn.Execute(updateSql, entity) > 0);
}
}
/// <summary>
/// 根据主键Id删除一个用户角色
/// </summary>
/// <param name="id">主键Id</param>
/// <param name="connectionString">链接字符串</param>
/// <returns></returns>
public bool DeleteEntityById(int id, string connectionString = null)
{
using (IDbConnection conn = DataBaseConfig.GetSqlConnection(connectionString))
{
string deleteSql = @"DELETE FROM [dbo].[Role]
WHERE Id = @Id";
return(conn.Execute(deleteSql, new { Id = id }) > 0);
}
}
/// <summary>
/// 获取所有用户角色
/// </summary>
/// <param name="connectionString">链接字符串</param>
/// <returns></returns>
public IEnumerable <Role> RetriveAllEntity(string connectionString = null)
{
using (IDbConnection conn = DataBaseConfig.GetSqlConnection(connectionString))
{
string querySql = @"SELECT [Id],[Name],[Description]
FROM [dbo].[Role]";
return(conn.Query <Role>(querySql));
}
}
/// <summary>
/// 获取数据列表
/// </summary>
/// <param name="ControllerId"></param>
/// <param name="connectionString"></param>
/// <returns></returns>
public IEnumerable <Actions> RetriveAllEntity(int ControllerId, string connectionString = null)
{
using (IDbConnection conn = DataBaseConfig.GetSqlConnection(connectionString))
{
string querySql = @"SELECT [Id],[ControllerId],[Name],[Description]
FROM [dbo].[Action] WHERE ControllerId=@ControllerId";
return(conn.Query <Actions>(querySql, new { ControllerId = ControllerId }));
}
}
public IEnumerable <Menu> GetListByParentId(int parentId, string connectionString = null)
{
using (IDbConnection conn = DataBaseConfig.GetSqlConnection(connectionString))
{
string querySql = @"SELECT [Id],[ParentId],[SortNum],[DepthNum],[Name],[Code],[Type],[Url],[Icon],[Description]
FROM [dbo].[Menu] WHERE ParentId=@ParentId ORDER BY SortNum ASC";
return(conn.Query <Menu>(querySql, new { ParentId = parentId }));
}
}
public User CheckUser(string UserName, string Password, string connectionString = null)
{
using (IDbConnection conn = DataBaseConfig.GetSqlConnection(connectionString))
{
string querySql = @"SELECT [Id],[UserName],[Password],[Name],[EMail],[MobileNumber],[LastLoginTime],[LoginTimes],[CreateDate],[IsDeleted],[RoleIds]
FROM [dbo].[User]
WHERE UserName = @UserName AND Password=@Password";
return(conn.QueryFirstOrDefault <User>(querySql, new { UserName = UserName, Password = Password }));
}
}
/// <summary>
/// 根据主键Id获取一个用户角色
/// </summary>
/// <param name="id">主键Id</param>
/// <param name="connectionString">链接字符串</param>
/// <returns></returns>
public Role RetriveOneEntityById(int id, string connectionString = null)
{
using (IDbConnection conn = DataBaseConfig.GetSqlConnection(connectionString))
{
string querySql = @"SELECT [Id],[Name],[Description]
FROM [dbo].[Role]
WHERE Id = @Id";
return(conn.QueryFirstOrDefault <Role>(querySql, new { Id = id }));
}
}
/// <summary>
/// 根据主键Id获取一个用户
/// </summary>
/// <param name="id">主键Id</param>
/// <param name="connectionString">链接字符串</param>
/// <returns></returns>
public Menu RetriveOneEntityById(int id, string connectionString = null)
{
using (IDbConnection conn = DataBaseConfig.GetSqlConnection(connectionString))
{
string querySql = @"SELECT [Id],[ParentId],[SortNum],[DepthNum],[Name],[Code],[Type],[Url],[Icon],[Description]
FROM [dbo].[Menu]
WHERE Id = @Id ";
return(conn.QueryFirstOrDefault <Menu>(querySql, new { Id = id }));
}
}
/// <summary>
/// 创建一个用户角色
/// </summary>
/// <param name="entity">用户角色</param>
/// <param name="connectionString">链接字符串</param>
/// <returns></returns>
public bool InsertEntity(Role entity, string connectionString = null)
{
using (IDbConnection conn = DataBaseConfig.GetSqlConnection(connectionString))
{
string insertSql = @"INSERT INTO [dbo].[Role]
([Name],[Description])
VALUES
(@Name,@Description)";
return(conn.Execute(insertSql, entity) > 0);
}
}
/// <summary>
/// 创建一个用户
/// </summary>
/// <param name="entity">用户</param>
/// <param name="connectionString">链接字符串</param>
/// <returns></returns>
public bool InsertEntity(User entity, string connectionString = null)
{
using (IDbConnection conn = DataBaseConfig.GetSqlConnection(connectionString))
{
string insertSql = @"INSERT INTO [dbo].[User]
([UserName],[Password],[Name],[EMail],[MobileNumber],[LastLoginTime],[RoleIds])
VALUES
(@UserName,@Password,@Name,@EMail,@MobileNumber,@RoleIds)";
return(conn.Execute(insertSql, entity) > 0);
}
}
/// <summary>
/// 创建一个用户
/// </summary>
/// <param name="entity">用户</param>
/// <param name="connectionString">链接字符串</param>
/// <returns></returns>
public bool InsertEntity(Menu entity, string connectionString = null)
{
using (IDbConnection conn = DataBaseConfig.GetSqlConnection(connectionString))
{
string insertSql = @"INSERT INTO [dbo].[Menu]
([ParentId],[SortNum],[DepthNum],[Name],[Code],[Type],[Url],[Icon],[Description])
VALUES
(@ParentId,@SortNum,@DepthNum,@Name,@Code,@Type,@Url,@Icon,@Description)";
return(conn.Execute(insertSql, entity) > 0);
}
}
/// <summary>
/// 根据主键Id获取一个用户
/// </summary>
/// <param name="id">主键Id</param>
/// <param name="connectionString">链接字符串</param>
/// <returns></returns>
public User RetriveOneEntityById(int id, string connectionString = null)
{
using (IDbConnection conn = DataBaseConfig.GetSqlConnection(connectionString))
{
string querySql = @"SELECT [Id],[UserName],[Password],[Name],[EMail],[MobileNumber],[LastLoginTime],[LoginTimes],[CreateDate],[IsDeleted],[RoleIds]
FROM [dbo].[User]
WHERE Id = @Id";
return(conn.QueryFirstOrDefault <User>(querySql, new { Id = id }));
}
//using (IDbConnection conn = DataBaseConfig.GetSqlConnection(connectionString))
//{
// return conn.QueryFirstOrDefault<User>("sp_GetUsers", new { Id = id }, commandType: CommandType.StoredProcedure);
//}
}
/// <summary>
/// 修改一个用户
/// </summary>
/// <param name="entity">要修改的用户</param>
/// <param name="connectionString">链接字符串</param>
/// <returns></returns>
public bool UpdateEntity(User entity, string connectionString = null)
{
using (IDbConnection conn = DataBaseConfig.GetSqlConnection(connectionString))
{
string updateSql = @"UPDATE [dbo].[User]
SET [UserName] = @UserName
,[Password] = @Password
,[Name] = @Name
,[EMail] = @EMail
,[MobileNumber] = @MobileNumber
,[IsDeleted] = @IsDeleted
,[RoleIds]=@RoleIds
WHERE Id = @Id";
return(conn.Execute(updateSql, entity) > 0);
}
}
/// <summary>
/// 修改一个用户
/// </summary>
/// <param name="entity">要修改的用户</param>
/// <param name="connectionString">链接字符串</param>
/// <returns></returns>
public bool UpdateEntity(Menu entity, string connectionString = null)
{
using (IDbConnection conn = DataBaseConfig.GetSqlConnection(connectionString))
{
string updateSql = @"UPDATE [dbo].[Menu] SET
[SortNum] = @SortNum
,[DepthNum] = @DepthNum
,[Name] = @Name
,[Url] = @Url
,[Code] = @Code
,[Type] = @Type
,[Icon] = @Icon
,[Description] = @Description
WHERE Id = @Id";
return(conn.Execute(updateSql, entity) > 0);
}
}
