Up and Running with SQLite on .NET in 3 Minutes
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 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:
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 + "'";
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.
Todd Montgomery Dec 19, 2014