Up and Running with SQLite on .NET in 3 Minutes

Posted by Robert Bazinet on Jan 17, 2008

Sections

Architecture & Design,

Development,

Operations & Infrastructure

Topics

Data Access ,

.NET

Tags

nHibernate ,

SQLite ,

SubSonic ,

LINQ

SQLite is an open source database that has been growing in popularity.  It's footprint is small and is used in a wide-variety of types of applications.

What is SQLite?

SQLite is defined on the SQLite web site as:

SQLite is a software library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine.

SQLite is an embedded SQL database engine. Unlike most other SQL databases, SQLite does not have a separate server process. SQLite reads and writes directly to ordinary disk files. A complete SQL database with multiple tables, indices, triggers, and views, is contained in a single disk file. The database file format is cross-platform - you can freely copy a database between 32-bit and 64-bit systems or between big-endian and little-endian architectures. These features make SQLite a popular choice as an Application File Format. Think of SQLite not as a replacement for Oracle but as a replacement for fopen()

SQLite is the most widely deployed SQL database engine in the world. It is used in countless desktop computer applications as well as consumer electronic devices including cellphones, PDAs, and MP3 players. The source code for SQLite is in the public domain.

SQLite has primarily used on Linux and OSX platforms but is a viable and useful alternative to SQL Express and Access for applications on Windows running .NET, with minimal database requirements.

An article from a developer, Mike Duncan, gives a tutorial to setup SQLite on .NET in 3 minutes.  The tutorial proves very useful and the result is being able to use a lightweight database that can handle many tasks you can throw at it.

3 Minute Tutorial

Tutorial starts with first downloading 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

 Grab the DLL:

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

Download and install a SQLite GUI tool, SQLiteMan has a version for Windows which works very well.  The tutorial points out:

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.

The final step given is to create a SQLite database:

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

Once the System.Data.SQLite.dll is reference as part of the .NET project using it is as simple as using System.Data.SQLite at the top of your application.  A parameterized query would look like this using the ADO.NET wrapper:

string lookupValue;
using (SQLiteCommand cmd = cnn.CreateCommand())
{
  for (int i = 0; i < 100; i++)
  {
    lookupValue = getSomeLookupValue(i);
    cmd.CommandText = @"UPDATE [Foo] SET [Value] = [Value] + 1
                        WHERE [Customer] LIKE '" + lookupValue + "'";
    cmd.ExecuteNonQuery();

  }
}

Data Providers

SQLite has indeed made some inroads into .NET development and there have been some data providers created to be used with popular Object-Relational Mappers (O/RM). 

• SQLite NHibernate Provider
• SQLite Subsonic Provider
• SQLite LINQ Provider

The LINQ provider allows for .NET 3.5 developers to be able to take advantage of the new LINQ framework and use SQLite as the back-end data store.

SQLite can be a great alternative to Access or SQL Express to get database applications up and running quickly.   The databases are also able to be used on Linux and Mac OSX platforms, so creating an application whose database can be use cross-platform is easy.

1. Back to top

   Copyright?

   by ben aaronson

   www.mikeduncan.com/tech-post-ripoff/

   Reply

2. Back to top

   Re: Copyright?

   by Jim Nasium

   I don't see anyplace where the article is claiming to be original content. There is a link to the blog and original article.

   Reply

3. Back to top

   Re: Copyright?

   by Floyd Marinescu

   ben, this has been discussed in depth at the link you provided, a number of InfoQ editors including myself also weighed in. Thanks.

   Reply

4. Back to top

   joe blow comment removed

   by Floyd Marinescu

   Note to any readers, the comment by joe blow was removed due to the use of clearly unacceptable swearing.

   Reply

• Older >