Blog

Introduction

SQLite DB is a lightweight and the most user-friendly embedded database engine available for private or commercial use, and the most attractive selling point is that it is absolutely free to use. But unfortunately, most of the time, developers struggle to find a good documentation that would explain how to get started without all the unnecessary complexity. This is what compelled me to write this post in the first place.

Not only will we learn how to use SQLite DB but also how to set up the project and to install all the necessary dependencies to make our job easier, So without any further delay let us begin.

📌 Outline

• Setting Up Our Project
• Installing The Required Packages
• Project Architecture
• Creating A Table in SQLite DB
• Writing To A Table in SQLite DB
• Reading From A Table in SQLite DB

📌 Prerequisites

• Basic Knowledge of SQL
• Visual Studio 2019/2022 IDE (Community/Professional/Enterprise)

Setting Up Our Project

Now before we get started follow these steps

Our project has been created.

Installing The Required Packages

Now that our project setup is complete, the next step is to acquire the necessary packages.

Install the following packages

• System.Data.SQLite
• Dapper

Project Architecture

Now for the most important part, an optimal code and file structure which would keep everything neat and tidy. When our Console App is created, it comes with a Program.cs file, but we will add two more .cs files to our project. For this example, We will create a simple Database which consists of data about the posts on this site. Add the following .cs files to your project

• Posts.cs
• SQLiteOps.cs

⚡ Posts.cs

This file will consist of all the attributes (or columns) which the database table called “posts” will consist of. For the sake of simplicity, only 3 columns will be added.

NOTE: Be careful to name the Database Columns the same way as you name these properties in Posts.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace CodeGitz
{
    internal class Posts
    {
        // The Post ID
        public int Id { get; set; }

        // The Post Title
        public String Name { get; set; }

        // The Post URL
        public String Url { get; set; }
    }
}

⚡ SQLiteOps.cs

This is the class which will handle all of our database connections and operations. It will consist of the following functions

Function	Purpose
private static String LoadConnectionString()	As the name suggests, this function returns the Database Connection string
public static void CreateTable(String CreateStatement)	This function takes the table creation query as parameter and creates the table in the database if it doesn’t exist already
public static List<Posts> ReadTable()	This function returns a list of “Posts” object we created in Posts.cs
public static void InsertRecords(List<String>InsertStatements)	This function takes a List of Insert statements and then inserts the records into the database table

Before continuing any further, In your SQLiteOps.cs file add the following code snippet

using System.Data;
using System.Data.SQLite;
using Dapper;

namespace CodeGitz
{
    internal static class SQLiteOps
    {
        private static String LoadConnectionString()
        {
            return "Data Source=.\\codegitz.db;Version=3;";
        }
    }
}

Replace the return statement with your own connection string, but this is the one I am using. Now we’re all set for Database Operations.

Creating A Table in SQLite DB

🔷 Program.cs

The first thing to do with a database is to create a table because without a table we can neither read from nor write our data to anything. Let’s get started, firstly you need to add these lines of code to Program.cs

namespace CodeGitz
{
    internal class Program
    {
        static void Main(string[] args)
        {
            // Create The "posts" Table
            CreateTable();

        }

        static void CreateTable()
        {
            // Create a Table With Columns Id, Name and URL Where ID is The Primary Key
            var CreateStatement = @"CREATE TABLE IF NOT EXISTS posts (Id INTEGER NOT NULL UNIQUE, Name TEXT NOT NULL,Url TEXT NOT NULL,PRIMARY KEY(Id AUTOINCREMENT))";
            
            // Call the CreateTable Function From SQLiteOps.cs
            SQLiteOps.CreateTable(CreateStatement);
        }
    }

}

This will call the CreateTable function from SQLiteOps.cs, but we don’t have that yet, so on to the next step.

🔷 SQLiteOps.cs

Now we will create a connection to the database after that our SQL Query will be executed the table will be created. In order for that to happen, add the following lines of code in SQLiteOps.cs

        // Creates a Table From The String Parameter
        public static void CreateTable(String CreateStatement)
        {

            // Load Database
            using(IDbConnection conn = new SQLiteConnection(LoadConnectionString()))
            {
                // Open Connection To Database
                conn.Open();

                // Create a Command Object For Use With Connection
                SQLiteCommand SqliteController = (SQLiteCommand)conn.CreateCommand();
                
                // Assign SQL Query
                SqliteController.CommandText = CreateStatement;
                
                // Execute Query
                SqliteController.ExecuteNonQuery();

                // Close The Connection
                conn.Close();
            }
        }

