Stacking Code

This is post #9 in the Building Neno series. Please click here for a description of the Building Neno project and instructions on how to access the source code for this post.

It's time to build the database. I'm using NHibernate so I can choose any of the relational databases out there. I'm going to go with SQL Server first up because it's well known to me and it will give me a chance to play with the new database project types in Visual Studio 2010.

NHibernate Configuration

I have my NHibernate mapping files which contain a lot of information about what my database should look like. Table names, column names, column types, primary keys and foreign keys. It really makes sense to let NHibernate have first crack at generating the database schema and then modify it from there.

I have to configure my web application for NHibernate at some stage, so I'm going to do that now and then make a temporary web page to create the schema. Without FluentNHibernate, NHibernate is configured through the traditional .config of any application. Something like this...

<configuration>
    <configSections>
        <section name="hibernate-configuration" type="NHibernate.Cfg.ConfigurationSectionHandler, NHibernate" />
    </configSections>
    <connectionStrings>
        <add name="Neno" connectionString="Server=.\SQLEXPRESS;Database=Neno;Integrated Security=True" providerName="System.Data.SqlClient" />
    </connectionStrings>
    <hibernate-configuration xmlns="urn:nhibernate-configuration-2.2">
        <session-factory>
            <property name="cache.default_expiration">60</property>
            <property name="cache.provider_class">NHibernate.Caches.SysCache.SysCacheProvider, NHibernate.Caches.SysCache</property>
            <property name="cache.use_query_cache">true</property>
            <property name="connection.connection_string_name">Neno</property>
            <property name="connection.isolation">ReadCommitted</property>
            <property name="connection.provider">NHibernate.Connection.DriverConnectionProvider</property>
            <property name="dialect">NHibernate.Dialect.MsSql2008Dialect</property>
            <property name="proxyfactory.factory_class">NHibernate.ByteCode.Castle.ProxyFactoryFactory, NHibernate.ByteCode.Castle</property>
            <mapping assembly="StackingCode.Neno.Repositories.NHibernate" />
        </session-factory>
    </hibernate-configuration>
</configuration>

The cache settings are optional, but without a secondary cache I will have a lot more database traffic with my current design. It's usually a good idea to always have a secondary cache enabled. The mapping assembly at the bottom tells NHibernate which assembly to look in for my .hbm.xml mapping files.

Schema Generation

Time to create a web page for my database schema generation via hbm2ddl.

using System;
using System.Web.Mvc;
using NHibernate.Cfg;
using NHibernate.Tool.hbm2ddl;

namespace StackingCode.Neno.WebApplication.Controllers
{
    public class DatabaseController : Controller
    {
        public ActionResult Create()
        {
            try
            {
                var configuration = new Configuration();
                configuration.Configure();
                Response.Write("NHibernate configured<br />");
                var schemaExport = new SchemaExport(configuration);
                schemaExport.Drop(false, true);
                Response.Write("Schema dropped<br />");
                schemaExport.Create(false, true);
                Response.Write("Schema created<br />");
            }
            catch (Exception ex)
            {
                while (ex != null)
                {
                    Response.Write(ex.Message + "<br />");
                    ex = ex.InnerException;
                }
            }

            return new EmptyResult();
        }
    }
}

I've got the Neno database created in SQL Server Express with nothing in it. Time to run the web page and see what happens.

I get this error a lot on new projects. It's a SQL error -- SQL Server doesn't like me using a reserved word for a column name (in this case User). It wouldn't be a problem if NHibernate wrapped column names in square brackets; but it doesn't, so the solution is to manually do it in the mapping file.

<many-to-one name="User" column="[User]" />

By default, if a column isn't specified, NHibernate uses the name of the property. Right, time to try the schema generation again...

Success! A quick look in SQL Server Management Studio confirms all six tables are there (one for the post/tag many-to-many relationship), as well as keys and foreign keys. The data types are correct too, but wrong in length in the case of strings, and NHibernate has made columns NULL wherever it could, which is wrong for my domain model in most cases. I could have put more information in my mapping files to make hbm2ddl more accurate, but this is good enough. I'll manually tweak what I need.

The Database Project

Which brings me to the database project that I'll be using in the Neno solution. It's a SQL Server 2008 Database Project (found under Database, SQL Server, Advanced) and I'll call it StackingCode.Neno.Database. It comes with a handy import wizard to generate SQL source code from my database schema. Now I can tweak to my heart's content and then build/deploy back.

Database done. Neno is getting very close to having the full stack working together.