存储过程分页查询和在ado.net中的使用，返回查询总数

1、创建查询分页的存储过程

 1 CREATE PROCEDURE PageQuery
 2 @tableName VARCHAR(50),-- 表名
 3 @strWhere VARCHAR(1000),-- 查询条件
 4 @columnNames VARCHAR(1000),-- 查询要显示的列名
 5 @pageIndex INT,-- 页面索引（第几页）
 6 @pageSize INT,-- 页面大小
 7 @recordCount INT OUTPUT -- 查询总数
 8 
 9 AS
10 
11 BEGIN
12  DECLARE @strOrderBy NVARCHAR(50) = 'ORDER BY ID' 
13 
14 -- recordCount
15 DECLARE @strCountSql NVARCHAR(1000)
16 SET  @strCountSql='SELECT @RecordCount= COUNT(*) FROM'+@tableName +' WHERE 1 = 1' + @strWhere 
17 EXEC sp_executesql @strCountSql, N'@RecordCount int OUTPUT', @recordCount OUTPUT
18 
19 -- query page
20 DECLARE @strSql NVARCHAR(1000)
21 SET @strSql = 'SELECT TOP ' + CONVERT(VARCHAR(10), @pageIndex*@pageSize) + ' ROW_NUMBER() OVER('+ @strOrderBy +') AS RowID, '+@columnNames+' FROM '+ @tableName+' WHERE 1 = 1' + @strWhere 
22 SET @strSql = 'SELECT * FROM (' + @strSql + ') as temp WHERE temp.RowID > ' + CONVERT(VARCHAR(10), (@pageSize * (@pageIndex - 1)))
23 EXEC(@strSql)
24 
25 END;

为了通用，我把表名，要显示的列名，查询条件都当参数传了进来，不需要的可以去掉某些参数。

2、在ADO.NET中使用

 1    public virtual DataTable QueryProcedureDataTable(string tableName,string strWhere,string columns,int pageIndex,int pageSize,out int recordCount)
 2         {
 3             SqlConnection conn = (SqlConnection)Context.Database.Connection;
 4 
 5             SqlCommand cmd = new SqlCommand();
 6             cmd.Connection = conn;
 7             cmd.CommandType = CommandType.StoredProcedure;
 8             cmd.CommandText = "PageQuery"; //存储过程名称 
 9 
10             //存储过程参数
11             recordCount = 0;
12             SqlParameter[] sqlParameters = new SqlParameter[]
13             {
14                 new SqlParameter("tableName",tableName),
15                 new SqlParameter("strWhere",strWhere),
16                 new SqlParameter("columnNames",columns),
17                 new SqlParameter("pageIndex",pageIndex),
18                 new SqlParameter("pageSize",pageSize),
19                 new SqlParameter("recordCount",recordCount)
20             };            
21             cmd.Parameters.AddRange(sqlParameters);
22 
23             SqlDataAdapter adapter = new SqlDataAdapter(cmd);
24             DataTable table = new DataTable();
25             adapter.Fill(table);
26 
27             conn.Close();
28             conn.Dispose();
29             return table;
30         }