The following is the stored procedure (sqlserver2000)
-- most common paging stored procedures
-- gets the data for the specified page
CREATE PROCEDURE pagination
@tblName varchar ( 255 ), -- Table name
@strGetFields varchar ( 1000 ) = '' * '' , -- columns that need to be returned
@fldName varchar ( 255 ) = '''' , -- sorted name of field
@PageSize int = Ten , -- Page Size
@PageIndex int = 1 , -- Page Number
@doCount bit = 0 , -- returns the total number of records, not 0 values return
@OrderType bit = 0 , -- set sort type, not 0 value descending
@strWhere varchar ( 1500 ) = '''' -- Query Criteria (Note: Do not add where)
as
Declare @strSQL varchar ( 5000 ) -- subject Sentence
Declare @strTmp varchar ( the ) -- Temporary variable
Declare @strOrder varchar ( - ) -- Sort Type
if @doCount != 0
begin
if @strWhere != ''''
Set @strSQL = '' Select COUNT (*) as Total from [ '' + @tblName + '' ] where '' + @strWhere
Else
Set @strSQL = '' Select COUNT (*) as Total from [ '' + @tblName + '' ] ''
End
-- The above code means that if @docount passes over 0, the total count is executed. All of the following code is
-- it's @docount 0.
Else
begin
if @OrderType != 0
begin
Set @strTmp = '' < (select min ''
Set @strOrder = '' Order BY [ '' + @fldName + '' ] Desc ''
-- if @ordertype is not 0, it is important to perform descending order.
End
Else
begin
Set @strTmp = '' > (SELECT Max ''
Set @strOrder = '' Order BY [ '' + @fldName + '' ] ASC ''
End
if @PageIndex = 1
begin
if @strWhere != ''''
Set @strSQL = '' Select Top '' + Str ( @PageSize ) + '' '' + @strGetFields + '' From [ '' + @tblName + '' ] where '' + @strWhere + '' '' + @strOrder
Else
Set @strSQL = '' Select Top '' + Str ( @PageSize ) + '' '' + @strGetFields + '' From [ '' + @tblName + '' ] '' + @strOrder
-- if it's the first page, execute the above code, which will speed up execution.
End
Else
begin
-- The following code gives @strsql the SQL code to actually execute
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 '' + @strWhere + '' '' + @strOrder + '' ) as Tbltmp) and '' + @strWhere + '' '' + @strOrder
End
End
exec ( @strSQL )
Go
The following is the code for C #
using System.Data;
using System.Data.SqlClient;
using Microsoft.ApplicationBlocks.Data;
using system.web;
using System.Web.UI;
namespace Rsslayer.pagehelper
{
/**/ /// <summary>
/// A summary description of the paging class Pagerhelper.
/// </summary>
Public classPagerhelper
{
Private stringconnectionString;
PublicPagerhelper (stringTblname,stringSortname,BOOLDocount,stringconnectionString)
{
this. tblname = tblname;
this. Fldname = sortname;
this. connectionString = connectionString;
this. Docount = docount;
}
PublicPagerhelper (stringTblname,BOOLDocount,
stringStrgetfields,stringFldname,intpagesize,
intpageindex,BOOLOrderType,stringStrwhere,stringConnectionString
)
{
This. Tblname=tblname;
This. Docount=Docount;
This. Strgetfields=Strgetfields;
This. Fldname=Fldname;
This. pagesize=pagesize;
This. pageindex=pageindex;
This. OrderType=OrderType;
This. Strwhere=strwhere;
This. connectionString=connectionString;
}
/**//// <summary>
/// Gets the constructor of the recordset
/// </summary>
/// <param name= "tblname" ></param>
/// <param name= "strwhere" ></param>
/// <param name= "connectionString" ></param>
PublicPagerhelper (stringTblname,stringStrwhere,stringconnectionString)
{
this. tblname = tblname;
this. strwhere = strwhere;
this. Docount = true;
this. connectionString = connectionString;
}
Private stringtblname;
Public stringtblname
{
get {return tblname;}
Set{tblname =value;}
}
Private stringStrgetfields="*";
Public stringStrgetfields
{
get {return strgetfields;}
Set{strgetfields =value;}
}
Private stringFldname=string. Empty;
Public stringFldname
{
get {return fldname;}
Set{fldname =value;}
}
Private intpagesize=Ten;
Public intPageSize
{
get {return pagesize;}
Set{pagesize =value;}
}
Private intpageindex=1;
Public intPageIndex
{
get {return pageindex;}
Set{pageindex =value;}
}
Private BOOLDocount=false;
Public BOOLDocount
{
get {return docount;}
Set{docount =value;}
}
Private BOOLOrderType=false;
Public BOOLOrderType
{
get {return ordertype;}
Set{ordertype =value;}
}
Private stringstrwhere=string. Empty;
Public stringstrwhere
{
get {return strwhere;}
Set{strwhere =value;}
}
PublicIDataReader Getdatareader ()
{
if( This. Docount)
{
throw new ArgumentException (" to return to the recordset, Docount attribute must be false");
}
//System.Web.HttpContext.Current.Response.Write (pageindex);
returnSqlhelper.executereader (Connectionstring,commandtype.storedprocedure,"pagination",
NewSqlParameter ("@tblName", This. tblname),
NewSqlParameter ("@strGetFields", This. Strgetfields),
NewSqlParameter ("@fldName", This. Fldname),
NewSqlParameter ("@PageSize", This. pagesize),
NewSqlParameter ("@PageIndex", This. pageindex),
NewSqlParameter ("@doCount", This. Docount),
NewSqlParameter ("@OrderType", This. OrderType),
NewSqlParameter ("@strWhere", This. strwhere)
);
}
PublicDataSet GetDataSet ()
{
if( This. Docount)
{
throw new ArgumentException (" to return to the recordset, Docount attribute must be false");
}
returnSqlHelper.ExecuteDataset (Connectionstring,commandtype.storedprocedure,"pagination",
NewSqlParameter ("@tblName", This. Tblname),
NewSqlParameter ("@strGetFields", This. Strgetfields),
NewSqlParameter ("@fldName", This. Fldname),
NewSqlParameter ("@PageSize", This. pagesize),
NewSqlParameter ("@PageIndex", This. pageindex),
NewSqlParameter ("@doCount", This. Docount),
NewSqlParameter ("@OrderType", This. OrderType),
NewSqlParameter ("@strWhere", This. strwhere)
);
}
Public intGetCount ()
{
if(! This. Docount)
{
throw new ArgumentException (" to return the total count, Docount attribute must be true");
}
return (int) sqlhelper.executescalar (Connectionstring,commandtype.storedprocedure,"pagination",
NewSqlParameter ("@tblName", This. tblname),
NewSqlParameter ("@strGetFields", This. Strgetfields),
NewSqlParameter ("@fldName", This. Fldname),
NewSqlParameter ("@PageSize", This. pagesize),
NewSqlParameter ("@PageIndex", This. pageindex),
NewSqlParameter ("@doCount", This. Docount),
NewSqlParameter ("@OrderType", This. OrderType),
NewSqlParameter ("@strWhere", This. strwhere)
);
}
}
}
