tsJensen

A quest for software excellence...

SQLite on Visual Studio with NuGet and Easy Instructions

My most popular blog post ever was entitled SQLite on Visual Studio 2010 Setup Instructions which I wrote nearly 18 months ago. A lot has changed since then but the page is still as popular as ever. Here is the replacement for that antiquated post.

I’m now using Visual Studio 2012 at home. I’m still using Visual Studio 2010 at work. I have tried the latest x86/x64 SQLite NuGet package on both and it works very well. So forget about my post from 18 months ago and read on.

If you don’t have the NuGet Package Manager installed, go get it and get it installed.

For testing purposes, create a simple console application. If you’re like me, you’ll change the project platform target so that it will build for Any CPU. And if you’re using VS 2012, you’ll uncheck the “Prefer 32-bit” checkbox so that your console app will run as an x64 app in your environment. Unless, of course, if you are running in an x86 OS, you will want to stick with that. (As an aside, if you are still running in 32-bit mode, I recommend you upgrade.)

Now comes the fun part. Illustrated and very easy instructions. Don’t worry, we’ll get to some code later on.

First, right click on your project in the Solution Explorer and select Manage NuGet Packages…

sqlite_nuget_01

In the Package Manager dialog, click the “Online” tab on the left and then search for “sqlite”. You’ll see something like this:

sqlite_nuget_02

Select the (x86/x64) package for maximum flexibility. You will end up with two folders in your project with the appropriate interop assemblies in each. Those files are marked as “Content” and “Copy always” in your project. In this way, you no longer have to worry about which platform your application is running on. Here’s what it looks like:

sqlite_nuget_03

Save and build. You are now ready for some real code. Yes. It is that easy. So much easier than 18 months ago.

Now here’s the test code. This code shows you two approaches in Test1() and Test2() methods. Test1 uses the SQLiteFactory to create a standard System.Data.Common.DbConnection object so that you can plug in your standard ADO.NET code. Test2 uses the System.Data.SQLite namespace and its SQLiteConnection, SQLiteCommand, SQLiteParameter and SQLiteDataReader classes.

using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace SQLiteExperiment
{
  class Program
  {
    static void Main(string[] args)
    {
      var dbFile = @"D:\SQLData\SQLite\test.db";
      if (File.Exists(dbFile)) File.Delete(dbFile);

      var connString = string.Format(@"Data Source={0}; Pooling=false; FailIfMissing=false;", dbFile);

      //test using System.Data.Common and SQLiteFactory
      Test1(connString);

      if (File.Exists(dbFile))
        Console.WriteLine("Test1 succeeds");
      else
        Console.WriteLine("Test1 fails.");

      //prepare for test 2 using SQLite
      //Note: If Pooling=true in connection string the 
      //      File.Delete will fail with IOException - The process cannot
      //      access the file because it is being used by another process.
      if (File.Exists(dbFile)) File.Delete(dbFile);

      
      Console.WriteLine(string.Empty);

      //test using System.Data.SQLite namespace including SQLiteConnection
      Test2(connString);

      if (File.Exists(dbFile))
        Console.WriteLine("Test2 succeeds");
      else
        Console.WriteLine("Test2 fails.");

      Console.ReadLine(); //prevent close while in debug
    }

    static void Test1(string connString)
    {
      Console.WriteLine("Begin Test1");
      using (var factory = new System.Data.SQLite.SQLiteFactory())
      using (System.Data.Common.DbConnection dbConn = factory.CreateConnection())
      {
        dbConn.ConnectionString = connString;
        dbConn.Open();
        using (System.Data.Common.DbCommand cmd = dbConn.CreateCommand())
        {
          //create table
          cmd.CommandText = @"CREATE TABLE IF NOT EXISTS T1 (ID integer primary key, T text);";
          cmd.ExecuteNonQuery();

          //parameterized insert
          cmd.CommandText = @"INSERT INTO T1 (ID,T) VALUES(@id,@t)";
          
          var p1 = cmd.CreateParameter();
          p1.ParameterName = "@id";
          p1.Value = 1;

          var p2 = cmd.CreateParameter();
          p2.ParameterName = "@t";
          p2.Value = "test1";

          cmd.Parameters.Add(p1);
          cmd.Parameters.Add(p2);

          cmd.ExecuteNonQuery();

          //read from the table
          cmd.CommandText = @"SELECT ID, T FROM T1";
          using (System.Data.Common.DbDataReader reader = cmd.ExecuteReader())
          {
            while (reader.Read())
            {
              long id = reader.GetInt64(0);
              string t = reader.GetString(1);
              Console.WriteLine("record read as id: {0} t: {1}", id, t);
            }
          }
          cmd.Dispose();
        }
        if (dbConn.State != System.Data.ConnectionState.Closed) dbConn.Close();
        dbConn.Dispose();
        factory.Dispose();
      }
      Console.WriteLine("End Test1");
    }

    private static void Test2(string connString)
    {
      Console.WriteLine("Begin Test2");

      using (var dbConn = new System.Data.SQLite.SQLiteConnection(connString))
      {
        dbConn.Open();
        using (System.Data.SQLite.SQLiteCommand cmd = dbConn.CreateCommand())
        {
          //create table
          cmd.CommandText = @"CREATE TABLE IF NOT EXISTS T1 (ID integer primary key, T text);";
          cmd.ExecuteNonQuery();

          //parameterized insert - more flexibility on parameter creation
          cmd.CommandText = @"INSERT INTO T1 (ID,T) VALUES(@id,@t)";

          cmd.Parameters.Add(new System.Data.SQLite.SQLiteParameter 
            { 
              ParameterName = "@id", 
              Value = 1 
            });

          cmd.Parameters.Add(new System.Data.SQLite.SQLiteParameter
          {
            ParameterName = "@t",
            Value = "test2"
          });

          cmd.ExecuteNonQuery();

          //read from the table
          cmd.CommandText = @"SELECT ID, T FROM T1";
          using (System.Data.SQLite.SQLiteDataReader reader = cmd.ExecuteReader())
          {
            while (reader.Read())
            {
              long id = reader.GetInt64(0);
              string t = reader.GetString(1);
              Console.WriteLine("record read as id: {0} t: {1}", id, t);
            }
          }
        }
        if (dbConn.State != System.Data.ConnectionState.Closed) dbConn.Close();
      }
      Console.WriteLine("End Test2");
    }
  }
}

