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: 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.
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.
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.
62 comments ↓
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)
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.
@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.
I laugh in the face of groin injuries! SQLite sounds pretty “dope.”
Great write-up. Are there any resources comparing all non-Sql Server alternatives (MySql, SQLite, etc.) for .NET?
–jb
[...] SQLite on .NET in 3 minutes - A good quick start guide for the SQLite embedded database [...]
Really good article. Very interesting.
[...] 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 [...]
[...] SQLite on .NET in 3mins link [...]
[...] 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 , [...]
[...] SQLite in 3 Minuten erklärt - wie man .NET und SQLite zusammen benutzen kann. [...]
[...] 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? [...]
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.
[...] 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 [...]
Seems ‘DataSource’ should be ‘Data Source’.
Otherwise, a great article!
Great great great
Smile xml you are kicked to the trash
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?
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.
[...] 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 [...]
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!
[...] 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 [...]
[...] Short tutorial for .Net Published Apr 09 2008, 10:37 PM by sergiopereira Filed under: .NET, Tools [...]
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
I’ve switched almost exclusively to SQLite. I appreciate this great resource. Thanks!
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.
“ass-kicking like a transactional Jackie Chan” sounds great!
Been using it in VB6 for years now. Now that I have ‘finally’ moved to .NET this was my first stop. SQLite is GREAT!!!
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.
hey, so i created the database. now i have to place my application on a client machine. how do i copy that db across?
Too good, the first hit on google took me to right place and I don’t need to see somewhere else.
that`s what i was looking for. great tutorial. thanks.
damn… why i didn`t read all the comments? was really confused because of that unsplited “DataSource”.
Thanks Mike,
Awsome article, but the DataSource –> Data Source thingie took me two days to figure out!
Thanks for the info.
You tutorial in 3 min is extremely help.
Going to save it for further reference.
Would pay for more help.
Thanks for a Quick Start Step for Sqlite.
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
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?
SQlite is the shit, I really like it.
Got it to work with C# very fast.
Thanks
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
Your post is riddled with hilarity. Thanks!
Your SQLite LINQ provider link is broken
[...] 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 [...]
it’s well encapsulated
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.
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.
you are great! thank you:D
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.
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
Thanks! Great post!
Great post. I’ve used Sqlite in several of my projects and am currently using it in my latest endeavor - Task Se7en.
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!
catch {
// Catching exceptions is for communists
}
Thank you, thank you, thank you!
Great post! Thanks for sharing.
Great Post Man! Thanks a bunch. >:D< Hugssssss!
// Catching exceptions is for communists
Hahaha ! That’s the best of my day !
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.
Hi! I was surfing and found your blog post… nice! I love your blog.
Cheers! Sandra. R.
Thanks for the useful post. Just in case you did not catch it - I assume you meant I DIGRESS rather than I DIGEST?
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.
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 ?? “”;
}
}
That’s great ! Thank you so much !
I just wrote a blog entry that was inspired by this one (although it expands on the usage a bit) and wanted to let you know. I used a modified version of some of your code, but expanded it significantly and credited you for the parts you wrote, so feel free to check it out and let me know what you think!
http://brennydoogles.wordpress.com/2010/02/26/using-sqlite-with-c-2/
Leave a Comment