最好用的兼容多种数据库通用高效的大数据分页功能
通用权限管理系统底层有一个通用分页查询功能，该功能可实现多种数据库的查询，支持多表关联分页查询，目前是最完善的分页功能实现。
下面代码是使用的方法截图：
///////////////////////////////
后台代码截图1
///////////////////////////////
后台代码截图2
///////////////////////////////
后台代码截图3
///////////////////////////////
后台代码截图4
///////////////////////////////
后台代码截图5
///////////////////////////////
页面后台代码实现参考：
public partial class TabSite : AuthBasePage
{
/// <summary>
/// 使用吉日嘎拉通用权限管理系统底层功能实现的分页查询
/// 支持多表联合关联分页查询
///
/// <author>
/// <name>宋彪</name>
/// <date>2014.08.07</date>
/// </author>
/// </summary>
/// <summary>
/// 页码
/// </summary>
protected int pageNo = 1;
/// <summary>
/// 页容量
/// </summary>
protected int pageSize = 10;
/// <summary>
/// 查询主表
/// </summary>
protected string tableName = "UserInfo A";
/// <summary>
/// 总记录
/// </summary>
protected int totalRows;
/// <summary>
/// 排序
/// </summary>
protected string sort = BasePage.RequestString("sort", "SITE_CODE");
/// <summary>
/// 排序方向
/// </summary>
protected string direction = BasePage.RequestString("direction", "asc");
/// <summary>
/// 查询关键词
/// </summary>
protected string searchKey = RequestString("searchKey");
/// <summary>
/// 输出模式
/// </summary>
protected string outPutMode = RequestString("outPutMode", "pagerlist");
protected void Page_Load(object sender, EventArgs e)
{
List<KeyValuePair<string, object>> dbParameters = new List<KeyValuePair<string, object>>();
List<string> listWhere = new List<string>();
string conditions = string.Empty;
//具体排序
string orderBy = " B." + sort + " " + direction;
//输出字段控制
string selectField = " B.CODE,B.NAME,B.AREA_NAME,B.TYPE,-1 as DISTANCE ";
string connectionString = ConfigHelper.GetConfigString("ConnectionStringWeb");
IDbHelper dbHelper = new OracleHelper(connectionString);
tableName = " UserInfo A LEFT JOIN UserContact B ON A.ID = B.Uid ";
orderBy = " B." + sort + " " + direction;
if (!string.IsNullOrWhiteSpace(searchKey))
{
string searchKeytmp = searchKey;
if (searchKey.IndexOf("%") < 0)
{
searchKeytmp = string.Format("%{0}%", searchKey);
}
listWhere.Add("( B.SITE_CODE LIKE " + dbHelper.GetParameter("searchKey") + "or B.SITE_NAME LIKE " + dbHelper.GetParameter("searchKey") + " or B.MANAGER LIKE " + dbHelper.GetParameter("searchKey") + ")");
dbParameters.Add(new KeyValuePair<string, object>("searchKey", searchKeytmp));
}
if (listWhere.Count > 0)
{
conditions = string.Join(" and ", listWhere.ToArray());
}
if (string.Equals(outPutMode, "pagerlist", StringComparison.OrdinalIgnoreCase))
{
//页面分页数据
pageNo = BasePage.RequestInt32("pageNo", 1);
pageSize = BasePage.RequestInt32("pageSize", 10);
//DataTable dt = CommonManager.GetDataTableByPage(dbHelper, out totalRows, tableName, selectField, pageNo, pageSize, conditions, dbParameters, orderBy);
DataTable dt = DbLogic.GetDataTableByPage(dbHelper, out totalRows, tableName, selectField, pageNo, pageSize, conditions, dbParameters, orderBy);
this.dataList.DataSource = dt;
this.dataList.DataBind();
}
else if (string.Equals(outPutMode, "dropdownjson", StringComparison.OrdinalIgnoreCase))
{
Response.ContentType = "application/json";
//下拉数据
selectField = " QUOTE_NAME as \"key\",QUOTE_ID as \"value\" ";
DataTable dtResult = DbLogic.GetDataTable(dbHelper, tableName, dbParameters, conditions, 0, orderBy, selectField);
//CommonManager.GetDataTable(dbHelper, tableName, dbParameters, conditions, 0, orderBy, selectField);
StringBuilder jsonString = new StringBuilder();
jsonString.Append("{\"list\":" + DataTableHelper.DataTable2Json(dtResult) + ",");
span = DateTime.Now - begin;
jsonString.Append("\"span\":\"" + span.TotalMilliseconds + "\"");
jsonString.Append("}");
Response.Write(jsonString);
Response.End();
}
else if (string.Equals(outPutMode, "gridjson", StringComparison.OrdinalIgnoreCase))
{
Response.ContentType = "application/json";
//grid的分页数据
pageNo = RequestInt32("pager.pageNo", 1);
pageSize = RequestInt32("pager.pageSize", 10);
//DataTable dt = CommonManager.GetDataTableByPage(dbHelper, out totalRows, tableName, selectField, pageNo, pageSize, conditions, dbParameters, orderBy);
DataTable dt = DbLogic.GetDataTableByPage(dbHelper, out totalRows, tableName, selectField, pageNo, pageSize, conditions, dbParameters, orderBy);
StringBuilder jsonString = new StringBuilder();
if (!string.IsNullOrWhiteSpace(RequestString("openFirst")) && string.Equals("1", RequestString("openFirst"), StringComparison.OrdinalIgnoreCase))
{
jsonString.Append("{\"rows\":" + DataTableHelper.DataTable2Json(dt, true) + ",");
}
else
{
jsonString.Append("{\"rows\":" + DataTableHelper.DataTable2Json(dt) + ",");
}
jsonString.Append("\"pager.totalRows\":\"" + totalRows + "\",");
span = DateTime.Now - begin;
jsonString.Append("\"sort\":\"" + sort + "\",");
jsonString.Append("\"direction\":\"" + direction + "\",");
jsonString.Append("\"span\":\"" + span.TotalMilliseconds + "\"");//查询耗时 毫秒数
jsonString.Append("}");
Response.Write(jsonString);
Response.End();
}
else
{
Response.Write("本页面需要传入outPutMode参数");
Response.End();
}
}
}
///////////////////////////////
分页功能调用代码
/// <summary>
/// 吉日嘎拉 获取分页数据（防注入功能的）
/// 宋彪 2014-06-25 构造List<KeyValuePair<string, object>>比IDbDataParameter[]方便一些
/// dbHelper.MakeParameters(dbParameters)--》IDbDataParameter[]
/// </summary>
/// <param name="recordCount">记录条数</param>
/// <param name="dbHelper">dbHelper</param>
/// <param name="tableName">数据来源表名</param>
/// <param name="selectField">选择字段</param>
/// <param name="pageIndex">当前页</param>
/// <param name="pageSize">每页显示多少条</param>
/// <param name="conditions">查询条件</param>
/// <param name="dbParameters">查询参数</param>
/// <param name="orderBy">排序字段</param>
/// <returns>数据表</returns>
public static DataTable GetDataTableByPage(IDbHelper dbHelper, out int recordCount, string tableName, string selectField, int pageIndex, int pageSize, string conditions, List<KeyValuePair<string, object>> dbParameters, string orderBy)
{
DataTable result = null;
recordCount = 0;
if (null != dbHelper)
{
recordCount = DbLogic.GetCount(dbHelper, tableName, conditions, dbHelper.MakeParameters(dbParameters));
result = DbLogic.GetDataTableByPage(dbHelper, tableName, selectField, pageIndex, pageSize, conditions, dbHelper.MakeParameters(dbParameters), orderBy);
}
return result;
}
///////////////////////////////
底层分页功能实现，可通过源码查看
/// <summary>
/// Oracle 获取分页数据（防注入功能的）兼容多种数据库
/// </summary>
/// <param name="dbHelper">数据库连接</param>
/// <param name="tableName">数据来源表名</param>
/// <param name="selectField">选择字段</param>
/// <param name="pageIndex">当前页</param>
/// <param name="pageSize">每页显示多少条</param>
/// <param name="conditions">查询条件</param>
/// <param name="dbParameters">查询参数</param>
/// <param name="orderBy">排序字段</param>
/// <returns>数据表</returns>
public static DataTable GetDataTableByPage(IDbHelper dbHelper, string tableName, string selectField, int pageIndex, int pageSize, string conditions, IDbDataParameter[] dbParameters, string orderBy, string currentIndex = null)
{
string sqlStart = ((pageIndex - 1) * pageSize).ToString();
string sqlEnd = (pageIndex * pageSize).ToString();
if (currentIndex == null)
{
currentIndex = string.Empty;
}
if (!string.IsNullOrEmpty(conditions))
{
conditions = "WHERE " + conditions;
}
string sqlQuery = string.Empty;
if (dbHelper.CurrentDbType == CurrentDbType.Oracle)
{
if (!string.IsNullOrEmpty(orderBy.Trim()))
{
orderBy = " ORDER BY " + orderBy;
}
sqlQuery = string.Format("SELECT * FROM(SELECT ROWNUM RN, H.* FROM ((SELECT " + currentIndex +" "+ selectField+" FROM {0} {1} {2} )H)) Z WHERE Z.RN <={3} AND Z.RN >{4}"
, tableName, conditions, orderBy, sqlEnd, sqlStart);
}
else if (dbHelper.CurrentDbType == CurrentDbType.SqlServer)
{
sqlQuery = string.Format("SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY {0}) AS RowIndex, " + selectField + " FROM {1} {2}) AS PageTable WHERE RowIndex BETWEEN {3} AND {4}"
, orderBy, tableName, conditions, sqlStart, sqlEnd);
}
else if (dbHelper.CurrentDbType == CurrentDbType.MySql
|| dbHelper.CurrentDbType == CurrentDbType.SQLite)
{
sqlQuery = string.Format("SELECT {0} FROM {1} {2} ORDER BY {3} LIMIT {4}, {5}", selectField, tableName, conditions, orderBy, sqlStart, pageSize);
}
var dt = new DataTable(tableName);
if (dbParameters != null && dbParameters.Length > 0)
{
dt = dbHelper.Fill(sqlQuery, dbParameters);
}
else
{
dt = dbHelper.Fill(sqlQuery);
}
return dt;
}
这个分页功能可以兼容多种数据库，多表关联查询
