Using SQLite: An Example of CRUD Operations in C#.NET

• Download DEMO and DB file - WinNotif v.1

Intro

SQLite is a perfect solution to some applications that need serverless, zero-configuration, transactional (relational database *), single data file.

(*) Need to enable manually:

sqlite> PRAGMA foreign_keys = ON;

In this post, I'll show you how to use System.Data.SQLite, an ADO.NET wrapper for SQLite.

Also, I'd like to introduce you a program that makes use of SQLite.

Get System.Data.SQLite Package

Using Visual Studio's GuGet Package Manager for your project, search for 'SQLite':

If you plan to use Entity Framework, or LINQ, choose yours. If you want to use all of them, install System.Data.SQLite. But if you only use the 'traditional style' SQL query syntax, just install the Core, which is the one I use. Visual Studio will automatically add reference for you. We are now ready to code query the database!

Introduce the DEMO (WinNotif program)

WinNotif is a program to display quotes on the screen. The quotes are from single sqlite database file. Each quote has properties such as: author source, language, and text content. Author, Language, and Quote are the 3 models (C# classes).

The language model is defined as follow:

public class Language
{
    int _id;
    string _langTitle;
    public int Id
    {
        get { return _id; }
        set { _id = value; }
    }
    public string LangTitle
    {
        get { return _langTitle; }
        set { _langTitle = value; }
    }
}

Database Schema

Data Type

Note that SQLite data types are different from SQL Server or other database systems. While text (string, varchar) and numeric (integer, double) are common and easily converted based on our needs. I found the date time is a little different. To avoid the problems (headache) that might happen across platforms in the future (.NET, Java, PHP), I choose not to use SQLite's built-in Date and Time Functions.

There are 3 main tables corresponding to our models: Language, Author, and Quote table.

(*) Note: I would recommend you NEVER hesitate to set constraints on your database tables. It enforces the integrity of your data, and can only make your database better.

Language Table

CREATE TABLE Language (
    Id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE,
    LangTitle TEXT NOT NULL UNIQUE CHECK (LangTitle <> '')
)

Author Table

CREATE TABLE Author (
    Id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE,
    Name TEXT NOT NULL UNIQUE,
    Nationality TEXT CHECK (Nationality <> ''),
    BirthDay INTEGER CHECK (BirthDay > 0 AND BirthDay < 32),
    BirthMonth INTEGER CHECK (BirthMonth > 0 AND BirthMonth < 13),
    BirthYear INTEGER CHECK (BirthYear > 0 AND BirthYear < 5000)
)

Quote Table

CREATE TABLE Quote (
    Id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE,
    Content TEXT NOT NULL UNIQUE CHECK (Content <> ''),
    AuthorId INTEGER NOT NULL DEFAULT '1' REFERENCES Author (Id) _
ON DELETE RESTRICT ON UPDATE RESTRICT,
    LanguageId INTEGER NOT NULL DEFAULT '1' _
    REFERENCES Language (Id) ON DELETE RESTRICT ON UPDATE RESTRICT)

As you see, the foreign key constraints establishes the relationship between the Quote and Language table, via the Quote's LanguageId and Language's Id columns. In other word, the LanguageId (which is the foreign key) column of the Quote table maps to the Id (which is the primary key) column of the Language table. The same for Quote and Author table. Language and Author tables are called parent tables. Quote is called child table.

As a result, you cannot add a new quote to the Quote (child) table with the language id that doesn't exist in the Language (parent) table. You cannot update the current valid quote to make its language id that doesn't correspond to a row in Language table. You also cannot delete a row in the Language (parent) table as long as that row still has reference to a row in the Quote (child) table.

With your dummy data inserted, or by the sample database file download from the link above. We are now ready to write C#.NET code to do CRUD (Create, Read, Update, and Delete) operations.

Example Code

Make use to add the directive: using System.Data.SQLite; first.

Connection String

string connectionString = @"Data Source=PATH_TO_DB_FILE\...\file.ABC; 
Version=3; FailIfMissing=True; Foreign Keys=True;";

(*) VERY important to add: Foreign Keys=True; option explicitly, since SQLite doesn't enforce foreign key constraints automatically. Or else, all your efforts to keep your data valid and integrity will be wasted!!

(*) PATH_TO_DB_FILE\...\file.ABC can be absolute or relative path the DB file.

Retrieve Data

Retrieve all languages from the Language table. If argument language id passed is 0, then select all.

public static List<language> GetLanguages(int langId)
{
    List<language> langs = new List<language>();
    try
    {
        using (SQLiteConnection conn = new SQLiteConnection(connectionString))
        {
            conn.Open();
            string sql = "SELECT * FROM Language WHERE Id = " + langId;
            if (langId == 0)
            {
                sql = "SELECT * FROM Language";
            }
            using (SQLiteCommand cmd = new SQLiteCommand(sql, conn))
            {
                using (SQLiteDataReader reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        Language la = new Language();
                        la.LangTitle = reader["LangTitle"].ToString();
                        la.Id = Int32.Parse(reader["Id"].ToString());
                        langs.Add(la);
                    }
                }
            }
            conn.Close();
        }
    }
    catch (SQLiteException e)
    {
        ...
    }
    return langs;
}

