一个通用的分页类
结合一个存储过程,将分页做成最简单,请看以下源码
此分页类所操作的存储过程
using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Collections;
using System.Text;
namespace solucky
{
/// <summary>
/// 分页模式
/// </summary>
public enum PageMode
{
/// <summary>
/// 数字分页
/// </summary>
Num =0,
/// <summary>
/// 字符分页
/// </summary>
Str =1
}
/// <summary>
/// 分页类,能过存储过程进行分页,功能相当强大。
/// </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";
数据连接
public Pager()
{
//
// TODO: 在此处添加构造函数逻辑
//
//Enum.Parse(tyo
}
public Pager(string connstr )
{
if (connstr!=null)
this.SqlConnectionString=connstr;
}
/// <summary>
/// 分页查寻结果
/// </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>
/// 分页操作存储过程函数
/// </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;
}
}
生成分页
}
}
using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Collections;
using System.Text;
namespace solucky
{
/// <summary>
/// 分页模式
/// </summary>
public enum PageMode
{
/// <summary>
/// 数字分页
/// </summary>
Num =0,
/// <summary>
/// 字符分页
/// </summary>
Str =1
}
/// <summary>
/// 分页类,能过存储过程进行分页,功能相当强大。
/// </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";
数据连接
public Pager()
{
//
// TODO: 在此处添加构造函数逻辑
//
//Enum.Parse(tyo
}
public Pager(string connstr )
{
if (connstr!=null)
this.SqlConnectionString=connstr;
}
/// <summary>
/// 分页查寻结果
/// </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>
/// 分页操作存储过程函数
/// </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;
}
}
生成分页
}
}