nHibernate: Great screencast series

Stepen Bohlen is running a great screencast series called Summer of NHibernate .  So far he has 6 sessions posted.  If you are new to NHibernate it is a great way to get up to speed quickly. 


Latest ActiveRecord Codesmith Template

I have made some changes to the Codesmith template that I created for ActiveRecord.  One of the major changes made was for nullable database fields to use the c# nullable type (e.g. int?, DateTime?).

 Download: ActiveRecord.zip (13.15 kb)


ActiveRecord, ObjectDataSource, and GridView Custom Paging

  • Posted by justin on February 26, 2008
  • 1 comments


Updated 4/25/2008: Add sample source code

Problem: How to efficiently implement paging in GridView using an ObjectDataSource

For small amounts of data the built-in paging for the GridView worked fairly well and takes practically no work to do it. However, once you get to a decent amount of data, it becomes incredible slow and provide for a horrible user experience. The problem is that basically the GridView and ObjectDataSource are returning all of the records each time from the database and only showing the ones for the current page. This is a very inefficient use of bandwidth.

Solution:

To get around this problem, you can implement paging with the ObjectDataSource. When you implement paging with the ObjectDataSource, it automatically passes in the values for the startIndex and maximumRows to your select statement and needs to have a SelectCount method defined so that it can get the total number.

Sounds really easy right? Once you figure out how to get the RowCount for the query it is. For simple queries you can use the CountQuery class in ActiveRecord. However for more complicated queries that use a DetachedCriteria, there is currently no overload for CountQuery that take a DetachedCriteria. Hopefully this will be in a future release since there was a patch submitted on March 5th for this feature.

Luckily both NHibernate and ActiveRecord are open source so I was able to look thru their code and tests. In ActiveRecord it is well structure with a directory in their ActiveRecord Framework project called Queries.

In my research it became clear that a Projection query was what I needed but I still was not sure

So I finally found a ScalarProjectionQuery class that took a DetachCriteria. Below is an example call to the method. query is a DetachedCriteria

ScalarProjectionQuery<WorkOrder, int> proj = new ScalarProjectionQuery<WorkOrder, int>(Projections.RowCount(), query);

Int count = proj.Execute();

To implement the actual paging for the ObjectDataSource , I used the ActiveRecord SlicedFindAll method for my model along with the Sql 2005 Dialect.

Gotcha:

With turning on paging for the ObjectDataSource is that it automatically passes the startIndex and maximumRows parameters into your defined Select method as the last 2 parameters and if you use the designer to configure the ObjectDataSource, it adds those parameters to the parameter collection defined in the html code. This in turn causes the startIndex and maximumRows to be passed twice into the SelectMethod .

Sample Code:

ObjectDataSource Definition:

<asp:ObjectDataSource ID="ObjectDataSource1" runat="server" SelectMethod="FindUserBlogs"
        TypeName="MyClassThatMyFindMethodIsIn" OldValuesParameterFormatString="original_{0}"
        OnSelecting="ObjectDataSource1_Selecting" EnableViewState="False" EnablePaging="True"
        SelectCountMethod=" FindUserBlogsCount">
        <SelectParameters>
            <asp:Parameter Name="userID" Type="Int32" />
        </SelectParameters>
    </asp:ObjectDataSource>

ActiveRecord Method Definition:

public static Blog[]  FindUserBlogs (int userID, int startRowIndex, int maximumRows) {
     // use SlicedFindAll to start at a specific row and limit the results }

public static int FindUserBlogsCount (int userID) {
    //query is a DetachCriteria    
     ScalarProjectionQuery<Blog, int> proj =
             new ScalarProjectionQuery<Blog, int>(Projections.RowCount(), query);
      return proj.Execute();
}

Gotcha with gridview custom paging and disabled viewstate

I ran into a problem with a GridView with custom paging throwing a arithmetic overflow error when I click on the Last pager button. It turns out for whatever reason, Microsoft stores the GridView's total page count in viewstate instead of the control state like the rest of the GridView properties. Luckily it is really easy to work around this issue. All you need to do is change the Last pager button's command argument in the code-behind to the GridView.PageCount property and the error goes away.


NHibernate, ActiveRecord and SQL2005

Recently I needed to do paging for a GridView using an ObjectDataSource and ActiveRecord. I wanted to use the SQL2k5 ROW_NUMBER function that I blogged about in this post to do real paging in sql but everything I had seen on using NHibernate with SQl2k5 said to use the SQL2k dialect. There was no indication that a SQL2k5 dialect even existed. However, during a search for another NHibernate question, I found a SQL2k5 dialect that someone had written to do exactly what I wanted. The code was available off the NHibernate contrib. site so I figured it worked and I could integrate it into my solution. As I was looking to figure out how dialects are integrated, I found that the dialect is already part of the NHibernate 1.2 release but none of the documents are updated to reflect this. To use the dialect, you just need to set <add key="hibernate.dialect" value="NHibernate.Dialect.MsSql2005Dialect"/> in your ActiveRecord configuration.


nHibernate Expression Notes

  • Posted by justin on February 14, 2007
  • 0 comments

Expression.Sql: used to add sql to the where clause. To do stuff like MyTableName.Column you would use {alias}.Column and nHibernate would replace {alias} with the actual alias that it used for the MyTableName table.

Expression.Ge: This is used for greater than or equal to.