This code is ultra simplistic but illustrates exactly how easy it is use to use SQLite after “installing” the NuGet package. I have not tried to use a visual designer with this package. I’ll leave that discussion to another day.

SQLite and Fluent NHibernate with “Code First” in C#: Rewriting the OpenCollective Wiki

Introduction
Seven years ago, I wrote a wiki for managing requirements for multiple software development projects. I used C# in a simplistic ASP.NET web forms application with SQL Server as my data store. I published it under the name OpenCollective on Code Project and since then it has been viewed on just over 196,000 occasions and downloaded a little more than 1,600 times.

I still get an occasional question about OpenCollective, so a month or two ago I decided to rewrite it in order to explore using SQLite and Fluent NHibernate in a “code first” approach set in an ASP.NET MVC 3 application written in C#. The original OpenCollective was in some ways an experiment with technologies and techniques I was interested in at that time, so a rewrite to learn and explore something new seemed like a good idea.

The plan is simple. As time allows, I’ll rewrite OpenCollective and blog about my progress whenever it seems I have something of value to share. With some of these posts, I’ll share the entire code base for download. Others may just illustrate something I’ve learned or something I want to preserve for my own future reference. I tackled the first chore over a month ago but work and other activities pushed this project aside for a while, so I’m just getting around to blogging about the code now. I suspect this will be a priority driven pattern for this project.

Code First with Fluent NHibernate and SQLite
The first thing I wanted to accomplish in the OpenCollective rewrite was a move to SQLite and Fluent NHibernate in a “code first” approach to replace the SQL Server schema and the old ADO.NET code in the original. Some quick searching led me to the Fluent NHibernate Getting Started page. (If you haven’t read it, do so now.) Combining that with what I have already learned in exploring SQLite in this blog, I soon had a code first database being generated.

I’ll attach the entire code in 7zip (saves me 25% in bandwidth over standard zip), so I’m not going to go through all of the code in text here, but I do want to share with you some of the key steps in the my code first with Fluent NHibernate and SQLite adventure.

First, create your POCO entity class:

public class Project
{
	public virtual long Id { get; private set; }
	public virtual string Name { get; set; }
	public virtual DateTime Updated { get; set; }

