SQLite on .NET - Get up and running in 3 minutes.

You found it! The quick and dirty guide to setting up SQLite with .Net in 3 minutes. Small, fast, and ass-kicking like a transactional Jackie Chan. At least that’s what this campy image created just for this post says. (Shout out to righteous graphics dude Brian Cook for the sweet photoshoppery)

SQLite on .Net, small, fast, ass-kicking.

SQLite: The compelling introduction
You’re writing some smallish app. You know the type. Those little one-offs where you clean up some data, scrape your competitor’s web site, change traffic light colors, blah blah blah. It’s cool enough to warrant it’s own solution, but not so big that you are going to tie into your company’s SQL Server instances. Let’s face it, you don’t really need the hassle of dealing power tripping DBA / IT guys for this project, especially now that their delusions of grandeur have been magnified by the return of American Gladiators to prime time.

Enter SQLite: the quick skinny / speed-date overview:
SQLite, if you weren’t already aware, is an open source, tiny (600K!), *zero install*, transactional database. But theres more! SQLite is as fast or faster than some of the big guys you use every day, largely because there is no server process, no listeners, no FD&C Yellow #5, none of that crapola. The database is just a single file and you communicate with it through a linked in library. Think of the old access days, but not really.

SQLite implements most of the SQL-92 standard. All of the query related stuff you actually care about is there, triggers, views, etc. The only thing missing is stored procedures but that’s probably not a requirement for your project, if at all. And no, I don’t want to get in to the old sps vs. not-sps tangent, man thats so aggressively nerdy in a Kirk vs Picard way.

… But I digest….

As the the SQLite home page will happily tell you, "SQLite is the most widely deployed SQL database engine in the world." It’s used by little guys you may have heard of like, oh, I don’t know, Adobe, Apple, FireFox, GE, Google, Skype, Sun, and several shadow governments best left un-named. The crazy part is that you rarely hear about SQLite as a real .Net Dude / Dudette developer. I think this is because it’s often bundled inside of things you didn’t really think were using a database like FireFox or Google Gears, but the research bears out that this bad boy is more than capable for real-life tasks like being the back-end db for dynamic web sites with some pretty serious traffic.

SQLite is great for that small-mediumish application we already spec-ed out so lets get into the quick and dirty setup to get this hog rockin’ on .NET.

SQLite : The Quick and Dirty Setup for .NET.

1) Download SQLite
While you can get the generic windows binary on the SQLite download page, I’m going to recommend you instead grab the ADO.NET 2.0 Provider for SQLite from sourceforge. I’m not saying this is the most performant version (it does have an ADO wrapper with its attendant malarkey), but it really is a super-easy starting implementation that’s probably good enough for the long haul.

2) Copy the resultant DLL (System.Data.SQLite.DLL) to your project and add a reference.

3) Download and install one of the billions of SQLite GUI clients. I’ve been using the aptly named "SQLite Administrator" (FREE) which has a sweet, Query Analyzer-alike interface. You can find a big list of SLQLite gui clients here http://www.sqlite.org/cvstrac/wiki?p=ManagementTools if you are so inclined.SQLite administrator tool, free and badassed.

4) Through the GUI, create a database and make a test table of whatever floats your boat. The result will be a single file with a .s3db extension.

5) There is no step 5! DONE! You can now query, insert, update, delete, create, truncate, etc, to your heart’s content using the System.Data.SQLite ADO wrapper. Here is a little helper db util type class to show you the basic schleck:

public static DataTable GetDataTable (string sql)

 

   {

 

    DataTable dt = new DataTable();

 

    try

 

    {

 

    SQLiteConnection cnn = new SQLiteConnection("Data Source=C:CheckoutWorldDominator.s3db");

 

    cnn.Open();

 

    SQLiteCommand mycommand = new SQLiteCommand(cnn);

 

    mycommand.CommandText = sql;

 

    SQLiteDataReader reader = mycommand.ExecuteReader();

 

    dt.Load(reader);

 

    reader.Close();

 

    cnn.Close();

 

    } catch {

 

    // Catching exceptions is for communists

 

    }

 

    return dt;

 


}

public static int ExecuteNonQuery(string sql)

 

