完整的分頁儲存過程以及c#呼叫方法

高效分頁儲存過程

USE [db] GO /****** 物件: StoredProcedure [dbo].[p_Page2005] 指令碼日期: 06/17/2010 11:24:21 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ----------------------------------------------------- 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 out AS begin set nocount on declare @strSQL nvarchar(4000) -- main sql declare @strTmp varchar(110) -- temp variable declare @strOrder varchar(400) -- sorttype ----記錄數 set @strSQL = 'select @total=count(*) from [' + @tblName + '] where 1=1' + @strWhere; --print @strSQL exec 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' end else 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 end else begin 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 + ' ' + @strOrder end --print @strSQL exec (@strSQL) set nocount off end --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