	public virtual Author Author { get; set; }
	public virtual IList<Topic> Topics { get; set; }
	public virtual IList<Attachment> Attachments { get; set; }

	public Project()
	{
		Topics = new List<Topic>();
		Attachments = new List<Attachment>();
	}

	public virtual void AddTopic(Topic topic)
	{
		topic.Project = this;
		Topics.Add(topic);
	}

	public virtual void AddAttachment(Attachment attachment)
	{
		attachment.Project = this;
		Attachments.Add(attachment);
	}
}

Second, create a Fluent NHibernate mapping class:

public class ProjectMap : ClassMap<Project>
{
	public ProjectMap()
	{
		Id(x => x.Id);
		Map(x => x.Name);
		Map(x => x.Updated);
		References(x => x.Author);

		HasMany(x => x.Attachments)
			.Cascade.All();

		HasMany(x => x.Topics)
			.Cascade.All();
	}
}

Third, create a factory class that will return the NHibernate ISessionFactory and generate the SQLite schema and database file:

internal class DataFactory
{
	string _dbFile = string.Empty;
	bool _overwriteExisting = false;

	public DataFactory(string dbFile, bool overwriteExisting)
	{
		_dbFile = dbFile;
		_overwriteExisting = overwriteExisting;
	}

	public ISessionFactory CreateSessionFactory()
	{
		return Fluently.Configure()
			.Database(
				SQLiteConfiguration.Standard
					.UsingFile(_dbFile)
			)
			.Mappings(m => m.FluentMappings.AddFromAssemblyOf<DataFactory>())
			.ExposeConfiguration(BuildSchema)
			.BuildSessionFactory();
	}

	private void BuildSchema(Configuration config)
	{
		if (_overwriteExisting)
		{
			if (File.Exists(_dbFile)) File.Delete(_dbFile);

			var se = new SchemaExport(config);
			se.Create(false, true);
		}
	}
}

Fourth, create a repository class that will let you create the database and perform CRUD operations using your entity classes:

public static class OcDataRepository
{
	public static void Create(string dbFile)
	{
		DataFactory df = new DataFactory(dbFile, true);
		using (var sf = df.CreateSessionFactory())
		{
			sf.Close();
		}
	}

	public static void AddProject(string dbFile, Project project)
	{
		DataFactory df = new DataFactory(dbFile, false);
		using (var sf = df.CreateSessionFactory())
		using (var session = sf.OpenSession())
		using (var trans = session.BeginTransaction())
		{
			session.SaveOrUpdate(project.Author);
			session.SaveOrUpdate(project);
			trans.Commit();
		}
	}
}

Finally, write a little test console app to see if it all works:

class Program
{
	static void Main(string[] args)
	{
		string dbFile = @"C:\temp\test4.db";
		OcDataRepository.Create(dbFile);

		Project project = new Project
		{
			Name = "My First Project",
			Updated = DateTime.Now,
			Author = new Author { Name = "John Smith", Username = "smithj", Email = "jsmith@gmial.com" }
		};
		OcDataRepository.AddProject(dbFile, project);

		Console.WriteLine("done");
		Console.ReadLine();
	}
}

Examine the SQLite db file with your favorite SQLite tool and see the SQL:

CREATE TABLE "Project" (
	Id  integer, 
	Name TEXT, 
	Updated DATETIME, 
	Author_id INTEGER, 
	primary key (Id)
)

If you don’t have 7-zip, download and install it first. Download the code OcWiki.7z (695.8KB)

System.Data.SQLite Write Comparison of SQLite 3 vs Berkeley DB Engines

In my last post, I covered getting the Berkeley DB SQL API running under the covers of the System.Data.SQLite library for .NET. Another weekend has come and I’ve had some time to run some tests. The whitepaper Oracle Berkeley DB SQL API vs. SQLite API indicates that the Berkeley engine will have relatively the same read speed as SQLite but better write speed due to it’s page locking vs. file locking.

Berkeley Read Bug
Originally, my code would write a certain number of rows in a given transaction on one or more threads and then read all rows on one or more threads, but I had to give up on my read code because the Berkeley implementation of System.Data.SQLite threw a "malloc() failed out of memory" exception whenever I ran the read test with more than one thread. You can read the code below and decide for yourself whether I made a mistake or not, but the test ran fine with the SQLite assembly from the sqlite.org site.

