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

金融论坛

http://www.888fin.com/

IT精英团

http://www.itnpc.com/

存储过程：

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author:

-- Create date:

-- 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");//总记录

///

/// 封装执行存储过程

///

/// 存储过程参数

/// 存储过程名称

///

public static System.Data.DataTable

ExecStoreProcedureForGettingTable(System.Collections.Generic.Dictionary

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 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;

}

}

}

///

/// 返回受影响行数

///

/// 存储过程参数

/// 存储过程名称

///

public static int

ExecStoreProcedureForGettingResult(System.Collections.Generic.Dictionary

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 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;

}

}

}