SQL分页存储过程例子

using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using MyLib;

namespace SqlHelp
{
    /// <summary>
    /// The SqlHelper class is intended to encapsulate high performance,
    /// scalable best practices for common uses of SqlClient.
    /// </summary>
    public sealed class SqlHelper
    {
        private SqlHelper() { }

        //Database connection strings
        //public static readonly string CONN_STRING = ConfigurationManager.ConnectionStrings["Sql2005Express"].ConnectionString;
        public static readonly string CONN_STRING = CDsn.GetDsn();

        /// <summary>
        /// Execute a SqlCommand that returns a resultset against the database specified in the connection string
        /// using the provided parameters.
        /// </summary>
        /// <remarks>
        /// e.g.: 
        ///  SqlDataReader r = ExecuteReader(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
        /// </remarks>
        /// <param name="cmdType">the CommandType (stored procedure, text, etc.)</param>
        /// <param name="cmdText">the stored procedure name or T-SQL command</param>
        /// <param name="cmdParms">an array of SqlParamters used to execute the command</param>
        /// <returns>A SqlDataReader containing the results</returns>
        public static SqlDataReader ExecuteReader(CommandType cmdType, string cmdText, params SqlParameter[] cmdParms)
        {
            SqlCommand cmd = new SqlCommand();
            SqlConnection conn = new SqlConnection(CONN_STRING);

            // we use a try/catch here because if the method throws an exception we want to
            // close the connection throw code, because no datareader will exist, hence the
            // commandBehaviour.CloseConnection will not work
            try
            {
                PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
                SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                cmd.Parameters.Clear();
                return rdr;
            }
            catch
            {
                conn.Close();
                throw;
            }
        }

        /// <summary>
        /// Execute a SqlCommand that returns the first column of the first record against the database specified in the connection string
        /// using the provided parameters.
        /// </summary>
        /// <remarks>
        /// e.g.: 
        ///  Object obj = ExecuteScalar(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
        /// </remarks>
        /// <param name="cmdType">the CommandType (stored procedure, text, etc.)</param>
        /// <param name="cmdText">the stored procedure name or T-SQL command</param>
        /// <param name="cmdParms">an array of SqlParamters used to execute the command</param>
        /// <returns>An object that should be converted to the expected type using Convert.To{Type}</returns>
        public static object ExecuteScalar(CommandType cmdType, string cmdText, params SqlParameter[] cmdParms)
        {
            SqlCommand cmd = new SqlCommand();

            using (SqlConnection conn = new SqlConnection(CONN_STRING))
            {
                PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
                object val = cmd.ExecuteScalar();
                cmd.Parameters.Clear();
                return val;
            }
        }

        /// <summary>
        /// Execute a SqlCommand that returns the first column of the first record against an existing database connection
        /// using the provided parameters.
        /// </summary>
        /// <remarks>
        /// e.g.: 
        ///  Object obj = ExecuteScalar(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
        /// </remarks>
        /// <param name="conn">an existing database connection</param>
        /// <param name="cmdType">the CommandType (stored procedure, text, etc.)</param>
        /// <param name="cmdText">the stored procedure name or T-SQL command</param>
        /// <param name="cmdParms">an array of SqlParamters used to execute the command</param>
        /// <returns>An object that should be converted to the expected type using Convert.To{Type}</returns>
        public static object ExecuteScalar(SqlConnection conn, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms)
        {

            SqlCommand cmd = new SqlCommand();

            PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
            object val = cmd.ExecuteScalar();
            cmd.Parameters.Clear();
            return val;
        }

