SQLServer2005分页存储过程

CREATE PROCEDURE SP_PagingLarge
@TableNames VARCHAR(300),--表名，可以是多个表，但不能用别名
@PrimaryKey VARCHAR(100),--主键，可以为空，但@Order为空时该值不能为空
@Fields    VARCHAR(350),--要取出的字段，可以是多个表的字段，可以为空，为空表示select *
@PageSize INT,--每页记录数
@CurrentPage INT,--当前页，0表示第1页
@Filter VARCHAR(200) = '',--条件，可以为空，不用填 where
@Group VARCHAR(200) = '',--分组依据，可以为空，不用填 group by
@Order VARCHAR(200) = '',--排序，可以为空，为空默认按主键升序排列，不用填 order by
@RecordCount int = 0 output
AS
BEGIN
    DECLARE @SortColumn VARCHAR(200)
    DECLARE @Operator CHAR(2)
    DECLARE @SortTable VARCHAR(200)
    DECLARE @SortName VARCHAR(200)
    DECLARE @TmpSelect  NVarchar(200)
    IF @Fields = ''
        SET @Fields = '*'
    IF @Filter = ''
        SET @Filter = 'WHERE 1=1'
    ELSE
        SET @Filter = 'WHERE ' +  @Filter
    IF @Group <>''
        SET @Group = 'GROUP BY ' + @Group

    IF @Order <> ''
    BEGIN
        DECLARE @pos1 INT,@pos2 INT
        SET @Order = REPLACE(REPLACE(@Order,' asc',' ASC'),' desc',' DESC')
        IF CHARINDEX(' DESC',@Order) > 0
            IF CHARINDEX(' ASC',@Order) > 0
            BEGIN
                IF CHARINDEX(' DESC',@Order) < CHARINDEX(' ASC',@Order)
                    SET @Operator = '<='
                ELSE
                    SET @Operator = '>='
            END
            ELSE
                SET @Operator = '<='
        ELSE
            SET @Operator = '>='
        SET @SortColumn = REPLACE(REPLACE(REPLACE(@Order,' ASC',''),' DESC',' ','')
        SET @pos1 = CHARINDEX(',',@SortColumn)
        IF @pos1 > 0
            SET @SortColumn = SUBSTRING(@SortColumn,1,@pos1-1)
        SET @pos2 = CHARINDEX('.',@SortColumn)
        IF @pos2 > 0
        BEGIN
            SET @SortTable = SUBSTRING(@SortColumn,@pos2-1)
            IF @pos1 > 0
                SET @SortName = SUBSTRING(@SortColumn,@pos2+1,@pos1-@pos2-1)
            ELSE
                SET @SortName = SUBSTRING(@SortColumn,LEN(@SortColumn)-@pos2)
        END
        ELSE
        BEGIN
            SET @SortTable = @TableNames
            SET @SortName = @SortColumn
        END
    END
    ELSE
    BEGIN
        SET @SortColumn = @PrimaryKey
        SET @SortTable = @TableNames
        SET @SortName = @SortColumn
        SET @Order = @SortColumn
        SET @Operator = '>='
    END

    DECLARE @type varchar(50)
    DECLARE @prec int
    SELECT @type=t.name,@prec=c.prec
    FROM sysobjects o
    JOIN syscolumns c on o.id=c.id
    JOIN systypes t on c.xusertype=t.xusertype
    WHERE o.name = @SortTable AND c.name = @SortName
    IF CHARINDEX('char',@type) > 0
    SET @type = @type + '(' + CAST(@prec AS varchar) + ')'
   
    DECLARE @TopRows INT
    SET @TopRows = @PageSize * @CurrentPage + 1
    print @TopRows
    print @Operator
    EXEC('
        DECLARE @SortColumnBegin ' + @type + '
        SET ROWCOUNT ' + @TopRows + '
        SELECT @SortColumnBegin=' + @SortColumn + ' FROM  ' + @TableNames + ' ' + @Filter + ' ' + @Group + ' ORDER BY ' + @Order + '
        SET ROWCOUNT ' + @PageSize + '
        SELECT ' + @Fields + ' FROM  ' + @TableNames + ' ' + @Filter  + ' AND ' + @SortColumn + '' + @Operator + '@SortColumnBegin ' + @Group + ' ORDER BY ' + @Order + '    
    ')
    DECLARE @str_Count_SQL nvarchar(500)
    SET @str_Count_SQL= 'SELECT @TotalCount=count('+@PrimaryKey+') FROM ' + @TableNames + ' ' + @Filter
    EXEC sp_executesql @str_Count_SQL,N'@TotalCount int=0 output',@RecordCount output
End
GO

C#中运用：

// 创建用于执行存储过程的 SqlCommand。
        private static SqlCommand CreateSqlCommand(string storeProcedureName,SqlConnection connection)
        {

            SqlCommand command = new SqlCommand(storeProcedureName,connection);
            command.CommandType = CommandType.StoredProcedure;

            return command;
        }

        /// <summary>
        /// 从在 System.Data.SqlClient.SqlCommand 中指定的存储过程中检索参数信息并填充指定的
        /// System.Data.SqlClient.SqlCommand 对象的 System.Data.SqlClient.SqlCommand.Parameters 集合。
        /// </summary>
        /// <param name="sqlCommand">将从其中导出参数信息的存储过程的 System.Data.SqlClient.SqlCommand 对象。</param>
        static internal void DeriveParameters(SqlCommand sqlCommand)
        {
            try
            {
                sqlCommand.Connection.Open();
                SqlCommandBuilder.DeriveParameters(sqlCommand);
                sqlCommand.Connection.Close();
            }
            catch
            {
                if (sqlCommand.Connection != null)
                {
                    sqlCommand.Connection.Close();
                }
                throw;
            }
        }

        // 用指定的参数值列表为存储过程参数赋值。
        private static void AssignParameterValues(SqlCommand sqlCommand,params object[] paraValues)
        {
            if (paraValues != null)
            {
                if ((sqlCommand.Parameters.Count - 1) != paraValues.Length)
                {
                    throw new ArgumentNullException("储存过程参数个数不匹配.");
                }
                for (int i = 0; i < paraValues.Length; i++)
                {
                    sqlCommand.Parameters[i + 1].Value = (paraValues[i] == null) ? DBNull.Value : paraValues[i];
                }
            }
        }

/// <summary>
        /// 执行存储过程，返回 System.Data.DataTable。
        /// </summary>
        /// <param name="paraValues">传递给存储过程的参数值列表。</param>
        /// <returns>包含查询结果的 System.Data.DataTable。</returns>
        public static DataTable ExecuteProcedureDataTable(string storedProcName,params object[] paraValues)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand command = CreateSqlCommand(storedProcName,connection);
                try
                {
                    DeriveParameters(command);
                    AssignParameterValues(command,paraValues);
                    SqlDataAdapter adapter = new SqlDataAdapter(command);
                    DataTable dataTable = new DataTable();
                    adapter.Fill(dataTable);
                    return dataTable;
                }
                catch
                {
                    throw;
                }
            }
        }

           Object[] str = new Object[9];
            str[0] = "websites,admin";  //需要用到的表
            str[1] = "websites.id";       //主表主键
            str[2] = "websites.url......";  //需要得到的字段
            str[3] = pageSize + "";      //每页显示条数
            str[4] = currentPage - 1;    //当前页
            str[5] = "admin.id=websites.user_id" + sqlstr; //条件
            str[6] = "";                                                  //分组依据
            str[7] = "websites.id desc";                         //排序
            str[8] = "0";

            DataTable dt = DbHelper.ExecuteProcedureDataTable("SP_PagingLarge",str);