{

 

    SQLiteConnection cnn = new SQLiteConnection("Data Source=C:CheckoutWorldDominator.s3db");

 

    cnn.Open();

 

    SQLiteCommand mycommand = new SQLiteCommand(cnn);

 

    mycommand.CommandText = sql;

 

    int rowsUpdated = mycommand.ExecuteNonQuery();

 

    cnn.Close();

 

    return rowsUpdated;

 

}

 

public static string ExecuteScalar(string sql)

 

{

 

    SQLiteConnection cnn = new SQLiteConnection("Data Source=C:CheckoutWorldDominator.s3db");

 

    cnn.Open();

 

    SQLiteCommand mycommand = new SQLiteCommand(cnn);

 

    mycommand.CommandText = sql;

 

    object value = mycommand.ExecuteScalar();

 

    cnn.Close();

 

if (value != null)

 

    {

 

        return value.ToString();

 

    }

 

    return "";

 

}

 

NOTE: Above code is quicky crap. It’s just to show you the gist.

Some uses for SQLite to consider:

Configs / Settings: SQLite is a good alternative to xml config files or registry settings for things like user account info and application preferences. It supports encryption, so feel free to keep your brilliant patent ideas in there.

Persistent Caching: You can use SQLite as a DB for cache data that needs to persist through reboots / application recycles. Maybe you have some expensive one-time-on-loadup queries from your enterprise db that you cache up and use in your website or app. By timestamping the data into a local SQLite db, you can live through application restarts and only refresh your cache at the threshold you want to.

Slicing and Dicing data: Load in some data and query to your heart’s content. Great for analyzing data at your leisure, worming through subsets of data, etc. Since its just a little db on your on box, no one is going to hassle you. Managers who were once developers will appreciate being to query through data with SQLite vs. using excel as they usually are too crusty to still have permissions on the real db.

Full DB for One-off apps: Sometimes you write a quickie app that just harvests something in a funky way and collects data. You can output the data as you are grabbing it in all kinds of ways, but throwing into a db is ideal.

Linkage to some more in-depth stuff ……….

- SQLite is pretty hip with with the alt.net scene, you can follow on from here to check out a SQLite NHibernate provider, or here for a SQLite Subsonic provider.

- If you are rockin’ the 3.0 framework there is even a SQLite Linq provider.

- For a comprehensive SQLite how-to (emphasizing command line, non-MS specific) : SQLite Tutorial

- And in case you missed it up top, the main SQLite project page is here.

But football in the groin had a football in the groin!Have a good idea for ways to use SQLite in .Net projects? Been hit in the groin with a football for even suggesting using something like this at your company? Have some award-winning successes or outlandish failures already with SQLite? Leave a funky-fresh comment!

NOTE: “Data Source” typo now fixed in code sample.

kick it on DotNetKicks.com

62 comments ↓

#1 mikedopp on 01.15.08 at 1:08 pm

Hey thanks for the information. SQLlite Rocks. I am going to start to implement this for myself after reading this. Excellent article. Keep up the good work.
~mike (mikedopp.com/.net)

#2 GlenH on 01.15.08 at 1:21 pm

It should be said, for those unfamiliar with SQLite, that there are serious performance issues for applications that use multi-threaded (multiple) writers. This is due to the not-very-granular locking mechanism used by SQLite during writes. Multiple simultaneous readers are A-OK.

#3 Duncan on 01.15.08 at 1:25 pm

@GlenH, thanks for the tip! I’ve only been writing to my installs locally but I can see how that might be a deal-breaker for certain apps.

#4 Brian on 01.15.08 at 1:30 pm

I laugh in the face of groin injuries! SQLite sounds pretty “dope.”

#5 jb on 01.15.08 at 1:56 pm

Great write-up. Are there any resources comparing all non-Sql Server alternatives (MySql, SQLite, etc.) for .NET?

–jb

#6 Reflective Perspective - Chris Alcock » The Morning Brew #11 on 01.16.08 at 3:14 am

[...] SQLite on .NET in 3 minutes - A good quick start guide for the SQLite embedded database [...]

#7 skain on 01.16.08 at 2:33 pm

Really good article. Very interesting.

#8 .NET & SQLite | Hodson Report on 01.17.08 at 5:09 pm

[...] quick guides to getting up and running with SQLite over at Mike Duncan’s blog. In his post SQLite on .NET - Get up and running in 3 minutes Mike gives you pretty all the info you will need for a quick start using SQLite in your .NET [...]

