分页排序通用存储过程带C#代码
分享一个通用的带分页、带排序、带条件、带分组的全功能的存储过程，而且返回总页数，总记录、另外附上C#调用代码存储过程:
USE
GO
/****** Object:StoredProcedure . Script Date: 07/15/2013 11:48:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE . --分页通用存储过程支持分组、排序
@TotalCount INT OUTPUT, --总记录数
@TotalPage INT OUTPUT, --总页数
@Table NVARCHAR(1000), --查询的表名（可多表，例如：Person p LEFT JOIN TE a ON a.PID=p.Id ）
@Column NVARCHAR(1000), --查询的字段，可多列或者为*
@OrderColumn NVARCHAR(100), --排序字段
@GroupColumn NVARCHAR(150), --分组字段
@PageSize INT, --每页记录数
@CurrentPage INT, --当前页数
@Group TINYINT, --是否使用分组，0否1是
@Condition NVARCHAR(4000), --查询条件
@OrderType INT --排序方式1降序 0升序
AS
DECLARE @PageCount INT, --总页数
@strSql NVARCHAR(4000), --主查询语句
@strTemp NVARCHAR(2000), --临时变量
@strCount NVARCHAR(1000), --统计语句
@strOrderTypeNVARCHAR(1000) --排序语句
BEGIN
SET @PageCount = @PageSize * (@CurrentPage -1)
IF @OrderType=1
BEGIN
SET @strOrderType = ' ORDER BY ' + @OrderColumn + ' desc '
END
ELSE
BEGIN
SET @strOrderType = ' ORDER BY ' + @OrderColumn + ' asc '
END
IF @Condition != ''
BEGIN
IF @CurrentPage = 1
BEGIN
IF @GROUP = 1
BEGIN
SET @strCount = 'SELECT@TotalCount=COUNT(*) FROM ' + @Table
+ ' WHERE ' + @Condition + ' GROUP BY ' + @GroupColumn
SET @strCount = @strCount + ' SET @TotalCount=@@ROWCOUNT'
SET @strSql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @Column
+ ' FROM ' + @Table + ' WHERE ' + @Condition +
' GROUP BY ' + @GroupColumn + ' ' + @strOrderType
END
ELSE
BEGIN
SET @strCount = 'SELECT@TotalCount=COUNT(*) FROM ' + @Table
+ ' WHERE ' + @Condition
SET @strSql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @Column
+ ' FROM ' + @Table + ' WHERE ' + @Condition + ' ' + @strOrderType
END
END
ELSE
BEGIN
IF @GROUP = 1
BEGIN
SET @strCount = 'SELECT@TotalCount=COUNT(*) FROM ' + @Table
+ ' WHERE ' + @Condition + ' GROUP BY ' + @GroupColumn
SET @strCount = @strCount + ' SET @TotalCount=@@ROWCOUNT'
SET @strSql = 'SELECT* FROM (SELECT ' + @Column
+ ',ROW_NUMBER() OVER(' + @strOrderType +
') AS NUM FROM ' + @Table + ' WHERE ' + @Condition +
' GROUP BY ' + @GroupColumn +
') AS T WHERE NUM BETWEEN ' + STR(@PageCount + 1) +
' AND ' + STR(@PageCount + @PageSize)
END
ELSE
BEGIN
SET @strCount = 'SELECT@TotalCount=COUNT(*) FROM ' + @Table
+ ' WHERE ' + @Condition
SET @strSql = 'SELECT* FROM (SELECT ' + @Column
+ ',ROW_NUMBER() OVER(' + @strOrderType +
') AS NUM FROM ' + @Table + ' WHERE ' + @Condition +
') AS T WHERE NUM BETWEEN ' + STR(@PageCount + 1) +
' AND ' + STR(@PageCount + @PageSize)
END
END
END
print @strSql
EXEC sp_executesql @strCount,
N'@TotalCount INT OUTPUT',
@TotalCount OUTPUT
IF @TotalCount%@PageSize = 0
BEGIN
SET @TotalPage = @TotalCount / @PageSize
END
ELSE
BEGIN
SET @TotalPage = @TotalCount / @PageSize + 1
END
SET NOCOUNT ON
EXEC (@strSql)
END
SET NOCOUNT OFF
C#调用代码分享：
返回DataTable类型的
/// <summary>
/// 通用分页结果存储过程
/// </summary>
public DataTable GetPageExecuteProcedureQuery(string sp, SqlParameter[] parameter, out int count, out int page)
{
dbConnection = new SqlConnection(SQLCONNECTSTR);
SqlCommand command = new SqlCommand(sp, dbConnection);
count = 0;//数据总数
page = 0;//总页数
SqlDataAdapter da = new SqlDataAdapter(command);
try
{
dbConnection.Open();
if (parameter != null)
{
command.CommandType = System.Data.CommandType.StoredProcedure;
command.Parameters.AddRange(parameter);
command.Parameters.Add("@TotalCount", SqlDbType.Int, 32);
command.Parameters.Add("@TotalPage", SqlDbType.Int, 32);
command.Parameters["@TotalCount"].Direction = System.Data.ParameterDirection.Output;
command.Parameters["@TotalPage"].Direction = System.Data.ParameterDirection.Output;
}
da.Fill(ds);
count = Convert.ToInt32(command.Parameters["@TotalCount"].Value);
page = Convert.ToInt32(command.Parameters["@TotalPage"].Value);
}
catch (System.Exception ex)
{
throw ex;
}
finally
{
da.Dispose();
}
return ds;
}
:lol 真不错，谢谢分享。。。
页:
[1]