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 1.0.91.3, 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
https://damienbod.wordpress.com/2013/11/18/using-sqlite-with-entity-framework-6-and-the-repository-pattern/

Code: https://github.com/damienbod/SQLiteExamples

What is it? (Taken from http://schimpf.es/sqlite-vs-mysql )
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 http://blog.tigrangasparian.com/2012/02/09/getting-started-with-sqlite-in-c-part-one/)

using System;
using System.Data.SQLite;

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

        public static void Run()
        {
            // http://blog.tigrangasparian.com/2012/02/09/getting-started-with-sqlite-in-c-part-one/
            // 
            // ### Create the database
            // SQLiteConnection.CreateFile("MyDatabase.sqlite");

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

            // ### 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"]);

            Console.ReadKey();
        }
    }
}

Using Entity Framework 6 with SQLite (taken from brice-lambson.blogspot.ch )

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 https://www.myget.org/F/bricelam/

Here’s the docs:
http://brice-lambson.blogspot.ch/2013/06/systemdatasqlite-on-entity-framework-6.html
http://brice-lambson.blogspot.ch/2012/10/entity-framework-on-sqlite.html

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"?>
<configuration>
  <configSections>
    <section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" />
    <!-- For more information on Entity Framework configuration, visit http://go.microsoft.com/fwlink/?LinkID=237468 -->
  </configSections>
  <startup>
    <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5" />
  </startup>
  <entityFramework>
    <defaultConnectionFactory type="System.Data.Entity.Infrastructure.SqlConnectionFactory, EntityFramework" />
    <providers>
      <provider invariantName="System.Data.SQLite" type="System.Data.SQLite.SQLiteProviderServices, System.Data.SQLite.Linq, Version=2.0.88.0, Culture=neutral, PublicKeyToken=db937bc2d44ff139" />
    </providers>
  </entityFramework>
  <connectionStrings>
    <add name="ChinookContext" connectionString="Data Source=.\Chinook_Sqlite_AutoIncrementPKs.sqlite" providerName="System.Data.SQLite" />
  </connectionStrings>
  <system.data>
    <DbProviderFactories>
      <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" />  
    </DbProviderFactories>
  </system.data>
</configuration>

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 brice-lambson.blogspot.ch 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)
                {
                    Console.WriteLine(artist.Name);
                }
            }

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

            Console.ReadKey();
        }
    }

    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
            modelBuilder.Conventions
                .Remove<PluralizingTableNameConvention>();
        }
    }
}

SQLite Administration
Use the Firefox addon: https://addons.mozilla.org/de/firefox/addon/sqlite-manager/

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

Link for SQL as understood by SQLite: http://www.sqlite.org/lang.html

Example of a 1:n relationship in SQLite SQL

CREATE TABLE [Artist]
(
    [ArtistId] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    [Name] NVARCHAR(120)
)

CREATE TABLE [Album]
(
    [AlbumId] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    [Title] NVARCHAR(160)  NOT NULL,
    [ArtistId] INTEGER  NOT NULL,
    FOREIGN KEY ([ArtistId]) REFERENCES [Artist] ([ArtistId]) 
		ON DELETE NO ACTION ON UPDATE NO ACTION
)

Links:

http://system.data.sqlite.org

https://addons.mozilla.org/de/firefox/addon/sqlite-manager/

http://sqliteadmin.orbmu2k.de/

http://blog.tigrangasparian.com/2012/02/09/getting-started-with-sqlite-in-c-part-one/

http://brice-lambson.blogspot.ch/2012/10/entity-framework-on-sqlite.html
http://brice-lambson.blogspot.ch/2013/06/systemdatasqlite-on-entity-framework-6.html

http://www.connectionstrings.com/sqlite/

http://www.codeproject.com/Articles/236918/Using-SQLite-embedded-database-with-entity-framewo

http://stackoverflow.com/questions/2514785/how-to-create-an-entity-framework-model-from-an-existing-sqlite-database-in-visu

http://www.thomasbelser.net/2009/01/25/c-sharp-und-sqlite-eine-kleine-einfuhrung/

http://cplus.about.com/od/howtodothingsinc/ss/How-To-Use-Sqlite-From-Csharp.htm

http://stackoverflow.com/questions/11591002/how-can-i-use-sqlite-in-a-c-sharp-project