The code snippet above will do the following tasks

• Open connection to Database
• Assign SQL Query To Command Object
• Execute The SQL Query
• Finally, Close The Connection

⚡ Before Running The Code

This is how my Database structure looked like before I ran the code

⚡ After Running The Code

This is how my Database structure looks after the code runs

As evident above, the table has been created successfully and is now ready for use.

Writing To A Table in SQLite DB

🔷 Program.cs

As we did previously, go back to Program.cs. Then add these lines of code there.

        static void Main(string[] args)
        {
            // Insert Records Into "posts"
            InsertData();
        }

        static void InsertData()
        {
            List<String> InsertStatements = new();

            InsertStatements.Add("INSERT INTO posts (Name,Url) VALUES ('C# - Debugging StackOverflow Exception','https://codegitz.com/c-debugging-stackoverflow-exception/');");

            InsertStatements.Add("INSERT INTO posts (Name,Url) VALUES ('C# - How To Convert JSON Array To List?','https://codegitz.com/c-how-to-convert-json-array-to-list/');");

            InsertStatements.Add("INSERT INTO posts (Name,Url) VALUES ('C# - How To Implement Shortcut Keys in Winforms?','https://codegitz.com/how-to-implement-shortcut-keys-in-winforms/');");

            SQLiteOps.InsertRecords(InsertStatements);
        }

The following code snippet basically creates there Insert Query Statements and then InsertRecords function is called from SQLiteOps.cs

🔷 SQLiteOps.cs

Now we will write the function to Insert Data into the Database table “posts”, add the following lines of code in SQLiteOps.cs

        public static void InsertRecords(List<String>InsertStatements)
        {
            // Load Database
            using (IDbConnection conn = new SQLiteConnection(LoadConnectionString()))
            {
                // Open Connection To Database
                conn.Open();

                // Create a Command Object For Use With Connection
                SQLiteCommand SqliteController = (SQLiteCommand)conn.CreateCommand();

                // Loop Through Every Insert Statement
                foreach (String statement in InsertStatements)
                {
                    // Current Query
                    SqliteController.CommandText = statement;
                    
                    // Execute The SQL Query
                    SqliteController.ExecuteNonQuery();
                }

                // Close The Connection
                conn.Close();
            }
        }

The code snippet above will do the following tasks

• Open connection to Database
• Assign SQL Query To Command Object
• Loop through all Insert Statements
• Execute all Statements/Queries
• Finally, Close The Connection

⚡ Before Running The Code

Before running the code, as you can see there are no records

⚡ After Running The Code

After running the code, This is how the Database table looks

As evident above, the records were inserted and the database was updated successfully.

Reading From A Table in SQLite DB

🔷 Program.cs

As we did previously in previous two examples, go back to Program.cs again. Then add these lines of code.

namespace CodeGitz
{
    internal class Program
    {
        static void Main(string[] args)
        {
            // Display Data Entries
            ReadTable();
        }

        static void ReadTable()
        {

            List<Posts> CodeGitzPosts = SQLiteOps.ReadTable();

            foreach (Posts Post in CodeGitzPosts)
            {
                Console.WriteLine($"Post ID: {Post.Id} \nPost Title: {Post.Name} \nPost URL: {Post.Url}\n\n");
            }
        }
    }
}

The code snippet above calls ReadTable function from SQLiteOps.cs and receives a list of “Posts” objects and then the records are looped through and the data is printed on to the console.

🔷 SQLiteOps.cs

Now we will write the function to Read Data from the Database table “posts” and convert it to List, add the following lines of code in SQLiteOps.cs

        public static List<Posts> ReadTable()
        {
            // Load Database
            using(IDbConnection conn = new SQLiteConnection(LoadConnectionString()))
            {
                // SQL Query That Reads Everything From The Table "posts"
                var sql = "SELECT * FROM posts";

                // Execute Query and Convert it To Type List
                var output = conn.Query<Posts>(sql).ToList();

                // Return The List Of "posts" Objects
                return output;
            }
        } 

The code snippet above will do the following tasks

• Load Database
• Read All Records From Database Table “posts”
• Execute The Query and Convert The Result To The List of “posts” Objects
• Return The List of “posts” objects

⚡ Running The Code

After running the code, This is the output

Post ID: 1
Post Title: C# - Debugging StackOverflow Exception
Post URL: https://codegitz.com/c-debugging-stackoverflow-exception/


Post ID: 2
Post Title: C# - How To Convert JSON Array To List?
Post URL: https://codegitz.com/c-how-to-convert-json-array-to-list/


