Tag: sqlite

I was planning to put a quick spike together on putting single sign-on, using CardSpace, OpenID and Windows Live, into an application to test it out for a project at work. Maybe it was because of the weekend, but I was quite enthusiastic and added several technologies to the sample which I had never used before. Six hours later, there was still no single sign-on or even a fully working sample application to add the behaviour to. So instead of posting one post, I’ll have a mini series where SSO will actually be a side track.

Getting the database set up was one of the first things I wanted to do. I didn’t want to add a MS SQL database to the project since that would require anyone who downloaded this project to have the database engine running. On various other blogs and .Net sites I’ve read there was talk about a lightweight alternative in the form of Sqlite. The .net provider can be downloaded here and boy was I lucky, the day I wanted to try it out they released a new version. Their latest version includes designer support in Visual Studio. Halfway august the Spring.Net team had also released a new version (1.2 M1) so I grabbed that version and NHibernate went 2.0 GA as well.

Since the Spring.Net assemblies are strongly signed they expected SQLite 1.0.56 and not 1.0.58 which was the one I downloaded. To make use of the new dll’s I added this to my configuration.

<db:provider id="DbProvider" provider="SQLite-1.0.56" connectionString="${db.datasource}"/>

Spring.Net has several DbProviders already configured, one of them being for version 1.0.56. Normally you don’t want to configure the entire provider for a new version so I just reused the existing configuration and added an assembly redirect.

<assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
  <dependentAssembly>
    <assemblyIdentity name="System.Data.SQLite" culture="neutral" publicKeyToken="db937bc2d44ff139"/>
    <bindingRedirect oldVersion="1.0.56.0" newVersion="1.0.58.0"/>
  </dependentAssembly>
</assemblyBinding>

This is all defined in the spring manual, but it actually took me quite some time to get it running. Only when adding the xml namespace the redirect was picked up when running the application. The NHibernate version I had was also newer so a redirect was necessary for those dll’s as well.

With this out of my way I still had to tell NHibernate to use SQLite. The official site gives this sample configuration:

<?xml version="1.0" encoding="utf-8"?>
  <hibernate-configuration  xmlns="urn:nhibernate-configuration-2.2" >
    <session-factory name="NHibernate.Test">
      <property name="connection.provider">NHibernate.Connection.DriverConnectionProvider</property>
      <property name="connection.driver_class">NHibernate.Driver.SQLiteDriver</property>
      <property name="connection.connection_string">
				Data Source=nhibernate.db;Version=3
      </property>
      <property name="dialect">NHibernate.Dialect.SQLiteDialect</property>
      <property name="query.substitutions">true=1;false=0</property>
    </session-factory>
  </hibernate-configuration>

Which is almost correct, but not quite. Below you find the complete correct springified configuration, so extract the pieces you need when using plain NHibernate. The difference is in the driver class.

<object id="NHibernateSessionFactory" type="Spring.Data.NHibernate.LocalSessionFactoryObject, Spring.Data.NHibernate20">
  <property name="DbProvider" ref="DbProvider"/>
  <property name="MappingAssemblies">
    <list>
      <value>TodoCore</value>
    </list>
  </property>
  <property name="HibernateProperties">
    <dictionary>
      <entry key="connection.provider"
			 value="NHibernate.Connection.DriverConnectionProvider"/>
      <entry key="connection.driver_class" 
             value="NHibernate.Driver.SQLite20Driver"/>
      <entry key="dialect" 
             value="NHibernate.Dialect.SQLiteDialect"/>
      <entry key="query.substitutions" 
             value="true=1;false=0"/>
    </dictionary>
  </property>
  <property name="ExposeTransactionAwareSessionFactory" value="true" />
</object>

Great we’ve configured our data access! But the repository is giving me errors, it can’t find the database file. The project structure I have is illustrated below.

My first attempt was to use relative path names.

<add key="db.datasource" value="Data Source=~App_DataTodoDb.db;Version=3;"/>

But that just failed, to get this working I used the, for me, new |DataDirectory| variable which did cause the database to be found and used.

<add key="db.datasource" value="Data Source=|DataDirectory|TodoDb.db;Version=3;"/>

The data was now being retrieved, isn’t that wonderful, only took me maybe two or three hours. As front end I first added a WPF application, but if you see that Window1.xaml file for the first time you have a bit of a writer’s block. Even when you drag a button on it, you don’t get to see one and adding a grid to the window and then trying to add a column was quite overwhelming, there even seem more options available compared to the third party components I use at work. So for now just good old Winforms, but a WPF front end will definitely be added. I also played around with the bindinglist you can create when using a grid, I must admit I’ve never used it before. I’ve always used my own implementation, we’ll see where it goes when I add an edit mode to the application. So for now I can only give you a configured application which shows your todos, so basically the same setup as my last spring.net sample which illustrated the support for webservices.

SampleApplication.rar (2.74 mb)

And before anyone asks, yes tests should be added.