求一通用分页SQL（不要存储过程）（C#实现）
要实现这么一个功能的函数：
输入参数：一个取得记录集的Select SQL，开始行行号，最大返回行数，唯一键列名
返回：返回一个基于给出的SQL的分页SQL
我实现的一个函数如下：
/// <summary>
/// 执行SQL查询，返回分页记录集
/// </summary>
/// <param name= "sql "> SQL语句 </param>
/// <param name= "startRowIndex "> 开始行，1开始 </param>
/// <param name= "maximumRows "> 最大返回行数 </param>
/// <param name= "keyColumn "> 主键列。用于not in分页 </param>
/// <returns> </returns>
public virtual DataSet Query(String sql, Int32 startRowIndex, Int32 maximumRows, String keyColumn)
{
// 从第一行开始，不需要分页
if (startRowIndex <= 1)
{
if (maximumRows < 1)
return Query(sql);
else
return Query(String.Format( "Select Top {0} * From ({1}) a ", maximumRows, sql));
}
if (maximumRows < 1)
sql = String.Format( "Select * From ({1}) b Where {2} Not In(Select Top {0} {2} From ({1}) a) ", startRowIndex - 1, sql, keyColumn);
else
sql = String.Format( "Select Top {0} * From ({1}) b Where {2} Not In(Select Top {3} {2} From ({1}) a) ", startRowIndex + maximumRows - 1, sql, keyColumn, startRowIndex - 1);
return Query(sql);
}
在实际应用中发现，对于Access，该方法可行，对于MSSQL，就不一定了。
如果参数中的sql带有order by，而又没有top，那么，这个SQL是不能作为子查询的，上面的方法就行不通了。
后来我又加了下面一个函数：
/// <summary>
/// 为子查询准备。
/// 如果一个SQL用了order by，而没有top时，在SqlServer中是不能作为子查询的。
/// </summary>
/// <param name= "sql "> </param>
/// <returns> </returns>
private String PrepairSqlForSubSelect(String sql)
{
String str = sql.Trim().ToLower();
// Select开头，不是存储过程
if (str.StartsWith( "select ") && str.Contains( "order by "))
{
str = str.Substring(0, 30);
if (!Regex.IsMatch(str, @ "^ *select( +(all|distinct))? +top ", RegexOptions.IgnoreCase))
{
sql = Regex.Replace(sql, @ "^ *select +all ", "Select All Top 100000000 ", RegexOptions.IgnoreCase);
sql = Regex.Replace(sql, @ "^ *select +distinct ", "Select Top 100000000 ", RegexOptions.IgnoreCase);
sql = Regex.Replace(sql, @ "^ *select + ", "Select Top 100000000 ", RegexOptions.IgnoreCase);
}
}
return sql;
}
用来检查参数sql是否是这种情况，如果是的话，用正则修改这个sql，在select后面加上Top 100000000。可以解决一部分问题。
后来又发现，如果参数sql中，select后面的选择列中带有ntext类型字段的话，是不能用top。
我现在没办法了，请各位帮帮忙。
存储过程的方法就不用啦，谢谢^_^
------解决方案--------------------
难道没有看我的回复吗？
还是我写得太烂了，没有说明白。
拆开SQL语句不就可以了吗？
Page1.SqlTableNames = "SD_Product "; //表名
Page1.SqlColumns= "clo1,clo2,... "; //显示的字段
Page1.SqlPageSize = 8; //一页的记录数
Page1.SqlOrderColumn = "ProductID "; //排序字段
Page1.SqlOrderColumnKind = "int "; //排序字段类型
Page1.IsOrderDesc = true; //升序or降序
其他属性。
这样分开负值不就可以了吗？
很难吗？行不通吗？还是我没有说清楚？
[解决办法]
/// <summary>
/// MS SQLSERVER 分页SQL语句生成器，同样适用于ACCESS数据库
/// </summary>
/// <param name= "strSQLInfo "> 原始SQL语句 </param>
/// <param name= "strWhere "> 在分页前要替换的字符串，用于分页前的筛选 </param>
/// <param name= "PageSize "> 页大小 </param>
/// <param name= "PageNumber "> 页码 </param>
/// <param name= "AllCount "> 记录总数 </param>
/// <returns> 生成SQL分页语句 </returns>
private static string MakePageSQLStringByMSSQL(string strSQLInfo,string strWhere,int PageSize,int PageNumber,int AllCount)
{
#region 分页位置分析
string strSQLType=string.Empty ;
if(AllCount!=0)
{
if(PageNumber==1) //首页
{
strSQLType= "First ";
}
else if(PageSize*PageNumber> AllCount) //最后的页 @@LeftSize
{
PageSize=AllCount-PageSize*(PageNumber-1);
strSQLType= "Last ";
}
else //中间页
{
strSQLType= "Mid ";
}
}
else if(AllCount <0) //特殊处理 dth,2006.10.19
{
strSQLType= "First ";
}
else
{
strSQLType= "Count ";
}
#endregion
#region SQL 复杂度分析
//SQL 复杂度分析 开始
bool SqlFlag=true;//简单SQL标记
string TestSQL=strSQLInfo.ToUpper ();
int n=TestSQL.IndexOf ( "SELECT ",0);
n=TestSQL.IndexOf ( "SELECT ",n+7);
if(n==-1)
{
//可能是简单的查询，再次处理
n=TestSQL.IndexOf ( " JOIN ",n+7);
if(n!=-1) SqlFlag=false;
else
{
//判断From 谓词情况
n=TestSQL.IndexOf( "FROM ",9);
if(n==-1) return " ";
//计算 WHERE 谓词的位置
int m=TestSQL.IndexOf ( "WHERE ",n+5);
// 如果没有WHERE 谓词
if(m==-1) m=TestSQL.IndexOf ( "ORDER BY ",n+5);
//如果没有ORDER BY 谓词，那么无法排序，退出；
if(m==-1)
throw new Exception ( "查询语句分析：当前没有为分页查询指定排序字段！请适当修改SQL语句。\n "+strSQLInfo);
string strTableName=TestSQL.Substring (n,m-n);
//表名中有 , 号表示是多表查询
if(strTableName.IndexOf ( ", ")!=-1)
SqlFlag=false;
}
}
else
{
//有子查询；
SqlFlag=false;
}
//SQL 复杂度分析 结束
#endregion
#region 排序语法分析
//排序语法分析 开始
int iOrderAt=strSQLInfo.ToLower ().LastIndexOf ( "order by ");
//如果没有ORDER BY 谓词，那么无法排序分页，退出；
if(iOrderAt==-1)
throw new Exception ( "查询语句分析：当前没有为分页查询指定排序字段！请适当修改SQL语句。\n "+strSQLInfo);
string strOrder=strSQLInfo.Substring (iOrderAt+9);
strSQLInfo=strSQLInfo.Substring(0,iOrderAt);
string[] strArrOrder=strOrder.Split (new char []{ ', '});
for(int i=0;i <strArrOrder.Length ;i++)
{
string[] strArrTemp=(strArrOrder[i].Trim ()+ " ").Split (new char[]{ ' '});
//压缩多余空格
for(int j=1;j <strArrTemp.Length ;j++)
{
if(strArrTemp[j].Trim ()== " ")
{
continue;
}
else
{
strArrTemp[1]=strArrTemp[j];
if(j > 1 ) strArrTemp[j]= " ";
break;
}
}
//判断字段的排序类型
switch(strArrTemp[1].Trim ().ToUpper ())
{
case "DESC ":
strArrTemp[1]= "ASC ";
break;
case "ASC ":
strArrTemp[1]= "DESC ";
break;
default:
//未指定排序类型，默认为降序
strArrTemp[1]= "DESC ";
break;
}
//消除排序字段对象限定符
if(strArrTemp[0].IndexOf ( ". ")!=-1)
strArrTemp[0]=strArrTemp[0].Substring (strArrTemp[0].IndexOf ( ". ")+1);
strArrOrder[i]=string.Join ( " ",strArrTemp);
}
//生成反向排序语句
string strNewOrder=string.Join ( ", ",strArrOrder).Trim ();
strOrder=strNewOrder.Replace ( "ASC ", "ASC0 ").Replace ( "DESC ", "ASC ").Replace ( "ASC0 ", "DESC ");
//排序语法分析结束
#endregion
#region 构造分页查询
string SQL=string.Empty ;
if(!SqlFlag)
{
//复杂查询处理
switch(strSQLType.ToUpper ())
{
case "FIRST ":
SQL= "Select Top @@PageSize * FROM (\n " +strSQLInfo+
"\n) P_T0 @@Where ORDER BY "+strOrder;
break;
case "MID ":
SQL=@ "SELECT Top @@PageSize * FROM
(SELECT Top @@PageSize * FROM
(
SELECT Top @@Page_Size_Number * FROM ( ";
SQL+= "\n "+strSQLInfo+ " ) P_T0 @@Where ORDER BY "+strOrder+ "\n ";
SQL+=@ ") P_T1
ORDER BY "+ strNewOrder + ") P_T2 \n "+
"ORDER BY "+strOrder;
break;
case "LAST ":
SQL=@ "SELECT * FROM (
Select Top @@LeftSize * FROM ( "+ "\n\r "+strSQLInfo+ "\r ";
SQL+= " ) P_T0 @@Where ORDER BY "+ strNewOrder+ "\n\r "+
" ) P_T1 ORDER BY "+strOrder;
break;
case "COUNT ":
SQL= "Select COUNT(*) FROM ( " +strSQLInfo+ " ) P_Count @@Where ";
break;
default:
SQL=strSQLInfo+strOrder;//还原
break;
}
}
else
{
//简单查询处理
switch(strSQLType.ToUpper ())
{
case "FIRST ":
SQL=strSQLInfo.ToUpper().Replace ( "SELECT ", "SELECT TOP @@PageSize ");
SQL+= " @@Where ORDER BY "+strOrder;
break;
case "MID ":
string strRep=@ "SELECT Top @@PageSize * FROM
(SELECT Top @@PageSize * FROM
(
SELECT Top @@Page_Size_Number ";
SQL=strSQLInfo.ToUpper().Replace ( "SELECT ",strRep);
SQL+= " @@Where ORDER BY "+strOrder;
SQL+= " \r\n) P_T0 ORDER BY "+ strNewOrder+ "\n\r "+
" ) P_T1 ORDER BY "+strOrder;
break;
case "LAST ":
string strRep2=@ "SELECT * FROM (
Select Top @@LeftSize ";
SQL=strSQLInfo.ToUpper().Replace ( "SELECT ",strRep2);
SQL+= " @@Where ORDER BY "+ strNewOrder+ "\n\r "+
" ) P_T1 ORDER BY "+strOrder;
break;
case "COUNT ":
SQL= "Select COUNT(*) FROM ( " +strSQLInfo+ " ) P_Count @@Where ";
break;
default:
SQL=strSQLInfo+strOrder;//还原
break;
}
}
//执行分页参数替换
SQL=SQL.Replace ( "@@PageSize ",PageSize.ToString ())
.Replace ( "@@Page_Size_Number ",Convert.ToString (PageSize * PageNumber))
.Replace ( "@@LeftSize ",PageSize.ToString ())
.Replace ( "@@Where ",strWhere);
return SQL;
#endregion
}
