完整的分页存储过程以及c#调用方法

高效分页存储过程

USE [db]GO/****** 对象: StoredProcedure [dbo].[p_Page2005] 脚本日期: 06/17/2010 11:24:21 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-----------------------------------------------------create PROCEDURE [p_Page] @tblName varchar(255),-- TableName @strGetFields varchar(1000) = '*',-- Select field @fldName varchar(255)='',-- Primary field name @PageSize int = 10,-- Page size @PageIndex int = 1,-- Page index @OrderType bit = 0,-- SortType, not 0 is DESC @strWhere varchar(1500) = '',-- QueryCodition (Note: Don't include where) @total int =0 outASbegin set nocount ondeclare @strSQL nvarchar(4000) -- main sqldeclare @strTmp varchar(110) -- temp variabledeclare @strOrder varchar(400) -- sorttype ----记录数set @strSQL = 'select @total=count(*) from [' + @tblName + '] where 1=1' + @strWhere;--print @strSQLexec sp_executesql @strSQL,N'@total int output',@total output; --print @total;----记录集if (@OrderType != 0) begin set @strTmp = '<(select min' set @strOrder = ' order by [' + @fldName +'] desc' endelse begin set @strTmp = '>(select max' set @strOrder = ' order by [' + @fldName +'] asc' end if @PageIndex = 1 begin set @strSQL = 'select top ' + rtrim(ltrim(str(@PageSize))) +' '+@strGetFields+ ' from [' + @tblName + '] where 1=1' + @strWhere + ' ' + @strOrder endelsebegin set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ ' from [' + @tblName + '] where [' + @fldName + ']' + @strTmp + '(['+ @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['+ @fldName + '] from [' + @tblName + ']' + @strOrder + ') as tblTmp)'+ @strOrder if @strWhere != '' set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ ' from [' + @tblName + '] where [' + @fldName + ']' + @strTmp + '([' + @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' [' + @fldName + '] from [' + @tblName + '] where 1=1' + @strWhere + ' ' + @strOrder + ') as tblTmp) and 1=1' + @strWhere + ' ' + @strOrderend --print @strSQLexec (@strSQL)set nocount offend--exec p_Page2005 't_alarm_data','*','id',10,5,0,''

 

c#调用方法

public static DataSet GetRecordByPage(string tblName, int PageSize, int PageIndex, string strWhere, out int count) { return GetRecordByPage(tblName, "*", "ID", PageSize, PageIndex, strWhere, out count); } public static DataSet GetRecordByPage(string tblName, string strGetFields, string fldName, int PageSize, int PageIndex,string strWhere, out int count) { DataSet ds = new DataSet(); try { using (SqlConnection conn = new SqlConnection(SQLHelper.CONN_STRING_SQL)) { SqlCommand cmd = new SqlCommand(); cmd.Connection = conn; SetParams(cmd); cmd.Parameters[0].Value = tblName; cmd.Parameters[1].Value = strGetFields; cmd.Parameters[2].Value = fldName; cmd.Parameters[3].Value = PageSize; cmd.Parameters[4].Value = PageIndex; cmd.Parameters[5].Value = 0; cmd.Parameters[6].Value = strWhere; cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "p_Page"; cmd.CommandTimeout = 180; SqlDataAdapter adapter = new SqlDataAdapter(); adapter.SelectCommand = cmd; DataSet source = new DataSet(); adapter.Fill(ds); //ds.Tables.RemoveAt(0); object o = cmd.Parameters["@total"].Value; count = (o==null || o== DBNull.Value)?0: System.Convert.ToInt32(o); } } catch (SqlException e) { throw e; } return ds; } private static void SetParams(SqlCommand cmd) { cmd.Parameters.Add(new SqlParameter("@tblName", SqlDbType.VarChar, 255)); cmd.Parameters.Add(new SqlParameter("@strGetFields", SqlDbType.VarChar, 1000)); cmd.Parameters.Add(new SqlParameter("@fldName", SqlDbType.VarChar, 255)); cmd.Parameters.Add(new SqlParameter("@PageSize", SqlDbType.Int)); cmd.Parameters.Add(new SqlParameter("@PageIndex", SqlDbType.Int)); cmd.Parameters.Add(new SqlParameter("@OrderType", SqlDbType.Bit)); cmd.Parameters.Add(new SqlParameter("@strWhere", SqlDbType.VarChar,1500)); SqlParameter param = new SqlParameter("@total", SqlDbType.Int); param.Direction = ParameterDirection.Output; cmd.Parameters.Add(param); } #endregion