| 最近用NHibernate做个项目 由于数据库用的是mssql2000 NHibernate 对mssql2000的分页查询支持的不是很好 于是自己动手实现一个mssql2000方言 原 NHibernate.Dialect 命名空间下的mssql2000的方言类 MsSql2000Dialect 里的 GetLimitString 方法 如下： Code public override SqlString GetLimitString(SqlString querySqlString, int offset, int limit) { if (offset > 0) { throw new NotSupportedException("SQL Server does not support an offset"); } /* * "SELECT TOP limit rest-of-sql-statement" */ return querySqlString.Insert(GetAfterSelectInsertPoint(querySqlString), " top " + limit.ToString()); } 看上面这段代码大家都知道这种分页方式的性能有多么的差 下面我把我自己实现的方言类代码贴出来 Code public class mySqlServer2000Dialect : MsSql2000Dialect { private static int GetFromIndex(SqlString querySqlString) { string subselect = querySqlString.GetSubselectString().ToString(); int fromIndex = querySqlString.IndexOfCaseInsensitive(subselect); if (fromIndex == -1) { fromIndex = querySqlString.ToString().ToLowerInvariant().IndexOf(subselect.ToLowerInvariant()); } return fromIndex; } private static string RemoveSortOrderDirection(string sortExpression) { // Drop the ASC/DESC at the end of the sort expression which might look like "count(distinct frog.Id)desc" or "frog.Name asc". return Regex.Replace(sortExpression.Trim(), @"(\)|\s)(?i:asc|desc)$", "$1").Trim(); } public override bool SupportsLimitOffset { get { return true; } } public override NHibernate.SqlCommand.SqlString GetLimitString(SqlString querySqlString, int offset, int limit) { if (offset == 0) { return base.GetLimitString(querySqlString, offset, limit); } SqlString myQuery = new SqlString(RemoveSortOrderDirection(querySqlString.ToString().ToLower())); int orderIndex = myQuery.LastIndexOfCaseInsensitive(" order by "); if (orderIndex <= 0) { throw new NotSupportedException("must specify 'order by' statement to support limit operation with offset in SqlServer2000"); } string orderBy = myQuery.Substring(orderIndex).ToString().Trim(); string[] sortExpressions = myQuery.Substring(orderIndex).ToString().Trim().Substring(9).Split(','); int fromIndex = GetFromIndex(myQuery); SqlString from = myQuery.Substring(fromIndex, orderIndex - fromIndex).Trim(); string fromAsName = from.ToString().Substring(from.ToString().IndexOf(' ', 5)).Trim(); SqlString select = myQuery.Substring(0, fromIndex); int PageSize = limit - offset; SqlStringBuilder result = new SqlStringBuilder(); result.Add("declare @indextable table(id int identity(1,1),nid int) set rowcount " + limit.ToString()); result.Add(" insert into @indextable(nid) select"); result.Add(" " + RemoveSortOrderDirection(sortExpressions[0]).Replace(fromAsName,"t") + " " + from.ToString().Replace(fromAsName,"t") + " " + orderBy.Replace(fromAsName,"t") + " desc "); result.Add(" " + select.ToString() + " " + from.ToString() + ",@indextable a where " + RemoveSortOrderDirection(sortExpressions[0]) + " = a.nid and a.[id] between " + (offset + 1).ToString() + " and " + limit.ToString()); return result.ToSqlString(); } } 这个类继承自原 NHibernate 的 mssql2000方言类 重写了 SupportsLimitOffset 属性 返回 true 让 NHibernate 支持 offset 重写了 GetLimitString 方法 使用 内存变量表的方式进行数据分页 前提是 hql 语句里必须带有主键排序字段 主键必须是 自动增长 下面介绍如何使用这个类来替换 NHibernate 下的原mssql2000方言： 在NHibernate配置文件里的找到 <property name="dialect">NHibernate.Dialect.MsSql2000Dialect</property> 替换成 <property name="dialect">myMsSql2000Dialect类的命名空间.myMsSql2000Dialect</property> |