One of the things we learned in the previous chapters about session is that it opens an ADO.NET connection to the database when it needs to connect to database for the first time. But when is this connection closed? That is what connection release mode defines. There are two connection release modes, described as follows:

In the coming chapters, we will talk more about NHibernate transactions where it would be clearer for you to understand where exactly the second connection release mode fits in the grand scheme of things. For now, remember that there are two supported connection release modes.

Connection release mode is configured on session factory. There are three configuration options available, two corresponding to the two modes discussed previously and a third and default option of auto that uses AfterTransaction as the connection release mode. It is recommended to use the default setting.

Dialect refers to the type of database, for example, SQL Server 2008, Oracle, MySQL, and so on. NHibernate needs to know which type of database your application interacts with. Declaring dialect serves two purposes. One, NHibernate knows which platform dependent features to enable or disable based on underlying database technology. Second, NHibernate is able to generate correct set of SQL statements for a given database technology. Though SQL is a language understood by most databases, there are subtle differences between implementations of SQL by each database. NHibernate offers you one way to interact with database no matter which database you are connecting to. NHibernate is able to do this because it knows how to generate correct SQL based on dialect information you provided during configuration. Every dialect that NHibernate supports is implemented as a class that inherits from NHibenate.Dialect.Dialect. For instance, dialect for SQL Server 2012 is implemented in the class NHibernate.Dialect.Sql2012Dialect.

Dialect is configured as one of the properties by calling the SetProperty method on configuration object as can be seen in the following code snippet:

var config=newConfiguration();
config.SetProperty(Environment.Dialect,
typeof(Sql2012Dialect).AssemblyQualifiedName)

FNH has made dialect declaration slightly easier by aggregating related database technologies under one helper class. This helper class has static properties on it for specific databases. For instance, all MS SQL Server versions are available under helper class FluentNHibernate.Cfg.Db.MsSqlConfiguration. This class has static properties to then choose versions 2000, 2005, 2008 R2, and so on. of MS SQL Server. To declare dialect, you can use method Database() that is available on the FluentConfiguration class. Following is how you can configure MS SQL Server 2012 database:

Fluently.Configure()
.Database(MsSqlConfiguration.MsSql2012
.ConnectionString(c=> c.FromConnectionStringWithKey("EmployeeBenefits")))

For every supported database, NHibernate has a dialect defined. Following table lists the major supported dialects along with information about which database the dialect refers to, and any other important information you should know of:

Every database technology is different and so are the libraries that allow you to connect to and interact with databases. A database driver is client library that lets you connect to database, send SQL commands, and receive results of executing the SQL commands on database server. While most databases have one preferred driver library available, there are cases where a particular database technology has more than one driver library in use. Moreover, some database technologies support more than one protocol of interacting with database server, for instance, SQL Server supports a native protocol along with OLE DB and ODBC protocols. So, you need to tell NHibernate which database driver you want to use in order to connect to database. Similar to dialect, NHibernate implements a class for each driver that it supports. A class implementing a driver support inherits from NHibernate.Driver.DriverBase.

Driver configuration is exactly the same as dialect configuration. SQL Server 2012 uses SQL client driver whose implementation is in class SqlClientDriver. Following is how the driver configuration will be done:

varconfig=newNHibernate.Cfg.Configuration()
.SetProperty(Environment.ConnectionDriver,
        typeof(SqlClientDriver).AssemblyQualifiedName)

If dialect is known then it is easier to make an assumption about the default driver that can be used. NHibernate does exactly that. For every supported dialect, NHibernate sets the default driver so that you do not have to worry about setting it up yourself. You only use the preceding code when you know that you want to use a specific driver. In case of any MS SQL Server version, NHibernate uses SqlClientDriver. Finding out default driver that FNH assumes for different database technologies is something I will leave to curious readers to explore.

We covered mappings in Chapter 3, Let's tell NHibernate about our database, and we know why mappings exist. Mappings would be of no use if NHibernate does not know about mappings that exist in our application. Hence, mappings are an important configuration option. As with other options, you can configure mappings through XML, programmatically, or fluently. But even within each method, you have got multiple ways of configuring mappings.

If you remember from Chapter 3, Let's Tell NHibernate About Our Database, there are three types of mappings, namely XML mappings, mappings by code, and fluent mappings. XML configuration can only map XML mappings. Programmatic configuration is able to map both mapping by code and XML mapping. Fluent configuration is capable of mapping all three types of mappings. Moreover, there are multiple ways of configuring a particular mapping configuration. For instance, you can configure individual mapping files/classes one by one or you can ask configuration system to scan an assembly and configure all the mapping files/classes found in that assembly. In this chapter we are only going to look at a representative examples but do explore other options as well.

