The title of this article is so long, I briefly considered using an acronym. Unfortunately, FNHAMASPDNMVC3CW doesn’t communicate much except to the nerdiest of nerds.

This is the third article in a series on using Fluent NHibernate with auto mappings. The first one demonstrated its basic functionality in a console application, and the second one showed how to convert the first one into an ASP.NET MVC 3 application. This article will cover how to use Castle Windsor for dependency injection, which is important for unit testing. I won’t include any unit tests in this article, but will lay the foundation that will make unit testing possible for my next article.

Much of the code below is directly from the Castle Windsor documentation, which really is exceptional.

Here’s what the completed project will look like:

Once again, create a new ASP.NET MVC 3 web application in Microsoft Visual Studio 2010 and use the empty project template. This time, call your project CastleFluentNHibernateMvc3. Next, right click on the project in the Solution Explorer and choose Manage NuGet Packages… Search the online gallery for Fluent NHibernate and install it. We also need Castle Windsor, so search for that and install it too.

EDIT 12/18/2012: You can get the source code on GitHub.

Once you’ve installed the packages, copy the Controllers, Models and Repositories folders from our previous project to our new project. Also, copy the Views/Home folder to our new project. Make sure you change the namespace in each file accordingly.

The first thing we need is a Windsor controller factory. The Windsor controller factory overrides two methods of the default MVC controller factory and is central to how Castle Windsor resolves controller dependencies. Create this class in a new Windsor directory at the root level of the project:

namespace CastleFluentNHibernateMvc3.Windsor
{
    public class WindsorControllerFactory : DefaultControllerFactory
    {
        private readonly IKernel kernel;

        public WindsorControllerFactory( IKernel kernel )
        {
            this.kernel = kernel;
        }

        public override void ReleaseController( IController controller )
        {
            kernel.ReleaseComponent( controller );
        }

        protected override IController GetControllerInstance( RequestContext requestContext, Type controllerType )
        {
            if (controllerType == null)
            {
                throw new HttpException( 404, string.Format( "The controller for path '{0}' could not be found.", requestContext.HttpContext.Request.Path ) );
            }

            return (IController)kernel.Resolve( controllerType );
        }
    }
}

Next, we need to tell MVC to use the Windsor controller factory instead of the default one. To do that, we’ll add a couple of methods to the MvcApplication class, which resides in the Global.asax.cs file at the root of the project:

namespace CastleFluentNHibernateMvc3
{
    public class MvcApplication : System.Web.HttpApplication
    {
        private static IWindsorContainer container;

        private static void BootstrapContainer()
        {
            container = new WindsorContainer()
                .Install( FromAssembly.This() );

            var controllerFactory = new WindsorControllerFactory( container.Kernel );

            ControllerBuilder.Current.SetControllerFactory( controllerFactory );
        }

        // RegisterGlobalFilters and RegisterRoutes methods go here...

        protected void Application_Start()
        {
            AreaRegistration.RegisterAllAreas();

            RegisterGlobalFilters( GlobalFilters.Filters );
            RegisterRoutes( RouteTable.Routes );

            BootstrapContainer();
        }

        protected void Application_End()
        {
            container.Dispose();
        }
    }
}

Now, we need to tell Windsor about our controllers, including where they reside and how to configure them. In other words, we’ll “register” our controllers with the Windsor “container.” We do this using an “installer.” Create this class in your Windsor directory:

namespace CastleFluentNHibernateMvc3.Windsor
{
    public class ControllersInstaller : IWindsorInstaller
    {
        public void Install( IWindsorContainer container, IConfigurationStore store )
        {
            container.Register( Classes.FromThisAssembly()
                                .BasedOn<IController>()
                                .LifestyleTransient() );
        }
    }
}

Next, create a new class named PersistenceFacility in your Windsor directory, and move the CreateSessionFactory, CreateDbConfig, CreateMappings and UpdateSchema methods of our NHibernateSessionPerRequest class there:

namespace CastleFluentNHibernateMvc3.Windsor
{
    public class PersistenceFacility : AbstractFacility
    {
        protected override void Init()
        {
            Kernel.Register(
                Component.For<ISessionFactory>()
                    .UsingFactoryMethod( CreateSessionFactory ),
                Component.For<ISession>()
                    .UsingFactoryMethod( OpenSession )
                    .LifestylePerWebRequest() );
        }

        private static ISession OpenSession( IKernel kernel )
        {
            return kernel.Resolve<ISessionFactory>().OpenSession();
        }

        // CreateSessionFactory, CreateDbConfig, CreateMappings and UpdateSchema methods go here...
    }
}

In the Init method, we’re telling Castle Windsor what to do when it encounters a dependency on ISessionFactory or ISession. If we ask for an ISession, it will resolve the dependency using the OpenSession method. Notice that this method needs an ISessionFactory, which Castle Windsor will resolve by calling CreateSessionFactory.

We’re also telling Castle Windsor that an ISession instance should be bound to a web request. Since we aren’t specifying a lifestyle for ISessionFactory, the default value of singleton will be used. In other words, only one session factory will be instantiated during the lifetime of our app, and it will hang around in memory until our app dies. This is basically the same thing we did before with our custom HTTP module, except the dependencies will only be resolved when needed. We can verify this by placing breakpoints in the OpenSession and CreateSessionFactory methods and running the app.

Now, we need to register our persistence facility with the Windsor container. Again, we do this using an installer. Create this class in your Windsor directory:

namespace CastleFluentNHibernateMvc3.Windsor
{
    public class PersistenceInstaller : IWindsorInstaller
    {
        public void Install( IWindsorContainer container, IConfigurationStore store )
        {
            container.AddFacility<PersistenceFacility>();
        }
    }
}

