NHibernate2.0.1下實(shí)現(xiàn)SQL2000真分頁

最近用NHibernate做個(gè)項(xiàng)目 由于數(shù)據(jù)庫用的是mssql2000 NHibernate 對(duì)mssql2000的分頁查詢支持的不是很好 于是自己動(dòng)手實(shí)現(xiàn)一個(gè)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 limit rest-of-sql-statement"
    */
    return querySqlString.Insert(GetAfterSelectInsertPoint(querySqlString), " top " + limit.ToString());
    }
    看上面這段代碼大家都知道這種分頁方式的性能有多么的差 下面我把我自己實(shí)現(xiàn)的方言類代碼貼出來
    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();
    }
    }
    這個(gè)類繼承自原 NHibernate 的 mssql2000方言類 重寫了 SupportsLimitOffset 屬性 返回 true 讓 NHibernate 支持 offset
    重寫了 GetLimitString 方法 使用 內(nèi)存變量表的方式進(jìn)行數(shù)據(jù)分頁 前提是 hql 語句里必須帶有主鍵排序字段 主鍵必須是 自動(dòng)增長(zhǎng)
    下面介紹如何使用這個(gè)類來替換 NHibernate 下的原mssql2000方言：
    在NHibernate配置文件里的找到 NHibernate.Dialect.MsSql2000Dialect
    替換成 myMsSql2000Dialect類的命名空間.myMsSql2000Dialect