2013年1月16日补充：

            using (SqlConnection sqlConn = new SqlConnection(ConnectionString))
            {
                SqlCommand sqlComm = new SqlCommand("SP_PagingLarge",sqlConn);
                sqlComm.CommandType = CommandType.StoredProcedure;
                sqlComm.Parameters.Add("@TableNames",SqlDbType.VarChar);
                sqlComm.Parameters.Add("@PrimaryKey",SqlDbType.VarChar);
                sqlComm.Parameters.Add("@Fields",SqlDbType.VarChar);
                sqlComm.Parameters.Add("@PageSize",SqlDbType.Int);
                sqlComm.Parameters.Add("@CurrentPage",SqlDbType.Int);
                sqlComm.Parameters.Add("@Filter",SqlDbType.VarChar);
                sqlComm.Parameters.Add("@Group",SqlDbType.VarChar);
                sqlComm.Parameters.Add("@Order",SqlDbType.VarChar);
                sqlComm.Parameters.Add("@RecordCount",SqlDbType.Int);
                sqlComm.Parameters[0].Value = "tHisRec";
                sqlComm.Parameters[1].Value = "RecID";
                sqlComm.Parameters[2].Value = "RecID,ConversationID,FromUri,ToUri,FromContactDispName,ToContactDispName,MessageText,SendDT";
                sqlComm.Parameters[3].Value = pageSize;
                sqlComm.Parameters[4].Value = currentIndex - 1;
                sqlComm.Parameters[5].Value = strWhere;
                sqlComm.Parameters[6].Value = null;
                sqlComm.Parameters[7].Value = "RecID desc";
                sqlComm.Parameters[8].Direction = ParameterDirection.Output;
                sqlConn.Open();
                SqlDataAdapter adapter = new SqlDataAdapter(sqlComm);
                DataTable dt = new DataTable();
                adapter.Fill(dt);
                
                .....

              ?countMessage = Convert.ToInt32(sqlComm.Parameters["@RecordCount"].Value);//记录总数
            }

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!