Next, we need to add a few more methods to our repository in order to manage NHibernate transactions: BeginTransaction, Commit and Rollback. Previously, we created a custom HTTP module to take care of these operations at the beginning and end of each web request. This time, we’ll manage each transaction ourselves, gaining more control and performance in the process. The GetAll, Get, SaveOrUpdateAll and SaveOrUpdate methods will stay the same:

namespace CastleFluentNHibernateMvc3.Repositories
{
    public interface IRepository<T>
    {
        void BeginTransaction();
        void Commit();
        void Rollback();
        // GetAll, Get, SaveOrUpdateAll and SaveOrUpdate signatures go here...
    }
}

namespace CastleFluentNHibernateMvc3.Repositories
{
    public class Repository<T> : IRepository<T>
    {
        private readonly ISession session;

        public Repository( ISession session )
        {
            this.session = session;
        }

        public void BeginTransaction()
        {
            session.BeginTransaction();
        }

        public void Commit()
        {
            session.Transaction.Commit();
        }

        public void Rollback()
        {
            session.Transaction.Rollback();
        }

        // GetAll, Get, SaveOrUpdateAll and SaveOrUpdate implementations go here...
    }
}

Notice that the repository constructor has been modified to accept an argument of type ISession. This is called dependency injection, and its benefits will become more clear when we use it in our home controller.

But first, we need to register our repositories with the Windsor container. How do we do that? Right! (You did say “use an installer,” right? Good.) Create this class in your Windsor directory:

namespace CastleFluentNHibernateMvc3.Windsor
{
    public class RepositoriesInstaller : IWindsorInstaller
    {
        public void Install( IWindsorContainer container, IConfigurationStore store )
        {
            container.Register( Classes.FromThisAssembly()
                                .Where( Component.IsInSameNamespaceAs<Repository<Store>>() )
                                .WithService.DefaultInterfaces()
                                .LifestyleTransient() );
        }
    }
}

Now, modify the home controller constructor as follows:

// Constructs our home controller
public HomeController( IRepository<Store> storeRepository )
{
    this.storeRepository = storeRepository;
}

The benefit of using dependency injection here is that we no longer have a dependency on a concrete repository. The home controller doesn’t care what the repository does with our data, as long as it implements IRepository. This way, we can change our repository methods without changing our controller actions. Later, when we write unit tests for our controller actions, we’ll be able to create an instance of the home controller using a fake repository so that our tests don’t touch our data.

Because we are no longer using a custom HTTP module to manage NHibernate transactions, we need to call the BeginTransaction method of our repository whenever we get data from or save data to the database. We also need to call the Commit method to flush the NHibernate session, and call the Rollback method if any errors occur:

// Gets all the stores from our database and returns a view that displays them
public ActionResult Index()
{
    storeRepository.BeginTransaction();

    var stores = storeRepository.GetAll();

    if ( stores == null || !stores.Any() )
    {
        storeRepository.Rollback();

        return View( "Error" );
    }
    
    try
    {
        storeRepository.Commit();

        return View( stores.ToList() );
    }
    catch
    {
        storeRepository.Rollback();

        return View( "Error" );
    }
}

// Gets and modifies a single store from our database
public ActionResult Test()
{
    storeRepository.BeginTransaction();

    var barginBasin = storeRepository.Get( s => s.Name == "Bargin Basin" ).SingleOrDefault();

    if (barginBasin == null)
    {
        storeRepository.Rollback();

        return View( "Error" );
    }

    try
    {
        barginBasin.Name = "Bargain Basin";
    
        storeRepository.Commit();

        return RedirectToAction( "Index" );
    }
    catch
    {
        storeRepository.Rollback();

        return View( "Error" );
    }
}

// Adds sample data to our database
public ActionResult Seed()
{
    // Create a couple of Stores each with some Products and Employees
    var barginBasin = new Store { Name = "Bargin Basin" };
    var superMart = new Store { Name = "SuperMart" };

    var potatoes = new Product { Name = "Potatoes", Price = 3.60 };
    var fish = new Product { Name = "Fish", Price = 4.49 };
    var milk = new Product { Name = "Milk", Price = 0.79 };
    var bread = new Product { Name = "Bread", Price = 1.29 };
    var cheese = new Product { Name = "Cheese", Price = 2.10 };
    var waffles = new Product { Name = "Waffles", Price = 2.41 };

    var daisy = new Employee { FirstName = "Daisy", LastName = "Harrison" };
    var jack = new Employee { FirstName = "Jack", LastName = "Torrance" };
    var sue = new Employee { FirstName = "Sue", LastName = "Walkters" };
    var bill = new Employee { FirstName = "Bill", LastName = "Taft" };
    var joan = new Employee { FirstName = "Joan", LastName = "Pope" };

    // Add Products to the Stores
    // The Store-Product relationship is many-to-many
    AddProductsToStore( barginBasin, potatoes, fish, milk, bread, cheese );
    AddProductsToStore( superMart, bread, cheese, waffles );

    // Add Employees to the Stores
    // The Store-Employee relationship is one-to-many
    AddEmployeesToStore( barginBasin, daisy, jack, sue );
    AddEmployeesToStore( superMart, bill, joan );
    
    storeRepository.BeginTransaction();

    try
    {
        storeRepository.SaveOrUpdateAll( barginBasin, superMart );
    
        storeRepository.Commit();

        return RedirectToAction( "Index" );
    }
    catch
    {
        storeRepository.Rollback();

        return View( "Error" );
    }
}

You can now run your application. If you get any errors, make sure you have an empty database and a valid connection string. In that case, see my previous article for instructions on setting that up. If all you see is the word “Index,” it’s because you don’t have any data in your database yet. In your browser, navigate to the Seed action of the home controller. You should then see “Index” at the top followed by a list of stores, products and employees.

Next time, we’ll look as using NUnit and Moq for unit testing. Surprisingly, it’s relatively easy! As always, let me know if you have any difficulties. Good luck!