The Tests
There were four tests, each inserting similar but different data into a single table with a column representing each of the main data types that might be used in a typical application. Two single threaded tests, the first inserting 6,000 rows in a single transaction and the second 30,000 rows. The third and fourth tests were multithreaded: 6 threads inserting 1,000 rows each and then 10 threads inserting 500 rows each. 

Here’s the SQL for the table:

CREATE TABLE [TT] 
(
  [src] TEXT, 
  [td] DATETIME, 
  [tn] NUMERIC, 
  [ti] INTEGER, 
  [tr] REAL, 
  [tb] BLOB
);

The Results
The results do not show a vast difference in insert speed for Berkeley as I might have expected. The tests were run on an AMD 6 core machine with 8GB of RAM. I’m writing to an SSD drive which may or may not affect results as well.

sqlitevbdb

You may choose to interpret the results differently, but I don’t see enough difference to make me abandon the more stable SQLite 3 engine at this time.

The Code
I invite you to review the code. If I’ve missed something, please let me know. It’s pretty straightforward. With each test, I wipe out the database environment entirely, starting with a fresh database file. I use the System.Threading.Tasks library.

class Program
{
  static void Main(string[] args)
  {
    Console.WriteLine("SQLite Tests");
    var tester = new Tester();
    tester.DoTest("1 thread with 6000 rows", 1, 6000);
    tester.DoTest("6 threads with 1000 rows each", 6, 1000);

    tester.DoTest("1 thread with 30000 rows", 1, 30000);
    tester.DoTest("10 threads with 500 rows each", 10, 500);

    Console.WriteLine("tests complete");
    Console.ReadLine();
  }
}

class Tester
{
  public void DoTest(string testName, int threadCount, int rowCount)
  {
    DataMan.Create();
    Console.WriteLine("");
    Console.WriteLine(testName + " started:");

    List<Task> insertTasks = new List<Task>();
    DateTime beginInsert = DateTime.Now;
    for (int i = 0; i < threadCount; i++)
    {
      insertTasks.Add(Task.Factory.StartNew(() => InsertTest(i, rowCount), TaskCreationOptions.None));
    }

    Task.WaitAll(insertTasks.ToArray());
    DateTime endInsert = DateTime.Now;
    TimeSpan tsInsert = endInsert - beginInsert;

    //List<Task> readTasks = new List<Task>();
    //DateTime beginRead = DateTime.Now;
    //for (int i = 0; i < threadCount; i++)
    //{
    //   readTasks.Add(Task.Factory.StartNew(() => ReadTest(i), TaskCreationOptions.None));
    //}

    //Task.WaitAll(readTasks.ToArray());
    //DateTime endRead = DateTime.Now;
    //TimeSpan tsRead = endRead - beginRead;

    double rowsPerSecondInsert = (threadCount * rowCount) / tsInsert.TotalSeconds;
    //double rowsPerSecondRead = (threadCount * rowCount) / tsRead.TotalSeconds;
    Console.WriteLine("{0} threads each insert {1} rows in {0} ms", threadCount, rowCount, tsInsert.TotalMilliseconds);
    //Console.WriteLine("{0} threads each read all rows in {1} ms", threadCount, tsRead.TotalMilliseconds);
    Console.WriteLine("{0} rows inserted per second", rowsPerSecondInsert);
    //Console.WriteLine("{0} rows read per second", rowsPerSecondRead);
  }

  void InsertTest(int threadId, int rowCount)
  {
    DateTime begin = DateTime.Now;
    DataMan.InsertRows(rowCount);
    DateTime end = DateTime.Now;
    TimeSpan ts = end - begin;
    //Console.WriteLine("{0} lines inserted in {1} ms on thread {2}", rowCount, ts.TotalMilliseconds, threadId);
  }

  void ReadTest(int threadId)
  {
    DateTime begin = DateTime.Now;
    int count = DataMan.ReadAllRows();
    DateTime end = DateTime.Now;
    TimeSpan ts = end - begin;
    //Console.WriteLine("{0} rows read in {1} ms on thread {2}", count, ts.TotalMilliseconds, threadId);
  }
}

internal static class DataMan
{
  private const string DbFileName = @"C:\temp\bdbvsqlite.db";
  private const string DbJournalDir = @"C:\temp\bdbvsqlite.db-journal";