#9 SQLite on .NET in 3mins « MogBlog on 01.18.08 at 2:02 pm

[...] SQLite on .NET in 3mins link [...]

#10 ?? | ????? - .NET Atlantis on 01.19.08 at 12:27 am

[...] SQLite on .NET - Get up and running in 3 minutes. http://www.mikeduncan.com/sqlite-on-dotnet-in-3-mins/ Yes~?Portable & Standalone???Database,?????SQLite , [...]

#11 Wöchentliche Rundablage: .NET 3.5, WPF, LINQ, Tests, System.AddIn, SubSonic, Sandcastle | Code-Inside Blog on 01.21.08 at 3:41 pm

[...] SQLite in 3 Minuten erklärt - wie man .NET und SQLite zusammen benutzen kann. [...]

#12 Daily Find #22 | TechToolBlog on 01.24.08 at 8:50 am

[...] SQLite on .NET - SQLite is the DB of choice for fast, small footprint architecture.  Think Mobile OS or Google Gears.  Speaking of Google Gears where is Microsoft’s version? [...]

#13 Sam on 01.27.08 at 11:39 pm

I have used System.Data.SQLite and it is awesome–faster than SQLCE, and it has full-text index. FTS3 is amazing. I have been doing work with it and it is a real gem.

#14 Links of the Week #20 (week 3/2008) on 02.06.08 at 5:13 pm

[...] Library Source Code now available - News of the week/month(/year?) Going agile with Biztalk 2006 SQLite on .NET - Get up and running in 3 minutes - The quick and dirty guide to setting up SQLite with .Net in 3 minutes. Small, fast, and [...]

#15 Lyndon on 02.12.08 at 6:45 pm

Seems ‘DataSource’ should be ‘Data Source’.

Otherwise, a great article!

#16 wicherqm on 02.14.08 at 2:59 pm

Great great great :)
Smile xml you are kicked to the trash

#17 Vishal on 03.10.08 at 11:34 am

For some reason, I can’t get the dt.Load(reader) to work. Every time the program passes that line, it crashes with an “…encountered a problem and needs to close.” error. Does anyone else have this problem?

#18 Jay Godse on 03.13.08 at 9:43 am

Great article Mike. I’m not an ADO.Net guy so your page is a great resource. If you want more stuff on SQLite (including comparisons to LINQ), check out my web page on it at http://www.squidoo.com/sqlitehammer.

#19 How YOUR tech blog posts are RIPPED OFF while you sleep! on 03.24.08 at 8:13 am

[...] my story. As avid readers of http://www.mikeduncan.com you’ve undoubtedly read my scintillating post SQLite on .NET - Get up and running in 3 minutes. Catchy, isn’t it? It was birthed into the world as the timestamp clearly reminds us on [...]

#20 SDC on 03.24.08 at 8:40 pm

SQLite is da bomb! There’s a shockingly broad array of things for which, no, you don’t need the Enterprise version of SQL Server, or even SQL Express for that matter. Good lookin’ out, getting the word out there.

Peace!

#21 SQLite on .NET - 3 minutes Guide « Rams On It - .NET on 03.26.08 at 5:27 pm

[...] minutes Guide 26 Mar 2008 Posted by ramsonit in Tools. trackback Mike has written an article SQLite on .NET - Get up and running in 3 minutes, Its very good quick guide for .NET developers. He also suggested how we can user SQLite in our [...]

#22 Trying to get rid of MS Access - Sergio Pereira on 04.09.08 at 11:44 pm

[...] Short tutorial for .Net Published Apr 09 2008, 10:37 PM by sergiopereira Filed under: .NET, Tools [...]

#23 Tom on 04.27.08 at 11:38 pm

Great tutorial, thanks. One error:

In this line:
SQLiteConnection cnn = new SQLiteConnection(“DataSource=C:CheckoutWorldDominator.s3db”);

“DataSource” should be two words, not one, like “Data Source”

http://sqlite.phxsoftware.com/forums/p/532/532.aspx

#24 Carlo on 06.26.08 at 1:12 pm

I’ve switched almost exclusively to SQLite. I appreciate this great resource. Thanks!