In yesterday’s post “CodeFluent Entities Performance Comparison”, we saw that CodeFluent Entities performed better than ADO.NET Entity Framework and NHibernate, but a little slower than LINQ to SQL and dapper-dot-net because of extra-features it provides (gets values by column name and not by an ordinal making it easier to maintain, uses default values so developers don’t have to hassle with DbNulls).

In this post we’ll see how to lower these loading times using a few techniques.

Step #1: Removing extra features

If all you need is performance and don’t care about all those extra features provided by the tool by default: disable them! You’ll sure gain milliseconds without:

• concurrency management (select your entity and set “Concurrency Mode” to “None” in the property grid),
• property tracking (select your entity and set “Default Property Tracking Modes” to “None” in the property grid),
• entity tracking (select your entity and set “Tacking Modes” to “None” in the advanced view of the property grid),
• ensure the collection type is set to “ListCollection” or “Collection” and not “List” as you’ll waste time each time the Contains method is called.

Doing this, and without writing custom code yet, you should manage to gain a few precious milliseconds.

Step #2: Squeeze strongly typed collections

In the “CodeFluent Entities Performance Comparison” post, the code used in the test was as follows:

Stopwatch sw = new Stopwatch();
sw.Start();

for (int ct = 0; ct < 100; ct++)
{
    OrderCollection orders = OrderCollection.LoadAll();
    foreach (Order o in orders)
    {
         int i = o.OrderID;
    }
}

sw.Stop();
Console.WriteLine("Elapsed milliseconds: " + sw.ElapsedMilliseconds);

From lines in the database an OrderCollection was built, but you might not even need an OrderCollection object. If so, you can use the PageData<MethodName> method which returns a IDataReader object. Which lets you do something like this:

Stopwatch sw = new Stopwatch();
sw.Start();

for (int ct = 0; ct < 100; ct++)
{
    foreach (Order o in LoadOrders())
    {
        int i = o.OrderID;
    }
}

sw.Stop();
Console.WriteLine("Elapsed milliseconds: " + sw.ElapsedMilliseconds);

As you can see we replaced our OrderCollection.LoadAll() call by a call to a custom method named LoadOrders. Here’s the method:

static IEnumerable<Order> LoadOrders()
{
    using (IDataReader reader = OrderCollection.PageDataLoadAll(null))
    {
        while (reader.Read())
        {
            Order o = new Order();
            o.RaisePropertyChangedEvents = false;
            ((ICodeFluentEntity)o).ReadRecord(reader);
            yield return o;
        }
        CodeFluentPersistence.CompleteCommand(Constants.NorthwindStoreName);
    }
}

In the code above we’ve:

• used CodeFluent Entities to generate the PageDataLoadAll and the ReadRecord methods which allowed us to load and map results to .NET objects,
• yet we didn’t build and OrderCollection but returned an IEnumerable<Order>,
• extra-tip: we disabled RaisePropertyChangedEvents before filling in our object to gain some more valuable milliseconds

Thanks to this last tip, it allowed to gain around 140ms which is pretty good given the reasonable amount of coding (14 lines).

Step #3: Do it yourself!

The last code chunk which makes our time consumption superior than in yesterday’s handmade test using a SqlCommand and doing the mapping yourself is the generated ReadRecord method. This method provides two features:

• it retrieves values by column name instead of using an ordinal to make the code more readable and robust (changing the column order won’t break your code),
• it uses default values for value types so you don’t have to struggle with DbNulls.

Consequently, if all you need is performance and don’t care for those two features, just write your own ReadRecord method such as this one:

public void CustomReadRecord(IDataReader reader)
{
    if (reader == null)
        throw new System.ArgumentNullException("reader");

    if (!reader.IsDBNull(0)) _orderID = reader.GetInt32(0);
    if (!reader.IsDBNull(1)) _orderDate = reader.GetDateTime(1);
    if (!reader.IsDBNull(2)) _requiredDate = reader.GetDateTime(2);
    if (!reader.IsDBNull(3)) _shippedDate = reader.GetDateTime(3);
    if (!reader.IsDBNull(4)) _freight = reader.GetDecimal(4);
    if (!reader.IsDBNull(5)) _shipName = reader.GetString(5);
    if (!reader.IsDBNull(6)) _shipAddress = reader.GetString(6);
    if (!reader.IsDBNull(7)) _shipCity = reader.GetString(7);
    if (!reader.IsDBNull(8)) _shipRegion = reader.GetString(8);
    if (!reader.IsDBNull(9)) _shipPostalCode = reader.GetString(9);
    if (!reader.IsDBNull(10)) _shipCountry = reader.GetString(10);
    if (!reader.IsDBNull(11)) _customerCustomerID = reader.GetString(11);
    if (!reader.IsDBNull(12)) _employeeEmployeeID = reader.GetInt32(12);
    if (!reader.IsDBNull(13)) _shipViaShipperID = reader.GetInt32(13);
}

Using this CustomReadRecord instead of the standard ReadRecord, I obtained even better times than the handmade data access test (possibly because CodeFluent Entities executes stored procedures whereas I was using SqlCommand in my handmade test?).

Hope this helps,

Carl Anderson

We often get questions on how CodeFluent Entities performs compared to other tools as LINQ to SQL, ADO.NET Entity Framework, or NHibernate.

We compared those technologies by writing simple console applications doing the same queries on the same Northwind database and running on the same machine. Here’s in pseudo code what each console application is doing:

Start timer
Iterate 100 times
   Load all orders from database
   Loop through loaded orders
End of iteration
Stop timer
Print elapsed time (ms)

You’ll find the solution used here so you can run/edit the tests yourself: PerformanceTests.zip

Technologies compared are:

• CodeFluent Entities (Build 663 available here),
• ADO.NET Entity Framework 4.0 (edmx version 2.0),
• LINQ to SQL (.NET 4.0),
• NHibernate 3.3,
• Dapper-dot-net,
• Handmade code using a SqlCommand, SqlDataReader (named SqlClient in the table below).

