Learn to use C# to connect with SQLite database for Beginners on .NET Platform

In this tutorial, We will learn to connect with a Sqlite (sqlite3) database using C# on the .NET platform across multiple platforms like Windows, Linux and macOS. 

The tutorial guide is aimed at beginners who are new to ADO.NET interface for the SQLite library and wants to program the sqlite3 library using C# language for CRUD (Create, Read, Update and Delete) operations.

Contents 

• Source Codes
• What is SQLite
• What is ADO.NET
• Required .NET Namespaces for SQLite Database Access
• Compilers and IDE's used 
  • Creating a C# SQLite Project in Visual Studio
  • Creating a C# SQLite Project in .NET SDK CLI tools
• Connecting to a SQLite Database using C#
  • What are Connection Strings 
• Creating and adding data to SQLite Database using C#
  • Creating a table inside SQLite database 
  • Inserting Data to a SQLite Database
  • Reading Data from SQLite Database

Source Codes

You can find all the C# example code for creating and updating the SQLite Database from our GitHub Repository, We have also provided solution files for Visual Studio Community edition and .NET SDK CLI projects in our Repo. Link is shown below

• Browse Basic SQLite Database CRUD Operations using C# on .NET Platform

• Download  Basic SQLite Database CRUD Operations using C# on .NET Platform as Zip File

What is SQlite

SQLite is a self-contained, serverless, and opensource SQL database engine known for its ease of use and versatility. It doesn't need a separate server process like MySQL/MariaDB, PostgreSQL or Microsoft SQL Server, storing the entire database, including schema and data, in a single disk file.

SQLite and SQLite3 essentially refer to the same database engine.

SQLite supports ACID transactions, ensuring data integrity and reliability. As a relational database management system, it offers standard SQL features such as tables, indexes, views, and triggers, facilitating structured data manipulation.

SQLite is cross-platform and can run on various operating systems like Windows ,Linux,MacOS  including mobile platforms like iOS and Android, making it popular for embedded systems and mobile app development.

Being open-source (public domain software), SQLite is freely available for use, modification, and distribution without any licensing restrictions.

What is ADO.NET

ADO.NET  is a framework developed by Microsoft that allows developers to access and manipulate data stored in databases like MySQL, SQlite,MS SQL Server etc. It's a part of the larger .NET platform and provides a set of classes for interacting with data sources such as relational databases.

Key components of ADO.NET are

• Connection: A connection object represents a connection to a data source. It provides methods for opening and closing connections to the database. For Sqlite ,it is SQLiteConnection

• Command: A command object represents a SQL statement or a stored procedure that is executed against a data source. It includes methods for executing commands and retrieving results. For Sqlite ,it is SQLiteCommand

• DataReader: The DataReader object provides a forward-only, read-only stream of data from a data source. It's efficient for retrieving large volumes of data when you need to read records sequentially.

• DataAdapter: A DataAdapter serves as a bridge between a dataset and a data source. It populates a dataset with data from the data source and updates changes made to the dataset back to the data source.

• DataSet: A DataSet is an in-memory cache of data retrieved from a data source. It represents a disconnected set of tables that can hold multiple result sets from one or more data sources.

Required Namespaces 

For accessing the sqlite3 database using ADO.NET we need to include the System.Data.Sqlite namespace in our C# program .

System.Data.SQLite is a .NET data provider for SQLite ,which allows developers to interact with SQLite databases using the ADO.NET programming model. This namespace will provide classes and properties needed to access the SQLite database. 

Another namespace you can use is the one provided by the Microsoft called Microsoft.Data.Sqlite

Both libraries offer similar functionalities for working with SQLite databases, including connection management, command execution, data retrieval, data manipulation, and transaction support.

Compilers and IDE's used 

Here we will be working with C# and the .NET platform to develop software that will communicate with a SQlite Database on Disk. For dotnet development we can either use 

1. Visual Studio Community Edition or 

2. .NET SDK CLI tools

3. DB Browser for SQLite

All tools are free to download and use.

If you are not familiar with .NET SDK CLI tools ,do check our tutorial on how to use .NET SDK CLI here. 

We cannot use a Text editor like Notepad to view the contents (rows and tables) of the SQLite database created by our program .

We needed to use specialized program like DB Browser for SQLite to view the data inside our SQLite database.

DB Browser for SQLite can be downloaded from here.

Creating a C# SQLite Project in Visual Studio

We will be create a simple console project targeting the .NET platform as shown below. Give a name you like to the project and select a location to store your project.

Select the version of the .NET platform, you want to use ,Here i am using .NET 8.0 LTS version.

You will be greeted by the following screen

Now we need to install the System.Data.SQlite NuGet package to our project file ,so we can access the classes and methods needed to access the SQLite database.

