/ .Net Core

Connecting to a Sqlite Database using .Net Core

Requirements
In order to follow along with this post, you will need **.Net Core 1.1 SDK** and **Visual Studio Code** installed.
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: sqlite_app.csproj and Program.cs. Open the sqlite_app.csproj and add the required Entity Framework dependencies. Your files should look as follows:
<Project Sdk="Microsoft.NET.Sdk">
  <PropertyGroup>
    <OutputType>Exe</OutputType>
    <TargetFramework>netcoreapp1.1</TargetFramework>
  </PropertyGroup>
  <ItemGroup>
    <PackageReference Include="Microsoft.EntityFrameworkCore.Sqlite" Version="1.1.1" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.Design" Version="1.1.1" PrivateAssets="All" />
  </ItemGroup>
  <ItemGroup>
    <DotNetCliToolReference Include="Microsoft.EntityFrameworkCore.Tools.DotNet" Version="1.0.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