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

1.实例表代码
[code]
/****** 对象: 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] [text] 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]
[/code]
2.存储过程代码
[code]
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
Alter PROCEDURE [dbo].[TestSelect]
@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,
@CreateDate datetime=null
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with Select statements.
SET NOCOUNT ON;
--设置字符型的查询Like
SET @Name = '%'+RTRIM(@Name) + '%'
SET @Alias='%'+RTRIM(@Alias)+'%'
--判断是否进行分页
IF (@PageSize>0)
BEGIN
--取得总页数
Select @TotalPage=Count(Identifier) FROM Test Where
(@Identifier is null or Identifier=@Identifier)
and (@Name is null or Name like @Name)
and (@Alias is null or Alias like @Alias)
and (@Texts is null or Texts like @Texts)
and (@Price is null or Price=@Price)
and (@CreateDate is null or CreateDate=@CreateDate)
--进行页数加1
IF(@TotalPage>Round(@TotalPage,0))
BEGIN
--大于当前页面+1
SET @TotalPage=Round(@TotalPage,0)+1
END
ELSE
BEGIN
SET @TotalPage=Round(@TotalPage,0)
END
--执行分页查询
Select TOP (@PageSize) Identifier,Name,Alias,Texts,Price,CreateDate FROM Test
Where Identifier NOT IN (
Select Top (@PageSize*(@PageSize-1))Identifier
FROM Test
Where
(@Identifier is null or Identifier=@Identifier)
and (@Name is null or Name like @Name)
and (@Alias is null or Alias like @Alias)
and (@Texts is null or Texts like @Texts)
and (@Price is null or Price=@Price)
and (@CreateDate is null or CreateDate=@CreateDate)
)
and
(@Identifier is null or Identifier=@Identifier)
and (@Name is null or Name like @Name)
and (@Alias is null or Alias like @Alias)
and (@Texts is null or Texts like @Texts)
and (@Price is null or Price=@Price)
and (@CreateDate is null or CreateDate=@CreateDate)
END
ELSE
BEGIN
-- Insert statements for procedure here
Select Identifier,Name,Alias,Texts,Price,CreateDate
FROM Test
Where
(@Identifier is null or Identifier=@Identifier)
and (@Name is null or Name like @Name)
and (@Alias is null or Alias like @Alias)
and (@Texts is null or Texts like @Texts)
and (@Price is null or Price=@Price)
and (@CreateDate is null or CreateDate=@CreateDate)
END
END
[/code]
3.C#调用存储过程代码
[code]
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();
[/code]