config.AddFile("Mappings/Xml/Community.hbm.xml");

config.AddAssembly("Persistence");

varmodelMapper=newModelMapper();
modelMapper.AddMapping<EmployeeMappings>();
modelMapper.AddMapping<AddressMappings>();
modelMapper.AddMapping<BenefitMappings>();
modelMapper.AddMapping<LeaveMappings>();
modelMapper.AddMapping<SkillsEnhancementAllowanceMappings>();
modelMapper.AddMapping<SeasonTicketLoanMappings>();
modelMapper.AddMapping<CommunityMappings>();
config.AddMapping(modelMapper.CompileMappingForAllExplicitlyAddedEntities());

var modelMapper=new ModelMapper();
modelMapper.AddMappings(typeof(EmployeeMappings).Assembly.GetTypes());
config.AddMapping(modelMapper.CompileMappingForAllExplicitlyAddedEntities());

var config = Fluently.Configure()
.Mappings(mapper=>
{
  mapper.FluentMappings.Add<EmployeeMappings>();
  mapper.FluentMappings.Add<CommunityMappings>();
  mapper.FluentMappings.Add<BenefitMappings>();
  mapper.FluentMappings.Add<LeaveMappings>();
  mapper.FluentMappings.Add<SkillsEnhancementAllowanceMappings>();
  mapper.FluentMappings.Add<SeasonTicketLoanMappings>();
  mapper.FluentMappings.Add<AddressMappings>();
})

var config = Fluently.Configure()
.Mappings(mapper=>
{
  mapper.FluentMappings.AddFromAssemblyOf<EmployeeMappings>();
});

Think of the connection string as address of your database instance. NHibernate needs to know connection string in order to connect to the database successfully. Connection strings are written in different formats for different database technologies but in a nutshell, it is just a string. Similar to dialect and driver, connection string is configured as a property. We have already looked at how connection string is configured so I will try to keep myself brief here by not repeating code examples.

If you have declared connection string in application configuration file of your application under the connectionStrings node, then you can also use the name of the connection string to configure NHibernate programmatically, as shown next:

var config = Fluently.Configure()
.Database(MsSqlConfiguration
.MsSql2012
.ConnectionString(c => c.FromConnectionStringWithKey("EmployeeBenefits")));

If you are not sure how to write connection string for your database, then check out www.connectionstrings.com. This website has collection of all possible connection string formats for all databases that exist.

NHibernate can cache the entities it loaded from database into memory or in a second level cache, such as ASP.NET cache. There are three types of caches available–first level cache, second level cache, and query cache.

First level cache is held within session object and managed by session for you. This is turned on by default and you cannot turn it off. All entities that a session loads can be kept in cache till the session is open. If application requests the same entity again, NHibernate returns it from first level cache.

Second level cache has a wider scope than one session. Entities added to second level cache can be accessed by any session. Use of second level cache needs a thorough thinking and design because of the complex nature of this caching strategy. Though caching enhances performance, use of second level cache should be considered a last resort after you have investigated every other area of your data access layer to improve performance. Moreover, it is only master or lookup data that should be cached because it changes less frequently.

If your application runs a query with same inputs multiple times then you can turn on query caching for that particular query. For the first time, results for the query will be fetched from database but subsequent executions of that query would return from cache. If any entity in the result set of that query changes, then that result set in invalidated and next time the query hits the database.

There is more to caching than this. NHibernate lets you declare different read/write strategies for entities that can be cached. This complicates the system at runtime but knowledge of this is very useful when you are trying to optimize the performance of data access layer using caching. We would cover caching in more detail in Chapter 11, A Whirlwind Tour of Other NHibernate Features where some of the occasionally used features are explained.

Every time you need to interact with a database, you need to get hold of a session object. Technically speaking, you can use a single session object for all your database interactions. However, that would lead to subtle defects since multiple threads would be accessing same session object which is not built to be thread safe. You might also have potential data inconsistency issues. Such usage of session is strongly discouraged. On the other hand, you can open a new session every time you need to interact with database. However, this would be too chatty and would have negative impact on performance. Moreover, you would not be able to use database transaction properly to maintain the integrity of data. What you need is something in the middle of these two options. NHibernate offers session context which can be used for precise control over when new sessions are opened and existing ones are closed. Depending on the runtime characteristics of your application, you can choose the right session context implementation. Following session contexts are available:

We are not going to cover these in detail right now. In one of the upcoming chapters, we will see how the preceding session context implementations manage session for you and which one to use in which situation.

