Create PROCEDURE [dbo].[spCommonPageData]
@Select NVARCHAR(500), -- 要查询的列名,用逗号隔开(Select后面From前面的内容)
@From NVARCHAR(200), -- From后的内容
@Where NVARCHAR(500) = NULL, -- Where后的内容
@OrderBy NVARCHAR(100) = NULL, -- 排序字段
@Key NVARCHAR(50), -- 分页主键
@Page INT, -- 当前页 ***计数从1开始***
@PageSize INT, -- 每页大小
@TotalCount INT OUTPUT, -- 总记录数
@Result TINYINT OUTPUT -- 执行是否成功
AS
BEGIN
SET NOCOUNT ON;
Declare @Sql nVarchar(1000), @Sql2 NVARCHAR(500)
Set @Sql=
'Select '+@Select+'
From (Select '+@Select+',ROW_NUMBER() OVER(ORDER BY '+@Key+') AS ROWNUMBER
From '+@From+
Case IsNull(@Where,'') When '' Then '' Else ' Where '+@Where End+')T
Where ROWNUMBER Between '+Cast(@PageSize*(@Page-1)+1 As Varchar(10))+' And '+Cast(@PageSize*@Page As Varchar(10))+
Case IsNull(' Order By '+@OrderBy,'') When '' Then '' Else
' Order By '+@OrderBy End
Exec(@Sql)
Set @Sql2='Select 1 As Count From '+@From+Case IsNull(@Where,'') When
'' Then '' Else ' Where '+@Where End
Exec(@Sql2)
SET @TotalCount = @@ROWCOUNT
SET @Result = 0
END
/* 对于该存储过程再C#中的调用代码 */
// 创建选择命令对象。
SqlCommand selectCommand = Connection.CreateCommand();
selectCommand.CommandText = "spCommonPageData";
selectCommand.CommandType = CommandType.StoredProcedure;
// 给命令对象添加参数
selectCommand.Parameters.Add("@Select", SqlDbType.Char, 200).Value = "ORDERID,CUSTOMERID,EMPLOYEEID,FREIGHT,SHIPNAME,SHIPCITY";
selectCommand.Parameters.Add("@From", SqlDbType.Char, 50).Value = "Orders";
selectCommand.Parameters.Add("@Where", SqlDbType.Char, 50).Value = DBNull.Value;
selectCommand.Parameters.Add("@OrderBy", SqlDbType.Char, 100).Value = "OrderID";
selectCommand.Parameters.Add("@Key", SqlDbType.Char, 100).Value = "OrderID";
selectCommand.Parameters.Add("@Page", SqlDbType.Int).Value = page;
selectCommand.Parameters.Add("@PageSize", SqlDbType.Int).Value = pagesize;
// 对于输出参数需要设置他们的Direction属性
SqlParameter sqTotalCount = selectCommand.Parameters.Add("@TotalCount", SqlDbType.Int);
sqTotalCount.Direction = ParameterDirection.Output;
SqlParameter sqResult = selectCommand.Parameters.Add("@Result", SqlDbType.Int);
sqResult.Direction = ParameterDirection.Output;
// 打开数据库连接
Connection.Open();
DataSet ds = new DataSet();
SqlDataAdapter sa = new SqlDataAdapter();
sa.SelectCommand = selectCommand;
sa.Fill(ds, "OrderID");
// 关闭数据库连接,必须关闭以后才可以访问他的输出参数,否则取不到值。
Connection.Close();
// 获得输出参数的值。
int a = (int)sqTotalCount.Value;
int b = (int)sqResult.Value;