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…
In the Package Manager dialog, click the “Online” tab on the left and then search for “sqlite”. You’ll see something like this:
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:
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.