完整的分页存储过程以及c#调用方法

高效分页存储过程

USE [db]
GO
/****** 对象:  StoredProcedure [dbo].[p_Page2005]    脚本日期: 06/17/2010 11:24:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-----------------------------------------------------
create PROCEDURE [p_Page]
    @tblName   varchar(255),                -- TableName
    @strGetFields varchar(1000) = '*',        -- Select field 
    @fldName varchar(255)='',                -- Primary field name
    @PageSize   int = 10,                    -- Page size
    @PageIndex  int = 1,                    -- Page index
    @OrderType bit = 0,                        -- SortType, not 0 is DESC
    @strWhere  varchar(1500) = '',            -- QueryCodition (Note: Don't include where)
    @total int =0 out
AS
begin
  set nocount on

declare @strSQL   nvarchar(4000)      -- main sql
declare @strTmp   varchar(110)        -- temp variable
declare @strOrder varchar(400)        -- sorttype
 
----记录数
set @strSQL = 'select @total=count(*) from [' + @tblName + '] where 1=1' + @strWhere;
--print @strSQL
exec sp_executesql @strSQL,N'@total int output',@total output;     
--print @total;

----记录集
if (@OrderType != 0)
  begin
       set @strTmp = '<(select min'
       set @strOrder = ' order by [' + @fldName +'] desc'
  end
else
  begin
       set @strTmp = '>(select max'
       set @strOrder = ' order by [' + @fldName +'] asc'
  end
     
if @PageIndex = 1
    begin
       set @strSQL = 'select top ' + rtrim(ltrim(str(@PageSize))) +' '+@strGetFields+ ' from [' + @tblName + '] where 1=1' + @strWhere + ' ' + @strOrder
    end
else
    begin
        set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ '  from ['
            + @tblName + '] where [' + @fldName + ']' + @strTmp + '(['+ @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['+ @fldName + '] from [' + @tblName + ']' + @strOrder + ') as tblTmp)'+ @strOrder
     
        if @strWhere != ''
            set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ '  from ['
                + @tblName + '] where [' + @fldName + ']' + @strTmp + '(['
                + @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['
                + @fldName + '] from [' + @tblName + '] where 1=1' + @strWhere + ' '
                + @strOrder + ') as tblTmp) and 1=1' + @strWhere + ' ' + @strOrder
    end 

--print @strSQL
exec (@strSQL)
set nocount off
end

--exec p_Page2005 't_alarm_data','*','id',10,5,0,''

c#调用方法

public static DataSet GetRecordByPage(string tblName, int PageSize, int PageIndex, string strWhere, out int count)
        {
            return GetRecordByPage(tblName, "*", "ID", PageSize, PageIndex, strWhere, out count);
        }

        public static DataSet GetRecordByPage(string tblName, string strGetFields, string fldName, int PageSize, int PageIndex,string strWhere, out int count)
        {
            DataSet ds = new DataSet();
            try
            {
                using (SqlConnection conn = new SqlConnection(SQLHelper.CONN_STRING_SQL))
                {
                    SqlCommand cmd = new SqlCommand();
                    cmd.Connection = conn;
                    SetParams(cmd);

                    cmd.Parameters[0].Value = tblName;
                    cmd.Parameters[1].Value = strGetFields;
                    cmd.Parameters[2].Value = fldName;
                    cmd.Parameters[3].Value = PageSize;
                    cmd.Parameters[4].Value = PageIndex;
                    cmd.Parameters[5].Value = 0;
                    cmd.Parameters[6].Value = strWhere;

                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = "p_Page";
                    cmd.CommandTimeout = 180;

                    SqlDataAdapter adapter = new SqlDataAdapter();
                    adapter.SelectCommand = cmd;
                   
                    DataSet source = new DataSet();
                    adapter.Fill(ds);
                    //ds.Tables.RemoveAt(0);

                    object o = cmd.Parameters["@total"].Value;
                    count = (o==null || o== DBNull.Value)?0: System.Convert.ToInt32(o);
                }
            }
            catch (SqlException e)
            {
                throw e;
            }
            return ds;
        }

        private static void SetParams(SqlCommand cmd)
        {
            cmd.Parameters.Add(new SqlParameter("@tblName", SqlDbType.VarChar, 255));
            cmd.Parameters.Add(new SqlParameter("@strGetFields", SqlDbType.VarChar, 1000));
            cmd.Parameters.Add(new SqlParameter("@fldName", SqlDbType.VarChar, 255));
            cmd.Parameters.Add(new SqlParameter("@PageSize", SqlDbType.Int));
            cmd.Parameters.Add(new SqlParameter("@PageIndex", SqlDbType.Int));
            cmd.Parameters.Add(new SqlParameter("@OrderType", SqlDbType.Bit));
            cmd.Parameters.Add(new SqlParameter("@strWhere", SqlDbType.VarChar,1500));

            SqlParameter param = new SqlParameter("@total", SqlDbType.Int);
            param.Direction = ParameterDirection.Output;
            cmd.Parameters.Add(param);
        }
        #endregion