存储过程--分页与C#代码调用

页面导航:首页 > 软件编程 > C#教程 > 存储过程--分页与C#代码调用

存储过程--分页与C#代码调用

来源: 作者: 时间:2016-01-15 14:58 【

存储过程:SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- =============================================-- Author:Author,,QiangWang-- Create date: Create Date,,-- Description:Descri...
存储过程:
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)[email protected]+1
SET @[email protected][email protected]
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  '[email protected]
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 '[email protected]+')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 '[email protected]+')'
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;
                }
            }
        } 

 


Tags:

文章评论

最 近 更 新
热 点 排 行
Js与CSS工具
代码转换工具

<