[原创]C#执行分页查询且有OUTPUT参数的存储过程

1.实例表代码

/****** 对象:  Table [dbo].[Test]    脚本日期: 07/25/2008 10:37:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create TABLE [dbo].[Test](
[Identifier] [int] IDENTITY(1,1) NOT NULL,
[Name] [nchar](10) COLLATE Chinese_PRC_90_CI_AI NULL,
[Alias] [nvarchar](50) COLLATE Chinese_PRC_90_CI_AI NULL,
[Texts]  COLLATE Chinese_PRC_90_CI_AI NULL,
[Price] [decimal](18, 0) NULL,
[CreateDate] [datetime] NULL,
CONSTRAINT [PK_Test] PRIMARY KEY CLUSTERED
(
[Identifier] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

2.存储过程代码

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- =============================================
-- Author:		<Author,,Name>
-- Cr&#101;ate date: <Cr&#101;ate Date,,>
-- Description:	<Description,,>
-- =============================================
Alt&#101;r PROCEDURE [dbo].[TestSel&#101;ct]
@PageSize int=0,
@CurrentPage int=1,
@TotalPage int OUTPUT,
@Identifier int=null,
@Name nchar(10)=null,
@Alias nvarchar(50)=null,
@Texts text=null,
@Price decimal(18,0)=null,
@Cr&#101;ateDate datetime=null
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with Sel&#101;ct statements.
SET NOCOUNT ON;
--设置字符型的查询Like
SET @Name = &#39;%&#39;+RTRIM(@Name) + &#39;%&#39;
SET @Alias=&#39;%&#39;+RTRIM(@Alias)+&#39;%&#39;
--判断是否进行分页
IF (@PageSize>0)
BEGIN
--取得总页数
Sel&#101;ct @TotalPage=Count(Identifier) FROM Test Wh&#101;re
(@Identifier is null o&#114; Identifier=@Identifier)
and (@Name is null o&#114; Name like @Name)
and (@Alias is null o&#114; Alias like @Alias)
and (@Texts is null o&#114; Texts like @Texts)
and (@Price is null o&#114; Price=@Price)
and (@Cr&#101;ateDate is null o&#114; Cr&#101;ateDate=@Cr&#101;ateDate)
--进行页数加1
IF(@TotalPage>Round(@TotalPage,0))
BEGIN
--大于当前页面+1
SET @TotalPage=Round(@TotalPage,0)+1
END
ELSE
BEGIN
SET @TotalPage=Round(@TotalPage,0)
END
--执行分页查询
Sel&#101;ct TOP (@PageSize) Identifier,Name,Alias,Texts,Price,Cr&#101;ateDate FROM Test
Wh&#101;re Identifier NOT IN (
Sel&#101;ct Top (@PageSize*(@PageSize-1))Identifier
FROM Test
Wh&#101;re
(@Identifier is null o&#114; Identifier=@Identifier)
and (@Name is null o&#114; Name like @Name)
and (@Alias is null o&#114; Alias like @Alias)
and (@Texts is null o&#114; Texts like @Texts)
and (@Price is null o&#114; Price=@Price)
and (@Cr&#101;ateDate is null o&#114; Cr&#101;ateDate=@Cr&#101;ateDate)
)
and
(@Identifier is null o&#114; Identifier=@Identifier)
and (@Name is null o&#114; Name like @Name)
and (@Alias is null o&#114; Alias like @Alias)
and (@Texts is null o&#114; Texts like @Texts)
and (@Price is null o&#114; Price=@Price)
and (@Cr&#101;ateDate is null o&#114; Cr&#101;ateDate=@Cr&#101;ateDate)
END
ELSE
BEGIN
-- Ins&#101;rt statements for procedure here
Sel&#101;ct Identifier,Name,Alias,Texts,Price,Cr&#101;ateDate
FROM Test
Wh&#101;re
(@Identifier is null o&#114; Identifier=@Identifier)
and (@Name is null o&#114; Name like @Name)
and (@Alias is null o&#114; Alias like @Alias)
and (@Texts is null o&#114; Texts like @Texts)
and (@Price is null o&#114; Price=@Price)
and (@Cr&#101;ateDate is null o&#114; Cr&#101;ateDate=@Cr&#101;ateDate)
END
END

3.C#调用存储过程代码

SqlCommand cmd = new SqlCommand();
SqlConnection cn = new SqlConnection(connectionString);
cn.Open();
cmd.Connection = cn;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "TestSelect";
cmd.Parameters.Add(new SqlParameter("@PageSize", 1));
cmd.Parameters.Add(new SqlParameter("@CurrentPage",1));
cmd.Parameters.Add(new SqlParameter("@TotalPage",SqlDbType.Int));
cmd.Parameters["@TotalPage"].Direction = ParameterDirection.Output;
cmd.ExecuteNonQuery();
Console.Write("totalPage:{0}",cmd.Parameters["@TotalPage"].Value.ToString());
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Console.Write("totolPage:{0}",reader[6]);
}
Console.Read();