由于项目需要一个分页存储过程,在网上找来找去,用起来总是不方便,自己写了一个,效率没有做过多的测试,基本还能用. sql2005
USE [mydb] GO /****** 对象: StoredProcedure [dbo].[p_query] 脚本日期: 05/30/2009 19:29:03 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: <Author,blank> -- Create date: <Create Date,2009-05-24> -- Description: <Description,,> exec 'p_query','t_dataexp_test001','*' -- ============================================= ALTER proc [dbo].[p_query] @table varchar(50), --表名,视图---不接受sql语句 @fields varchar(5000) = '*', --字段名(全部字段为*) @order varchar(5000), --排序字段(必须!支持多字段) @where varchar(5000) = Null,--条件语句(不用加where) @pageSize int, --每页多少条记录 @index int = 1 , --指定当前为第几页 @total int out as begin declare @sql nvarchar(4000); set @sql = 'select @total=count(id) from ' + @table +' where 1=1' + @where exec sp_executesql @sql,N'@total int output',@total output--计算总记录数 if (@total=null) begin set @total = 0 end if (@order=null or @order='') begin set @order = 'order by id' end set @sql = 'select top '+ cast(@pageSize as varchar(3)) +'* from (select '+ @fields+', row_number() over ('+ @order +') AS rownum from ' set @sql = @sql + @table + ' WHERE 1 = 1' + @where + ') AS a ' set @sql = @sql + 'WHERE rownum>'+ cast(@pageSize*(@index-1) as varchar(3))+' AND rownum<= '+ cast(@pageSize*(@index) as varchar(3)) ----print @sql exec (@sql) end --exec p_query 'table','id,mCate','','',12,1,0
C#调用
#region 调分页存储过程 p_query public static DataSet GetDataProc(string table,string where, int size, int pages, string order, out int count) { DataSet ds = new DataSet(); try { using (SqlConnection conn = new SqlConnection(SQLHelper.CONN_STRING_SQL)) { SqlCommand cmd = new SqlCommand(); cmd.Connection = conn; SetProcParams(cmd); cmd.Parameters[0].Value = table; cmd.Parameters[1].Value = "*"; cmd.Parameters[2].Value = order; cmd.Parameters[3].Value = where; cmd.Parameters[4].Value = size; cmd.Parameters[5].Value = pages; cmd.Parameters[6].Value = 0; //cmd.Parameters[6].Direction = ParameterDirection.Output; cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "p_query"; SqlDataAdapter adapter = new SqlDataAdapter(); adapter.SelectCommand = cmd; DataSet source = new DataSet(); adapter.Fill(ds); //ds = SQLHelper.ExecuteDataset(conn, CommandType.StoredProcedure, "p_query", Params); count = Convert.ToInt32(cmd.Parameters["@total"].Value); //count = 180;// Convert.ToInt32(Params[6].Value); } } catch (SqlException e) { throw e; } return ds; } /// <summary> /// 存储过程参数列表 /// </summary> /// <returns></returns> private static void SetProcParams(SqlCommand cmd) { cmd.Parameters.Add(new SqlParameter("@table", SqlDbType.VarChar,50)); cmd.Parameters.Add(new SqlParameter("@fields", SqlDbType.VarChar,5000)); cmd.Parameters.Add(new SqlParameter("@order", SqlDbType.VarChar,5000)); cmd.Parameters.Add(new SqlParameter("@where", SqlDbType.VarChar,5000)); cmd.Parameters.Add(new SqlParameter("@pageSize", SqlDbType.Int)); cmd.Parameters.Add(new SqlParameter("@index", SqlDbType.Int)); SqlParameter param = new SqlParameter("@total",SqlDbType.Int); param.Direction = ParameterDirection.Output; cmd.Parameters.Add(param); } //private static SqlParameter[] GetProcParams() //{ // SqlParameter[] Param = new SqlParameter[] // { // new SqlParameter("@table", SqlDbType.VarChar, 50), // new SqlParameter("@fields", SqlDbType.VarChar, 5000), // new SqlParameter("@order", SqlDbType.VarChar, 5000), // new SqlParameter("@where", SqlDbType.VarChar, 5000), // new SqlParameter("@pageSize", SqlDbType.Int), // new SqlParameter("@index", SqlDbType.Int), // new SqlParameter("@total", SqlDbType.Int) // }; // return Param; //} #endregion