Update Data

Now, we use parameterized queries to make it securer for database operations.

public static int UpdateLang(int id, string newLangTitle)
{
    int result = -1;
    using (SQLiteConnection conn = new SQLiteConnection(connectionString))
    {
        conn.Open();
        using (SQLiteCommand cmd = new SQLiteCommand(conn))
        {
            cmd.CommandText = "UPDATE Language "
                + "SET LangTitle = @Lang "
                + "WHERE Id = @Id";
            cmd.Prepare();
            cmd.Parameters.AddWithValue("@Lang", newLangTitle);
            cmd.Parameters.AddWithValue("@Id", id);
            try
            {
                result = cmd.ExecuteNonQuery();
            }
            catch (SQLiteException)
            {
                ...
            }
        }
        conn.Close();
    }
    return result;
}

The result, if greater than -1, is the number of rows affected by the update operation.

Insert Data

Very similar to the update operation:

public static int AddLang(string langTitle)
{
    int result = -1;
    using (SQLiteConnection conn = new SQLiteConnection(connectionString))
    {
        conn.Open();
        using (SQLiteCommand cmd = new SQLiteCommand(conn))
        {
            cmd.CommandText = "INSERT INTO Language(LangTitle) VALUES (@Lang)";
            cmd.Prepare();
            cmd.Parameters.AddWithValue("@Lang", langTitle);
            try
            {
                result = cmd.ExecuteNonQuery();
            }
            catch (SQLiteException e)
            {
                ...
            }
        }
        conn.Close();
    }
    return result;
}

Delete Data

public static int DeleteLang(int id)
{
    int result = -1;
    using (SQLiteConnection conn = new SQLiteConnection(connectionString))
    {
        conn.Open();
        using (SQLiteCommand cmd = new SQLiteCommand(conn))
        {
            cmd.CommandText = "DELETE FROM Language WHERE Id = @I";
            cmd.Prepare();
            cmd.Parameters.AddWithValue("@I", id);
            try
            {
                result = cmd.ExecuteNonQuery();
            }
            catch (SQLiteException e)
            {
                ...
            }
        }
        conn.Close();
    }
    return result;
}

Update Quote

Similiar example to update language, just a few more parameters:

public static int UpdateQuote(int id, string newContent, int newAuthId, int newLangId)
{
    int result = -1;
    using (SQLiteConnection conn = new SQLiteConnection(connectionString))
    {
        conn.Open();
        using (SQLiteCommand cmd = new SQLiteCommand(conn))
        {
            cmd.CommandText = "UPDATE Quote "
                + "SET Content = @C, AuthorId = @A, LanguageId = @L "
                + "WHERE Id = @I";
            cmd.Prepare();
            cmd.Parameters.AddWithValue("@C", newContent);
            cmd.Parameters.AddWithValue("@A", newAuthId);
            cmd.Parameters.AddWithValue("@L", newLangId);
            cmd.Parameters.AddWithValue("@I", id);
            try
            {
                result = cmd.ExecuteNonQuery();
            }
            catch (SQLiteException)
            {
                ...
            }
        }
        conn.Close();
    }
    return result;
}

Conclusion

SQLite is a very good option for embedded devices, and for standalone applications just like this one. There is no need to configure and administrate database server. A single file data is all we need.

You are welcome to try my newest app, WinNotif v.1 The program is under development. More features will be added later. Your inputs will be helpful.