分页相关SQL常量:
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
namespace BookShopSqlServerDAL
{
public partial class BaseService
{
//总条数
private const String _SQL_ROWCOUNT = "SELECT COUNT(*) FROM {TABLE_NAME} as model WHERE {CONDITION}";
//计算分页结果
private const String _SQL_PAGER = "SELECT TOP {PAGE_SIZE} * FROM {TABLE_NAME} as model " +
" WHERE {PKEY} NOT IN " +
"(SELECT TOP {PASS_OUT} {PKEY} FROM {TABLE_NAME} as model WHERE {CONDITION} {ORDER_LIST})" +
" AND {CONDITION} {ORDER_LIST}";
}
}
分页实现:
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using BookShopModel;
using System.Data.SqlClient;
using BookShopSqlServerDAL;
namespace BookShopSqlServerDAL
{
public partial class BaseService
{
/// <summary>
/// 分页方法
/// </summary>
/// <param name="pi"></param>
public virtual void Pager(PageInfo pi)
{
if (String.IsNullOrEmpty(pi.TableName) ||
String.IsNullOrEmpty(pi.Pkey))
{
return;
}
//表名特殊处理
if (pi.TableName.ToUpper().Contains("FROM "))//如果包含FROM说明是一个子查询
{
pi.TableName = " (" + pi.TableName + ") ";//如果是子查询则添加一对圆括号
}
#region 处理条件
StringBuilder _conditions = new StringBuilder();//where field1=@field1 and field2=@field2 and field3=@field
List<SqlParameter> parList = new List<SqlParameter>();
PrepareCondition(_conditions, parList, pi.Conditions);
#endregion
#region 总条数