储过程实现简单的数据分页

创建存储过程的sql语句:

USE [DB_WMS]  /* DB_WMS 为数据库名称，可替换*/
GO

/****** Object:  StoredProcedure [dbo].[ProcPaging]    Script Date: 01/24/2014 16:05:00 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE proc [dbo].[ProcPaging]
( 
    @tableName nvarchar(20),
    @orderBy nvarchar(10),
    @pageSize int,
    @pageCurrentIndex int,/*起始页为0*/
    @where nvarchar(2000),
    @pageCount int output,
    @recordCount int output
)
as
begin
    if isnull(@where,'')=''
        set @where=N''
    else 
        set @where=N' where '+@where    
    declare @top int,@sql nvarchar(4000)
    /*记录数据总数*/
    begin
        set @sql = N'Select @recordCount = count(*) from ' + @tableName + @where
        exec sp_executesql @sql,N'@recordCount int output',@recordCount output
    end
    /*计算总页数*/
    set @pageCount = ceiling(@recordCount * 1.0 / @PageSize)    
    /*计算要获取的页面的top数据*/
    set @top =@pageCurrentIndex*@pageSize            
    /*如果是第一页或者总页数为1*/
    if @pageCurrentIndex=0 or @pageCount<=1   
    begin
        set @sql=N'select top(' + convert(nvarchar(10), @pageSize)+N') * from ' + @tableName + @where + N' order by '+@orderBy
        Exec sp_executesql @sql
    end
    else 
    begin
        /*如果是最后一页*/
        if @pageCurrentIndex=@pageCount-1
        begin    
            set @sql=N'Select TOP(' + convert(nvarchar(10), @recordCount) + N' - ' + convert(nvarchar(10), @top) + N') * from ' + @tableName + @where + N' order by '+@orderBy+N' desc'
            Exec sp_executesql @sql
        end
        else
        begin
            set @sql = N'Select TOP(' + convert(nvarchar(10), @pageSize) + N') * from (Select TOP(' + convert(nvarchar(10), @top) + N' + ' + convert(nvarchar(10), @pageSize) + N') * from ' + @tableName + @where + N' order by '+@orderBy+N' ASC) T order by '+@orderBy+N' desc'
            exec sp_executesql @sql
        end
    end
    select @pageCount as 'pageCount',@recordCount as 'recordCount',@pageCurrentIndex 'Index'
end

GO

后台调用存储过程代码:

        public static DataSet ExcuteProcPaging(string tableName,string orderBy,int pageCurrentIndex, string @where, out int pageCount)
        {
            using (SqlCommand command = new SqlCommand())
            {
                command.Connection = conn;
                command.CommandText = "ProcPaging";
                command.CommandType = CommandType.StoredProcedure;
                //存储过程参数
                command.Parameters.Add("@tableName", SqlDbType.NVarChar, 20).Value = tableName;
                command.Parameters.Add("@orderBy", SqlDbType.NVarChar, 10).Value = orderBy;
                command.Parameters.Add("@pageSize", SqlDbType.Int, 20).Value = 20; //在此修改每页显示的数据条数
                command.Parameters.Add("@pageCurrentIndex", SqlDbType.Int).Value = pageCurrentIndex;
                command.Parameters.Add("@where", SqlDbType.NVarChar, 2000).Value = @where;
                command.Parameters.Add("@pageCount", SqlDbType.Int).Direction = ParameterDirection.Output;
                command.Parameters.Add("@recordCount", SqlDbType.Int).Direction = ParameterDirection.Output;
                DataSet ds = new DataSet();
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                } 
                //数据适配器执行存储过程
                using (SqlDataAdapter sda = new SqlDataAdapter())
                {
                    sda.SelectCommand = command;
                    try
                    {
                        sda.Fill(ds);
                        pageCount = Convert.ToInt32(command.Parameters["@pageCount"].Value);
                    }
                    catch 
                    {
                        pageCount = 0;
                        return null;
                    }
                    finally
                    {
                        conn.Close();
                    }
                }
                return ds;
            }
        }

在对存储过程参数赋值后，除了数据适配器SqlDataAdapter执行存储过程外，还可以通过command.ExecuteScalar()方法执行存储过程。下面一段代码跟数据分页无关。

        public static string ExcuteProCheck(string userName, string pwd)
        {
            using (SqlCommand command = new SqlCommand())
            {
                userName = userName.Replace("'", "''");
                pwd = pwd.Replace("'", "''");
                command.Connection = conn;
                command.CommandText = "ProCheck";
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Add("@UserName", SqlDbType.NVarChar, 150).Value = userName;
                command.Parameters.Add("@PassWord", SqlDbType.NVarChar, 150).Value = pwd;                
                command.Connection.Open();
                try
                {
                    string roleName = command.ExecuteScalar().ToString();
                    return roleName;
                }
                catch
                {
                    return null;
                }
                finally
                {
                    command.Connection.Close();
                }
            }
        }