要实现这么一个功能的函数:
输入参数:一个取得记录集的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。我现在没办法了,请各位帮帮忙。
存储过程的方法就不用啦,谢谢^_^
输入参数:一个取得记录集的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。我现在没办法了,请各位帮帮忙。
存储过程的方法就不用啦,谢谢^_^
解决方案 »
- 大家帮俺想个问题吧。
- 求高并发情况下生成唯一订单号解决方案
- 如何实现asp.net 调用局域网影音文件
- asp.net如何使用外部控件,比如AspNetPager.dll,直接用就行吗?
- 像这样的存储过程,写在那个文件里?
- 请帮帮忙,将这段javascript代码改造一下,以便可以在ie和firefox下都能运行!
- 如何设置treeview控件,让显示的时候是透明的?
- 数据库怎么还原?
- 如何做可以跳转页的datagrid~?
- sql数据庫有没有目录的说法,就是说这个数据库建立在那个目录下面?:)
- 如何写一个判断conn关没关闭的方法啊?
- 急求购一套完整的(大型的最好)网站源代码.信息主要是产品发布新闻方面
-_-!!
text字段的经常用!没有发现什么问题!
ntext应该一样的。 # region 分页SQL代码
private string SQL(SQLModel s_model)
{
string strSql = null;
if (s_model.PageNo == 1)
{
string strCondition = (s_model.Condition.ToLower() == "none" ? "" : s_model.Condition);
strSql = "Select Top " + s_model.PageSize + " " + s_model.Fields + " From " + s_model.TableName + " " + strCondition + " Order By " + s_model.OrderField + " " + s_model.OrderType;
}
else
{
if (s_model.Condition.ToLower() == "none" || s_model.Condition == "")
{
strSql = "Select Top " + s_model.PageSize + " " + s_model.Fields + " From " + s_model.TableName + " Where " + s_model.OrderField + (s_model.OrderType.ToLower() == "desc" ? " < (Select Min(" : " > (Select Max(") + s_model.OrderField + ") From (Select Top " + (s_model.PageSize * (s_model.PageNo - 1)) + " " + s_model.OrderField + " From " + s_model.TableName + " Order By " + s_model.OrderField + " " + s_model.OrderType + ") Temp) Order By " + s_model.OrderField + " " + s_model.OrderType;
}
else
{
strSql = "Select Top " + s_model.PageSize + " " + s_model.Fields + " From " + s_model.TableName + " " + s_model.Condition + " And " + s_model.OrderField + (s_model.OrderType.ToLower() == "desc" ? " < (Select Min(" : " > (Select Max(") + s_model.OrderField + ") From (Select Top " + (s_model.PageSize * (s_model.PageNo - 1)) + " " + s_model.OrderField + " From " + s_model.TableName + " " + s_model.Condition + " Order By " + s_model.OrderField + " " + s_model.OrderType + ") Temp) Order By " + s_model.OrderField + " " + s_model.OrderType;
}
}
return strSql;
}
#endregion
{
SqlConnection myconn = new SqlConnection("Connstring");
SqlAdpter GetData = new ("SELECT * FROM "+tname+" ORDER BY "+key,myconn);
DataSet GetAll = new DataSet();
GetData.Fill(GetAll); int Records = GetAll.Tabel[0].Rows.Count;
int TotalPage = Records/pagesize;
if(TotalPage*pagesize < Records) TotalPge++;
if(page <=1) page = 1;
else if(page >= TotalPage) page = TotalPage;
int StartLine = (page - 1) * pagesize;
int EndLine = StartLine + pagesize;
if(EndLine > Records) EndLine = Records DataSet PageData = new DataSet();
PagefData = GetAll.Clone(); for(int i = StartLine;i < EndLine;i++)
{
DataRow row = PageData.Table[0].AddRow();
row.ItemArray = GetAll.Tabel[0].Row[i].ItemArray;
PageData.Table[0].Rows.Add(row);
}
return PageData;
} 可能程序会有些问题,自己改下吧,主要是给你个思想,有很多东西其实自己可以多琢磨下的
currentPage ---当前页码
perPage ---每页记录数
Page1.SqlColumns= "clo1,clo2,..."; //显示的字段
Page1.SqlPageSize = 8; //一页的记录数
Page1.SqlOrderColumn = "ProductID"; //排序字段
Page1.SqlOrderColumnKind = "int"; //排序字段类型
Page1.IsOrderDesc = true; //升序or降序其他属性。这样你就可以随意组合了,而且更不受数据库类型的限制了。哪个数据库没有表名和字段名呀。
大部分情况下,都是业务层调用数据层,但是有时候,业务层的更上一层,可能会需要直接调用数据层,以实现某些特殊的功能,所以,不能把业务层的处理移到数据层。◎jyk
我也想这么做,但是,你可有发现,这么做,只能实现非常简单的语句而已,带有组合查询、子查询等负责的语句,就无能为力了,我曾试过想按照TSQL92的标准来分析,发现,太复杂了,所以不了了之。我就是想封装好数据层,使用数据层的,不仅仅是业务层呀,所以数据层不能做成只为业务层服务。
MS Server 里面的视图是大家很忽略的地方,很少人去使用吧。我几乎是处处都在用视图,相对的很少用存储过程。2、属性可以灵活使用>>Page1.SqlTableNames = "SD_Product"; //表名不仅是表名,还可以是视图名,也可以是 多表联合的(from ......where 这之间的都可以)>>Page1.SqlColumns= "clo1,clo2,..."; //显示的字段这里也是可以放子查询的呀,比如(select aa from bb where ...) as col3还有一个属性是“查询条件”,可以不设置,可以设置简单查询,还可以设置 子查询。几乎没有做不到的。当然了难处理的是排序字段的这一块,灵活了很难适应分页算法,不灵活点呢,适用范围就要受到限制。程序员要考虑到所有的情况,但是也要考虑到特殊情况所占的比例。如果某种情况之占1%,但是实现他却需要50%的“精力”,那么就可以忽略他。
2、“执行”部分。(类似于SQLHelp)第一部分可以叫做“程序逻辑” ——现对于业务逻辑来说的——其实我想直接叫做业务逻辑的,但是古迹很多人都会反对的,也会产生混淆,因为三层已经“根深蒂固”了。这一部分是不通用的,每一个项目每一个模块,每一个“页面”都是不同的,需要分别对待。而第二部分就是通用的,说白了就是对ADO.NET的进一步封装。
我的数据访问层就是这个思路(参见我的blog)PS:1、注意“数据层”和“数据访问层”字眼上的区别,数据层的范围是很大的,数据库部分算不算呀?存储过程算不算呀?不算的话他们是哪一层的呀?第四层?所以我使用“数据访问层”,就是实现访问数据库的功能,至于是访问那个表,提取多少条记录,显示那些字段,都不是“数据访问层”的事情,而是“程序逻辑”的事情。这样做的好处是可以进一步分离,如果使用这种思路的话,你会发现以前不好处理的地方都会迎刃而解!2、“程序逻辑” 这个名字也不是太符合情况,只是我还没有想到更好的词语,先凑合一下了:)
/// 生成分页SQL语句
/// </summary>
/// <param name="sql">任意查询语句</param>
/// <param name="pageSize">每页记录数</param>
/// <param name="currPage">当前页,即需要第几页的数据</param>
/// <param name="keyColumn">排序字段</param>
/// <param name="OrderType">排序方法,ASC或DESC</param>
/// <returns></returns>
public string Pager(string sql,int pageSize,int currPage,string keyColumn,string OrderType)
{
string strSql=string.Empty;
string NOrderType;
if(OrderType.ToLower().Equals("asc"))
NOrderType="desc";
else if(OrderType.ToLower().Equals("desc"))
NOrderType="asc";
else
return string.Empty;
if(currPage==1)
{
strSql=String.Format("Select Top {0} * From ({1}) a order by {2}", pageSize, sql,OrderType);
}
if(currPage>1)
{
strSql=String.Format("select * from (select top {0} * from (select top {1} * from ({2}) as a order by {3} {4}) as t order by {3} {5}) as a order by {3} {4}",
pageSize,pageSize*currPage,sql,keyColumn,OrderType,NOrderType);
}
return strSql;
}
----------------------
才根据上面几位的代码启示写的,随便写了几条SQL语句测试通过
{
strSql=String.Format("Select Top {0} * From ({1}) a order by {2} {3}", pageSize, sql,keyColumn,OrderType);
}
-----------
有点小错误,才改了下
1、要想提高分页的效率就得根据数据库的特性来做,MSSQL是对top 支持的“最好”的,而利用这个top 加上单一主键,就可以实现很高的效率和通用性,而这种方法对于SQLanywhere 就不行,因为他不支持top嵌套。据说orcale根本就不支持top (我一点都不会orcale,所以也不知道我说的对不对)。其它的数据库也是不一样的。听说 SQL2005又加了一个新的东东,可以利用这个来分页了,同一种数据库不同的版本都会有不同的情况,所以说想通用是很能的,想即通用又高效又支持各种数据库,那更是难上加难。
我是写了一个接口,每种数据库一个DAL,都实现这个接口,然后整个数据层对外暴露的,只是一个类,这个类记录着当前数据库类型,通过接口调用对应DAL的方法。sqlserver的确是top,sqlserver2005有了一个新函数。
至于,oracle,呵呵,分页实在是太简单了,是的,它不支持top,它有一个rownum,给你看看我的方法:
/// <summary>
/// 已重写。获取分页
/// </summary>
/// <param name="sql"></param>
/// <param name="startRowIndex"></param>
/// <param name="maximumRows"></param>
/// <param name="key"></param>
/// <returns></returns>
public override DataSet Query(String sql, int startRowIndex, int maximumRows, String key)
{
// return base.Query(sql, startRowIndex, maximumRows, key);
// 从第一行开始,不需要分页
if (startRowIndex <= 1)
{
if (maximumRows < 1)
return Query(sql);
else
return Query(String.Format("Select * From ({1}) as a Where rownum<={0}", maximumRows, sql));
}
if (maximumRows < 1)
sql = String.Format("Select * From ({1}) as a Where rownum>={0}", startRowIndex, sql);
else
sql = String.Format("Select * From ({1}) as a Where rownum>={0} and rownum<={2}", startRowIndex, sql, startRowIndex + maximumRows - 1);
return Query(sql);
}
1,业务完全实现在代码中;
2,业务完全实现在数据库中,比如存储过程。两种方法各有优点,但是如果像你的数据访问层那样,如果要支持多种数据库,是不是必须把业务SQL写到各种数据库DAL中呢?呵呵,如果是这样的话,那么,这个数据访问层,也就只有这个系统能够使用,而在别的系统,就必须修改了。建议研究一下NBear。
>>2、“执行”部分。(类似于SQLHelp)你说的业务逻辑要放也是放到第一个部分,而我说的一个数据库对应一个dal,指的是第二个部分,不包括第一个部分。==============>>1,业务完全实现在代码中;
>>2,业务完全实现在数据库中,比如存储过程。唉,又是绝对的说法。难道不能综合一下吗?放在哪里方便就放在那里,一个项目里面业务逻辑,一部分放在代码中,另一部分放在存储过程里面。当然也可以全放在代码(或存储过程)里面。应该没有死规定,看情况来灵活掌握。这样不可以吗?当然要有一个文档来说明一下,某一个模块(功能)的业务逻辑放在哪里了。我觉得不管使用什么方式文档都应该有这个功能的。
在你的范围内,你的做法,的确是最好的了。我大概数一数我们公司用到的数据库:MSSQL、Oracle、Sybase、DB2、MySQL……似乎各种数据库都有的……置于做的项目类型嘛,那就更是五花八门了,从web到winform,都有吧。网站只是其中一点。我们的一些项目上线后,经历过很多次:更换数据库,合并数据库……同一套系统,用什么数据库,得由客户决定。
你所说的数据层,我举一个例子吧,MSSQL、Oracle、MySQL三种数据库的分页SQL写法,各不相同,根据具体业务具体表来写,才是最好最合理的。如果真的这么做,那么,就得在写业务的时候,在需要分页的地方,判断不同数据库,从而执行不同数据库的业务分页SQL了。这样,业务就和数据库参杂在一起啦。做过多数据库的人知道,这是极不愿意看到的。
我看过一些ORM对这方面的实现,NH是通过自创的强大HQL引擎,在底层根据不同的数据库转成不同的SQL的;NBear用的方法和我类似,对不同数据库,写不同的DAL,然后在DAL里面根据参数sql生成真正的分页SQL,我仔细分析,发现似乎也会碰到我那样的问题,并不能支持复杂的sql语句。
http://www.cnblogs.com/jyk/archive/2007/02/20/652942.html1、 DAL并不是放在一个DLL里面,而是分别放在各个的DLL里面,需要哪个数据库就使用哪个DLL文件。
2、 DAL里面的命名空间、类名、参数类型都是一样的,至少主要功能都是一样的。
3、 分页控件还没有考虑好,现在只是针对MSSQL来做的,其它的数据库还没太考虑。
4、 如果情况特殊分页控件也还是可以“分别”对待的,就是若干个数据库对应一个分页控件。另外一个数据库对应另一个分页控件。
1、select top 10000000 的那个id (这句将占99%的查询时间)
2、select top 每页显示多少 ... where id 大于 那个 id
Page1.SqlTableNames = "SD_Product"; //表名
Page1.SqlColumns= "clo1,clo2,..."; //显示的字段
Page1.SqlPageSize = 8; //一页的记录数
Page1.SqlOrderColumn = "ProductID"; //排序字段
Page1.SqlOrderColumnKind = "int"; //排序字段类型
Page1.IsOrderDesc = true; //升序or降序
Page1.SQLQuery = "" ; //查询条件
======在分页控件里面可以这样组合SQL语句,"select top " + SqlPageSize + " " + SqlColumns " from + SqlTableNames + " where " + SQLQuery + " order by " + SqlOrderColumn + IsOrderDesc 注意:
1、这只是一个实例,实际上要判断是不是有查询条件,没有的话就不需要 加 where 了。
2、上面的是第一页的SQL语句,其他的根据你的算法来组合。
3、这里就是图上面的组成SQL语句的地方,很显然一种数据库只需要一个。这样的方法你一万个业务也是一样的,只是一种情况,不会出现你所说的 M * 1 的情况。就是 1 个。
------------------下面再来看看多个数据库的情况。应该不用多说了吧,你所说得 M×N 其实就是 N 。你的数据库类型在多也不会超过20个吧。
PS:
对了分页是不需要存储过程的,只是在分页控件里面根据属性(外加分页算法)来组合SQL语句。
谢谢你的提醒,我差点忘了top的损耗,不过你放心,后面排序用的是索引列,top的损耗就为0%了。
至于2那个写法,不够通用。◎jyk
我的目标是M×N<=N。也就是说M=1,多种数据库的时候,某些数据库的分页写法是一致的,如AC和MSSQL。
编写业务层的同事,可能根本不懂分页算法,也不知道现在是哪种数据库,只能按常规的写法写一个select * from table,然后调用函数Select("select * from table", 开始行, 最大函数)来达到分页效果。这样做,不是非常好么?
一个显示帖子的控件...数据绑定感觉样子不怎么好控制~~~
所以自己写控件了~~也不知道怎么分页~~这个帖子学习了很多东西~呵呵 我基本已经搞定了~~
自己写一个类 其中一个静态方法~ 传3个参数进去 (第几页,每页多少条,哪个表)
返回一个SQL给我~~~
Page1.SqlColumns= "clo1,clo2,..."; //显示的字段
Page1.SqlPageSize = 8; //一页的记录数
Page1.SqlOrderColumn = "ProductID"; //排序字段
Page1.SqlOrderColumnKind = "int"; //排序字段类型
Page1.IsOrderDesc = true; //升序or降序其他属性。
这样分开负值不就可以了吗?很难吗?行不通吗?还是我没有说清楚?
缺点是:要改数据的读取类
如果是不支持存储过程的数据源,那就另行处理.比如建个类.
public enum DatasourceType{
mysql,
mssql,
access,
.......
}
根据不同的DatasourceType去选择使用.例如mssql 当然使用sqlclient....为最佳选择.
Page1.SqlColumns= "clo1,clo2,..."; //显示的字段
Page1.SqlPageSize = 8; //一页的记录数
Page1.SqlOrderColumn = "ProductID"; //排序字段
Page1.SqlOrderColumnKind = "int"; //排序字段类型
Page1.IsOrderDesc = true; //升序or降序
Function GetPageSQL(TableName,FieldName,SearchWhere,OrderFieldName,OnlyFieldName,OrderType,PageSizeNum,Page)
'Power by Love_Computer 2005-05-26 12:28
' www.56390.com
StrWhere = ""
StrOrder = ""
StrSQL = ""
MaxMinID = ""
If SearchWhere = "" Then
StrWhere = ""
MaxMinID = " WHERE ( [" & OnlyFieldName & "] "
Else
StrWhere = " WHERE " & SearchWhere & " "
MaxMinID = " AND ( [" & OnlyFieldName & "] "
End If
If OrderType = 0 Then
MaxMinID = MaxMinID & " > ( SELECT MAX"
StrOrder = " ORDER BY [" & OrderFieldName & "] ASC "
Else
MaxMinID = MaxMinID & " < ( SELECT MIN"
StrOrder = " ORDER BY [" & OrderFieldName & "] DESC "
End If
If Page = 1 or Page = 0 Then
StrSQL = "SELECT TOP " & PageSizeNum & " " & FieldName & " FROM [" & TableName & "] " & StrWhere & StrOrder
Else
StrSQL = "SELECT TOP " & PageSizeNum & " " & FieldName & " FROM [" & TableName & "] " & StrWhere & MaxMinID
StrSQL = StrSQL & " ([" & OnlyFieldName & "]) FROM ( SELECT TOP "
StrSQL = StrSQL & PageSizeNum * (Page - 1) & " [" & OnlyFieldName & "] FROM [" & TableName & "] " & StrWhere & StrOrder
StrSQL = StrSQL & " ) AS TempTable ) ) " & StrOrder
End If
GetPageSQL = StrSQL
End Function
参数:sql,开始行,最大行数
输出:基于SQL,带有分页功能的SQL语句楼上的不符合要求,不过还是谢谢这位好心的朋友。今天开始写那个类,进展不错,打算一次做到位,能支持复杂SQL语句的分析。唯一碰到的难题就是:用于分析SQL语句的正则表达式老是有问题。
那个也是输入多参数,不能符合我的要求,我要求的参数只有三个:sql,开始行,最大行数
select id, title, posttime from(
select top 450000 id, title, content, posttime from nl_news
union all
select top 450010 id, title, content, posttime from nl_news
)
a
group by id, title, posttime
having count(id)=1经过测试,比Not In还要慢六七倍
这里,http://blog.csdn.net/AnyJack/archive/2003/11/29/16130.aspx也可以使用行计数SET ROWCOUNT ,这可是好东西,只是不知道为什么,很少人使用这个。它可是又容易又快(有人专门测试过,这里http://blog.csdn.net/Leem/archive/2006/04/22/673009.aspx)
* SQL SERVER :
* 如果有这样简单的查询语句:
* SELECT SelectFields FROM TABLE @@Where ORDER BY OrderFields DESC
* 那么分页方案可以采用下面的方式:
*
* 第一页:
* SELECT TOP @@PageSize SelectFields FROM TABLE @@Where ORDER BY OrderFields DESC;
* 中间页:
* SELECT Top @@PageSize * FROM
(SELECT Top @@PageSize * FROM
(
SELECT Top @@Page_Size_Number
SelectFields FROM TABLE @@Where ORDER BY OrderFields DESC
) P_T0 ORDER BY OrderFields ASC
) P_T1 ORDER BY OrderFields DESC
* 最后页:
* SELECT * FROM (
Select Top @@LeftSize
SelectFields FROM TABLE @@Where ORDER BY OrderFields ASC
) ORDER BY OrderFields DESC
* 函数 MakePageSQLStringByDBNAME 在此基础上实现了更为复杂的分页处理,这里的复杂时说查询
* 包含大量的子查询或者连接查询,因此评价查询复杂与否采用下面的标准:
*
* 只包含一个 SELECT 谓词;
* 没有 INNER JOIN,RIGHT JOIN,LEFT JOIN 等表连接谓词;
* 谓词 FROM 后只能有一个表名;
*
* 否则,视为该查询为一个复杂查询,采用复杂查询分页方案;
Oracle :
基本的分页原理利用Oracle内指的 rownum 伪列,它是一个递增序列,但是它在Order by 之前生成,通常
采用下面的分页语句:
select * from
(select rownum r_n,temptable.* from
( @@SourceSQL ) temptable
) temptable2 where r_n between @@RecStart and @@RecEnd
其中:
@@SourceSQL :当前任意复杂的SQL语句
@@RecStart:记录开始的点,等于 ((tCurPage -1) * tPageSize +1)
@@RecEnd :记录结束的点,等于 (tCurPage * tPageSize)
** 约束:
使用该分页方法要求 SQL语句本身必须满足下列条件:
1,最外层的查询不能含有 TOP 谓词(最好不要使用TOP,可以避免Oracle 不兼容的问题);
2,最外层查询必须含有 ORDER BY 语句(Oracle除外);
3,不能含有下列替换参数(区分大小写):@@PageSize,@@Page_Size_Number,@@LeftSize,@@Where
4,SQL必须符合 SQL-92 以上标准,且 最外层ORDER BY 语句之后不能有其他语句,
5,如果使用SQLSERVER 以外的数据库系统,请在Web.config配置节里面注明 EngineType 的值;
Group by 等放在Order by 之前;
**
*/
/// 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
}
/// 生成SQL分页语句,记录总数为0表示生成统计语句
/// </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>
public static string MakeSQLStringByPage(string strSQLInfo,string strWhere,int PageSize,int PageNumber,int AllCount)
{
//根据不同的数据库引擎调用不同生成器
string SQL=string.Empty ;
switch(DbmsType)
{
case DBMSType .ACCESS :
case DBMSType .SQLSERVER :
SQL=MakePageSQLStringByMSSQL( strSQLInfo, strWhere, PageSize, PageNumber, AllCount);
break;
case DBMSType .ORACLE :
SQL=MakePageSQLStringByOracle( strSQLInfo, strWhere, PageSize, PageNumber, AllCount);
break;
default:
SQL=MakePageSQLStringByMSSQL( strSQLInfo, strWhere, PageSize, PageNumber, AllCount);
break;
}
return SQL; }
如:已查出总条数为X条;
每页20条;
并已计算出有Y页;(确定排序方式为ID ASC);
要取第Z页(不是最后一页)的数据
则查询语句为"select * From (select Top 20 * From (select Top "+(20*Z).ToString()+" * From Table Order by ID ASC) Order by ID DESC) Order by ID Asc"
最外面一个select只为重新排序,中间两个查询是实际内容。
当Z=Y时要单独写。
"select Top 20 * From (select Top " + (X-(Y-1)*20).ToString()+ " FromTable Order by ID DESC) Order by ID ASC
//附:得到X的查询方法
select Count(*) From Table
实现的很好,在帖子列表和帖子内容中都用到了分页方法代码全部开放,下载:http://bbs.e-0631.cn/down.aspx不妨下载了参考一下!