- --MS SQL分页存储过程脚本
- 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
- //C#分页调用分页存储过程
- 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;
- }
- --该片段来自于http://www.codesnippet.cn/detail/0207201512989.html
来源: http://www.codesnippet.cn/detail/0207201512989.html