How to connect to a Sqlite Database using .Net Core

Steps for connection to a SqlLite using .Net Core

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 .
Copy

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>netcoreapp3.1</TargetFramework> 

 </PropertyGroup> <ItemGroup> <PackageReference Include="Microsoft.EntityFrameworkCore.Sqlite" Version="3.1.0" /> </ItemGroup> </Project>

Copy
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
Copy

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); 

 } 

 }

 }

 } 

 } 

}
Copy

4. Run Project

Go back to the terminal and run:dotnet run
Copy


You should see the list of three beers print on your command line terminal:LAGUNITAS IPA JAI ALAI IPA RANGER IPA