Subscribe to Developer Soapbox

Stay up to date! Get all the latest & greatest posts delivered straight to your inbox

.NET Core

Connecting to a Sqlite Database using .Net Core

  • Developer

    Our resident opinionated developer.

    More posts by Developer.

Developer

• 2 min read
Requirements

In order to follow along with this post, you will need:

  1. .Net Core SDK (version 3.1.0 used here, but example should work for any version if you adjust the dependency versions accordingly).
  2. Any text editor, but Visual Studio Code is highly recommended.
1. Create New Project

Since the purpose of this post is just to illustrate a basic connection to Sqlite, we will be using a simple console application. Using either Windows Powershell or Linux/Mac terminal:

mkdir sqlite_app
cd sqlite_app
dotnet new console 
code .

The last command (code .) will open the project in Visual Studio Code, assuming you are currently inside the project’s directory.

2. Update the .csproj File

You should have two files inside your project: sqliteapp.csproj and Program.cs.  Open the sqliteapp.csproj and add the required Entity Framework dependencies.  Your files should look as follows:

<Project Sdk="Microsoft.NET.Sdk">
    <PropertyGroup>
    <OutputType>Exe</OutputType>
    <TargetFramework>netcoreapp3.1</TargetFramework>
    </PropertyGroup>
    
    <ItemGroup>
    <PackageReference Include="Microsoft.EntityFrameworkCore.Sqlite" Version="3.1.0" />
    </ItemGroup>

</Project>

Go back to the terminal and restore the project by using the command below.  Restoring the project will fetch our newly added NuGet dependencies.

dotnet restore
3. Update the Program.cs
using System;
using Microsoft.Data.Sqlite;

namespace sqlite_app
{
    class Program
    {
        static void Main(string[] args)
        {
            var connectionStringBuilder = new SqliteConnectionStringBuilder();

            //Use DB in project directory.  If it does not exist, create it:
            connectionStringBuilder.DataSource = "./SqliteDB.db";

            using (var connection = new SqliteConnection(connectionStringBuilder.ConnectionString))
            {
                connection.Open();

                //Create a table (drop if already exists first):
                
                var delTableCmd = connection.CreateCommand();
                delTableCmd.CommandText = "DROP TABLE IF EXISTS favorite_beers";
                delTableCmd.ExecuteNonQuery();

                var createTableCmd = connection.CreateCommand();
                createTableCmd.CommandText = "CREATE TABLE favorite_beers(name VARCHAR(50))";
                createTableCmd.ExecuteNonQuery();

                //Seed some data:
                using (var transaction = connection.BeginTransaction())
                {
                    var insertCmd = connection.CreateCommand();

                    insertCmd.CommandText = "INSERT INTO favorite_beers VALUES('LAGUNITAS IPA')";
                    insertCmd.ExecuteNonQuery();

                    insertCmd.CommandText = "INSERT INTO favorite_beers VALUES('JAI ALAI IPA')";
                    insertCmd.ExecuteNonQuery();

                    insertCmd.CommandText = "INSERT INTO favorite_beers VALUES('RANGER IPA')";
                    insertCmd.ExecuteNonQuery();

                    transaction.Commit();
                }

                //Read the newly inserted data:
                var selectCmd = connection.CreateCommand();
                selectCmd.CommandText = "SELECT name FROM favorite_beers";

                using (var reader = selectCmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        var message = reader.GetString(0);
                        Console.WriteLine(message);
                    }
                }


            }
        }
    }
}
4. Run Project

Go back to the terminal and run:

dotnet run

You should see the list of three beers print on your command line terminal:

LAGUNITAS IPA
JAI ALAI IPA
RANGER IPA
Developer Soapbox © 2021