As we will see in Chapter 5, Let's Store Some Data into the Database, NHibernate sends SQL statements to database server in batches to reduce the number of roundtrips to database. NHibernate will use its own heuristics to determine the right batch size. You can override that setting if needed, by setting the batch_size configuration option. Following is how batch size can be set using loquacious configuration:

var config = new Configuration();
config.DataBaseIntegration(db =>
{
  db.BatchSize = 20;
})

Command timeout setting is used to define time after which a SQL command execution should time out. Execution of a SQL command blocks an application thread. If SQL command execution takes a long time then the application thread keeps blocked thus starving other requests. This results in long wait queue of requests to be served leading to performance issues. An optimal value of command timeout helps alleviate such issues. Following code snippet shows how to override default command timeout setting using loquacious configuration:

var config = new Configuration();
config.DataBaseIntegration(db =>
{
  db.Timeout = 5;
})

Note that the timeout value is in seconds.

If you want to log to console those SQL statements that NHibernate sends to database, you can turn on the show_sql setting by setting its value to true. We have used this setting in Chapter 3, Let's Tell NHibernate About Our Database. I usually leave it on in the configuration used for unit tests. This makes it easy to investigate failing tests. While show_sql is helpful in seeing what SQL is actually generated behind the scenes, the formatting of the SQL is not great. If you want properly formatted SQL then another configuration option named format_sql can be used in conjunction. format_sql takes Boolean value and when set to true would format the generated SQL by inserting tab spaces and new lines to make it more readable. Following code snippet shows how to set both these properties using loquacious configuration:

var config = new Configuration();
config.DataBaseIntegration(db =>
{
  db.LogSqlInConsole = true;
  db.LogFormattedSql = true;
})

We have covered most important and most commonly used configuration options. There are some more options available that can be useful in rare situations. Feel free to explore those on your own. Before we close this section, let's have a look at both programmatic and fluent configuration with all the previous options being configured.

Following is how programmatic configuration would look when all the previously discussed configuration options are specified. You should be familiar with most of this code by now:

var config = new NHibernate.Cfg.Configuration()
         .SetProperty(Environment.ReleaseConnections, "on_close")
         .SetProperty(Environment.Dialect, typeof(SQLiteDialect).AssemblyQualifiedName)
         .SetProperty(Environment.ConnectionDriver, typeof(SQLite20Driver).AssemblyQualifiedName)
         .SetProperty(Environment.ConnectionString, "data source=:memory:")
         .SetProperty(Environment.ShowSql, "true")
         .SetProperty(Environment.UseQueryCache, "true")
         .SetProperty(Environment.CurrentSessionContextClass, typeof(ThreadLocalSessionContext).AssemblyQualifiedName)
         .SetProperty(Environment.BatchSize, "20")
         .SetProperty(Environment.CommandTimeout,"30");
var modelMapper = new ModelMapper();
modelMapper.AddMappings(typeof(EmployeeMappings).Assembly.GetTypes());
config.AddMapping(modelMapper.CompileMappingForAllExplicitlyAddedEntities());
var sessionFactory = config.BuildSessionFactory();
session = sessionFactory.OpenSession();
new SchemaExport(config).Execute(true,true,false,session.Connection,Console.Out);

And following is how the same configuration would look when done fluently:

var config = Fluently.Configure()
             .Database(MsSqlConfiguration.MsSql2012
                      .ConnectionString(c=> c.FromConnectionStringWithKey("EmployeeBenefits"))
                      .ShowSql()
             .AdoNetBatchSize(50))
             .CurrentSessionContext<ThreadLocalSessionContext>()
      .Mappings(mapper=>
                 {
                   mapper.FluentMappings.AddFromAssemblyOf<EmployeeMappings>();
                 })
             .Cache(cacheBuilder=>
                {
                  cacheBuilder.UseQueryCache();
                  cacheBuilder.UseSecondLevelCache();
                  cacheBuilder.ProviderClass<HashtableCacheProvider>();
                })
             .ExposeConfiguration(cfg=>
               {
                 configuration = cfg;
                 cfg.SetProperty(Environment.CommandTimeout, "30");
               });
var sessionFactory = config.BuildSessionFactory();
session = sessionFactory.OpenSession();
new SchemaExport(configuration)
         .Execute(true,true,false,session.Connection,Console.Out);

Note that I have configured to use HashTableCacheProvider as cache provider. This is a default in-memory cache provider that NHibernate offers out of the box. This is not recommended for production use or for situations where you are putting huge amount of data in cache. The most common use of this cache provider is during unit tests. Also note, the use of ThreadLocalSessionContext a session context provider. This is again provided as an example but right session context must be chosen based on runtime characteristics of your application, for example, Web versus non-web, WCF, and so on.