NHibernate

ActiveRecord ICriterion Search Example (c#)

Filed in ActiveRecord | C# | NHibernate Leave a comment

In this post we are going to search using ICriterion to filter and sort the results. The only pre-requisite is that you properly configure your nHibernate environment, and like all my other code samples, know c#.

This one is pretty easy, first we are going to set the required references:

using NHibernate;
using NHibernate.Criterion;

Then we are going to define the ICriterion array. In this example, were are going to search for contacts that:

Belonging to the logged on owner (OwnerID variable).
LastName starts with some string entered by the user (LastNameValue variable)
Were added after a specific date chosen by the user (StartDate variable)

For the LastName like search, nHibernate gives us some options in the MatchMode enum: Anywhere, End, Exact, Start. In the snippet below, you will notice I went with Start.

Finally, we are going to sort the results by LastName, then FirstName.

ICriterion[] query = {
Expression.Eq("OwnerID", OwnerID),
Expression.Like("LastName",LastNameValue,MatchMode.Start),
Expression.Ge("CreationDate",StartDate) };

Order[] order = {
Order.Asc("LastName"),
Order.Asc("FirstName")};

Contact[] results = contact.FindAll(order, query);

That’s it!

Performing an ActiveRecord SimpleQuery search

ICriterion, Search

23Dec

How to implement paging with c#, mvc, nHibernate, ICriterion

Filed in ASP.NET MVC | C# | NHibernate Leave a comment

There is a really cool jQuery tool (http://tablesorter.com/docs/index.html) that uses PFM Technology (Pure Freakin’ Magic) to make your html table just magically page and sort. It works great with MVC and nHibernate, but I wanted to get fancy and make my own. Turned out to be surprisingly easy. Here’s what I did:

Preparing the Controller

1. Create you search criteria:

ICriterion[] query = { Expression.Eq(FieldName, FieldValue) };

2. Get the total record count matching the criteria without actually pulling back all the records. I throw the count into a ViewData variable for later processing on the page:

int count = ActiveRecordMediator<listing>.Count(query);
ViewData["count"] = count;

3. Execute a SlicedFindAll search to only get the subset instead of the entire recordset.

Contact[] pagedContacts = Contact.SlicedFindAll((currentPageIndex) * Rows), Rows, query);

The currentPageIndex variable is passed in from the page. At this point, the controller is ready!

Preparing the View

1. First we will create links to the individual pages. This is also where we use the ViewData["count"] value:

int TotalListings = int.Parse(ViewData["count"].ToString());
int RecordsPerPage = 15;    // <-- this can be defined anywhere, I wired it for this demo
int TotalPages = (TotalListings + RecordsPerPage - 1) / RecordsPerPage;

StringBuilder sb = new StringBuilder();
if (TotalPages > 1)
{
for (int i = 0; i < TotalPages; i++)
{
sb.Append("<a href='/your-url-path/" + i.ToString() + "'>" + (i + 1).ToString() + "</a>&nbsp;&nbsp;");
}

Response.Write(sb.ToString());
}

2. Loop through your recordset, adding your rows.

3. Add an optional footer:

if (TotalPages > 1)
{
Response.Write(sb.ToString());
}

That’s it! This could easily be extended to include next, previous, etc. links, but for demo purposes this works well.

ActiveRecord, MVC, NHibernate

06Dec

How to execute a Query in c# using nHibernate, ICriterion, HQL

Filed in ActiveRecord | C# | NHibernate Leave a comment

Below are two samples on how to create a query with ActiveRecord and ICriterion.

Example 1: Select all contacts from Texas

public static Contact[] FindByState(string State)
{
ICriterion[] query = { Expression.Eq("State", State) };
Order[] sort = { Order.Asc("LastName") }

return Contact.FindAll(sort, query);
}

The Controller’s syntax would be this:

Contact[] contacts = Contact.FindByState("TX");

Example 2: SQL style query

ArrayList stateslist = new ArrayList();
stateslist.Add("TX");
stateslist.Add("VA");

The next part is where all the magic happens. The “SELECT” part is done for us automatically, so we simply add our WHERE clause. In the example you’ll notice the parameter format (:variablename). The in parameter is wrapped with parentheses:

string hql = String.Format("FROM Contact c where State in (:states)");
SimpleQuery&lt;Contact&gt; q = new SimpleQuery<Contact>(hql);
q.SetParameterList("states", stateslist);
q.SetQueryRange(25);
Contact[] result = q.Execute();

If we wanted to execute a LIKE search:

string hql = String.Format("FROM Contact c LastName like :lastname");
...
q.SetParameter("lastname", stringvalue + "%");
...

Here is some further reading on HQL:

http://www.castleproject.org/ActiveRecord/documentation/v1rc1/usersguide/hql.html
https://www.hibernate.org/hib_docs/nhibernate/1.2/reference/en/html_single/#d0e8963

HQL, ICriterion, SetParameter, SetParameterList, SimpleQuery

06Dec

How To Create a Class with ActiveRecord

Filed in ActiveRecord | C# | NHibernate Leave a comment

So in this installment, we’re going to look at what goes into creating a class using ActiveRecord. One of the cool parts of ActiveRecord is the fact that you don’t have to waste time with CRUD statements because they are baked in for you! So here is the anatomy of a class:

1. The using statements:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using NHibernate;
using NHibernate.Criterion;
using Castle.ActiveRecord;

I created some CodeSmith templates which I use to generate all my classes, the above using statements are my defaults. Next is the class name, the required attributes, and the properties:

[ActiveRecord("<em>TABLE NAME</em>")]
public class Contact : ActiveRecordBase&lt;Contact&gt;
{
public Contact()
{
}

[PrimaryKey(PrimaryKeyType.Native, "ID")]
public int ID { get; set; }

[Property]
public string FirstName { get; set; }

[Property]
public string LastName { get; set; }

[Property]
public string State { get; set; }

[Property]
public DateTime CreationDate { get; set; }

[Property(Column="CreatedByUser"]
public int OwnerUserID { get; set; }

The [Property] attribute indicates that field exists in the table. If you don’t include [Property] then that field will not be included in CRUD statements. Also, if you want the property name to be different from the database column name, simply set Column=ColumnName like the “CreatedByUser” sample above.

ActiveRecord, ICriterion, NHibernate

06Dec

Using nHibernate, Castle ActiveRecord, and ASP.NET MVC Together

Filed in ActiveRecord | ASP.NET MVC | C# | Environment Setup | NHibernate 3 Comments

So this is my first post, with that this blog’s objective: to post all the code shortcuts, how-to’s, tips-tricks, etc. so they are handy whenever I need them. It always seems that at some point I need to do something that I did 5 projects ago, and forgot to save that snippet and can’t quite find it!

For this entry, we’re going to configure a new web application in the following sequence:

1. Create ASP.NET MVC 1.0 project in Visual Studio 2008

2. Copy and reference the required nHibernate dlls

3. Configure web.config

4. Initialize global.asax

Pre-requisites: If you haven’t already installed MVC, go here: http://www.asp.net/mvc/download/.

Create ASP.NET MVC 1.0 project in Visual Studio 2008

1. Open Visual Studio 2008

2. Click “File” –> “New Project” and select “ASP.NET MVC Web Application” under the c# menu

Select your location, project name, etc.

Copy and reference the required nHibernate files and dlls

1. Download the latest ActiveRecord release here: http://bit.ly/8HdvIU

For detailed documentation on ActiveRecord, visit the Castle Project site here: http://bit.ly/8pzxyb and here: http://bit.ly/8jzycs.

2. Unzip, copy and then reference the following assemblies into your bin folder:

Castle.ActiveRecord.dll
Castle.Core.dll
Castle.Components.Validator.dll
Castle.DynamicProxy.dll
NHibernate.dll
Iesi.Collections.dll
log4net.dll

Configure web .config

1. Add the following elements to configSections:

<section name="hibernate-configuration" type="NHibernate.Cfg.ConfigurationSectionHandler, NHibernate"/>
<section name="log4net" type="log4net.Config.Log4NetConfigurationSectionHandler,log4net" />
<section name="activeRecord" type="Castle.ActiveRecord.Framework.Config.ActiveRecordSectionHandler, Castle.ActiveRecord"/>

2. Add the following elements right after /configSections:

SQLServer 2005/2008

<hibernate-configuration xmlns="urn:nhibernate-configuration-2.2">
<session-factory>
<property name="dialect">NHibernate.Dialect.MsSql2005Dialect</property>
<property name="connection.provider">NHibernate.Connection.DriverConnectionProvider</property>
<property name="connection.connection_string">Server=localhost;initial catalog=<em>YOURDB</em>;uid=<em>YOURUSERNAME</em>;pwd=<em>YOURPASSWORD</em></property>
<property name="proxyfactory.factory_class">NHibernate.ByteCode.LinFu.ProxyFactoryFactory, NHibernate.ByteCode.LinFu</property>
<mapping assembly="<em>YOURASSEMBLY</em>"/>
</session-factory>
</hibernate-configuration>

<activeRecord isWeb="true">
<config>
<add key="hibernate.dialect" value="NHibernate.Dialect.MsSql2005Dialect"/>
<add key="hibernate.connection.provider" value="NHibernate.Connection.DriverConnectionProvider"/>
<add key="hibernate.connection.connection_string" value="Server=localhost;initial catalog=<em>YOURDB</em>;uid=<em>YOURUSERNAME</em>;pwd=<em>YOURPASSWORD</em>"/>
</config>
</activeRecord>

MySQL

<hibernate-configuration xmlns="urn:nhibernate-configuration-2.2">
<session-factory>
<property name="dialect">NHibernate.Dialect.MySQLDialect</property>
<property name="connection.provider">NHibernate.Connection.DriverConnectionProvider</property>
<property name="connection.driver_class">NHibernate.Driver.MySqlDataDriver</property>
<property name="connection.connection_string">Database=<em>YOURDB</em>;Server=localhost;User Id=<em>YOURUSERNAME</em>;Password=<em>YOURPASSWORD</em></property>
<property name="show_sql">true</property>
<property name="hbm2ddl.keywords">none</property>
<property name="proxyfactory.factory_class">NHibernate.ByteCode.LinFu.ProxyFactoryFactory, NHibernate.ByteCode.LinFu</property>
<mapping assembly="<em>YOURASSEMBLY</em>" />
</session-factory>
</hibernate-configuration>

<activeRecord isWeb="true">
<config>
<add key="hibernate.connection.driver class" value="NHibernate.Driver.MySqlDataDriver" />
<add key="hibernate.dialect" value="NHibernate.Dialect.MySQLDialect" />
<add key="hibernate.connection.provider" value="NHibernate.Connection.DriverConnectionProvider" />
<add key="hibernate.connection.connection_string" value="Database=<em>YOURDB</em>;Server=localhost;User Id=<em>YOURUSERNAME</em>;Password=<em>YOURPASSWORD</em>" />
</config>
</activeRecord>

For logging, you need to add the following elements, regardless of database type:

<log4net>
<appender name="NHibernateFileLog" type="log4net.Appender.RollingFileAppender">
<file value="Logs/nhibernate.txt" />
<appendToFile value="true" />
<rollingStyle value="Size" />
<maxSizeRollBackups value="10" />
<maximumFileSize value="100KB" />
<staticLogFileName value="true" />
<layout type="log4net.Layout.PatternLayout">
<conversionPattern value="%d{HH:mm:ss.fff} [%t] %-5p %c - %m%n" />
</layout>
</appender>
<appender name="GeneralLog" type="log4net.Appender.RollingFileAppender">
<file value="Logs/general.txt" />
<appendToFile value="true" />
<maximumFileSize value="100KB" />
<rollingStyle value="Size" />
<maxSizeRollBackups value="5" />
<layout type="log4net.Layout.PatternLayout">
<conversionPattern value="%d{HH:mm:ss.fff} [%t] %-5p %c - %m%n" />
</layout>
</appender>
<appender name="DataLog" type="log4net.Appender.RollingFileAppender">
<file value="Logs/data.txt" />
<appendToFile value="true" />
<maximumFileSize value="100KB" />
<rollingStyle value="Size" />
<maxSizeRollBackups value="5" />
<layout type="log4net.Layout.PatternLayout">
<conversionPattern value="%d{HH:mm:ss.fff} [%t] %-5p %c - %m%n" />
</layout>
</appender>
<!-- levels: DEBUG, INFO, WARN, ERROR, FATAL -->
<root>
<level value="DEBUG" />
<appender-ref ref="GeneralLog" />
</root>
<logger name="NHibernate" additivity="false">
<level value="DEBUG" />
<appender-ref ref="NHibernateFileLog" />
</logger>
<logger name="Pushable.Data" additivity="false">
<level value="DEBUG" />
<appender-ref ref="DataLog" />
</logger>
</log4net>

Initialize Global.asax

1. Open global.asax in code view

2. Add the following using statements:

using Castle.ActiveRecord;
using Castle.ActiveRecord.Framework;

3. Update Application_Start so it looks like this:

protected void Application_Start()
{
RegisterRoutes(RouteTable.Routes);

IConfigurationSource source = System.Configuration.ConfigurationManager.GetSection("activeRecord") as Castle.ActiveRecord.Framework.IConfigurationSource;
Assembly asm1 = typeof(<em>ONE OF YOUR ASSEMBLIES, I.E. CONTACT</em>).Assembly;

ActiveRecordStarter.Initialize(new Assembly[] { asm1 }, source);
}

That’s it! So what we’ve done here is configure our environment, in upcoming posts, we’ll dig into actual code and do some fun things with jQuery. Also, I’ll be revisiting this post with some screenshots in the near future. Finally, if you know of better/more efficient ways to do some of these same tasks, I’m all ears!

.NET, ASP.NET MVC, Castle ActiveRecord, Visual Studio 2008