How to Insert & Select data using SQLite in a C# console application

In this tutorial I will show you how to use SQLite in a C# console application.

For this tutorial I will be using Rider (which at the moment is my favourite IDE) to create a C# console application.

After you have created the project you will be greeted with a “Program.cs” file. Now create a class file named “DatabaseConfig” in the root of the project . This file will contain code that configures and creates our sqlite file.

The next step is to download a nuget package named “System.Data.Sqlite”

Once you have done this navigate to the “DatabaseConfig” file and add the “System.Data.Sqlite” package to the top of the page as well as the “System” and “System.IO” namespace.

In addition, within the “DatabaseConfig” class create a variable which is equal to type “SQLiteConnection”. We will use this to set the data source which will be an sqlite file and the data sources filename.

public SQLiteConnection mySQLiteConnection;

Now we need to create a constructor where we set the above variable equal to a value and use the “File.Exists” method of the “System.IO” namespace to create a sqlite file if it does not exist.

public DatabaseConfig()
{
    mySQLiteConnection = new SQLiteConnection("Data Source=wrestlerdatabase.sqlite3");
    if (!File.Exists("./wrestlerdatabase.sqlite3"))
    {
        
        SQLiteConnection.CreateFile("wrestlerdatabase.sqlite3");
        Console.WriteLine("Wrestler Database file created");
    }

}

So in the above code we are setting the SQLite connection equal to a file named “wrestlerdatabase” with a file extension of “sqlite3”. We are then doing an if statement to say if the file named “database” does not exist then create the file and write a message to the console.

Now that we have written this code navigate to the “Program.cs” file and create an instance of the “DatabaseConfig” class and add a “Console.ReadLine();”. Then run the project.

static void Main(string[] args)
{
    DatabaseConfig myDatabase = new DatabaseConfig();
    Console.ReadLine();
}

Since we have a constructor for this class and the constructor runs when we make an instance of the class the “database” file will be created as the code to do that is inside the constructor of the class “DatabaseConfig”.

After you have ran the project you will see a console message.

To see the file, right click on the “project” name on your IDE and click “reveal in finder” it may be worded differently on Windows such as “find in folder explorer” etc

You should now see folders such as “bin” and “obj”, navigate into the “bin” folder then go into the “Debug” folder then delve further and navigate into the “net5.0” and you should see a “wrestlerdatabase.sqlite3” file.

The next step requires you to have a piece of software downloaded called “DB Browser for SQLite” this allows you to perform CRUD operations on an SQLite database. It can be downloaded from the link below.

DB Browser for SQLite

After you have downloaded open this piece of software and click the “Open Database” button, this will then allow you to navigate to the “wrestlerdatabase.sqlite3” file that has been generated and open it.

Once you have done this click the “Create Table” button. For this tutorial we will be creating a “food” table so we will have an “FoodID” column that auto increments, a “FoodName” column and “FoodType” column.

Click “Ok” on the popup and now we have a table we can insert into using our code.

Also please remember to save the changes using “cmd + s” or “ctrl + s” in SQLite DB Browser otherwise the table won’t properly be created, I found this out after testing the project and an error printing in the console saying “no table named Food”. Navigate to the “Program.cs” file and create a string variable that contains an insert statement.

string insertQuery = "INSERT INTO Food (`FoodName`,`FoodType`) VALUES (@foodname,@foodtype)";

Below this string variable we will create an instance of the “SQLiteCommand” which will have 2 arguments. The first being the “insertQuery” variable and the second being the connection string.

SQLiteCommand mySQLiteCommand = new SQLiteCommand(insertQuery, myDatabase.mySQLiteConnection);

We now need to access the “Connection” property of “SQLiteCommand” and “Open” the connection.

mySQLiteCommand.Connection.Open();

Furthermore, we need to set the values of the parameters we will pass into the insert query.

mySQLiteCommand.Parameters.AddWithValue(parameterName,value);

The first argument will be the “parameterName” and the second value will be “value”. So our first “parameterName” is “foodname” and we will set a value of “strawberry” and we will give the second parameter name of “foodtype” a value of “fruit”.

mySQLiteCommand.Parameters.AddWithValue("@foodname", "Strawberry");
mySQLiteCommand.Parameters.AddWithValue("@foodtype", "Fruit");

We will then use a command named “ExecuteNonQuery”.

We can use this to execute an SQL statement which we are not returning anything from, however, “ExecuteNonQuery” returns the number of rows that were affected by the execution of the SQL. We will then need to close the connection after executing.

var fruitInsertResult = mySQLiteCommand.ExecuteNonQuery();
mySQLiteCommand.Connection.Close();

Run the project and you should the below in the console.

Now, you can change the parameter values to add a few more fruits or food in.

We will now write C# code to select the data from the database.

We will first use a “using” statement to create an sqlite connection and the instance of this connection called “connection” and the connection string we will pass into the “SQLiteConnection” will be “myDatabase.mySQLiteConnection”. “Using” is a very useful piece of syntax as it closes our connection for us saving us from manually having to close the connection.

using (SQLiteConnection connection = new SQLiteConnection(myDatabase.mySQLiteConnection)){
}

Inside of the “using” statement we will need to open the connection.

connection.Open();

We will now to write another “using” statement this time of type “SQLiteCommand” equal to creating a command on the connection. This will enable to set the “commandText” which will equate to the SQL query we want to run.

using (SQLiteCommand selectCMD = connection.CreateCommand())
{
    selectCMD.CommandText = "SELECT * FROM Food";
    selectCMD.CommandType = CommandType.Text;
}

We have set “CommandType” equal to text because we are running raw sql code and not a stored procedure.

We will now create a variable of type “SQLiteDataReader” which will enable us to read the selected data and perform actions on it. The variable will set equal to the “ExecuteReader” method of the “SQLiteCommand” variable “selectCMD”.

SQLiteDataReader myReader = selectCMD.ExecuteReader();

We can now retrieve and output to the console each of the returned data with the “myReader.Read()” code.

while (myReader.Read())
{
    Console.WriteLine(myReader["FoodName"] + " " + myReader["FoodType"]);
}

Now run the project and comment out the initial insert code and opening and closing of the first connection. So the code you should now have uncommented is the below.

using (SQLiteConnection connection = new SQLiteConnection(myDatabase.mySQLiteConnection))
{
    connection.Open();

    using (SQLiteCommand selectCMD = connection.CreateCommand())
    {
        selectCMD.CommandText = "SELECT * FROM Food";
        selectCMD.CommandType = CommandType.Text;
        SQLiteDataReader myReader = selectCMD.ExecuteReader();
        while (myReader.Read())
        {
            Console.WriteLine(myReader["FoodName"] + " " + myReader["FoodType"]);
        }
    }
    
}

Thank you very much for reading my article I am learning a lot about C#, ASP.NET Core and SQLite recently and I am having a blast.

Stay safe and have a good day :)