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