InfoQ

InfoQ

News

My Bookmarks

Login or Register to enable bookmarks for unlimited time.

The content has been bookmarked!

There was an error bookmarking this content! Please retry.

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). 

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.

Copyright? by ben aaronson Posted
Re: Copyright? by Jim Nasium Posted
Re: Copyright? by Floyd Marinescu Posted
joe blow comment removed by Floyd Marinescu Posted
  1. Back to top

    Copyright?

    by ben aaronson

  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.

  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.

  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.

Educational Content

Collaboration: At the Extremities of Extreme

Jason Ayers share the observations he made watching a team of developers collaborating in real time on the same code base, pushing XP, pair programming and continuous integration to their extremes.

Yesod Web Framework

Michael Snoyman presents Yesod, a web framework written in Haskell and containing a web server, templating, ORM, libraries (templating, gravatar, etc.).

Transactions without Transactions

Richard Kreuter and Kyle Banker on how to avoid classical RDBMS transactional systems by using compensation mechanisms, transactional messaging or transactional procedures.

Attila Szegedi on JVM and GC Performance Tuning at Twitter

Attila Szegedi talks about performance tuning Java and Scala programs at Twitter: how to approach GC problems, the importance of asynchronous I/O, when to use MySQL/Cassandra/Redis, and much more.

10 tips on how to prevent business value risk

One category of risk that project teams need to ensure they address is business value failure – delivering a product that fails to provide value for the business investor.

Interview: Software Systems Architecture: Working With Stakeholders Using Viewpoints and Perspectives

InfoQ spoke to the authors of Software Systems Architecture on a couple of new topics, the System Context viewpoint and Agile, which have been added to the second edition.

Beauty Is in the Eye of the Beholder

Alex Papadimoulis discusses ugly code, where it comes from, how to avoid it, and how to get rid of it.

Architecting Visa for Massive Scale and Continuous Innovation

John Davies examines Visa’s architecture and shows how enterprises have architected complex integrations incorporating Hadoop, memcached, Ruby on Rails, and others to deliver innovative solutions.