Tests use:

• Visual Studio 2010 SP1,
• .NET 4.0 (C#),
• SQL Server 2008,
• the Northwind database.

Results (in ms, sorted by performance results):

Averages:

• SqlClient: 519ms
• Dapper: 619ms
• LINQ to SQL: 762ms
• CodeFluent Entities: 946ms
• NHibernate: 2151ms
• ADO.NET Entity Framework: 2351ms

Without surprises SqlClient and Dapper performed faster as all they do is execute the query and map results to objects. Right after is LINQ to SQL losing a little time by handling relationships and so does CodeFluent Entities by handling in addition property changed events and default values (spares the developer the DbNull check for value types).

NHibernate and ADO.NET Entity Framework score last to this performance test for different reasons. NHibernate because it uses reflection to build a proxy of your classes to map database values to it. Entity Framework because we’re rebuilding a context at each iteration to ensure we’re loading all our orders from database (I tried doing a refresh on all orders but that’s even worse).

In this test we used CodeFluent Entities’ default values but in a following post we’ll see how to use CodeFluent Entities to perform as Dapper and LINQ to SQL as in particular scenarios, performance is more important than features.

Regards,

Carl Anderson

Recently I was tasked with converting an existing application’s NHibernate mappings to Fluent NHibernate. The original mappings were done as XML-based hbm files. Bottom line is that there was nothing wrong with the hbm mappings, and one is able to mix-and-match hbm mappings with fluent mappings, but for the purposes of the project we decided is preferable to settle on one standard.

One particular mapping proved to be a bit challenging to convert. The hbm XML file contained the following map:

<bag name="Features" access="field.camelcase-underscore" cascade="all" lazy="true" table="WebsiteFeatures">
 <cache usage="read-write"/>
 <key column="WebsiteID"/>
 <element column="Feature" type="Model.Types.FeatureType, Model" not-null="true"/>
</bag>

After creating the fluent map, I wanted to check that I was a close to the original hbm mapping as possible. To do this is actually quite simple. Just configure your Fluent configuration to export its mappings to file, like so:

return Fluently.Configure()
  .Database(MsSqlConfiguration.MsSql2008
  .ConnectionString(x => x.FromConnectionStringWithKey("DefaultConnection"))
  .Mappings(x => x.FluentMappings.AddFromAssemblyOf ().ExportTo(@"C:\BloggingTemp\"))
  .BuildConfiguration();

The XML mappings are then exported to your selected directory and you’re able to compare them against your original hbm maps.

My client has been insisting that using “uint” in code and in MS SQL is a good idea… well, so be it.

MS SQL however does not have a native “unit” datatype, therefore NHibernate does (quite reasonebly) not offer such a mapping.

There are two ways around it: Using a user type, or patch Hibernate’s MSSQL dialect class.

Via IUserType

This solution is described here and works really well. It stores an C# UInt32 in a MSSQL INT column.

Downside: Large unsigned numbers appear as negative value in the database, and in the mapping you must specify that type.

Via Patched Dialect

The other solution involves a patched SQL Dialect and also a patch SQL Driver:

public class ExtendedMsSql2008Dialect : MsSql2008Dialect
{
  public ExtendedMsSql2008Dialect()
  {
    // Tell NH that we can handle the ADO DbTypes
    RegisterColumnType(DbType.UInt16, "INT");
    RegisterColumnType(DbType.UInt32, "BIGINT");
    RegisterColumnType(DbType.UInt64, "DECIMAL(28)");
  }
}

public class ExtendedSql2008ClientDriver : Sql2008ClientDriver
{
  protected override void InitializeParameter(System.Data.IDbDataParameter dbParam, string name, NHibernate.SqlTypes.SqlType sqlType)
  {
    if (sqlType == SqlTypeFactory.UInt16) sqlType = SqlTypeFactory.Int32;
    if (sqlType == SqlTypeFactory.UInt32) sqlType = SqlTypeFactory.Int64;
    if (sqlType == SqlTypeFactory.UInt64) sqlType = SqlTypeFactory.Decimal;
    base.InitializeParameter(dbParam, name, sqlType);
  }
}

And of course these two classes must be added to the NHibernate config file:

<property name="connection.driver_class">MyLib.ExtendedSql2008ClientDriver, MyLib</property>
<property name="dialect">MyLib.ExtendedMsSql2008Dialect, MyLib</property>

This solution makes it easier for inexperienced users, but it comes at a price: The SQL data type is wider than the C# datatype, therefore wasting some space/performance.

Conclusion

Pretty obvious – NHibernate is quite flexible, and there is no perfect solution

Max Result
.SetFirstResult(5)
Is Null
Restrictions.IsNull("Name")
Is Not Null
Restrictions.IsNotNull("Name")
Order
.AddOrder(Order.Asc("Id"))
.AddOrder(Order.Desc("Id"))
Count
.SetProjection(Projections.Count("Id"));
Or
.Add(Restrictions.Or(Restrictions.Le("EndDate", endDate),Restrictions.IsNull("EndDate")))

http://nhforge.org/blogs/nhibernate/archive/2010/04/25/first-three-nhibernate-quickstart-tutorials-available.aspx

public void Method(int id)
{const string sql = “exec ProcedureName :id”;

Session.CreateSQLQuery(sql).SetTimeout(600)

.SetInt32(“Id”, id)

.ExecuteUpdate();
}

HQL is the original query language of NHibernate. It likely similar with SQL, but more object-oriented. HQL queries are defined as strings, so are not typed-safe.

Each HQL query is created by calling the CreateQuery method of the ISession interface and passing the HQL string as the parameter.

FROM Clause

Use FROM clause if you want to load a persistent objects into memory.

Example:

String hql = "FROM Employee";
Query query = session.createQuery(hql);
List results = query.list();

If you need to fully qualify a class name in HQL, just specify the package name and class name as follow:

String hql = "FROM com.hibernatebook.criteria.Employee";
Query query = session.createQuery(hql);
List results = query.list();

AS Clause

The AS clause can be used to assign alias to the classes in the HQL queries, specially when we have long queries.

Example:

String hql = "FROM Employee AS E";
Query query = session.createQuery(hql);
List results = query.list();

The AS keyword is optional and you can also specify the alias directly after the class name, as follows:

String hql = "FROM Employee E";
Query query = session.createQuery(hql);
List results = query.list();

SELECT Clause

The SELECT clause provides more control over the result set to the FROM clause. Use SELECT when you need to get some properties of objects, not whole objects.

Example:

String hql = "SELECT E.firstName FROM Employee E";
Query query = session.createQuery(hql);
List results = query.list();

Note that firstName here is a property of Employee object, not a column in the Employee table of Database.

WHERE Clause

If you want to get objects with some conditions, use WHERE clause as the example below:

String hql = "FROM Employee E WHERE E.id = 10";
Query query = session.createQuery(hql);
List results = query.list();

This example is about getting object whose id is equals 10

ORDER BY Clause

Use ORDER BY clause to sort your result with ascending (ASC) or descending (DESC)

String hql = "FROM Employee E WHERE E.id > 10 ORDER BY E.salary DESC";
Query query = session.createQuery(hql);
List results = query.list();

If you wanted to sort by more than one property, you would just add the additional properties to the end of the order by clause, separated by commas as follows:

String hql = "FROM Employee E WHERE E.id > 10 " +
             "ORDER BY E.firstName DESC, E.salary DESC ";
Query query = session.createQuery(hql);
List results = query.list();

GROUP BY Clause

GROUP BY clause help us to group the results base on the specified properties of objects.

String hql = "SELECT SUM(E.salary), E.firtName FROM Employee E " +
             "GROUP BY E.firstName";
Query query = session.createQuery(hql);
List results = query.list();

UPDATE Clause

This clause is used to update properties for one or many specified objects.

"WHERE id = :employee_id";
 Query query = session.createQuery(hql); 
query.setParameter("salary", 1000); 
query.setParameter("employee_id", 10); 
int result = query.executeUpdate(); System.out.println("Rows affected: " + result);

String hql = "DELETE FROM Employee " + "WHERE id = :employee_id";
 Query query = session.createQuery(hql); query.setParameter("employee_id", 10);
 int result = query.executeUpdate(); System.out.println("Rows affected: " + result);

String hql = "INSERT INTO Employee(firstName, lastName, salary)" + "SELECT firstName, lastName, salary FROM old_employee"; 
Query query = session.createQuery(hql); 
int result = query.executeUpdate(); 
System.out.println("Rows affected: " + result);

Note: you must call excuteUpdate() method of query interface after INSERT,UPDATE or DELETE objects. 
The return value indicate the number of rows were effected by the INSERT, UPDATE or DELETE command.

String hql = "SELECT count(distinct E.firstName) FROM Employee E"; Query query = session.createQuery(hql); List results = query.list();

String hql = "FROM Employee"; 
Query query = session.createQuery(hql);
 query.setFirstResult(1); 
query.setMaxResults(10); 
List results = query.list();

String hql = "FROM Employee E WHERE E.id = :employee_id";
Query query = session.createQuery(hql);
query.setParameter("employee_id",10);
List results = query.list();

Introduction

One of the common problems experienced when using Fluent NHibernate is how to convert a particular mapping, previously in HBM file format, to a Fluent NHibernate syntax.

In this tutorial example I will model an Entity to Entity-Settings structure. Typically used to stored additional information on the entity when you don’t want to designate a particular column to do the job. So we provide an open structure in the form of a key-value pair table.

In classic NHibernate HBM files you would use a <MAP> element to represent this relationship. However in Fluent NHibernate it’s mapped slightly differently.

Dependencies:

• NHibernate 3.2.400
• FluentNHibernate 1.3.0.717

Tutorial:

This is the database structure we will deal with:

I’m intentionally keeping it simple. We have a Customer table that holds basic customer information and a CustomerSettings table which hold key-value pair string information – in other words it holds whatever we want that fits the structure of a key-value pair. I also want any changes to the Customer settings to be saved when I persist the Customer object.

My classes look as follows:

    public class Customer
    {
        public Customer()
        {
            Settings = new Dictionary();
        }

        public virtual int Id { get; set; }
        public virtual string Name { get; set; }
        public virtual string Surname { get; set; }
        public virtual IDictionary Settings { get; set; }
    }

The mapping is actually really simple but it took forever to figure out how to do it properly. So the classmap looks like so:

    public class CustomerMap : ClassMap
    {
        public CustomerMap()
        {
            Table("Customer");
            Id(x => x.Id).Not.Nullable().GeneratedBy.Identity();
            Map(x => x.Name).Not.Nullable().Length(50);
            Map(x => x.Surname).Not.Nullable().Length(50);

            HasMany(x => x.Settings)
            .Table("CustomerSettings")
            .KeyColumn("CustomerId")
            .AsMap(
                index => index.Column("`Key`").Type(),
                element => element.Column("Value").Type()
            )
            .Cascade.All();
        }
    }

And that’s it! Now all you need to do is a basic NHibernate save, with your customer information and settings and the settings will also save in your cascade:

            DatabaseContext.Initialize();

            var customer = new Customer
                        {
                            Name    = "Joe",
                            Surname = "Blogs"
                        };
            customer.Settings.Add("Age", "30");
            customer.Settings.Add("Country", "ZA");

            using (var session = DatabaseContext.SessionFactory.OpenSession())
            {
                using (var tx = session.BeginTransaction())
                {
                    session.SaveOrUpdate(customer);
                    tx.Commit();
                }
            }

As I said in an earlier post, I’d decided to use NHibernate in a series of programs I’m writing because I preferred the stability and the  maturity of the code, especially since I have to use it against .NET 3.5 and an Oracle database. I know that Oracle updated ODP.NET to work with Entity Framework, but I’ve only seen it working on .NET 4 I’m concerned about using it since it just came out of beta earlier this year. Yeah, I know that this becomes a preference thing, but I get to make that call since I have to be comfortable with what I’m coding and how I’m doing it. If the database were SQLServer anything, I would switch to EF because it is written by Microsoft for Microsoft products (SQLServer, .NET, Visual Studio, etc.) but with the environment I have, I think I’m making the best choice.

The first part of setting it up seems pretty straight forward. I need classes which contain my tables so I can work with POCOs (I even understand what they are these days…) and I need some sort of mapping to link class to database. That way I can create the objects I need and ensure they are both type safe and useful when I need to do CRUD operations or if I need to create code which makes decisions on what it finds in the objects themselves. This is when I also am very thankful for NuGet, because I can

I’ve looked at several books, but they all leave me feeling dumber than when I started. I’ve run across a 6 part tutorial which seems to make it more understandable for me at least. The tutorial is at Geeks with Blogs and can be found here. I worked through the tutorial, all 6 parts, and really started getting it, so many thanks to Bob Palmer for this and for helping start me over the hump. Now I have some ideas of what to code and how to code it. The question then becomes what can I do with it to get the things I need to do done? The first challenge is going to be with the admin code and being able to adjust to the intersection tables and ensure that what we CRUD in the base tables gets persisted so we can get the updates in and out the best way possible. I think this is where a lot of my NHibernate coding is going to be useful. I just have to look at how to wire it all up to a form so I can pull back data to edit, or I can create a record whole cloth.

I think I’m going to need the advanced queries when I get into the actual logic of the program. That proggie has to read information from several tables to determine if this meets the send criteria or no. If so, then send it and delete it. If not, then it may need to persist it long enough to see if it will meet the send criteria for up to 30 minutes after creation. After that, forget it.

In my previous article, I demonstrated how to use Fluent NHibernate with auto mappings. After that, my plan was to move the code into an ASP.NET MVC 3 application. I assumed it would be relatively simple, which goes to show just how little I know about these things. Anyways, hopefully someone else out there will be able to learn from my experience.

Here’s what the completed project will look like:

Create a new ASP.NET MVC 3 web application called FluentNHibernateMvc3 in Microsoft Visual Studio 2010 and use the empty project template. Next, right click on the project in the Solution Explorer and choose Manage NuGet Packages… Search the online gallery for Fluent NHibernate and install it.

EDIT 12/18/2012: You can get the source code on GitHub.

Once you’ve installed Fluent NHibernate, copy the Employee, Product and Store classes from the Entities folder of our previous project to the Models folder of our current project. Make sure you change the namespace in each file accordingly.

Next, create a new controller called HomeController in the Controllers folder. Since my goal is to move the code from our previous project to our new project with as little changes as possible, the HomeController class will look very similar to the Program class with the following exceptions:

1. We’ll move the NHibernate specific methods to a new custom HTTP module.
2. We’ll use a repository to create a layer of abstraction between our main application and our persistence layer.
3. We’ll split the Main method into two actions: the Index action, which will display the data, and the Seed action, which will add sample data to our database. This is because we don’t want to add sample data every time our application runs.

Here’s a rough skeleton of the home controller:

namespace FluentNHibernateMvc3.Controllers
{
    public class HomeController : Controller
    {
        private readonly IRepository<Store> storeRepository;
        
        // Constructs our home controller
        public HomeController() { }
        
        // Gets all the stores from our database and returns a view that displays them
        public ActionResult Index() { }
        
        // Adds sample data to our database
        public ActionResult Seed() { }
        
        // AddProductsToStore and AddEmployeesToStore methods go here...
    }
}

The first thing you should notice is that the home controller has a field called storeRepository, which implements an IRepository of type Store. This needs to be set in the constructor method:

// Constructs our home controller
public HomeController()
{
    storeRepository = new Repository<Store>();
}

We’ll create the Repository class and IRepository interface later.

Next, we’ll move the bulk of the Main method from our previous project to the Seed action here:

// Adds sample data to our database
public ActionResult Seed()
{
    // Create a couple of Stores each with some Products and Employees
    var barginBasin = new Store { Name = "Bargin Basin" };
    var superMart = new Store { Name = "SuperMart" };

    var potatoes = new Product { Name = "Potatoes", Price = 3.60 };
    var fish = new Product { Name = "Fish", Price = 4.49 };
    var milk = new Product { Name = "Milk", Price = 0.79 };
    var bread = new Product { Name = "Bread", Price = 1.29 };
    var cheese = new Product { Name = "Cheese", Price = 2.10 };
    var waffles = new Product { Name = "Waffles", Price = 2.41 };

    var daisy = new Employee { FirstName = "Daisy", LastName = "Harrison" };
    var jack = new Employee { FirstName = "Jack", LastName = "Torrance" };
    var sue = new Employee { FirstName = "Sue", LastName = "Walkters" };
    var bill = new Employee { FirstName = "Bill", LastName = "Taft" };
    var joan = new Employee { FirstName = "Joan", LastName = "Pope" };

    // Add Products to the Stores
    // The Store-Product relationship is many-to-many
    AddProductsToStore( barginBasin, potatoes, fish, milk, bread, cheese );
    AddProductsToStore( superMart, bread, cheese, waffles );

    // Add Employees to the Stores
    // The Store-Employee relationship is one-to-many
    AddEmployeesToStore( barginBasin, daisy, jack, sue );
    AddEmployeesToStore( superMart, bill, joan );

    storeRepository.SaveOrUpdateAll( barginBasin, superMart );

    return RedirectToAction( "Index" );
}

There are a few important changes you should notice:

1. We don’t create a new session factory in the Seed action, or anywhere else in the home controller for that matter.
2. We don’t open a session here.
3. We don’t create or commit a transaction.

These tasks will be handled by our custom HTTP module at the beginning and end of each web request. Separating these tasks allows us to minimize the dependencies in our home controller, which is often referred to as loose coupling. Our controller doesn’t know anything about sessions or transactions – it isn’t even aware of NHibernate!

The Index action is very simple:

// Gets all the stores from our database and returns a view that displays them
public ActionResult Index()
{
    var stores = storeRepository.GetAll();

    return View( stores.ToList() );
}

Here, we call the GetAll method of our repository, cast the result to a list and send the list to the view. The Index view should go in your Views/Home folder and should look like this:

@model List<FluentNHibernateMvc3.Models.Store>

@{
    ViewBag.Title = "Index";
}

<h2>Index</h2>

<ul>
    @foreach ( var store in Model ) {
        <li>@store.Name</li>
        <ul>
            <li>Products:</li>
            <ul>
                @foreach ( var product in store.Products ) {
                    <li>@product.Name</li>
                }
            </ul>
            <li>Staff:</li>
            <ul>
                @foreach ( var employee in store.Staff ) {
                    <li>@employee.FirstName @employee.LastName</li>
                }
            </ul>
        </ul>
    }
</ul>

Now, let’s tackle that repository. Place these files in a new folder called Repositories at the root level of the project. This is where we’ll define what exactly the GetAll and SaveOrUpdateAll methods do that we’re using in our controller actions. First, create the interface:

namespace FluentNHibernateMvc3.Repositories
{
    public interface IRepository<T>
    {
        IQueryable<T> GetAll();
        IQueryable<T> Get( Expression<Func<T, bool>> predicate );
        IEnumerable<T> SaveOrUpdateAll( params T[] entities );
        T SaveOrUpdate( T entity );
    }
}

Then create the implementation:

namespace FluentNHibernateMvc3.Repositories
{
    public class Repository<T> : IRepository<T>
    {
        private readonly ISession session;

        public Repository()
        {
            session = NHibernateSessionPerRequest.GetCurrentSession();
        }

        public IQueryable<T> GetAll()
        {
            return session.Query<T>();
        }

        public IQueryable<T> Get( Expression<Func<T, bool>> predicate )
        {
            return GetAll().Where( predicate );
        }

        public IEnumerable<T> SaveOrUpdateAll( params T[] entities )
        {
            foreach ( var entity in entities )
            {
                session.SaveOrUpdate( entity );
            }

            return entities;
        }

        public T SaveOrUpdate( T entity )
        {
            session.SaveOrUpdate( entity );

            return entity;
        }
    }
}

Speaking of loose coupling, our repository doesn’t know anything about session factories or transactions, but it does have a session field that implements ISession. This field is set by calling the GetCurrentSession method of our custom HTTP module, which we’ll name NHibernateSessionPerRequest.

Here’s an outline of the NHibernateSessionPerRequest class, which you can put in a new Modules folder at the root level of the project:

namespace FluentNHibernateMvc3.Modules
{
    /// 

    /// http://www.bengtbe.com/blog/2009/10/08/nerddinner-with-fluent-nhibernate-part-3-the-infrastructure
    /// 
    public class NHibernateSessionPerRequest : IHttpModule
    {
        private static readonly ISessionFactory sessionFactory;

        // Constructs our HTTP module
        static NHibernateSessionPerRequest() { }
        
        // Initializes the HTTP module
        public void Init( HttpApplication context ) { }

        // Disposes the HTTP module
        public void Dispose() { }

        // Returns the current session
        public static ISession GetCurrentSession() { }

        // Opens the session, begins the transaction, and binds the session
        private static void BeginRequest( object sender, EventArgs e ) { }

        // Unbinds the session, commits the transaction, and closes the session
        private static void EndRequest( object sender, EventArgs e ) { }

        // Returns our session factory
        private static ISessionFactory CreateSessionFactory() { }

        // Returns our database configuration
        private static MsSqlConfiguration CreateDbConfig()

        // Returns our mappings
        private static AutoPersistenceModel CreateMappings()

        // Updates the database schema if there are any changes to the model,
        // or drops and creates it if it doesn't exist
        private static void UpdateSchema( Configuration cfg ) { }
    }
}

Notice that the sessionFactory field, which implements ISessionFactory, is static. Session factories can be expensive to create, especially with a large domain model. So we only want to instantiate one of these during the lifetime of our app, which will hang around in memory until our app dies:

// Constructs our HTTP module
static NHibernateSessionPerRequest()
{
    sessionFactory = CreateSessionFactory();
}

// Returns our session factory
private static ISessionFactory CreateSessionFactory()
{
    return Fluently.Configure()
        .Database( CreateDbConfig )
        .Mappings( m => m.AutoMappings.Add( CreateMappings() ) )
        .ExposeConfiguration( UpdateSchema )
        .CurrentSessionContext<WebSessionContext>()
        .BuildSessionFactory();
}

// Returns our database configuration
private static MsSqlConfiguration CreateDbConfig()
{
    return MsSqlConfiguration
        .MsSql2008
        .ConnectionString( c => c.FromConnectionStringWithKey( "testConn" ) );
}

// Returns our mappings
private static AutoPersistenceModel CreateMappings()
{
    return AutoMap
        .Assembly( System.Reflection.Assembly.GetCallingAssembly() )
        .Where( t => t.Namespace != null && t.Namespace.EndsWith( "Models" ) )
        .Conventions.Setup( c => c.Add( DefaultCascade.SaveUpdate() ) );
}

// Updates the database schema if there are any changes to the model,
// or drops and creates it if it doesn't exist
private static void UpdateSchema( Configuration cfg )
{
    new SchemaUpdate( cfg )
        .Execute( false, true );
}

Most of these methods can be copied to this class from our previous project. There are only a few changes:

1. We’ve replaced the DropCreateSchema method with a new method called UpdateSchema. As the name suggests, this method will only create the database schema if it doesn’t already exist. Otherwise, it will update it from the model.
2. NHibernate needs to be told what type of context it’s running in. We do that by calling the CurrentSessionContext extension method in our fluent configuration. It’s strongly typed, so we give it a type of WebSessionContext.
3. Instead of building a connection string in the CreateDbConfig method, I’m using a connection string from the Web.config file. You can do it either way.
4. The CreateMappings method will create auto mappings for all types in our assembly that have a namespace ending in “Models”. This is preferable over matching the entire namespace, since it will require less changes moving forward.

Here are the remaining methods:

// Initializes the HTTP module
public void Init( HttpApplication context )
{
    context.BeginRequest += BeginRequest;
    context.EndRequest += EndRequest;
}

// Disposes the HTTP module
public void Dispose() { }

// Returns the current session
public static ISession GetCurrentSession()
{
    return sessionFactory.GetCurrentSession();
}

// Opens the session, begins the transaction, and binds the session
private static void BeginRequest( object sender, EventArgs e )
{
    ISession session = sessionFactory.OpenSession();

    session.BeginTransaction();

    CurrentSessionContext.Bind( session );
}

// Unbinds the session, commits the transaction, and closes the session
private static void EndRequest( object sender, EventArgs e )
{
    ISession session = CurrentSessionContext.Unbind( sessionFactory );

    if ( session == null ) return;

    try
    {
        session.Transaction.Commit();
    }
    catch ( Exception )
    {
        session.Transaction.Rollback();
    }
    finally
    {
        session.Close();
        session.Dispose();
    }
}

Previously, we weren’t catching any exceptions when we tried to commit our transaction. Wrapping our call to commit in a try-catch statement gives us a chance to rollback if something goes wrong.

We’re almost done! We still need to register our custom HTTP module in IIS, so that it can receive and process HTTP requests. If you are using IIS 6.0 or IIS 7.0 in classic mode, add this to your Web.config file:

<configuration>
  <system.web>
    <httpModules>
      <add name="NHibernateSessionPerRequest" type="FluentNHibernateMvc3.Modules.NHibernateSessionPerRequest" />
     </httpModules>
  </system.web>
</configuration>

If you are using IIS 7.0 in integrated mode, add this to your Web.config file:

<configuration>
  <system.webServer>
    <modules>
      <add name="NHibernateSessionPerRequest" type="FluentNHibernateMvc3.Modules.NHibernateSessionPerRequest" />
    </modules>
  </system.webServer>
</configuration>

Finally, if you decided not to build a connection string in the CreateDbConfig method, you’ll need to add one under the configuration section of your Web.config file. Here’s mine, which is using SQL Server 2008:

<connectionStrings>
  <add name="testConn" connectionString="Server=testServer;Database=testDB;User ID=testUser;Password=testPass" providerName="System.Data.SqlClient" />
</connectionStrings>

Before running the application, make sure you create an empty database, and replace “testServer”, “testDB”, “testUser” and “testPass” with your actual server, database, username, and password.

When you run the application, you should see this:

Index

Not very exciting, it is? That’s because we haven’t added any data to the database yet! In your browser, navigate to the Seed action of the home controller, i.e. append /Home/Seed to the URL in your browser’s address bar. You should see this:

Index

• Bargin Basin
• Products:
• Potatoes
• Fish
• Milk
• Bread
• Cheese
• Staff:
• Daisy Harrison
• Jack Torrance
• Sue Walkters
• SuperMart
• Products:
• Bread
• Cheese
• Waffles
• Staff:
• Bill Taft
• Joan Pope

Look familiar? It should if you read my previous article.

I’d like to demonstrate one more thing before I sign off. Add the following action to the home controller:

// Gets and modifies a single store from our database
public ActionResult Test()
{
    var barginBasin = storeRepository.Get( s => s.Name == "Bargin Basin" ).SingleOrDefault();

    if ( barginBasin == null )
    {
        return RedirectToAction( "Index" );
    }

    barginBasin.Name = "Bargain Basin";

    return RedirectToAction( "Index" );
}

This action is fairly self-explanatory: It will find the Store record named “Bargin Basin” and rename it to “Bargain Basin.” The cool part is that we don’t have to manually save anything because it’s all taken care of by our repository and HTTP module! Navigate to the Test action of the home controller in your browser and you’ll see what I mean.

That does it for this article. Next time, I’ll cover unit testing and dependency injecting using Castle Windsor.

Hi All recently i got this issue when iam working on my application which is using NHibernate as the OR Mapper, and i seriously work around about this issue and got the tail of this issue.

This issue raised because i get an model object which had lazy load option true and modified the same object and trying to save/update the same object.. this is fine from our side but what accually happening inside the Nhibenate is due to lazy load the model object still running on a Nhibernate session, again when we say save the same object it created another session to save it as current context session is already running to fetch the model object.. so NHibernate doesnt accept an object running on two or more sessions.

Solution: Solution is simple we need to manage or code what ever the word.. try to have a single session always active..
for this we need to change a little coding as shown here.

private static ISession _mySession;

public static ISession mySession
        {
            get {
               
                if (!CurrentSessionContext.HasBind(SessionFactory))
                    CurrentSessionContext.Bind(SessionFactory.OpenSession());
                
                _mySession = SessionFactory.GetCurrentSession();
                return _mySession;
            }
        }

Here in the above couple of lines of code says to the NHibernate that if any session is aready bind to the Current Context then use the same session and if not create one session and bind the same to the Current Context.

Hope this Helps you….

Happy Coding