  public static void Create()
  {
    if (File.Exists(DbFileName)) File.Delete(DbFileName);
    if (Directory.Exists(DbJournalDir)) Directory.Delete(DbJournalDir, true);

    //Console.WriteLine(SQLiteConnection.SQLiteVersion);
    using (SQLiteConnection conn = new SQLiteConnection())
    {
      conn.ConnectionString = "Data Source=" + DbFileName + ";";
      conn.Open();
      using (SQLiteCommand cmd = conn.CreateCommand())
      {
        cmd.CommandTimeout = 180;
        cmd.CommandText = "DROP TABLE IF EXISTS [TT]; CREATE TABLE [TT] ([src] TEXT, [td] DATETIME, [tn] NUMERIC, [ti] INTEGER, [tr] REAL, [tb] BLOB);";
        cmd.CommandType = System.Data.CommandType.Text;
        int result = cmd.ExecuteNonQuery();
      }
      conn.Close();
    }
  }

  public static void InsertRows(int rowCount)
  {
    SQLiteParameter srParam = new SQLiteParameter();
    SQLiteParameter tdParam = new SQLiteParameter();
    SQLiteParameter tnParam = new SQLiteParameter();
    SQLiteParameter tiParam = new SQLiteParameter();
    SQLiteParameter trParam = new SQLiteParameter();
    SQLiteParameter tbParam = new SQLiteParameter();
    Random rnd = new Random(DateTime.Now.Millisecond);

    using (SQLiteConnection conn = new SQLiteConnection())
    {
      conn.ConnectionString = "Data Source=" + DbFileName + ";";
      conn.Open();
      using (SQLiteTransaction trans = conn.BeginTransaction())
      using (SQLiteCommand cmd = conn.CreateCommand())
      {
        cmd.CommandTimeout = 180;
        cmd.CommandType = CommandType.Text;
        cmd.CommandText = "INSERT INTO [TT] ([src],[td],[tn],[ti],[tr],[tb]) VALUES (?,?,?,?,?,?);";
        cmd.Parameters.Add(srParam);
        cmd.Parameters.Add(tdParam);
        cmd.Parameters.Add(tnParam);
        cmd.Parameters.Add(tiParam);
        cmd.Parameters.Add(trParam);
        cmd.Parameters.Add(tbParam);

        int count = 0;
        while (count < rowCount)
        {
          var data = MakeRow(rnd);
          srParam.Value = data.src;
          tdParam.Value = data.td;
          tnParam.Value = data.tn;
          tiParam.Value = data.ti;
          trParam.Value = data.tr;
          tbParam.Value = data.tb;
          int result = cmd.ExecuteNonQuery();
          count++;
        }
        trans.Commit();
      }
      conn.Close();
    }
  }

  public static int ReadAllRows()
  {
    List<DRow> list = new List<DRow>();
    using (SQLiteConnection conn = new SQLiteConnection())
    {
      conn.ConnectionString = "Data Source=" + DbFileName + ";";
      conn.Open();
      using (SQLiteCommand cmd = conn.CreateCommand())
      {
        cmd.CommandTimeout = 180;
        cmd.CommandText = "SELECT [rowid],[src],[td],[tn],[ti],[tr],[tb] FROM [TT];";
        using (SQLiteDataReader reader = cmd.ExecuteReader())
        {
          while (reader.Read())
          {
            var row = new DRow
            {
              rowid = reader.GetInt64(0),
              src = reader.GetString(1),
              td = reader.GetDateTime(2),
              tn = reader.GetDecimal(3),
              ti = reader.GetInt64(4),
              tr = reader.GetDouble(5)
              //tb = (byte[])reader.GetValue(6)
            };
            list.Add(row);
          }
          reader.Close();
        }
      }
      conn.Close();
    }
    return list.Count;
  }

  private static DRow MakeRow(Random rnd)
  {
    int start = rnd.Next(0, 250);
    byte[] b = new byte[512];
    rnd.NextBytes(b);
    return new DRow
    {
      src = spear.Substring(start, 250),
      td = DateTime.Now,
      tn = rnd.Next(2999499,987654321),
      ti = rnd.Next(3939329,982537553),
      tr = rnd.NextDouble(),
      tb = b
    };
  }

