Getting Fluent NHibernate to work with SQLite

Question

I'm sure there is something simple I've not done but I'm trying to get Fluent NHibernate to work with Sqlite on my machine.

I used NuGet to download fluent nhibernate and added the following entity and mapping:

public class Customer
{
    public virtual string CustomerCode { get; set; }
    public virtual string Name { get; set; }
}

public class CustomerMap : ClassMap<Customer>
{
    public CustomerMap ()
        {
        Id(x => x.CustomerCode);
        Map(x => x.Name);
        Table("tblCustomer");
        }
}

Then following the getting started with fluent guide I added the following code to a Windows Command project:

class Program
{
    static void Main(string[] args)
    {

        var sessionFactory = CreateSessionFactory();

        using (var session = sessionFactory.OpenSession())
        {
            using (var transaction = session.BeginTransaction())
            {

                var customer = new Customer { CustomerCode = "123", Name = "Bob" };
                session.SaveOrUpdate(customer);
                transaction.Commit();
            }
        }
    }

    private static ISessionFactory CreateSessionFactory()
    {
        return Fluently.Configure()
            .Database(
            SQLiteConfiguration.Standard
            .UsingFile("firstProject.db")
            )
            .Mappings(m =>
                        m.FluentMappings.AddFromAssemblyOf<Program>())
            .ExposeConfiguration(BuildSchema)
            .BuildSessionFactory();
    }

    private static void BuildSchema(Configuration config)
    {
        // delete the existing db on each run
        if (File.Exists("firstProject.db"))
            File.Delete("firstProject.db");

        // this NHibernate tool takes a configuration (with mapping info in)
        // and exports a database schema from it
        new SchemaExport(config)
          .Create(false, true);
    }
}

Finally I added the Sqlite dll using NuGet.. however I'm getting the following error when trying to run the program:

Top Exception:

An invalid or incomplete configuration was used while creating a SessionFactory. Check PotentialReasons collection, and InnerException for more detail.

Next Exception:

Could not create the driver from NHibernate.Driver.SQLite20Driver, NHibernate, Version=3.1.0.4000, Culture=neutral, PublicKeyToken=aa95f207798dfdb4.

Inner most exception:

Unable to find the requested .Net Framework Data Provider.  It may not be installed.

This is when it's trying to create the session factory.

Can anyone help with this? I'm running a 32 bit machine?

Thanks

Dave

Answer 1

You need two things:

1. Reference System.Data.SQLite in your project.
2. Include sqlite3.dll, but you also can’t add reference to sqlite3.dll, because it’s an unmanaged dll. Simply add it as element to solution and set it to copy to output directory.

Answer 2

You need the .NET provider for Sqlite. Download it here http://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki

Answer 3

After initial investigation I thought it was because my System.Data.SQLite assembly wasn't loaded into memory at the time, so I included code to preload the system.Data.SQLite assembly. However when running the application the real error arose:

Mixed mode assembly is built against version 'v2.0.50727' of the runtime and cannot be loaded in the 4.0 runtime without additional configuration information.

to fix this I changed my app.config to look like the following:

<?xml version="1.0"?>
<configuration>
  <startup useLegacyV2RuntimeActivationPolicy="true">
    <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.0"/>
  </startup>
</configuration>

with the important bit being useLegacyV2RuntimeActivationPolicy="true"