C#将SQL语句转换为分页SQL和获取记录数SQL

C#将SQL语句转换为分页SQL和获取记录数SQL

/// <summary>
/// 将语句转化为分页SQL语句，两条包含分页的获取数据SQL；获取记录数SQL 贾世义
/// </summary>
/// <param name="dbtype">支持数据类型（Sql2005、Oracle、Db2、Infomix）</param>
/// <param name="sql">SQL语句（Sql2005、Oracle、Db2必须包含order by，正常的一条SQL语句）</param>
/// <param name="pageIndex">开始（从0开始）</param>
/// <param name="pageSize">分页大小</param>
/// <returns>两条语句，包含分页的获取数据SQL；获取记录数SQL</returns>
public static string GetPageSql(DBType dbtype, string sql, int pageIndex, int pageSize)
{
if (String.IsNullOrEmpty(sql))
{
return null;
}
//不支持分页SQL
if (dbtype == DBType.Sql2000 || dbtype == DBType.OleDb || dbtype == DBType.Odbc)
{
return sql;
}
string start = "0";
if (pageIndex > 0)
{
start = Convert.ToString(pageIndex * pageSize);
}
int select = GetStartWith(sql, "select");
if (dbtype == DBType.OdbcInformix)
{
return "select skip " + start + " first " + pageSize.ToString()
+ " " + sql.Substring(select);
}
//拆分SQL
string end = Convert.ToString((pageIndex + 1) * pageSize);
string fromSql = GetFromSql(sql, "from");
//select部分 含select
string selectSql = "select " + sql.Substring(select, sql.Length - select - fromSql.Length);
string orderSql = GetFromSql(sql, "order by");
if (!String.IsNullOrEmpty(orderSql))
{
//将from后的order by去除
fromSql = fromSql.Substring(0, fromSql.Length - orderSql.Length);
}
else
{
throw new Exception(dbtype + "使用分页必须包含order by");
}
//合并成分页SQL
string strSql = "select * from (" + selectSql;
//order部分 含 order by
string rownum;
if (dbtype == DBType.OdbcDb2)
{
rownum = "rownumber()";
}
else
{
rownum = "row_number()";
}
strSql += "," + rownum + " over (" + orderSql + ") as rn " + fromSql
+ ") as data where rn>" + start + " and rn<=" + end;
if (!String.IsNullOrEmpty(GetFromSql(fromSql, "group by")))
{
fromSql = " from (select count(*) as amount " + fromSql + ") tbl";
}
return strSql + ";select count(*) as [" + Constants.MYQUERY_AMOUNT + "] " + fromSql;
}

欢迎访问：http://121.18.78.216 适易查询分析、工作流、内容管理及项目管理演示平台