  private const string spear = @"FROM fairest creatures we desire increase,That thereby beauty's rose might never die,But as the riper should by time decease,His tender heir might bear his memory:But thou, contracted to thine own bright eyes,Feed'st thy light'st flame with self-substantial fuel,Making a famine where abundance lies,Thyself thy foe, to thy sweet self too cruel.Thou that art now the world's fresh ornamentAnd only herald to the gaudy spring,Within thine own bud buriest thy contentAnd, tender churl, makest waste in niggarding.Pity the world, or else this glutton be,To eat the world's due, by the grave and thee.";
}

internal class DRow
{
  public long rowid { get; set; }
  public string src { get; set; }
  public DateTime td { get; set; }
  public decimal tn { get; set; }
  public long ti { get; set; }
  public double tr { get; set; }
  public byte[] tb { get; set; }
}

Upgrade to System.Data.SQLite 1.0.74.0 in Visual Studio 2010

I wrote about getting SQLite up and working in Visual Studio 2010 and your .NET 4.0 projects in June. Then I got distracted with work and didn’t have a chance to come back to my exploration of SQLite until today. So I decided to look to see if we have a new version. Yes, the new System.Data.SQLite install for 1.0.74.0 (SQLite version 3.7.7.1 which includes a few bug fixes) is ready to download and install.

Unfortunately, the designers are still not available, so if you want any designer, however buggy, in Visual Studio, you’re still stuck with my original path in my previous post. (See the readme once you finish the 1.0.74.0 install.)

After a variety of experiments with the new installer, I ended up taking the following steps that led to successfully running my budding test/prototype application. Follow these steps and you won’t go too wrong:

  1. Uninstall all SQLite related items from Control Panel | Program and Features
  2. Download and install my favorite free SQLite admin tool from http://osenxpsuite.net/?xp=3.
  3. Download the latest from http://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki. In my case, it was the sqlite-netFx40-setup-bundle-x64-2010-1.0.74.0.exe.
  4. This will have removed some GAC items and NOT put 1.0.74.0 into GAC. At least on my machine. I could not get the assemblies into the GAC to save my life.
  5. Make sure your project references are pointed to the correct version and that you have Copy Local set to true.
  6. Change your config settings to remove useLegacyV2RuntimeActivationPolicy=true from the startup node like this:
      <startup>
        <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.0" />
      </startup>
  7. Change your config settings in DbProviderFactories to remove strong name reference like this:
      <system.data>
        <DbProviderFactories>
          <remove invariant="System.Data.SQLite" />
          <add name="SQLite Data Provider"
              invariant="System.Data.SQLite"
              description=".Net Framework Data Provider for SQLite"
              type="System.Data.SQLite.SQLiteFactory, System.Data.SQLite" />
        </DbProviderFactories>
      </system.data>
  8. Test to taste.

SQLite on Visual Studio 2010 Setup Instructions

Obsolete

Go to the new SQLite on Visual Studio with NuGet post instead.


Be sure to read upgrade instructions I've just posted as a follow-up to this post.

I’m just starting to play with SQLite and the System.Data.SQLite library created by Robert Simpson and taken over by the SQLite.org folks. In attempting to get things working in Visual Studio 2010, I ran into a few issues, so this post is as much a reminder for myself as it is a help for other .NET developers who wish to use SQLite from within Visual Studio 2010.

