Click here to Skip to main content
13,196,946 members (53,001 online)
Click here to Skip to main content
Add your own
alternative version

Stats

29.4K views
1.7K downloads
16 bookmarked
Posted 20 Nov 2015

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

, 20 Nov 2015
Rate this:
Please Sign up or sign in to vote.
With a DEMO WPF application (WinNotif)

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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Lộc Nguyễn
Software Developer
United States United States
currentJob = new ComputerScientist("ND-1550", "02/04", "SPAWAR SSC PAC");

while (live) {
try {
learn();
code();
} catch (Exception ex) {
recover();
}
}

You may also be interested in...

Pro
Pro

Comments and Discussions

 
QuestionSource code Pin
Member 110760215-Dec-16 0:44
memberMember 110760215-Dec-16 0:44 
GeneralRe: Source code Pin
Member 124876987-Aug-17 17:33
memberMember 124876987-Aug-17 17:33 
GeneralMy vote of 3 Pin
Just Russell23-Nov-15 8:17
professionalJust Russell23-Nov-15 8:17 
QuestionThere is across platform problem in SQLite . Pin
Alenty21-Nov-15 3:08
memberAlenty21-Nov-15 3:08 
AnswerRe: There is across platform problem in SQLite . Pin
Lộc Nguyễn21-Nov-15 4:40
professionalLộc Nguyễn21-Nov-15 4:40 
GeneralRe: There is across platform problem in SQLite . Pin
Alenty25-Jan-16 21:05
memberAlenty25-Jan-16 21:05 
AnswerRe: There is across platform problem in SQLite . Pin
Lộc Nguyễn31-Jan-16 9:19
professionalLộc Nguyễn31-Jan-16 9:19 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.171020.1 | Last Updated 21 Nov 2015
Article Copyright 2015 by Lộc Nguyễn
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid