Sql-based paging

mogadanez · **Posted:** Fri May 27, 2005 8:18 am

Can i do it with nHibernate?

queries like that:

Code:

select IDENTITY(int,1,1) as RowNum, * into #temp_tbl 
from (
    SELECT 
       ....

      
    FROM ....
      
    WHERE .....

    ) as result

select *
    from #temp_tbl 
   Where RowNum >10 and RowNum<20

select count(*) as records_count from #temp_tbl

martijnb · **Joined:** Sat May 14, 2005 8:40 am **Posts:** 130

For PostgreSQL, MySQL, Oracle, DB2: yes
For SQL Server: unfortunately not

mogadanez · **Posted:** Fri May 27, 2005 12:01 pm

Why....?
i already have ready queries....
need apply it to hibernate

pedro · **Joined:** Fri May 13, 2005 2:13 pm **Posts:** 5

Is there any solution for paging in sql server?
Does NHibernate provide some support for cursors?
How do people manage do page through large tables?

I will probably have to write specific sql queries for paging, but i would rather not have to. Would implementing a new dialect allow me to have special new key words?

Any hints would be appreciate.

sergey · **Posted:** Tue Jun 21, 2005 3:58 pm

I just recently integrated into the CVS version a patch that enables some limited form of paging for SQL Server, this will be part of 0.9 release. But it doesn't do real paging, because it only limits the number of results using TOP, and scrolls down to the first result manually.

pedro · **Joined:** Fri May 13, 2005 2:13 pm **Posts:** 5

Thanks Sergey.

I ended up using a cusor to do the paging on the server side.
Unfortunately, I could not really figure out a generic solution but since I only need paging for one specific case, that ought to be enough.

Here is what I use, if it`s useful for anyone:

Code:

string query = @"
SET NOCOUNT ON

  DECLARE mensagens SCROLL CURSOR 
  FOR " + queryStr2 + @"     
  FOR READ ONLY


  OPEN mensagens
  DECLARE @v_counter int
  set @v_counter = 0


  SELECT *
  INTO #temptable
  FROM " + tableName + @"
  WHERE 1=0

  DECLARE @c1 int
  DECLARE @c2 datetime
  DECLARE @c3 varchar(11)
  DECLARE @c4 char(10)
  DECLARE @c5 int
  DECLARE @c6 varchar(200)
  DECLARE @c7 varchar(50)

  FETCH ABSOLUTE "+ (pager.MaxPageSize*(pager.CurrentPageNumber-1) + 1) +@" FROM mensagens INTO @c1,@c2,@c3,@c4,@c5,@c6,@c7

  WHILE (@v_counter < "+ (pager.MaxPageSize-1) + @" AND @@FETCH_STATUS = 0)
  BEGIN
     INSERT #temptable SELECT @c1,@c2,@c3,@c4,@c5,@c6,@c7
     FETCH next FROM mensagens INTO @c1,@c2,@c3,@c4,@c5,@c6,@c7     
     SET @v_counter = @v_counter + 1
  END

  SELECT * FROM #temptable

I have to create a IDbCommand and execute the query, and manually populate my objects (didn't try to use CreateSQLQuery since I read it's not functional yet, is that true?)

As you can see, I could not find out how to avoid specifying the specific column types. Do you know of a more generic way to achieve this?

I also had to manually create a SQL query. Is there any way to get SQL from HQL? I know it might yield more than one sql query, but it shouldn't in my case.

I am also having difficulties in extracting column and table names from class and property names. Couldn't really find the key properties in the Configuration object...

Any comments are welcome :)

DALT · **Posted:** Thu Aug 11, 2005 10:17 am

Hi.
As for the database columns try using this:

Code:

NHibernate.Mapping.PersistentClass clazz = config.GetClassMapping(--classname--);
ICollection columns = clazz.GetProperty(--propertyName--).ColumnCollection;
IEnumerator en = columns.GetEnumerator();
en.MoveNext();
Column col = (Column)en.Current;

Hope it helps