Combining a stored procedure makes paging the simplest thing. Please see the following source code.
The stored procedure operated by this paging class #region The stored procedure operated by this paging class
/**//************************************************ ************
*
* Powerful function, combined with the following stored procedure
*
************************************************* ******/
/**//*
-- Pager 1,10,0,0, 'EmployeeID>2 and EmployeeID<5 ' , 'Employees','*','LastName',0
CREATE PROCEDURE Pager
@PageIndex int,--index page 1
@PageSize int,--number of pages per page 2
@RecordCount int out,--Total number of rows 3
@PageCount int out,--total number of pages 4
@WhereCondition Nvarchar(1000),--query condition 5
@TableName nvarchar(500),--query table name 6
@SelectStr nvarchar(500) = '*',--Query column 7
@Order nvarchar(500),--sorted column 8
@OrderType bit = 0, -- Set the sorting type, non-0 value is descending order 9
@Groupby NVarChar(100) = ''
AS
declare @strSQL nvarchar(2000) -- main statement
declare @strTmp nvarchar(1000) -- temporary variable
declare @strOrder nvarchar(1000) -- sorting type
if @OrderType != 0
begin
set @strTmp = '<(select min'
set @strOrder = ' order by ' + @Order +' desc'
end
else
begin
set @strTmp = '>(select max'
set @strOrder = ' order by ' + @Order +' asc'
end
set @strSQL = 'select top ' + str(@PageSize) + ' ' + @SelectStr + ' from '
+ @TableName + ' where ' + @Order + '' + @strTmp + '(['
+ @Order + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['
+ @Order + '] from ' + @TableName + '' + @strOrder + ') as tblTmp)'
+ @Groupby + @strOrder
if @WhereCondition != ''
set @strSQL = 'select top ' + str(@PageSize) + ' ' + @SelectStr + ' from '
+ @TableName + ' where ' + @Order + '' + @strTmp + '(['
+ @Order + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['
+ @Order + '] from ' + @TableName + ' where (' + @WhereCondition + ') '
+ @strOrder + ') as tblTmp) and (' + @WhereCondition + ') ' + @Groupby + @strOrder
if @PageIndex = 1
begin
set @strTmp = ''
if @WhereCondition != ''
set @strTmp = ' where (' + @WhereCondition + ')'
set @strSQL = 'select top ' + str(@PageSize) + ' ' + @SelectStr + ' from '
+ @TableName + '' + @strTmp + ' ' + @Groupby + @strOrder
end
exec(@strSQL)
--print @strSQL
IF @WhereCondition <>''
Begin
SET @strTmp = 'SELECT -1 FROM ' + @TableName + ' Where ' + (@WhereCondition)
End
ELSE
Begin
SET @strTmp = 'SELECT -1 FROM ' + @TableName
End
EXEC SP_EXECUTESQL @strTmp
SET @RecordCount = @@RowCount
-- Get the total number of pages
-- "CEILING" function: Get the smallest integer that is not less than a certain number
SET @PageCount = CEILING(@RecordCount * 1.0 / @PageSize)
GO
*************************************************** ***************************/
/**//************************************************ *******************************
*
* Usage
*
************************************************* ******************************/
/**//*
Dim ts As String = Request.Form.Item("txtDate")
If (ts = "" Or ts Is Nothing) Then
ts = Request.QueryString("txtDate")
End If
Dim ts2 As String = Request.Form.Item("txtDate2")
If (ts2 = "" Or ts2 Is Nothing) Then
ts2 = Request.QueryString("txtDate2")
End If
Dim ps As String = Request.Form.Item("pageIndex")
If (ps = "" Or ps Is Nothing) Then
ps = Request.QueryString("pageIndex")
End If
Dim t As Integer = 2
Dim p As Integer = 1
If ts Is Nothing Then
ts = ""
End If
If ps Is Nothing Then
ps = ""
End If
If Not (ps = "") Then
p = Integer.Parse(ps)
End If
Dim pager As Pager = New Pager
pager.PageIndex = p
pager.PageSize = 20
pager.PageMode = PageMode.Str
pager.WhereCondition = "TheDate between convert(datetime,'" + ts + "') and convert(datetime,'" + ts2 + "')"
'pager.WhereCondition = " convert(char(10),TheDate,120)= '" + ts + "'"
pager.TableName = "LoadCountlog"
pager.SelectStr = "*"
pager.Order = "ID"
pager.OrderType = False
Dim dt As System.Data.DataTable = pager.GetDatas(p)
myDataGrid.DataSource = dt
myDataGrid.DataBind()
Dim goUrl As String = "WebForm1.aspx?txtDate=" + ts + "&txtDate2=" + ts2
Me.Label3.Text = "Total:" + pager.PageCount.ToString + "Page," + pager.RecordCount.ToString() + "Bar<strong>" + pager.OutPager(pager, goUrl, False) + "< /strong>"
*/
#endregion
using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Collections;
using System.Text;
namespace solucky
{
/**//// <summary>
/// Paging mode
/// </summary>
public enum PageMode
{
/**//// <summary>
///Number paging
/// </summary>
Num =0,
/**//// <summary>
/// Character paging
/// </summary>
Str =1
}
/**//// <summary>
/// The paging class can perform paging through stored procedures and is quite powerful.
/// </summary>
public class Pager
{
private int pageIndex = 0;
private int recordCount = 0;
private int pageSize = 20;
private int pageCount = 0;
private int rowCount = 0;
private string tableName = "";
private string whereCondition = "1=1";
private string selectStr = "*";
private string order = "";
private string procedure="pager";
private bool orderType = true;
private PageMode pageMode =PageMode.Num;
private string sqlConnectionString = ConfigurationSettings.AppSettings["database"];
private string databaseOwner = "dbo";
data connection#region data connection
/**//// <summary>
/// Data connection string
/// </summary>
private string SqlConnectionString
{
get
{
return this.sqlConnectionString;
}
set
{
this.sqlConnectionString=value;
}
}
/**//// <summary>
///Get the connection instance
/// </summary>
/// <returns></returns>
private SqlConnection GetSqlConnectionString()
{
try
{
return new SqlConnection(SqlConnectionString);
}
catch
{
throw new Exception("SQL Connection String is invalid.");
}
}
/**//// <summary>
/// Data object owner
/// </summary>
private string DatabaseOwner
{
get
{
return this.databaseOwner;
}
set{
this.databaseOwner=value;
}
}
#endregion
public Pager()
{
//
// TODO: add constructor logic here
//
//Enum.Parse(tyo
}
public Pager(string connstr)
{
if (connstr!=null)
this.SqlConnectionString=connstr;
}
#region
/**//// <summary>
/// The name of the stored procedure to be operated has a default paging stored procedure.
/// </summary>
public string Procedure
{
get{
return this.procedure;
}
set {
if (value==null || value.Length <=0)
{
this.procedure="pager";
}
else
{
this.procedure=value;
}
}
}
/**//// <summary>
/// The number of pages currently to be displayed
/// </summary>
public int PageIndex
{
get
{
return this.pageIndex;
}
set
{
this.pageIndex = value;
}
}
/**//// <summary>
///Total number of pages
/// </summary>
public int PageCount
{
get
{
return this.pageCount;
}
set
{
this.pageCount = value;
}
}
/**//// <summary>
///Total number of rows
/// </summary>
public int RecordCount
{
get
{
return this.recordCount;
}
set
{
this.recordCount = value;
}
}
/**//// <summary>
///Number of items per page
/// </summary>
public int PageSize
{
get
{
return this.pageSize;
}
set
{
this.pageSize = value;
}
}
/**//// <summary>
///Table name
/// </summary>
public string TableName
{
get
{
return tableName;
}
set
{
this.tableName = value;
}
}
/**//// <summary>
/// Conditional query
/// </summary>
public string WhereCondition
{
get
{
return whereCondition;
}
set
{
whereCondition = value;
}
}
/**//// <summary>
/// Query target (search target), such as: AddTime AS time, ID AS number
/// </summary>
public string SelectStr
{
get
{
return selectStr;
}
set
{
selectStr = value;
}
}
/**//// <summary>
/// Sorted columns
/// </summary>
public string Order
{
get
{
return order;
}
set
{
order = value;
}
}
/**//// <summary>
/// Sorting type true:asc false:desc
/// </summary>
public bool OrderType
{
get
{
return orderType;
}
set
{
orderType = value;
}
}
/**//// <summary>
/// Paging mode
/// </summary>
public PageMode PageMode
{
get
{
return this.pageMode;
}
set
{
this.pageMode = value;
}
}
/**//// <summary>
/// Get the currently returned quantity
/// </summary>
public int RowCount
{
get
{
return this.rowCount;
}
}
private string groupby;
public string Groupby
{
get
{
return this.groupby;
}
set
{
this.groupby = value;
}
}
#endregion
/**//// <summary>
/// Pagination search results
/// </summary>
public DataTable GetDatas(int pageIndex)
{
this.pageIndex = pageIndex;
Pager pager = this;
//pager.pageIndex = pageIndex;
DataTable returnTb = Pagination(ref pager).Tables[0];
this.rowCount = returnTb.Rows.Count;
return returnTb;
}
/**//// <summary>
/// Paging operation stored procedure function
/// </summary>
/// <param name="pager"></param>
/// <returns></returns>
private DataSet Pagination(ref Pager pager)
{
using (SqlConnection myConnection = GetSqlConnectionString() )
{
SqlDataAdapter myCommand = new SqlDataAdapter(pager.databaseOwner + "."+pager.Procedure, myConnection);
myCommand.SelectCommand.CommandType = CommandType.StoredProcedure;
SqlParameter parameterPageIndex = new SqlParameter("@PageIndex", SqlDbType.Int);
parameterPageIndex.Value = pager.PageIndex;
myCommand.SelectCommand.Parameters.Add(parameterPageIndex);
SqlParameter parameterPageSize = new SqlParameter("@PageSize", SqlDbType.Int);
parameterPageSize.Value = pager.PageSize;
myCommand.SelectCommand.Parameters.Add(parameterPageSize);
SqlParameter parameterRecordCount = new SqlParameter("@RecordCount", SqlDbType.Int);
parameterRecordCount.Value = 0;
parameterRecordCount.Direction = ParameterDirection.InputOutput;
myCommand.SelectCommand.Parameters.Add(parameterRecordCount);
SqlParameter parameterPageCount = new SqlParameter("@PageCount", SqlDbType.Int);
parameterPageCount.Value = 0;
parameterPageCount.Direction = ParameterDirection.InputOutput;
myCommand.SelectCommand.Parameters.Add(parameterPageCount);
SqlParameter parameterWhereCondition = new SqlParameter("@WhereCondition", SqlDbType.NVarChar,500);
parameterWhereCondition.Value = pager.WhereCondition;
myCommand.SelectCommand.Parameters.Add(parameterWhereCondition);
SqlParameter parameterTableName = new SqlParameter("@TableName", SqlDbType.NVarChar,500);
parameterTableName.Value = pager.TableName;
myCommand.SelectCommand.Parameters.Add(parameterTableName);
SqlParameter parameterOrder = new SqlParameter("@Order", SqlDbType.NVarChar,500);
parameterOrder.Value = pager.Order;
myCommand.SelectCommand.Parameters.Add(parameterOrder);
SqlParameter parameterSelectStr = new SqlParameter("@SelectStr", SqlDbType.NVarChar,500);
parameterSelectStr.Value = pager.SelectStr;
myCommand.SelectCommand.Parameters.Add(parameterSelectStr);
SqlParameter parameterGroupby = new SqlParameter("@Groupby", SqlDbType.NVarChar, 100);
parameterGroupby.Value = pager.Groupby;
myCommand.SelectCommand.Parameters.Add(parameterGroupby);
SqlParameter parameterOrderType = new SqlParameter("@OrderType", SqlDbType.Bit);
parameterOrderType.Value = pager.OrderType==false?0:1;
myCommand.SelectCommand.Parameters.Add(parameterOrderType);
DataSet returnDS = new DataSet();
//SqlDataAdapter sqlDA = myCommand.crnew SqlDataAdapter(myCommand);
myCommand.Fill(returnDS);
pager.PageCount = (int)parameterPageCount.Value;
pager.RecordCount = (int)parameterRecordCount.Value;
return returnDS;
}
}
Generate pagination #region Generate pagination
/**//// <summary>
/// Generate paging format
/// </summary>
/// <param name="pager"></param>
/// <param name="url"></param>
/// <param name="isBr"></param>
/// <returns></returns>
public string OutPager(Pager pager,string url,bool isBr)
{
StringBuilder returnOurWml;
if(isBr)
{
returnOurWml= new StringBuilder("["+ pager.PageCount.ToString() + "page," + pager.RecordCount.ToString() +"bar]<br/>");
}
else
{
returnOurWml = new StringBuilder();
}
if (pager.PageMode == PageMode.Num)
{
//The number displayed in each row of paging
int pagesCount = 10;
int pages = 0;
int startInt = 1;
int endInt = pager.PageCount;
int i = 1;
string endStr = "";
if (pager.PageCount>pagersCount)
{
//double k = ;
pagers = pager.PageIndex / pagersCount;
if (pagers == 0)
{
pagers = 1;
}
else if((pager.PageIndex % pagersCount)!=0)
{
pagers +=1;
}
endInt = pagers * pagersCount;
if (pager.PageIndex <= endInt)
{
startInt = endInt +1 - pagersCount;
if (startInt <1)
{
startInt = 1;
}
}
//pagersCount when the display quantity is insufficient
if (endInt>=pager.PageCount)
{
endInt = pager.PageCount;
}
else
{
//if (pager.PageIndex)
endStr = " <a href="";
endStr += url + "&pageIndex=" + (endInt + 1).ToString() + "" title='page" + (endInt + 1).ToString()+"page'>";
endStr += ">>";
endStr += "</a> ";
}
if (pagers > 1)
{
returnOurWml.Append(" <a href="");
returnOurWml.Append(url + "&pageIndex=" + (startInt - 1).ToString() + "" title='page" + (startInt - 1).ToString()+"page'>");
returnOurWml.Append("<<");
returnOurWml.Append("</a> ");
}
}
for (i = startInt; i<=endInt;i++)
{
if (i!=pager.PageIndex)
{
returnOurWml.Append(" <a href="");
returnOurWml.Append(url + "&pageIndex=" + i.ToString() + "" title='page"+ i.ToString()+"page'>");
returnOurWml.Append("["+i.ToString() + "]");
returnOurWml.Append("</a> ");
}
else
{
returnOurWml.Append("<u>"+ i.ToString() + "</u>");
}
}
returnOurWml.Append(endStr);
return returnOurWml.Append("<br/>").ToString();
}
else
{
if (pager.PageIndex > 1)
{
returnOurWml.Append(" <a href="");
returnOurWml.Append(url + "&pageIndex=" + (pager.PageIndex -1).ToString() + "">");
returnOurWml.Append("Previous page");
returnOurWml.Append("</a> ");
}
if (pager.PageIndex < pager.PageCount)
{
returnOurWml.Append(pager.PageIndex.ToString());
returnOurWml.Append(" <a href="");
returnOurWml.Append(url + "&pageIndex=" + (pager.PageIndex +1).ToString() + "">");
returnOurWml.Append("Next page");
returnOurWml.Append("</a> ");
}
return returnOurWml.Append("<br/>").ToString();
}
}
#endregion
}
}
http://www.cnblogs.com/solucky/archive/2006/09/20/509741.html