最近用NHibernate做个项目 由于数据库用是mssql2000 NHibernate 对mssql2000分页查询支持不是很好 于是自己动手实现个mssql2000方言

原 NHibernate.Dialect 命名空间下mssql2000方言类 MsSql2000Dialect 里 GetLimitString 思路方法 如下:

Code
public override SqlString GetLimitString(SqlString querySqlString, off, limit)
{
(off > 0)
{
throw NotSupportedException("SQL Server does not support an off");
}

/*
* "SELECT TOP limit rest-of-sql-statement"
*/

querySqlString.Insert(GetAfterSelectInsertPo(querySqlString), " top " + limit.);
}



看上面这段代码大家都知道这种分页方式性能有多么差 下面我把我自己实现方言类代码贴出来



Code
public mySqlServer2000Dialect : MsSql2000Dialect
{
private GetFromIndex(SqlString querySqlString)
{
subselect = querySqlString.GetSubselectString.;
fromIndex = querySqlString.IndexOfCaseInsensitive(subselect);
(fromIndex -1)
{
fromIndex = querySqlString..ToLowerInvariant.IndexOf(subselect.ToLowerInvariant);
}
fromIndex;
}


private RemoveSortOrderDirection( 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".
Regex.Replace(sortExpression.Trim, @"(\)|\s)(?i:asc|desc)$", "$1").Trim;
}

public override bool SupportsLimitOff
{
get
{
true;
}
}

public override NHibernate.SqlCommand.SqlString GetLimitString(SqlString querySqlString, off, limit)
{
(off 0)
{
base.GetLimitString(querySqlString, off, limit);
}

SqlString myQuery = SqlString(RemoveSortOrderDirection(querySqlString..ToLower));

orderIndex = myQuery.LastIndexOfCaseInsensitive(" order by ");

(orderIndex <= 0)
{
throw NotSupportedException("must specy 'order by' statement to support limit operation with off in SqlServer2000");
}

orderBy = myQuery.Sub(orderIndex)..Trim;

sortExpressions = myQuery.Sub(orderIndex)..Trim.Sub(9).Split(',');

fromIndex = GetFromIndex(myQuery);

SqlString from = myQuery.Sub(fromIndex, orderIndex - fromIndex).Trim;

fromAsName = from..Sub(from..IndexOf(' ', 5)).Trim;

SqlString select = myQuery.Sub(0, fromIndex);

PageSize = limit - off;

SqlStringBuilder result = SqlStringBuilder;

result.Add("declare @indextable table(id identity(1,1),nid ) rowcount " + limit.);

result.Add(" insert o @indextable(nid) select");

result.Add(" " + RemoveSortOrderDirection(sortExpressions[0]).Replace(fromAsName,"t") + " " + from..Replace(fromAsName,"t") + " " + orderBy.Replace(fromAsName,"t") + " desc ");

result.Add(" " + select. + " " + from. + ",@indextable a where " + RemoveSortOrderDirection(sortExpressions[0]) + " = a.nid and a.[id] between " + (off + 1). + " and " + limit.);

result.ToSqlString;
}
}
这个类继承自原 NHibernate mssql2000方言类 重写了 SupportsLimitOff 属性 返回 true 让 NHibernate 支持 off

重写了 GetLimitString 思路方法 使用 内存变量表方式进行数据分页 前提是 hql 语句里必须带有主键排序字段 主键必须是 自动增长

下面介绍如何使用这个类来替换 NHibernate 下原mssql2000方言:

　　　　在NHibernate配置文件里找到 <property name="dialect">NHibernate.Dialect.MsSql2000Dialect</property>

　　　　替换成 <property name="dialect">myMsSql2000Dialect类命名空间.myMsSql2000Dialect</property>

　　　 哪位高人有更好实现方式话 别忘了共享给我下