#25 Kurt on 07.15.08 at 4:55 am

Very useful HowTo for SQLite beginners!

But, as Lyndon already said, “DataSource” should be changed to “Data Source”.

So users wouldn’t be scared by the first error that says: “Data Source cannot be empty”. Well, but they will scared by the message: “Right and Full Outer Joins are not currently supported.

#26 Kurt on 07.15.08 at 5:01 am

“ass-kicking like a transactional Jackie Chan” sounds great! :-)

#27 Steve on 08.02.08 at 12:42 pm

Been using it in VB6 for years now. Now that I have ‘finally’ moved to .NET this was my first stop. SQLite is GREAT!!!

#28 Roshawn on 08.12.08 at 7:46 pm

Man, I was just looking for info regarding SQLite and I found this post. Awesome!

If test results are positive, I’ll ditch SQL Server Express 2005 in favor of SQLite.

#29 jd on 09.22.08 at 10:01 am

hey, so i created the database. now i have to place my application on a client machine. how do i copy that db across?

#30 Vikas on 09.24.08 at 11:31 pm

Too good, the first hit on google took me to right place and I don’t need to see somewhere else.

#31 Superfox on 09.27.08 at 11:24 am

that`s what i was looking for. great tutorial. thanks. :)

#32 Superfox on 09.27.08 at 1:41 pm

damn… why i didn`t read all the comments? was really confused because of that unsplited “DataSource”.

#33 ramesh on 10.04.08 at 4:39 am

Thanks Mike,
Awsome article, but the DataSource –> Data Source thingie took me two days to figure out!

#34 Mike West on 10.20.08 at 8:42 pm

Thanks for the info.
You tutorial in 3 min is extremely help.
Going to save it for further reference.
Would pay for more help.

#35 Jay on 12.18.08 at 7:01 am

Thanks for a Quick Start Step for Sqlite.

#36 Jameel on 12.26.08 at 10:19 am

Dude awesome post , needed to get up and running with SQLite and this was brilliant , didn need to go hunt around for what was needed.
Thanks Again

#37 Adeena on 01.02.09 at 3:13 pm

Your article is really good and thanks for the reference to the SQLIte Admin tool. VERY HELPFUL.
However, in MS Visual C# 2008 Express - when I go to add the connection, the “Test connection” works just fine but when I hit “ok” I get this error:
“Could not load file or assembly ‘Microsoft.VisualStudio.Data, Version=8.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a’ or one of its dependencies. The located assembly’s manifest definition does not match the assembly reference.”
any idea?

#38 Albin on 01.04.09 at 12:32 pm

SQlite is the shit, I really like it.

Got it to work with C# very fast.

Thanks

#39 Logan on 01.11.09 at 12:29 am

Great post. @jb et al.: Here’s a handy (if a little thin) comparison between embeddable .NET databases, including SQLite (which gets good grades):

http://www.samsaffron.com/blog/archive/2007/02/16/7.aspx

#40 JWC on 01.22.09 at 6:31 pm

Your post is riddled with hilarity. Thanks!

#41 JWC on 01.22.09 at 6:42 pm

Your SQLite LINQ provider link is broken

#42 SQLite - a database for .Net applications by Eric Rodriguez | Wavyx on 01.30.09 at 6:35 am

[...] recommend this 2 quick tutorials to get started with SQLite in a .Net environment: SQLite on .NET - Get up and running in 3 minutes and Using sqlite with [...]

#43 fangsang on 02.05.09 at 1:59 am

it’s well encapsulated

#44 JCollum on 02.28.09 at 7:36 pm

Hey can anyone address deploying SQLite on shared hosting? I’ve heard that this assembly requires full-trust to run but shared hosting will only allow you to use medium trust assemblies. This is a deal-killer for me.

#45 Abe on 03.20.09 at 5:30 pm

This is easily the best blog post I’ve read this year. I love Sqlite, and I think the combination of humor and pragmatism is great.

#46 pp on 04.08.09 at 4:58 pm

you are great! thank you:D

#47 DJ on 06.09.09 at 6:06 am

Hey JCollum
Have a look at hosting with somewhere like WebHost4Life. They support full trust on shared hosting.
I had the same problem when trying to use the ReportViewer control.

#48 JCollum on 06.09.09 at 10:07 am

