==============================創(chuàng)建存儲過程==================================

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,??????????? -- 返回記錄總數(shù), 非 0 值則返回
??? @OrderType??? bit = 0,??????????? -- 設(shè)置排序類型, 非 0 值則降序
??? @strWhere???? varchar(1000) = '' -- 查詢條件 (注意: 不要加 where)
AS
declare @strSQL?? varchar(6000)?????? -- 主語句
declare @strTmp?? varchar(500)??????? -- 臨時(shí)變量(查詢條件過長時(shí)可能會出錯(cuò)，可修改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)

?

==============================調(diào)用存儲過程 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");
??????? }

?

==============================調(diào)用存儲過程?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);
??????? }

==============================調(diào)用存儲過程 DAL 層方法1==================================

??????? /// <summary>
??????? /// 執(zhí)行存儲過程
??????? /// </summary>
??????? /// <param name="storedProcName">存儲過程名</param>
??????? /// <param name="parameters">存儲過程參數(shù)</param>
??????? /// <param name="tableName">DataSet結(jié)果中的表名</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;
??????????? }
??????? }

==============================調(diào)用存儲過程 DAL 層方法2==================================

?/// <summary>
??????? /// 構(gòu)建 SqlCommand 對象(用來返回一個(gè)結(jié)果集，而不是一個(gè)整數(shù)值)
??????? /// </summary>
??????? /// <param name="connection">數(shù)據(jù)庫連接</param>
??????? /// <param name="storedProcName">存儲過程名</param>
??????? /// <param name="parameters">存儲過程參數(shù)</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)
??????????????? {
??????????????????? // 檢查未分配值的輸出參數(shù),將其分配以DBNull.Value.
??????????????????? if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
??????????????????????? (parameter.Value == null))
??????????????????? {
??????????????????????? parameter.Value = DBNull.Value;
??????????????????? }
??????????????????? command.Parameters.Add(parameter);
??????????????? }
??????????? }
??????????? return command;
??????? }

?

==============================三層架構(gòu) 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;
??????????? }
??????? }

轉(zhuǎn)載于:https://www.cnblogs.com/taoligong/archive/2012/06/06/2537923.html

《新程序員》：云原生和全面數(shù)字化實(shí)踐50位技術(shù)專家共同創(chuàng)作，文字、視頻、音頻交互閱讀

總結(jié)

以上是生活随笔為你收集整理的C# 存储过程 分页的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。