        /// <summary>
        /// Prepare a command for execution
        /// </summary>
        /// <param name="cmd">SqlCommand object</param>
        /// <param name="conn">SqlConnection object</param>
        /// <param name="trans">SqlTransaction object</param>
        /// <param name="cmdType">Cmd type e.g. stored procedure or text</param>
        /// <param name="cmdText">Command text, e.g. Select * from Products</param>
        /// <param name="cmdParms">SqlParameters to use in the command</param>
        private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, CommandType cmdType, string cmdText, SqlParameter[] cmdParms)
        {

            if (conn.State != ConnectionState.Open)
                conn.Open();

            cmd.Connection = conn;
            cmd.CommandText = cmdText;

            if (trans != null)
                cmd.Transaction = trans;

            cmd.CommandType = cmdType;

            if (cmdParms != null)
            {
                foreach (SqlParameter parm in cmdParms)
                    cmd.Parameters.Add(parm);
            }
        }
    }
}

 

protected void Page_Load(object sender, EventArgs e)
    {
        int totalOrders = (int)SqlHelper.ExecuteScalar(CommandType.StoredProcedure, "P_GetOrderNumber");
        AspNetPager1.RecordCount = totalOrders;
        bindData();
    }
    void bindData()
    {
        rpSms.DataSource = SqlHelper.ExecuteReader(CommandType.StoredProcedure, "Pagination1",
                new SqlParameter("@tblName", "t_article_name"),
                new SqlParameter("@strGetFields", "*"),
                new SqlParameter("@fldName", "FID"),
                new SqlParameter("@PageSize", 10),
                new SqlParameter("@PageIndex", AspNetPager1.CurrentPageIndex),
                new SqlParameter("@doCount", false),
                new SqlParameter("@OrderType", false),
                new SqlParameter("@strWhere", " 2>1")
             );
        rpSms.DataBind();
    }

    protected void AspNetPager1_PageChanged(object src, EventArgs e)
    {
        bindData();
    }

ALTER PROCEDURE Pagination1 
 
@tblName   varchar(255),       -- 表名 
 
@strGetFields varchar(1000) = '*',  -- 需要返回的列 
 
@fldName varchar(255)='',      -- 排序的字段名 
 
@PageSize   int = 10,          -- 页尺寸 
 
@PageIndex  int = 1,           -- 页码 
 
@doCount  bit = 0,   -- 返回记录总数, 非 0 值则返回 
 
@OrderType bit = 0,  -- 设置排序类型, 非 0 值则降序 
 
@strWhere  varchar(1500) = ''  -- 查询条件 (注意: 不要加 where) 
 
AS 
 
declare @strSQL   varchar(5000)       -- 主语句 
 
declare @strTmp   varchar(110)        -- 临时变量 
 
declare @strOrder varchar(400)        -- 排序类型 
 
 
 
if @doCount != 0 
 
 begin 
 
   if @strWhere !='' 
 
   set @strSQL = 'select count(*) as Total from ['+ @tblName +'] where '+ @strWhere 
 
   else 
 
   set @strSQL = 'select count(*) as Total from ['+ @tblName +']'
 
end 
 
--以上代码的意思是如果@doCount传递过来的不是0，就执行总数统计。以下的所有代码都
--是@doCount为0的情况 
 
else 
 
begin 
 
 
 
if @OrderType != 0 
 
begin 
 
   set @strTmp = '<(select min' 
 
set @strOrder = ' order by ['+ @fldName +'] desc'
 
--如果@OrderType不是0，就执行降序，这句很重要！ 
 
end 
 
else 
 
begin 
 
   set @strTmp = '>(select max'
 
   set @strOrder = ' order by ['+ @fldName +'] asc'
 
end 
 
 
 
if @PageIndex = 1 
 
begin 
 
   if @strWhere != ''   
 
    set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ '  from ['+ @tblName +'] where ' + @strWhere + ' ' + @strOrder 
 
    else 
 
    set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ '  from ['+ @tblName +'] '+ @strOrder 
 
--如果是第一页就执行以上代码，这样会加快执行速度 
 
end 
 
else 
 
begin 
 
--以下代码赋予了@strSQL以真正执行的SQL代码 
 
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 ' + @strWhere + ' ' + @strOrder + ') as tblTmp) and ' + @strWhere + ' ' + @strOrder 
 
end 
 
end   
 
exec ( @strSQL)