Actually, I got it working on discountasp.net and posted about it here: http://codingmostly.blogspot.com/2009/05/why-you-need-fiddler-if-youre.html

#49 CFC on 06.12.09 at 1:42 pm

Thanks! Great post!

#50 T7 on 06.30.09 at 7:50 pm

Great post. I’ve used Sqlite in several of my projects and am currently using it in my latest endeavor - Task Se7en.

#51 Jonas on 07.01.09 at 6:02 am

Great post! Just wondered if there was a .NET-implementation for SQLite, and look what I found! also, the below catch block made my day! :-D

catch {
// Catching exceptions is for communists
}

#52 Mat on 07.11.09 at 12:43 am

Thank you, thank you, thank you!

#53 jerreychen on 07.22.09 at 11:28 am

Great post! Thanks for sharing.

#54 Me on 08.05.09 at 10:43 am

Great Post Man! Thanks a bunch. >:D< Hugssssss!

#55 Gabriel on 08.15.09 at 8:18 am

// Catching exceptions is for communists

Hahaha ! That’s the best of my day !

#56 Sam Y. on 09.04.09 at 9:53 am

Very cool, I have been using it with objective-c for a while now and have few iPhone apps with it as the backend. I was looking for how to set it on a windows machine and using it with C# now. Great write up. Thanks.

#57 sandrar on 09.10.09 at 2:34 pm

Hi! I was surfing and found your blog post… nice! I love your blog. :) Cheers! Sandra. R.

#58 megan fox fhm on 09.21.09 at 8:33 am

Sign: gnvkb Hello!!! kawtz and 1144chkasnpywu and 2217 My Comments: Thanks. We look forward to hearing from you again and for your opinions on the world of work.

#59 TetfrantDar on 09.27.09 at 11:22 pm

I think you made some good points in your post.

#60 Ron on 10.16.09 at 9:04 am

Thanks for the useful post. Just in case you did not catch it - I assume you meant I DIGRESS rather than I DIGEST?

#61 Mark on 11.10.09 at 9:58 pm

Thanks, but you could have reduced the 3 minutes to 3 seconds by just saying “grab the ADO.NET 2.0 Provider for SQLite from sourceforge”. That’s about the same amount information.

#62 Serkan on 01.07.10 at 7:54 pm

Refactored the sample code into a class. Using transaction for multiple statements. (Sample usage included. )

/* sample usage:
*
* using (var db = new DataBase())
{
db.ExecuteNonQuery(”DROP TABLE if exists names”);
db.ExecuteNonQuery(”CREATE TABLE if not exists names(name)”);
db.ExecuteNonQuery(string.Format(@”insert into names (name) values (’name{0}’)”, DateTime.Now.Ticks));
var names = db.Select(”select * from names”);
Assert.IsTrue(names.Rows.Count == 1);
}

using (var db = new DataBase())
{
db.ExecuteNonQuery(string.Format(@”insert into names (name) values (’name{0}’)”, DateTime.Now.Ticks));
var names = db.Select(”select * from names”);
Assert.IsTrue(names.Rows.Count == 2);
}
*
*/
public class DataBase : IDisposable
{
private SQLiteConnection _sqLiteConnection;
private SQLiteTransaction _transaction;

private SQLiteConnection Connection
{
get
{
if (_sqLiteConnection==null)
{
_sqLiteConnection = new SQLiteConnection(”Data Source=C:testDB.s3db”);
_sqLiteConnection.Open();
_transaction = _sqLiteConnection.BeginTransaction();
}
return _sqLiteConnection;
}
}

public void Dispose()
{
if (_sqLiteConnection == null) return;
if (_transaction != null) _transaction.Commit();
_sqLiteConnection.Close();
}

public DataTable Select(string select)
{
var dt = new DataTable();
var reader = new SQLiteCommand(Connection) {CommandText = select}.ExecuteReader();
dt.Load(reader);
reader.Close();
return dt;
}

public int ExecuteNonQuery(string nonQuery)
{
return new SQLiteCommand(Connection) {CommandText = nonQuery}.ExecuteNonQuery();
}

public object ExecuteScalar(string scalar)
{
var value = new SQLiteCommand(Connection) {CommandText = scalar}.ExecuteScalar();
return value ?? “”;
}
}

Leave a Comment