Getting Fluent NHibernate to work with SQLite

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" };
                ses开发者_开发百科sion.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

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.

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

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"

I was having the same problem today, and spend an hour searching for a solution. Anyway, I found one here:

http://thelongwayback.wordpress.com/2009/11/02/fluent-nhibernate-sqlite-problem-the-idbcommand-and-idbconnection-implementation-in-the-assembly-system-data-sqlite-could-not-be-found/

Basically, you'll have to use an older version of SQLite (v1.0.60) from here: http://sourceforge.net/projects/sqlite-dotnet2/files/

On another note, I got the same code working yesterday on a machine with VS2010 SP1. The same code wouldn't run today on a machine without SP1. If anyone gets a chance to test this one, i.e., by installing VS2010 SP1, let me know the outcome please.

None of the above solutions, or any else I could find on the Internet, worked for me... until...

I had to install both the x64 and x86 versions of SQLite (per Vadim's link: http://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki), specifically in that order.

Initially, I installed the 32-bit before the 64-bit version, which did not fix anything. On a whim, I uninstalled them both and reinstalled them in the opposite order. Now it works!

I've tested this on 2 different machines and verified this was the fix for both of them. Wacky, but effective.

I also encountered this problem.
Noticed that the starter sample project targets .net framework 3.5 and the one I created (exercise project) is targeting .net framework 4.0 client profile (default of vs2010).
I changed the target version to 3.5 and I was able to work on my exercise project.

In my case it worked to install the System.Data.SqLite via NuGet.

So I opened the "Tools/NuGet Package Manager/Package Manager console" and typed:

Install-Package System.Data.SqLite