14Jan

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!

Related articles:

,

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

, , , ,

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.

, ,

TOP