I’ve been working to get SQL CE to work with our application lately. This is a great step towards a more painless desktop deployment model for us.

It turned out to be a multi step process, and first out was discovering how to make a SQL CE file.

Creating the SQL Server CE .sdf file

There are two alternatives as I see it:

1. Create an empty database with ie SQL Enterprise Manager, and copy it for new deployment
2. Create it programmatically at runtime.

We already have a solution folder and a setup for sql-script templates, so we went with #1.

Using NHibernate hbm2ddl NAnt task for the schema

NHibernate has built in support for SQL Server CE, and what you have to do is the following:

1. Set the connection.connection_string property.
2. Set the connection.driver_class property.
3. Set the dialect property.

Our current NAnt build file has had support for SQL Express 2005 & SQL Developer Edition 2005, and our schema target have been doing a great job at creating the proper schema. The hbm2ddl task has been called with the following attributes:

<hbm2ddl
   connectionstring="${sql.nhibernate.connection}"
   droponly="false"
   exportonly="true">
   <assemblies>
      <include name="${directory.build}\ISY.Domain.dll"></include>
      <include name="${directory.build}\ISY.Infrastructure.dll"></include>
      <include name="${directory.build}\ISY.Repository.dll"></include>
   </assemblies>
</hbm2ddl>

So I go ahead and set the connectionstring attribute with the correct settings. And it fails miserably:

NHibernate.HibernateException: An error has occurred while establishing a connection to the server.  When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 – Error Locating Server/Instance Specified) —> System.Data.SqlClient.SqlException: An error has occurred while establishing a connection to the server.  When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 – Error Locating Server/Instance Specified)

Ok, that’s not good. It seems it cannot connect to the server. Checking the ins and outs of the my sql.nhibernate.connection property, I find that it should be pointing to the right file. After some investigation I find out that I miss #2 & #3; setting the correct dialect and driver_class. The hbm2ddl task had default values for dialect and driver_class which works great with both our previously two supported databases. So, I set them both like this:

<hbm2ddl
  connectionstring="${sql.nhibernate.connection}"
  dialect="${sql.dialect}"
  connectiondriverclass="${sql.driver}"
  droponly="false"
  exportonly="true">
  <assemblies>
    <include name="${directory.build}\ISY.Domain.dll"></include>
    <include name="${directory.build}\ISY.Infrastructure.dll"></include>
    <include name="${directory.build}\ISY.Repository.dll"></include>
  </assemblies>
</hbm2ddl>

Where:

<property name="sql.driver" value="NHibernate.Driver.SqlServerCeDriver" />
<property name="sql.dialect" value="NHibernate.Dialect.MsSqlCeDialect"/>

And we’re there with the hbm2ddl.

PS! Don’t forget to add the SQL CE dll into your folder with NAnt, or else it can’t find the right driver with the above settings (an easy to understand error message will tell you).

Populate the database with default data

After the schema is in place, we populate some tables with default system data from sql script files. For this we use the NAntContrib sql task like this:

<sql
  connstring="${sql.connectionstring}"
  delimiter="GO"
  delimstyle="Line"
  source="${target}"
  transaction="${sql.usetransactions}">
</sql>

First, the sql.connectionstring needs to reflect that I am talking to a SQL CE database, and ConnectionStrings.com comes to the rescue once more.

The first obstacle when running the first sql script file is this error:

Error while executing SQL statement.
    There was an error parsing the query. [Token line number,Token line offset,,Token in error,,]

Yes, very enlightening indeed! (Note to self: remember good error messages!). In my search for answers, this seems to be “the be all, know it all” error message.

Finally, it dawned upon me; SQL CE doesn’t support stored procs. And for that reason, why would it understand a script which practically could be a stored proc? Luckily, all our default data is inserted with “ordinary” statements, and the sql task does have an attribute called batch which defaults to true. With SQL CE you need to set it to false and you’re good to go with this setup (where the sql.batch property depends on which database is being used):

<sql
  connstring="${sql.connectionstring}"
  delimiter="GO"
  delimstyle="Line"
  source="${target}"
  batch="${sql.batch}"
  transaction="${sql.usetransactions}">
</sql>

Conclusion and further work

All in all, the effort to investigate and include the SQL CE to our array of supported databases is well worth. It will make a one off deployment issues a lot easier with no extra installation, and it is a champ when it comes to our database unit testing and integration testing.

What’s still missing is a script which populates our database with test data. The existing script is not easily split into single statements. We’ll dig into it and I’ll explain it all in a later post!