Now go to Tools -> "Nuget Package Manager" ->"Manage NuGet Packages for Solution". As shown below

which will bring up a new tabbed window as shown below

Below the "Browse" tab search for System.Data.SQlite. Select the project you want to install, here ConsoleApp1 and Press " Install " as shown in the above image. Approve changes and Accept the Licenses for individual libraries.

The installed library can be seen under "Packages" inside the Solution Explorer as shown above.

Creating a C# SQLite Project in .NET SDK CLI tools

Here we will create a console project using .NET SDK CLI tools for interfacing a SQlite3 library with C# Application and then add the required NuGet Packages to the command line project.

Before you start, Make sure that the .NET SDK CLI is installed on your Windows or Linux System.

Now lets create a simple console project using the below command. Here C#_Sqlite_DB_Project is the name of my project, feel free to give any name for your project.

dotnet new console  -n C#_Sqlite_DB_Project

Now change directory to C#_Sqlite_DB_Project

You can add  System.Data.SQlite. Nuget package to your Project using the below command 

dotnet add package System.Data.SQlite

Connecting to a SQLite DB using C# 

Once you have chosen your IDE , Visual Studio or .NET SDK CLI and added the required NuGet packages to your project as shown in the above paragraphs .

It is time to start building our code to connect with the SQLite database. 

SQLite is a self contained and server less database ,that means that we don't have to install any database software to our PC. The SQLite database can be created by our code while running or it can talk with an existing SQLite database file sitting on your disk. 

Here we will create the SQLite Database programmatically while running the code.

The code below will create a SQLite database called MySqliteDatabase.db and open a connection to that database and then close it. If you run the below code example ,you can find a file named MySqliteDatabase.db in your directory.

If you are using Visual Studio 2022 community Edition,  

• MySqliteDatabase.db will be found in \bin\Debug\net8.0\MySqliteDatabase.db

Size of the sqlite file will be 0 bytes as we are not writing any data to the file.

using System.Data.SQLite; // classes for db access
namespace SqliteDatabaseAccess
{
   class MySqliteDBOps
   {
       public static void Main()
       {
           String ConnectionString = @"Data Source=MySqliteDatabase.db";           // MySqliteDatabase.db will be in "\bin\Debug\net8.0\MySqliteDatabase.db"
           SQLiteConnection MyConnection = new SQLiteConnection(ConnectionString); // Create a SqliteConnection object called Connection
           MyConnection.Open(); //open a connection to the database
           MyConnection.Close();//Close the connection with database
       }//End of Main()
   }//End of Class
}//End of namespace

In Visual Studio ,you will find the database in

•  \bin\Debug\net8.0\MySqliteDatabase.db (if selected Debug config)

or 

•  \bin\Release\net8.0\MySqliteDatabase.db (if selected Release config)

   

on .NET SDK ,you can find the database inside the directory.

Understanding Database Connection Strings

A connection string is a key-value pair formatted string that specifies parameters for connecting to a data source such as a database. These parameters include information like the server (or file path in case of SQLite), authentication details, database name, etc.

According to Microsoft's documentation and general usage in .NET, including C# on the .NET platform, a semicolon (;) at the end of the connection string is not necessary. This means connection string "Data Source=MySqliteDatabase.db" is perfectly valid and sufficient.

Semicolons are used to separate different key-value pairs within the connection string. For example (not related to our code, just for illustration purpose)

string connectionString = @"Data Source=MySqliteDatabase.db;Version=3;DateTimeFormat=Ticks;";

Here, each parameter (Data Source, Version, DateTimeFormat) is separated by semicolons.

Here 

SQLiteConnection MyConnection = new SQLiteConnection(ConnectionString); 

creates a SQLiteConnection object called MyConnection using the parameters given by the string ConnectionString. 

ConnectionString contains the path to the database which we want to connect to. If no database with the name MySqliteDatabase.db exist at that location , a new SQlite file is created with the same name.

Another way to write it 

SQLiteConnection MyConnection = new SQLiteConnection("Data Source=MySqliteDatabase.db");

You can also provide absolute path to the SQLite database, As shown below. You can create the database at any location of your choice ,just give the full path.

String ConnectionString = @"Data Source=H:\C#_Sqlite_DB_Project\MySqliteDatabase.db"; //You can create the db at any location of your choice
SQLiteConnection MyConnection = new SQLiteConnection(ConnectionString);

Here @ is used to preserve backslashes.

Creating and adding data to SQLite Database using C#

Here we will learn to create a SQLite database programmatically using C# and Create a table inside our database and fill it with data.

Creating a database happens automatically when you use the connection object SQLiteConnection to create a connection to a particular SQLite database, if that database exist it will open a connection with our C# program. if no SQLite database exists ,a new database of the name given in the connection string will be created at that location.

