c# mysql分页存储过程_C#调用SQL Server分页存储过程

以SQL Server2012提供的offset ..rows fetch next ..rows only为例

e.g.

表名：Tab1

----------------------------------

IDName

1tblAttributeGroupDetail

2tblAttributeGroup

3tblAttribute

.......

50tblBRItemTypeAppliesTo

51tblBRItemProperties

52tblBRItem

53tblBRBusinessRule

54Test

--创建分页存储过程 rTabByCondition

USE [ExampleDB]

GO

if OBJECT_ID('rTabByCondition','P') is not null

drop procedure rTabByCondition

GO

create procedure [dbo].[rTabByCondition](

@PageCount int=1--页数

,@PageSize int=10--页显示记录数

,@Rowcount int=0 output--总记录数

)

as

set nocount on;

declare @Rows int;

select * from dbo.Tab1 order by ID offset (@PageCount-1)*@PageSize rows fetch next @PageSize rows only

set @Rows=@@ROWCOUNT

select @Rowcount=count(*) from dbo.Tab1;

return @Rows

go

declare @i int,@j int

exec @i=[rTabByCondition] @PageCount=6,@PageSize=10,@Rowcount=@j output

select @i as "@Rowcount",@j as "Return_Value"

go

显示结果：

--打开Visual Studio—创建项目—选择【控制台应用程序】

#region Directives

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

using System.Data;

using System.Data.SqlClient;

#endregion

namespace SQLStoredProcedure2

{

class Program

{

static void Main(string[] args)

{

SqlConnection thisConnection = new SqlConnection(@"Server=(Local)\SQL16;Integrated Security=True;Database=ExampleDB");

thisConnection.Open();

SqlCommand thisCommend = thisConnection.CreateCommand();

thisCommend.CommandType = CommandType.StoredProcedure;

thisCommend.CommandText = "rTabByCondition";

thisCommend.Parameters.AddWithValue("@PageCount", "6");//页数

thisCommend.Parameters.AddWithValue("@PageSize", "10");//页显示记录数

SqlParameter paraOut = thisCommend.Parameters.Add("@Rowcount", SqlDbType.Int);//输出参数定义

paraOut.Direction = ParameterDirection.Output;

SqlParameter paraRet = thisCommend.Parameters.Add("return_value", SqlDbType.Int);//返回值

paraRet.Direction = ParameterDirection.ReturnValue;

SqlDataReader thisReader = thisCommend.ExecuteReader();

while (thisReader.Read())

{

Console.WriteLine("ID:{0}\tName:{1}", thisReader[0], thisReader[1]);

}

thisReader.Close();

thisConnection.Close();

Console.WriteLine("Rows:{0};\tReturn_Value:{1};", paraOut.Value, paraRet.Value);

Console.WriteLine("Program finished,press Enter/Return to continue:");

Console.ReadLine();

}

}

}

显示效果：