时间:2021-07-01 10:21:17 帮助过:21人阅读
1.支持简单查询分页。 2.支持子表查询分页。 3.分页返回DataTable及cnt总记录数。 无 CREATE PROCEDURE [dbo].[sp_GetListByPageAndFileds]( @pageSize int,/** 每页数据量 **/ @currentPage int = 1,/** 当前页,默认为为1 **/ @fields varchar(2000),/** 查
1.支持简单查询分页。CREATE PROCEDURE [dbo].[sp_GetListByPageAndFileds] ( @pageSize int, /** 每页数据量 **/ @currentPage int = 1, /** 当前页,默认为为1 **/ @fields varchar(2000), /** 查询字段,可以用 * 表示所有 **/ @tablename varchar(max), /** 表名,或者为查询得出的 子表 ,子表查询sql需要括号括起来,并指定新表名 **/ @orderString varchar(1000), /** 排序字段 + asc/desc **/ @whereString varchar(1000) /** 不包含‘where’的字符串 **/ ) AS BEGIN DECLARE @sql varchar(2000) DECLARE @strOrder varchar(2000) DECLARE @strWhere varchar(2000) declare @recordcount int declare @convertorderstr varchar(2000) declare @countsql nvarchar(4000) declare @totalpage int set @strOrder = REPLACE(RTRIM(LTRIM(@orderString)),'order by','') if @strOrder != '' set @strOrder = ' order by ' + @strOrder else set @strOrder = ' order by ID DESC' set @strOrder=lower(@strOrder) set @convertorderstr=replace(@strOrder,'desc','d_e_s_c') set @convertorderstr=replace(@convertorderstr,'asc','desc') set @convertorderstr=replace(@convertorderstr,'d_e_s_c','asc') set @strWhere = REPLACE(RTRIM(LTRIM(@whereString)),'where','') if @strWhere != '' set @strWhere = ' where ' + @strWhere set @countsql='select @a=count(*) from ' + @tablename + @strWhere exec sp_executesql @countsql,N'@a int output',@recordcount output if @pageSize = 0 set @sql = 'select ' + @fields + ' from ' + @tablename + @strWhere + @strOrder else begin if @recordcount%@pageSize=0 set @totalpage=@recordcount/@pageSize else set @totalpage=@recordcount/@pageSize+1 if @totalpage <=1 set @currentPage=1 if @totalpage <@currentPage set @currentPage=@totalpage if @currentPage = 1 set @sql = 'select top ' + Str(@pageSize)+' '+ @fields + ' from ' + @tablename + @strWhere + @strOrder else if (@currentPage - 1) * @pageSize > @recordcount / 2 set @sql = 'select top ' + str(@pageSize) + ' * from (select top ' + str((@recordcount - (@currentPage - 1) * @pageSize)) + ' ' + @fields + ' from ' + @tablename + @strWhere + @convertorderstr + ') as t1 ' + @strOrder else set @sql = 'select * from(select top ' + str(@pageSize) + ' * from (select top ' + str(@pageSize * @currentPage) + ' ' + @fields + ' from ' + @tablename + @strWhere + @strOrder + ') as t1 ' + @convertorderstr + ') as t2 ' + @strOrder end set @sql = @sql + '; select '+str(@recordcount)+' as cnt' exec(@sql) END
public static DataTable GetDataTableByPager(int pageSize, int currentPage, string fields, string orderString, string whereString, string tablename, out int count) { if (fields == String.Empty) { fields = "*"; } count = 0; DataTable table = new DataTable(); SqlParameter[] param ={ new SqlParameter("@pageSize",pageSize) , new SqlParameter("@currentPage",currentPage) , new SqlParameter("@fields",fields) , new SqlParameter("@orderString",orderString) , new SqlParameter("@tablename",tablename) , new SqlParameter("@whereString",whereString) , }; DataSet ds = new DataSet(); ds = RunProcedure("sp_GetListByPageAndFileds", param); table = ds.Tables[0]; count = Convert.ToInt32(ds.Tables[1].Rows[0]["cnt"].ToString()); return table; } public static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters) { using (SqlConnection connection = new SqlConnection(Maticsoft.DBUtility.PubConstant.ConnectionString)) { DataSet dataSet = new DataSet(); connection.Open(); SqlDataAdapter sqlDA = new SqlDataAdapter(); sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters); sqlDA.Fill(dataSet); connection.Close(); return dataSet; } } private static SqlCommand BuildQueryCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters) { SqlCommand command = new SqlCommand(storedProcName, connection); command.CommandType = CommandType.StoredProcedure; foreach (SqlParameter parameter in parameters) { if (parameter != null) { // 检查未分配值的输出参数,将其分配以DBNull.Value. if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) && (parameter.Value == null)) { parameter.Value = DBNull.Value; } command.Parameters.Add(parameter); } } return command; }