SQLiteConnection MyConnection = new SQLiteConnection(ConnectionString);

The newly created database will be empty and we need to add data to it to make it functional. 

Creating a table inside SQLite database 

Here we will learn to create Tables inside the newly created SQLite database using C# language and populate the table with data. First we will create a SQL query to create the table we want and then execute the query using the methods and classes provided by the System.Data.SQLite namespace.

We are going to create the following table.

it will contain columns for 

1. Id is the PRIMARY KEY uses INTEGER
2. Name uses VARCHAR (255 characters) datatype
3. Age uses INTEGER
4. DateOfBirth, uses TEXT and stores date in ISO8601 format (YYYY-MM-DD)
5. Email uses VARCHAR (255 characters) datatype
6. Price uses REAL datatype

as shown above.

So We will use the following SQL query to create the table and store it in a String Variable .

Here we are adding @"CREATE TA..........." in front of the SQL query to make it span multiple lines.

//SQL Query to Create a Table for Storing data 
String SQLQueryCreateTable = @"CREATE TABLE IF NOT EXISTS Customers(Id    INTEGER PRIMARY KEY, 
                                                                   Name  VARCHAR, 
                                                                   Age   INTEGER,
                                                                   DateOfBirth TEXT,
                                                                   Email VARCHAR,
                                                                   Price REAL)";

SQLiteCommand is a class in the System.Data.SQLite namespace that represents an SQL statement to execute against a SQLite database. It is part of the ADO.NET data access framework and provides methods and properties to execute SQL commands and retrieve data from a SQLite database.

Now create a SQLiteCommand object using the "SQL Query we want to execute" and the connection to the SQLite Database as shown below .Before running this, we have to open a connection to an existing database using SQLiteConnection as described in the previous section(above)

//Pseudocode 
SQLiteCommand(SQL Query we want to execute , Connection to the SQLite Database);

//C# Code 
SQLiteCommand MyCommand = new SQLiteCommand(SQLQueryCreateTable, MyConnection); 

Once a SQLiteCommand object MyCommand is created we can use the .ExecuteNonQuery() method to execute SQL commands against our  SQLite database.

ExecuteNonQuery() is used to execute SQL commands that do not return data. These commands typically modify data in the database, such as INSERT, UPDATE, DELETE statements, or SQL commands that alter database schema. 

After execution, It returns an integer that represents the number of rows affected by the SQL command. Here we are creating a Table ,Number of modified rows will be zero.

MyConnection.Open(); //open a connection to the database
MyCommand.ExecuteNonQuery();           // Execute Create the Table SQL query
MyConnection.Close();//Close the connection with database 

This will create a Table inside our database.

You can use DB Browser to check your Database after running the code.

if you look under Browse Data Tab of DB browser ,you will find the columns empty.

Inserting Data in to a SQLite Database using C#

Once we have created the table inside the SQLite dB, it is time to insert some data into the SQLite database using C#.first thing we should do is to construct a SQL query to insert the data into our database. 

Here we will use the SQL INSERT command. Unlike the usual SQL INSERT command that would look something like this

INSERT INTO Customers (Name, Age, DateOfBirth, Email, Price) VALUES ('John Doe', 30, '1980-04-01', 'johndoe@email.com', 234.68)

Here, we will be using parameterized queries, allowing us to safely pass parameters into SQL commands to prevent SQL injection attacks.

string  SQLQueryInsertData = "INSERT INTO Customers (Name,Age,DateOfBirth,Email,Price) VALUES (@Name,@Age,@DateOfBirth,@Email,@Price)";

Here we will assign our new SQL Query to the MyCommand object. Then we will add the values that we want to insert into the sqlite database using .Parameters.AddWithValue() method. 

This method will replace the parameter (Eg @Name)  with the corresponding value (Eg "Johnny Dove").

Once all the values for one row have been loaded into the MyCommand object, 

We call .ExecuteNonQuery(); method that will insert the new values into the Sqlite 3 database.

The method will return the value 1,indicating that one row has been effected.

MyCommand = new SQLiteCommand( SQLQueryInsertData, MyConnection);
MyCommand.Parameters.AddWithValue("@Name", "Johnny Dove");
MyCommand.Parameters.AddWithValue("@Age", 30);
MyCommand.Parameters.AddWithValue("@DateOfBirth", "1984-04-01"); //Sqlite Supports ISO 8601 date format YYYY-MM-DD
MyCommand.Parameters.AddWithValue("@Email", "johnnydoe@email.com");
MyCommand.Parameters.AddWithValue("@Price", 25324.68);
          
RowsChanged = MyCommand.ExecuteNonQuery();
Console.WriteLine($"No of Rows Changed = {RowsChanged}");

After running the code ,you can check the inserted values using DB Browser.

Reading Data from SQLite Database using C#