C# 存储过程 分页

==============================创建存储过程==================================

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

Create  PROCEDURE [dbo].[UP_GetRecordByPage]
    @tblName      varchar(255),       -- 表名
    @fldName      varchar(255),       -- 主键字段名
    @PageSize     int = 10,           -- 页尺寸
    @PageIndex    int = 1,            -- 页码
    @IsReCount    bit = 0,            -- 返回记录总数, 非 0 值则返回
    @OrderType    bit = 0,            -- 设置排序类型, 非 0 值则降序
    @strWhere     varchar(1000) = '' -- 查询条件 (注意: 不要加 where)
AS
declare @strSQL   varchar(6000)       -- 主语句
declare @strTmp   varchar(500)        -- 临时变量(查询条件过长时可能会出错，可修改100为1000)
declare @strOrder varchar(400)        -- 排序类型

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

set @strSQL = 'select top ' + str(@PageSize) + ' * 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) + ' * from ['
        + @tblName + '] where [' + @fldName + ']' + @strTmp + '(['
        + @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['
        + @fldName + '] from [' + @tblName + '] where ' + @strWhere + ' '
        + @strOrder + ') as tblTmp) and ' + @strWhere + ' ' + @strOrder

if @PageIndex = 1
begin
    set @strTmp =''
    if @strWhere != ''
        set @strTmp = ' where ' + @strWhere

    set @strSQL = 'select top ' + str(@PageSize) + ' * from ['
        + @tblName + ']' + @strTmp + ' ' + @strOrder
end

if @IsReCount != 0
    set @strSQL = 'select count(*) as Total from [' + @tblName + ']'+' where ' + @strWhere

exec (@strSQL)

 

==============================调用存储过程 DAL 层==================================

 public static DataSet GetList(string tblName, string fldName, int p, int pageIndex, string strWhere)
        {
            System.Data.SqlClient.SqlParameter[] parameters = {
     new SqlParameter("@tblName", SqlDbType.VarChar, 255),
     new SqlParameter("@fldName", SqlDbType.VarChar, 255),
     new SqlParameter("@PageSize", SqlDbType.Int),
     new SqlParameter("@PageIndex", SqlDbType.Int),
     new SqlParameter("@IsReCount", SqlDbType.Bit),
     new SqlParameter("@OrderType", SqlDbType.Bit),
     new SqlParameter("@strWhere", SqlDbType.VarChar,1000),
     };
            parameters[0].Value = tblName;
            parameters[1].Value = fldName;
            parameters[2].Value = p;
            parameters[3].Value = pageIndex;
            parameters[4].Value = 0;
            parameters[5].Value = 1;
            parameters[6].Value = strWhere;
            return Help.DBHelpSQL.RunProcedure("UP_GetRecordByPage", parameters, "ds");
        }

 

==============================调用存储过程 BLL 层==================================

 public static System.Data.DataSet GetList(string tblName, string fldName, int p, int pageIndex, string strWhere)
        {
            return DAL.OutStockDAL.GetList(tblName, fldName, p, pageIndex, strWhere);
        }

==============================调用存储过程 DAL 层方法1==================================

        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <param name="tableName">DataSet结果中的表名</param>
        /// <returns>DataSet</returns>
        public static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                DataSet dataSet = new DataSet();
                connection.Open();
                SqlDataAdapter sqlDA = new SqlDataAdapter();
                sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters);
                sqlDA.Fill(dataSet, tableName);
                connection.Close();
                return dataSet;
            }
        }

==============================调用存储过程 DAL 层方法2==================================

 /// <summary>
        /// 构建 SqlCommand 对象(用来返回一个结果集，而不是一个整数值)
        /// </summary>
        /// <param name="connection">数据库连接</param>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <returns>SqlCommand</returns>
        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;
        }

 

==============================三层架构 UI层=================================

 

 private void BindList(int pageIndex)
        {
            string tblName = "OutOrder_Main";
            string fldName = "OutOrderNumber";
            LabelPageIndex.Text = Convert.ToString(pageIndex);
            System.Text.StringBuilder sb = new System.Text.StringBuilder(" 1=1 ");

            //if (!string.IsNullOrEmpty(this.txtNumbers.Text.Trim()))
            //{
            //    sb.Append(" and Numbers like '%" + this.txtNumbers.Text.Trim() + "%' ");
            //}

            DataSet ds = BLL.OutStockOrderBLL.GetList(tblName,fldName, 10, pageIndex, sb.ToString());
            RepeaterList.DataSource = ds;
            RepeaterList.DataBind();

            this.LabelAllCount.Text = BLL.PublicClassBLL.GetObject("count(1)", sb.ToString(), tblName).ToString();
            this.LabelPages.Text = Math.Ceiling(Convert.ToDouble((Convert.ToDouble(this.LabelAllCount.Text) / Convert.ToDouble(this.LabelPageSize.Text)))).ToString();

            if (LabelPages.Text == "0")
            {
                LabelPages.Text = "1";
            }

            if (LabelPageIndex.Text == "1")
            {
                LBHome.Enabled = false;
                LBUp.Enabled = false;
            }
            else
            {
                LBHome.Enabled = true;
                LBUp.Enabled = true;
            }
            if (LabelPageIndex.Text == LabelPages.Text)
            {
                LBNext.Enabled = false;
                LBEnd.Enabled = false;
            }
            else
            {
                LBNext.Enabled = true;
                LBEnd.Enabled = true;
            }
        }

转载于:https://www.cnblogs.com/taoligong/archive/2012/06/06/2537923.html