The current binary installers from sqllite.org for the System.Data.SQLite .NET assembly do not include the Visual Studio designers or the ADO.NET driver required for use of SQLite in the Server Explorer. So here’s the winning combination I’ve found for getting my environment set up properly.

  1. Download and install version 1.0.66.0 from sourceforge.net because current binary installs on SQLite.org at system.data.sqlite.org do not include the Visual Studio designer or ADO.NET driver install.
     
  2. Download and install the latest versions (x86 and x64) from system.data.sqlite.org (currently 1.0.73.0 which contains the SQLite 3.7.6.3 engine).
    Important Note: If you have Visual Studio 2008 and 2010, be sure to choose both when prompted as I found reports from others who had problems otherwise.
     
  3. Copy the x86 binaries into and overwriting file at C:\Program Files (x86)\SQLite.NET\bin (assuming you used the default install location for version 1.0.66.0 and you're running x64 Windows). And if you are on an x64 box, copy the x64 binaries to the C:\Program Files (x86)\SQLite.NET\bin\x64 directory, overwriting existing files.

Now you can open Visual Studio 2010 and navigate to the Server Explorer, right-click the Data Connections node, choose Add Connection and click the Change button for Data source. You can then select the SQLite Database file source like this:

dsource

Click the new button and navigate to your desired directory and supply a file name and you’ll end up with something like this:

dconn

With a new data connection, you can use the table designer, but it has limitations and it’s probably not the best approach with an embedded database engine from a development perspective anyway, since generally you’re going to want your app to be able to create the database from script embedded in code.

So now all that’s left to get started writing code against your database is to add your references like this:

dref

And since Entity Framework supports SQLite, you can add your ADO.NET Entity Data Model to your project and it will produce a nice connection string like this for you (VB-like line _ breaks added):

<connectionStrings>
  <add name="testEntities"
    connectionString="metadata=res://*/Model1.csdl|res://*/Model1.ssdl| _
    res://*/Model1.msl;provider=System.Data.SQLite; _
    provider connection string=&quot;data source=C:\Code\Projects\test.db&quot;"
    providerName="System.Data.EntityClient" />
</connectionStrings>

or a non EF 4 connection string of

<connectionStrings>
  <add name="mytest"
    connectionString="data source=C:\Code\Projects\test.db;"
    providerName="System.Data.SQLite" />
</connectionStrings>

Now remember, model first is not currently supported, so you need to create your data and then update your model from data. You also need to make sure that you add the following to your config file or you’ll get a nasty runtime error:

<startup useLegacyV2RuntimeActivationPolicy="true">
  <supportedRuntime version="v4.0" />
</startup>

UPDATE (30 minutes later) Just a bit more testing reveals that I missed one step. Very important to know and use.

First, I reinstalled the x86 and then x64 latest installs and checked the checkboxes to install to the GAC and modify the path. Then I ran “test” from the command line and after modifying the connection string by giving the file a path like C:\temp\test.db, the tests ran fine. Looking at the chm help file and the test.exe.config revealed the trick I needed.

The 1.0.66.0 install adds a factory reference to your .NET machine.config file like this:

</DbProviderFactories>
  ...
  <add name="SQLite Data Provider"
    invariant="System.Data.SQLite"
    description=".Net Framework Data Provider for SQLite"
    type="System.Data.SQLite.SQLiteFactory,
      System.Data.SQLite,
      Version=1.0.66.0,
      Culture=neutral,
      PublicKeyToken=db937bc2d44ff139" />
</DbProviderFactories>

While this entry makes the Data Connection and designer possible, it will cause you problems when trying to run using the latest 1.0.73.0 referenced assemblies until you add this to your own config file:

<system.data>
  <DbProviderFactories>
    <remove invariant="System.Data.SQLite" />
    <add name="SQLite Data Provider"
      invariant="System.Data.SQLite"
      description=".Net Framework Data Provider for SQLite"
      type="System.Data.SQLite.SQLiteFactory, System.Data.SQLite" />
  </DbProviderFactories>
</system.data>

Next step: experiment with updating my machine.config in its many incarnations to point to the latest and greatest x64 version 1.0.73.0 with token db937bc2d44ff139 like this:

</DbProviderFactories>
  ...
  <add name="SQLite Data Provider"
    invariant="System.Data.SQLite"
    description=".Net Framework Data Provider for SQLite"
    type="System.Data.SQLite.SQLiteFactory,
    System.Data.SQLite,
    Version=1.0.73.0,
    Culture=neutral,
    PublicKeyToken=db937bc2d44ff139" />
</DbProviderFactories>

Note that this will move the machine.config reference to the MSIL (Any CPU) version rather than the x86 version. For my machine, this means making changes in (and remember to open as Administrator so you can save the file):

C:\Windows\Microsoft.NET\Framework\v2.0.50727\CONFIG\machine.config
C:\Windows\Microsoft.NET\Framework\v4.0.30319\Config\machine.config
C:\Windows\Microsoft.NET\Framework64\v2.0.50727\CONFIG\machine.config
C:\Windows\Microsoft.NET\Framework64\v4.0.30319\Config\machine.config

Rebooting, just to be paranoid.

And now, run the same test after commenting out the local app.config’s <system.data><DbProviderFactories> node and everything works as expected. No more bad image exception. And the Data Connection designer continues to operate as expected. Now on to some real coding.