存儲過程--分頁與C#代碼調用
存儲過程：
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:<Author,,QiangWang>
-- Create date: <Create Date,,>
-- Description:<Description,分頁,>
-- =============================================
ALTER PROCEDURE OrderInfoPage
@startRecordIndex INT, --分頁頁碼
@pagesize int, --分頁行數
@strWhere varchar(500), --查詢條件
@strOrder varchar(200), --排序條件
@OUTpageCount INT OUT --輸出記錄條數
AS
BEGIN
DECLARE @SBegin int --開始記錄數
DECLARE @EEnd int --結束記錄數
DECLARE @strTmp NVARCHAR(1000) --當前條件下讀取到的數據列
DECLARE @strSQL NVARCHAR(3000)
SET @SBegin=(@startRecordIndex-1)*@pagesize+1
SET @EEnd=@pagesize*@startRecordIndex
BEGIN
SET @strTmp='SELECT @OUTpageCount=count(1) FROM OrderInfo oi left join OrderDetail od on oi.OrderNum=od.OrderNum left join ProductInfo pi on
od.ProductID=pi.ID left join Store s on pi.StoreID=s.ID left join RandomCode rc on pi.FromID=rc.Random left join UserInfo ui
on oi.UserID=ui.ID left join UserAddress ua on oi.AddressID=ua.ID '+@strWhere
exec sp_executesql @strTmp,N'@OUTpageCount int out',@OUTpageCount OUT
END
BEGIN
--with as 子查詢部分
--ROW_NUMBER() OVER 生成一個有順序的行號,而他生成順序的標准,就是後面緊跟的OVER(ORDER BY ID)
--還必須添加OVER語句以便告訴SQL Server你希望怎樣添加行序號。
set @strSQL='with temptbl as (SELECT ROW_NUMBER() OVER (ORDER BY '+@strOrder+')AS Row, oi.ID as oiID,oi.Title, oi.AddTime,oi.Address,oi.CourierNumber,oi.GoodsTime,oi.IsPrint2,oi.OrderNum,oi.PayTime,oi.PayType,oi.PricePay,oi.PriceMust
,oi.SecurityCode,oi.SendType,oi.Status,oi.SoureType,oi.UserID,od.BarCode,od.ProductID,od.ProductName,od.Count,s.StoreName,pi.Brand,ui.WeiXinName,
ua.ConsigneeName,ua.ConsigneeMobile from OrderInfo oi left join OrderDetail od on oi.OrderNum=od.OrderNum left join ProductInfo pi on
od.ProductID=pi.ID left join Store s on pi.StoreID=s.ID left join UserInfo ui
on oi.UserID=ui.ID left join UserAddress ua on oi.AddressID=ua.ID '+@strWhere+')'
set @strSQL+='SELECT * FROM temptbl where Row between '+STR(@SBegin)+' and '+STR(@EEnd)
exec sp_executesql @strSQL,N'@startRecordIndex int,@strWhere varchar(500), @strOrder varchar(200)',@startRecordIndex ,@strWhere,@strOrder
END
END
GO
C#代碼調用：
DataTable dt = Common.DbHelperSQL.ExecStoreProcedureForGettingTable(dir, "OrderInfoPage");//執行存儲過程
int total = Common.DbHelperSQL.ExecStoreProcedureForGettingResult(dir, "OrderInfoPage");//總記錄
/// <summary>
/// 封裝執行存儲過程
/// </summary>
/// <param name="parametersInstance">存儲過程參數</param>
/// <param name="storedProcedureName">存儲過程名稱</param>
/// <returns></returns>
public static System.Data.DataTable ExecStoreProcedureForGettingTable(System.Collections.Generic.Dictionary<string, object> parametersInstance, string storedProcedureName)
{
using (SqlConnection con = new SqlConnection(connectionString))
{
try
{
//設置Sql
SqlCommand cmd = new SqlCommand(storedProcedureName, con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandTimeout = 999;
if (parametersInstance != null)
{
foreach (KeyValuePair<string, object> item in parametersInstance)
{
SqlParameter parm = new SqlParameter(item.Key, item.Value);
cmd.Parameters.Add(parm);
}
}
DataTable dt = new DataTable(Guid.NewGuid().ToString());
SqlDataAdapter sdap = new SqlDataAdapter(cmd);
sdap.Fill(dt);
return dt;
}
catch (Exception er)
{
throw er;
}
}
}
/// <summary>
/// 返回受影響行數
/// </summary>
/// <param name="parametersInstance">存儲過程參數</param>
/// <param name="storedProcedureName">存儲過程名稱</param>
/// <returns></returns>
public static int ExecStoreProcedureForGettingResult(System.Collections.Generic.Dictionary<string, object> parametersInstance, string storedProcedureName)
{
using (SqlConnection con = new SqlConnection(connectionString))
{
try
{
//設置Sql
SqlCommand cmd = new SqlCommand(storedProcedureName, con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandTimeout = 999;
if (parametersInstance != null)
{
foreach (KeyValuePair<string, object> item in parametersInstance)
{
SqlParameter parm = new SqlParameter(item.Key, item.Value);
cmd.Parameters.Add(parm);
}
cmd.Parameters["@OUTpageCount"].Direction = ParameterDirection.Output;
}
DataTable dt = new DataTable(Guid.NewGuid().ToString());
con.Open();
cmd.ExecuteNonQuery();
int num = (int)cmd.Parameters["@OUTpageCount"].Value;
con.Close();
return num;
}
catch (Exception er)
{
throw er;
}
}
}
