Do you have a reference for the changes in syntax for 2012? Also, what are your thoughts on how this relates to this pull request? https://github.com/nhibernate/nhibernate-core/pull/40
/Oskar 2011/12/31 CSharper <[email protected]>: > Hi, > > I experimented a little with upcoming SQL Server 2012 and found that > there are some existing red unit tests for SQL Server in HQL > functions. I've copied two function definitions from > SybaseASE15Dialect to SQL 2005 dialect (I don't know if it would work > already in SQL Server 2000) and did a quick test implementation for > the new recommended syntax for using pagination in SQL Server 2012 > like > SELECT xxx FROM yyy > ORDER BY zzz OFFSET @p1 ROWS FETCH NEXT @p2 ROWS ONLY > It works, the pagination unit tests are all green (the others too). > > But why should we change that paging behaviour? > > The new syntax is less intrusive for the SQL string, it still works > when executing a native SQL query containing a common table > expression. If the original select is > WITH q AS (aaa) > SELECT xxx FROM yyy > the paging in SQL Server 2005 dialect fails with an exception because > the sql string does not start with a select. The new paging syntax > should work without any problems for such cases. > > How to continue with this? Are there opinions about it? Is somebody > interested in applying a patch and introducing an SQL Server 2012 > dialect already? > > The changes are as follows: > > MsSql2005Dialect.cs > @@ -3,10 +3,11 @@ using System.Collections.Generic; > using System.Data; > using NHibernate.Driver; > using NHibernate.Mapping; > using NHibernate.SqlCommand; > using NHibernate.Util; > +using NHibernate.Dialect.Function; > > namespace NHibernate.Dialect > { > public class MsSql2005Dialect : MsSql2000Dialect > { > @@ -34,10 +35,17 @@ namespace NHibernate.Dialect > { > base.RegisterKeywords(); > RegisterKeyword("xml"); > } > > + protected override void RegisterFunctions() > + { > + base.RegisterFunctions(); > + RegisterFunction("extract", new > SQLFunctionTemplate(NHibernateUtil.Int32, "datepart(?1, ?3)")); > + RegisterFunction("bit_length", new > SQLFunctionTemplate(NHibernateUtil.Int32, "datalength(?1) * 8")); > + } > + > public override SqlString GetLimitString(SqlString queryString, > SqlString offset, SqlString limit) > { > var result = new SqlStringBuilder(); > > if (offset == null) > @@ -168,11 +176,11 @@ namespace NHibernate.Dialect > /// Indicates whether the string fragment contains matching > parenthesis > /// </summary> > /// <param name="statement"> the statement to evaluate</param> > /// <returns>true if the statment contains no parenthesis or an > equal number of > /// opening and closing parenthesis;otherwise false </returns> > - private static bool HasMatchingParens(IEnumerable<char> > statement) > + protected static bool HasMatchingParens(IEnumerable<char> > statement) > { > //unmatched paren count > int unmatchedParen = 0; > > //increment the counts based in the opening and > closing parens in > the statement > > > MsSql2012Dialect.cs: > new file mode 100644 > @@ -0,0 +1,38 @@ > +using System.Data; > +using NHibernate.Cfg; > +using NHibernate.Dialect.Function; > +using NHibernate.Driver; > +using NHibernate.SqlCommand; > + > +namespace NHibernate.Dialect > +{ > + public class MsSql2012Dialect : MsSql2008Dialect > + { > + public override SqlString GetLimitString(SqlString > queryString, SqlString offset, SqlString limit) > + { > + var result = new SqlStringBuilder(queryString); > + > + int orderIndex = queryString.LastIndexOfCaseInsensitive(" > order by "); > + > + //don't use the order index if it is contained within a > larger statement(assuming > + //a statement with non matching parenthesis is part of a > larger block) > + if (orderIndex < 0 || ! > HasMatchingParens(queryString.Substring(orderIndex).ToString())) > + { > + // Use order by first column if no explicit ordering > is provided > + result.Add( " ORDER BY ") > + .Add("1"); > + } > + > + result.Add(" OFFSET ") > + .Add(offset ?? new SqlString("0")) > + .Add(" ROWS"); > + > + if (limit != null) > + { > + result.Add(" FETCH FIRST ").Add(limit).Add(" ROWS > ONLY"); > + } > + > + return result.ToSqlString(); > + } > + } > +} > \ No newline at end of file > > > NHibernate.csproj: > @@ -132,10 +132,11 @@ > <Compile Include="Connection\ConnectionProviderFactory.cs" /> > <Compile Include="Connection\DriverConnectionProvider.cs" /> > <Compile Include="Connection\IConnectionProvider.cs" /> > <Compile Include="Connection\UserSuppliedConnectionProvider.cs" / >> > <Compile Include="Criterion\IEnhancedProjection.cs" /> > + <Compile Include="Dialect\MsSql2012Dialect.cs" /> > <Compile Include="Dialect\DB2Dialect.cs" /> > <Compile Include="Dialect\Dialect.cs" /> > <Compile Include="Dialect\FirebirdDialect.cs" /> > <Compile Include="Dialect\Function\AvgQueryFunctionInfo.cs" /> > <Compile Include="Dialect\Function\CountQueryFunctionInfo.cs" /> > > >