Post ID: 3
Post Title: C# - How To Implement Shortcut Keys in Winforms?
Post URL: https://codegitz.com/how-to-implement-shortcut-keys-in-winforms/

As seen above, everything is in working order and all the records were retrieved from the Database and displayed successfully.

Complete Source Code

🔷 Program.cs

namespace CodeGitz
{
    internal class Program
    {
        static void Main(string[] args)
        {
            // Create The "posts" Table
            CreateTable();

            // Insert Records Into "posts"
            InsertData();

            // Display Data Entries
            ReadTable();
        }

        static void CreateTable()
        {
            // Create a Table With Columns Id, Name and URL Where ID is The Primary Key
            var CreateStatement = @"CREATE TABLE IF NOT EXISTS posts (Id INTEGER NOT NULL UNIQUE, Name TEXT NOT NULL,Url TEXT NOT NULL,PRIMARY KEY(Id AUTOINCREMENT))";
            
            // Call the CreateTable Function From SQLiteOps.cs
            SQLiteOps.CreateTable(CreateStatement);
        }

        static void InsertData()
        {
            List<String> InsertStatements = new();
            InsertStatements.Add("INSERT INTO posts (Name,Url) VALUES ('C# - Debugging StackOverflow Exception','https://codegitz.com/c-debugging-stackoverflow-exception/');");
            InsertStatements.Add("INSERT INTO posts (Name,Url) VALUES ('C# - How To Convert JSON Array To List?','https://codegitz.com/c-how-to-convert-json-array-to-list/');");
            InsertStatements.Add("INSERT INTO posts (Name,Url) VALUES ('C# - How To Implement Shortcut Keys in Winforms?','https://codegitz.com/how-to-implement-shortcut-keys-in-winforms/');");

            SQLiteOps.InsertRecords(InsertStatements);
        }

        static void ReadTable()
        {

            List<Posts> CodeGitzPosts = SQLiteOps.ReadTable();

            foreach (Posts Post in CodeGitzPosts)
            {
                Console.WriteLine($"Post ID: {Post.Id} \nPost Title: {Post.Name} \nPost URL: {Post.Url}\n\n");
            }
        }
    }
}

🔷 SQLiteOps.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SQLite;
using Dapper;

namespace CodeGitz
{
    internal static class SQLiteOps
    {

        // Creates a Table From The String Parameter
        public static void CreateTable(String CreateStatement)
        {

            // Load Database
            using(IDbConnection conn = new SQLiteConnection(LoadConnectionString()))
            {
                // Open Connection To Database
                conn.Open();

                // Create a Command Object For Use With Connection
                SQLiteCommand SqliteController = (SQLiteCommand)conn.CreateCommand();
                
                // Assign SQL Query
                SqliteController.CommandText = CreateStatement;
                
                // Execute Query
                SqliteController.ExecuteNonQuery();

                // Close The Connection
                conn.Close();
            }
        }

        public static List<Posts> ReadTable()
        {
            // Load Database
            using(IDbConnection conn = new SQLiteConnection(LoadConnectionString()))
            {
                // SQL Query That Reads Everything From The Table "posts"
                var sql = "SELECT * FROM posts";

                // Execute Query and Convert it To Type List
                var output = conn.Query<Posts>(sql).ToList();

                // Return The List Of "posts" Objects
                return output;
            }
        } 

        public static void InsertRecords(List<String>InsertStatements)
        {
            // Load Database
            using (IDbConnection conn = new SQLiteConnection(LoadConnectionString()))
            {
                // Open Connection To Database
                conn.Open();

                // Create a Command Object For Use With Connection
                SQLiteCommand SqliteController = (SQLiteCommand)conn.CreateCommand();

                // Loop Through Every Insert Statement
                foreach (String statement in InsertStatements)
                {
                    // Current Query
                    SqliteController.CommandText = statement;
                    
                    // Execute The SQL Query
                    SqliteController.ExecuteNonQuery();
                }

                // Close The Connection
                conn.Close();
            }
        }

        private static String LoadConnectionString()
        {
            return "Data Source=.\\codegitz.db;Version=3;";
        }
    }
}

🔷 Github Repository

For all project files Consider Visiting My Github Repo

Conclusion

Working with SQLite DB is extremely efficient and easy. Not only is it easy, but well optimized as well. Portability and Flexibility also add more to its portfolio. We learned how to perform 3/4 database operations today. Create, Read and Delete using SQLite DB. At last, we came to the conclusion that this entire process is hassle-free and easy to get going. Thanks for Visiting.

For more, Visit C# – Archives