Getting started, using SQLite with .NET

This post is a collection from docs, links, code examples which I found on various blogs, websites etc. I have included the references to all my sources. Thanks for all the cool blogs, websites and of course SQLite which made it so easy to get started with this database.

UPDATE 02.03.2014:
With the release of SQLite, the SQLite database can be used together with EntityFramework > 6.0.0 much easier. It requires different configuration due to the new EF6 SQLite providers. See using-SQlite with Entity Framework 6 and the Repository Pattern


What is it? (Taken from )
SQLite is a single-file based database which is useful for testing and for embedding in applications. This means that all the information is stored in a single file on a file system and you use a SQLite library to open this file to read and write your data.

Here’s 3 links to the SQLite website which are worth reading:

Or here’s a summary of the limitations:

  • It’s Slow
  • Locks whole file for writing.
  • No caching mechanism of it’s own.
  • Database size restricted to 2GB in most cases.
  • Not fully SQL92 compliant.
  • Not very scalable.

Hello World Project (Taken from

using System;
using System.Data.SQLite;

namespace SqlLiteTest.HelloWorld
    public static class BasicCrud
        static SQLiteConnection m_dbConnection;

        public static void Run()
            // ### Create the database
            // SQLiteConnection.CreateFile("MyDatabase.sqlite");

            // ### Connect to the database
            m_dbConnection = new SQLiteConnection("Data Source=MyDatabase.sqlite;Version=3;");

            // ### Create a table
            // string sql = "CREATE TABLE highscores (name VARCHAR(20), score INT)";
            // SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection);
            // command.ExecuteNonQuery();

            // ### Add some data to the table
            // string sql = "insert into highscores (name, score) values ('Me', 3000)";
            // SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection);
            // command.ExecuteNonQuery();
            // sql = "insert into highscores (name, score) values ('Myself', 6000)";
            // command = new SQLiteCommand(sql, m_dbConnection);
            // command.ExecuteNonQuery();
            // sql = "insert into highscores (name, score) values ('And I', 9001)";
            // command = new SQLiteCommand(sql, m_dbConnection);
            // command.ExecuteNonQuery();

            // ### select the data
            string sql = "select * from highscores order by score desc";
            SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection);
            SQLiteDataReader reader = command.ExecuteReader();
            while (reader.Read())
                Console.WriteLine("Name: " + reader["name"] + "\tScore: " + reader["score"]);


Using Entity Framework 6 with SQLite (taken from )

This an example doesn’t work quiet as easy as described. To use Entity Framework 6 with SQLite, the source code of the SQLite package needs to be changed, or you must get the pre-release package from

Here’s the docs:

Important, if it still doesn’t work, check the App.config. The ‘remove invariant=”System.Data.SQLite”‘ must be the first node in DbProviderFactories

<?xml version="1.0" encoding="utf-8"?>
    <section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" />
    <!-- For more information on Entity Framework configuration, visit -->
    <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5" />
    <defaultConnectionFactory type="System.Data.Entity.Infrastructure.SqlConnectionFactory, EntityFramework" />
      <provider invariantName="System.Data.SQLite" type="System.Data.SQLite.SQLiteProviderServices, System.Data.SQLite.Linq, Version=, Culture=neutral, PublicKeyToken=db937bc2d44ff139" />
    <add name="ChinookContext" connectionString="Data Source=.\Chinook_Sqlite_AutoIncrementPKs.sqlite" providerName="System.Data.SQLite" />
      <remove invariant="System.Data.SQLite" />
      <add name="SQLite Data Provider" invariant="System.Data.SQLite" description="Data Provider for SQLite" type="System.Data.SQLite.SQLiteFactory, System.Data.SQLite" />  

As the database is a file, it needs to be added to the project and deployed if newer. You must be careful here when deploying to productive systems. Here’s the source code, thanks to for providing this example.

using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Data.Entity.ModelConfiguration.Conventions;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace EntitySqlite
    public static class RunEntityExample
        public static void RunExample()
            using (var context = new ChinookContext())
                var artists = from a in context.Artists
                              where a.Name.StartsWith("A")
                              orderby a.Name
                              select a;

                foreach (var artist in artists)

            // ### Add a db record
            using (var context = new ChinookContext())
                    new Artist
                        Name = "1Anberlin",
                        Albums =
                            new Album { Title = "1Cities" },
                            new Album { Title = "1New Surrender" }


    public class Album
        public long AlbumId { get; set; }
        public string Title { get; set; }

        public long ArtistId { get; set; }
        public virtual Artist Artist { get; set; }

    public class Artist
        public Artist()
            Albums = new List<Album>();

        public long ArtistId { get; set; }
        public string Name { get; set; }

        public virtual ICollection<Album> Albums { get; set; }

    class ChinookContext : DbContext
        public DbSet<Artist> Artists { get; set; }
        public DbSet<Album> Albums { get; set; }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
            // Chinook Database does not pluralize table names

SQLite Administration
Use the Firefox addon:

Here you can browser select, insert as you wish. It is easy to use and uncomplicated.

Link for SQL as understood by SQLite:

Example of a 1:n relationship in SQLite SQL

    [Name] NVARCHAR(120)

    [Title] NVARCHAR(160)  NOT NULL,
    [ArtistId] INTEGER  NOT NULL,
    FOREIGN KEY ([ArtistId]) REFERENCES [Artist] ([ArtistId]) 



  1. This link contains step by step insert update delete data using Sqlite with Csharp with Source code available to download

    1. Thanks
      greetings Damien

    2. kiquenet · · Reply

      Thanks, Himans. IMHO, better samples for minimize learning curve are real applications with full source code and good patterns.

      Maybe full samples step-by-step in 20 minutes with SQLite, the best Getting Started?

      Damien references is good.

  2. kiquenet · · Reply

    Great reference, Damien. Thanks a lot.

    Any updates in aug 2014 ?

    IMHO, better samples for minimize learning curve are real applications with full source code and good patterns.

  3. Damien, great tutorial, thanks.

    I would also add to this list Devart’s ADO.NET provider for SQLite with support for Entity Framework and ASP.NET –

Leave a Reply to Lindermann's Blog | Getting started, using SQLite with .NET Cancel reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: