Getting started with SQLite and C#

SQLite is a powerful, extremely light-weight transactional SQL database provider. SQLite is free and open source, and it does not require any server processes to handle it. SQLite is cross platform and runs on multiple architectures, making it ideal for use on the major operating systems such as Windows, Mac OS, Linux, and even lower powered devices such as PDAs, MP3 players etc.

Prerequisites

It is possible to get up and running with SQLite quickly in C#, by adding the System.Data.SQLite package to your project.

Open the package manager console window run the following;

Install-package System.Data.SQLite

Or alternatively, right click your project and click Manage NuGet Packages and search the online gallery for System.Data.SQLite.

A Helpful Wrapper Class

I have put together a very simple wrapper class that will get you up and running quickly. Here it is;

public class SQLiteDatabase
{
    private readonly string _dbConnection;

<pre><code>public SQLiteDatabase(string dataSource)
{
    _dbConnection = string.Format(&amp;quot;Data Source={0}&amp;quot;, dataSource);
}

public DataTable GetDataTable(SQLiteCommand command)
{
    if (command == null) throw new ArgumentNullException(&amp;quot;command&amp;quot;);

    using (SQLiteConnection connection = new SQLiteConnection(_dbConnection))
    {
        connection.Open();
        command.Connection = connection;

        using (SQLiteDataReader reader = command.ExecuteReader())
        {
            DataTable result = new DataTable();
            result.Load(reader);
            return result;
        }
    }
}

public SQLiteCommand GetCommand(string sql)
{
    if (string.IsNullOrEmpty(sql))
        throw new ArgumentNullException(&amp;quot;sql&amp;quot;);

    return new SQLiteCommand {CommandText = sql, CommandType = CommandType.Text};
}

public int ExecuteNonQuery(SQLiteCommand command)
{
    if (command == null) throw new ArgumentNullException(&amp;quot;command&amp;quot;);

    using (SQLiteConnection connection = new SQLiteConnection(_dbConnection))
    {
        connection.Open();
        command.Connection = connection;

        return command.ExecuteNonQuery();
    }
}
</code></pre>

}

This wrapper is loosely based on some code written by Mike Duncan.

Usage

The constructor takes the database path (the extension is completely up to you);

SQLiteDatabase database = new SQLiteDatabase("Customers.db");

To execute a standard non query command, you first create your command and call the ExecuteNonQuery method;

SQLiteCommand create = database.GetCommand("CREATE TABLE Customers (Id int PRIMARY KEY, Name nvarchar(256), Address nvarchar(256), PostCode nvarchar(256))");
database.ExecuteNonQuery(create);

SQLiteCommand populate = database.GetCommand("INSERT INTO Customers ('Id', 'Name', 'Address', 'PostCode') VALUES (1, 'Jon Preece', 'My House', 'NN11NN')");
int affected = database.ExecuteNonQuery(populate);

You can retrieve data from the database in the form of the timeless DataTable object;

SQLiteCommand a = database.GetCommand("SELECT * FROM Customers");
DataTable res = database.GetDataTable(a);

Summary

SQLite gives us the ability to write extremely light-weight databases, that have no dependence on third party products or servers. In order to properly utilise SQLite in all but the simplest applications, a major investment would be required to write additional architectural code to load SQL from external SQL files, so as to avoid writing in-line SQL.

If you want to browse your SQLite database, you’ll need the SQLite Browser from Sourceforge.

  • Hi Jon do you know if we can use Migration with Sqlite. I read this post http://hintdesk.com/sqlite-with-entity-framework-code-first-and-migration/ saying that it’s not supported by default. The post suggests also a method like Android for Migration. I would like to ask if you maybe have another solution

  • JohnStodden

    I cannot get the above code to work on my platform (VS2015 Community Edition) writing a .NET Winforms app in C#.

    1. I do not understand the and directives, and have removed them.

    2. The _dbConnection string needs to have "readonly" removed

    3. I can apparently create a database, but as soon as I try to create a table I get a message that it needs sql.interop.dll. However when I try to add a referece to sql.interop.dll to the project, it says it is not a valid dll.

    4. There is no option to right-click the project and manage NUGET packages.

    Can you confirm your code should work in a .NET environment? Thank you

    John B