因项目中要用到多种数据库，根据SQL语句查询数据，然而每种数据库之间分页查询的方法又不一样，只有整理一个通用的方法，把SQL语句传出，并进行分页查询的语句组装，组装后再进行相应查询即可。现把代码分享出来，需要的朋友拿走，就不用再云整理了，代码如下：
/// <summary>
/// SQL语句附加分页
/// SqlServer只支持Sql2005以上版本
/// </summary>
public class DbPageHelper
{
/// <summary>
/// 输出分页Sql语句
/// </summary>
/// <param name="dataType"></param>
/// <param name="strSql"></param>
/// <param name="orderField"></param>
/// <param name="isAsc"></param>
/// <param name="pageSize"></param>
/// <param name="pageIndex"></param>
/// <returns></returns>
public static StringBuilder ToPageSql(DataType dataType, string strSql, string orderField, bool isAsc, int pageSize, int pageIndex)
{
StringBuilder builder = new StringBuilder();
switch (dataType)
{
case DataType.MySql:
case DataType.Dameng:
case DataType.KingbaseES:
return MySqlToPageSql(strSql, orderField, isAsc, pageSize, pageIndex);
case DataType.SqlServer:
return SqlServerToPageSql(strSql, orderField, isAsc, pageSize, pageIndex);
case DataType.Oracle:
return OracleToPageSql(strSql, orderField, isAsc, pageSize, pageIndex);
case DataType.PostgreSQL:
case DataType.Sqlite:
case DataType.ShenTong:
return PostgreSQLToPageSql(strSql, orderField, isAsc, pageSize, pageIndex);
default:
builder.Append(strSql);
return builder;
}
}
/// <summary>
/// Mysql分页Sql语句
/// </summary>
/// <param name="strSql"></param>
/// <param name="orderField"></param>
/// <param name="isAsc"></param>
/// <param name="pageSize"></param>
/// <param name="pageIndex"></param>
/// <returns></returns>
public static StringBuilder MySqlToPageSql(string strSql, string orderField, bool isAsc, int pageSize, int pageIndex)
{
StringBuilder builder = new StringBuilder();
if (pageIndex == 0)
{
pageIndex = 1;
}
int num = (pageIndex - 1) * pageSize;
string str = "";
if (!string.IsNullOrEmpty(orderField))
{
if ((orderField.ToUpper().IndexOf("ASC") + orderField.ToUpper().IndexOf("DESC")) > 0)
{
str = " Order By " + orderField;
}
else
{
str = " Order By " + orderField + " " + (isAsc ? "ASC" : "DESC");
}
}
builder.Append(strSql + str);
builder.Append(string.Concat(new object[] { " limit ", num, ",", pageSize }));
return builder;
}
/// <summary>
/// Oracle分页SQL语句
/// </summary>
/// <param name="strSql"></param>
/// <param name="orderField"></param>
/// <param name="isAsc"></param>
/// <param name="pageSize"></param>
/// <param name="pageIndex"></param>
/// <returns></returns>
public static StringBuilder OracleToPageSql(string strSql, string orderField, bool isAsc, int pageSize, int pageIndex)
{
StringBuilder builder = new StringBuilder();
if (pageIndex == 0)
{
pageIndex = 1;
}
int num = (pageIndex - 1) * pageSize;
int num2 = pageIndex * pageSize;
string str = "";
if (!string.IsNullOrEmpty(orderField))
{
if ((orderField.ToUpper().IndexOf("ASC") + orderField.ToUpper().IndexOf("DESC")) > 0)
{
str = " Order By " + orderField;
}
else
{
str = " Order By " + orderField + " " + (isAsc ? "ASC" : "DESC");
}
}
builder.Append("Select * From (Select ROWNUM,");
builder.Append(string.Concat(new object[] { " T.* From (", strSql, str, ") T ) N Where rowNum > ", num, " And rowNum <= ", num2 }));
return builder;
}
/// <summary>
/// SqlServer分页SQL语句
/// </summary>
/// <param name="strSql"></param>
/// <param name="orderField"></param>
/// <param name="isAsc"></param>
/// <param name="pageSize"></param>
/// <param name="pageIndex"></param>
/// <returns></returns>
public static StringBuilder SqlServerToPageSql(string strSql,string orderField, bool isAsc, int pageSize, int pageIndex)
{
StringBuilder builder = new StringBuilder();
if (pageIndex == 0)
{
pageIndex = 1;
}
int num = (pageIndex - 1) * pageSize;
int num2 = pageIndex * pageSize;
string str = "";
if (!string.IsNullOrEmpty(orderField))
{
if ((orderField.ToUpper().IndexOf("ASC") + orderField.ToUpper().IndexOf("DESC")) > 0)
{
str = " Order By " + orderField;
}
else
{
str = " Order By " + orderField + " " + (isAsc ? "ASC" : "DESC");
}
}
else
{
str = "order by (select 0)";
}
builder.Append("Select * From (Select ROW_NUMBER() Over (" + str + ")");
builder.Append(string.Concat(new object[] { " As rowNum, * From (", strSql, ") T ) As N Where rowNum > ", num, " And rowNum <= ", num2 }));
return builder;
}
/// <summary>
/// PostgreSQL分页Sql语句
/// </summary>
/// <param name="strSql"></param>
/// <param name="orderField"></param>
/// <param name="isAsc"></param>
/// <param name="pageSize"></param>
/// <param name="pageIndex"></param>
/// <returns></returns>
public static StringBuilder PostgreSQLToPageSql(string strSql, string orderField, bool isAsc, int pageSize, int pageIndex)
{
StringBuilder builder = new StringBuilder();
if (pageIndex == 0)
{
pageIndex = 1;
}
int num = (pageIndex - 1) * pageSize;
string str = "";
if (!string.IsNullOrEmpty(orderField))
{
if ((orderField.ToUpper().IndexOf("ASC") + orderField.ToUpper().IndexOf("DESC")) > 0)
{
str = " Order By " + orderField;
}
else
{
str = " Order By " + orderField + " " + (isAsc ? "ASC" : "DESC");
}
}
builder.Append(strSql + str);
builder.Append(string.Concat(new object[] { " limit ", pageSize, " OFFSET ", num }));
return builder;
}
}