所用存储过程
CREATE PROCEDURE Page
@iPage int=1, --当前页码
@iPageSize int=10,--每页条数
@StrTable varchar(200),--查询的表
@StrText varchar(1000),--查询的字段
@StrWhere varchar(1000),--条件
@StrIndex varchar(30),--索引
@StrOrder varchar(100)='',--排序字段
@StrTotals int output --返回总条数
AS
--定义变量
declare @SqlCount nvarchar(2000)
declare @Sql nvarchar(2000)
declare @TempOrder nvarchar(1000)
if @StrOrder<>""
begin
set @TempOrder=' order by '+@StrOrder
end
else
begin
set @TempOrder=''
end
--组合sql语句
if @iPage=1
begin
set @Sql='select top '+str(@iPageSize)+' '+@StrText+' from '+@StrTable+' where 1=1 '+@StrWhere+@TempOrder
end
else
begin
set @Sql='select top '+str(@iPageSize)+' '+@StrText+' from '+@StrTable+' where '+@StrIndex+' not in (select top '+str(@iPageSize*(@iPage-1))+' '+@StrIndex+' from '+@StrTable+' where 1=1 '+@StrWhere+@TempOrder+') '+@StrWhere+@TempOrder
end
set @SqlCount='select @StrTotals=isnull(count(*),10000) from '+@StrTable+' where 1=1 '+@StrWhere
--查询总记录数量
exec sp_executesql @SqlCount,N'@StrTotals int output',@StrTotals output
--执行sql语句返回
exec (@Sql)
GO
SQLHelper类
public class SQLHelper
{
private SqlConnection conn = null;
private SqlCommand cmd = null;
private SqlDataReader sdr = null;
public SQLHelper()
{
string connStr = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;
conn = new SqlConnection(connStr);
}
/// <summary>
/// 取得数据库的链接
/// </summary>
/// <returns></returns>
private SqlConnection GetConn()
{
#region
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
return conn;
#endregion
}
/// <summary>
/// 执行分页查询
/// </summary>
/// <param name="iPage">当前页码</param>
/// <param name="iPageSize">每页条数</param>
/// <param name="strTable">查询的表</param>
/// <param name="strText">查询的字段</param>
/// <param name="strWhere">查询的条件</param>
/// <param name="strIndex">索引</param>
/// <param name="strOrder">排序字段</param>
/// <param name="outCount">输出数据总条数</param>
/// <returns></returns>
public DataTable ExecutePage(int iPage, int iPageSize, string strTable, string strText, string strWhere, string strIndex, string strOrder, out int outCount)
{
#region
outCount = 0;
DataTable dt = new DataTable();
cmd = new SqlCommand("Page", GetConn());
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddRange(new SqlParameter[] {
new SqlParameter("@iPage",iPage),
new SqlParameter("@iPageSize", iPageSize),
new SqlParameter("@StrTable", strTable),
new SqlParameter("@StrText", strText),
new SqlParameter("@StrWhere", strWhere),
new SqlParameter("@StrIndex", strIndex),
new SqlParameter("@StrOrder", strOrder),
new SqlParameter("@StrTotals",outCount)
});
cmd.Parameters["@StrTotals"].Direction = ParameterDirection.Output;
using (sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
{
dt.Load(sdr);
outCount = int.Parse(cmd.Parameters["@StrTotals"].Value.ToString());
}
return dt;
#endregion
}
}
实体类字段 Model
/// <summary>
/// 分页实体
/// </summary>
public class PageInfo
{
private int iPage;
/// <summary>
/// 当前页码
/// </summary>
public int IPage
{
get { return iPage; }
set { iPage = value; }
}
private int iPageSize;
/// <summary>
/// 每页条数
/// </summary>
public int IPageSize
{
get { return iPageSize; }
set { iPageSize = value; }
}
private string strTable;
/// <summary>
/// 查询的表
/// </summary>
public string StrTable
{
get { return strTable; }
set { strTable = value; }
}
private string strText;
/// <summary>
/// 查询的字段
/// </summary>
public string StrText
{
get { return strText; }
set { strText = value; }
}
private string strWhere;
/// <summary>
/// 查询的条件
/// </summary>
public string StrWhere
{
get { return strWhere; }
set { strWhere = value; }
}
private string strIndex;
/// <summary>
/// 索引
/// </summary>
public string StrIndex
{
get { return strIndex; }
set { strIndex = value; }
}
private string strOrder;
/// <summary>
/// 排序字段
/// </summary>
public string StrOrder
{
get { return strOrder; }
set { strOrder = value; }
}
}
DAL 数据库操作层
/// <summary> /// 分页数据获取类
/// </summary>
public class PageDAO
{
private SQLHelper sqlhelper = null;
public PageDAO()
{
sqlhelper = new SQLHelper();
}
/// <summary>
/// 分页方法
/// </summary>
/// <param name="p">分页实体</param>
/// <param name="outCount">数据总数</param>
/// <returns></returns>
public DataTable Page(PageInfo p, out int outCount)
{
DataTable dt = new DataTable();
dt = sqlhelper.ExecutePage(p.IPage, p.IPageSize, p.StrTable, p.StrText, p.StrWhere, p.StrIndex, p.StrOrder, out outCount);
return dt;
}
}
BLL 业务逻辑层
private PageDAO pDao = null; public PageBll()
{
pDao = new PageDAO();
}
/// <summary>
/// 分页方法
/// </summary>
/// <param name="p">分页实体</param>
/// <param name="outCount">数据总数</param>
/// <returns></returns>
public DataTable Page(PageInfo p, out int outCount)
{
return pDao.Page(p, out outCount);
}
}
页面调用
private PageBll pBll = new PageBll();
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
//原SQL语句
//select l.id,l.inDate,l.outDate,u.[name],u.worderNumber,c.[name] from live l
//inner join [user] u on l.userId=u.id
//inner join card c on c.id=u.cardId
//where u.[name] like '杜毛' or u.worderNumber='dl0010'
//order by l.id desc
//加入分页实体
PageInfo p = new PageInfo();
p.IPage = 1;
p.IPageSize = 10;
p.StrTable = "live l inner join [user] u on l.userId=u.id inner join card c on c.id=u.cardId";
p.StrText = "l.id,l.inDate,l.outDate,u.[name],u.worderNumber,c.[name]";
p.StrWhere = "and u.[name] like '杜毛' or u.worderNumber='dl0010'";
p.StrIndex = "l.id";
p.StrOrder = "l.id desc";
int outCount;
GridView1.DataSource = pBll.Page(p, out outCount).DefaultView;
GridView1.DataBind();
Response.Write(outCount);
}
}