Saturday, October 1, 2016

SQLite DB and Code-First .NET Core console application

In a previous post (, I showed how to build a console .NET Core app that uses the EF Code First model with SQL Server. In this post we will use SQLite instead. The advantage of SQLite is that it is free, stable and runs on Windows, Linux, Mac, IoS, Android, etc…

A useful utility that comes in handy when working with the SQLite database is SQLiteStudio. Download SQLiteStudio from: Extract the ZIP file and place contents in a separate folder. Run SQLiteStudio.exe.

In this post we will look at building a simple console .NET Core application that works with SQLite. The database is created using the Code First development paradigm. We will build a simple console .NET Core app that uses the following Student entity:

1) Create a working directory named InstituteSQLite.

2) From within a command prompt in that folder, type: dotnet new. This creates a Hello-World .NET Core console application.

3) Using Visual Studio Code, navigate to the InstituteSQLite folder to open the project.

4) Open Program.cs and change the namespace to InstituteSQLite then save the file.

5) To build the “Hello World” console app and run it, execute the following from the command prompt:
dotnet restoredotnet build                 (optional)dotnet run
6) Since we will be accessing SQLite using Entity Framework, we will need to add the following to the dependencies block of the project.json file:
"Microsoft.EntityFrameworkCore": "1.0.1",
"Microsoft.EntityFrameworkCore.Sqlite": "1.0.1",
"Microsoft.EntityFrameworkCore.Tools": "1.0.0-preview2-final",
"Microsoft.Extensions.Configuration": "1.0.0",
"Microsoft.Extensions.Configuration.Json": "1.0.0"

7) Add another tools section to the project.json file as follows:
"tools": {
    "Microsoft.EntityFrameworkCore.Tools": {
      "version": "1.0.0-preview2-final",
      "imports": [
8) At this stage it is appropriate to restore all the new additional dependencies that we will be using. Therefore, execute the following at the command prompt:
dotnet restore
9) Add a folder named “Models” and add to it a C# class file named Student.cs. with the following code:
using System;
using System.Collections.Generic;
namespace InstituteSQLite.Models {
  public class Student {
    public int Id { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string Major { get; set; }
    public DateTime DateOfBirth { get; set; }

    public static List<Student> GetSampleStudents()   {
      List<Student> students = new List<Student>() {
        new Student {
          FirstName = "Ann",
          LastName = "Lee",
          Major = "Medicine",
          DateOfBirth = Convert.ToDateTime("2004/09/09")
        new Student
          FirstName = "Bob",
          LastName = "Doe",
          Major = "Engineering",
          DateOfBirth = Convert.ToDateTime("2005/09/09")
        new Student {
          FirstName = "Sue",
          LastName = "Douglas",
          Major = "Pharmacy",
          DateOfBirth = Convert.ToDateTime("2006/01/01")
        new Student {
          FirstName = "Tom",
          LastName = "Brown",
          Major = "Business",
          DateOfBirth = Convert.ToDateTime("2000/09/09")
        new Student {
          FirstName = "Joe",
          LastName = "Mason",
          Major = "Health",
          DateOfBirth = Convert.ToDateTime("2001/01/01")
      return students;
The above code defines the properties of a Student class and adds a static method GetSampleStudents() that retrieves some dummy data.

10) We will save the database connection string in a configuration JSON file. Create a file named appsettings.json in the root of your project with the following content:
  "ConnectionStrings": {
    "DefaultConnection": "Data Source=institute.sqlite"
11) It is necessary to have a helper method that reads name/value pairs from the appsettings.json configuration file. To this end, we will create a class file named Utility.cs in the Models folder that fulfills this task. This file contains the following code:
using Microsoft.Extensions.Configuration;
namespace InstituteSQLite.Models {
    public class Utility {
        public static string GetConnectionString(string key) {
            // Defines the sources of configuration information for the
            // application.
            var builder = new ConfigurationBuilder()

            // Create the configuration object that the application will
            // use to retrieve configuration information.
            var configuration = builder.Build();

            // Retrieve the configuration information.
            var configValue = configuration[key];

            return configValue;
12) Add another C# class file to the Models folder named InstituteContext.cs with the following code:
using Microsoft.EntityFrameworkCore;
namespace InstituteSQLite.Models {
    public class InstituteContext : DbContext {
        public DbSet<Student> Students { get; set; }

        protected override void OnModelCreating(ModelBuilder builder) {
            builder.Entity<Student>().HasKey(m => m.Id);
        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)  {
            string constr = Utility.GetConnectionString("ConnectionStrings:DefaultConnection");
            string path = System.IO.Directory.GetCurrentDirectory();
            constr = constr.Replace("=", "=" + path + "\\");

13) When the application is built, the .dll file is created in a directory somewhere under the bin folder. We need to make sure that the appsettings.json file is also copied to the same directory as the dll file so that it can be read. This is accomplished by adding the following to the “buildOptions” section of the project.json file:
"copyToOutput": {
  "include": [ "appsettings.json" ]
14) It is time to build our application. At a command-prompt in the project folder, type the following:
dotnet build
15) We can now create the database using Entity Framework’s code-first paradigm. While in the command prompt, execute the following two EF commands:
dotnet ef migrations add FirstMigration
dotnet ef database update
Upon completion of the above EF commands, the database will have been created.

16) We can now add and retrieve data to and from the database. Add the following methods to your Program.cs file:
private static void addStudents() {
    using (var db = new InstituteContext()) {
        var count = db.SaveChanges();
        Console.WriteLine("{0} records saved to database", count);
private static void displayStudents() {
    using (var db = new InstituteContext()) {
        Console.WriteLine("All students in database:");
        foreach (var s in db.Students) {
            Console.WriteLine("{0}\t{1}\t{2}", s.Id, s.FirstName, s.LastName);
17) Resolve the namespace for the Student and InstituteContext classes.

18) Replace the Main() method in Program.cs with the following code:
public static void Main(string[] args) {
19) This final step is the most exciting. If you run the application, it should add data to the Students table in the database and retrieve its contents. Can’t wait, lets do it. Execute the following from the command prompt:
dotnet build
dotnet run
20) If all goes well, you see the following results:
All students in database:
1       Ann     Lee
2       Bob     Doe
3       Sue     Douglas
4       Tom     Brown
5       Joe     Mason

Because we are using SQLite with .NET Core, this application will run equally well on Windows, Linux and Mac platforms.

Related Posts:

SQL Server LocalDB and Code-First .NET Core console application
PostgreSQL DB and Code-First .NET Core console application

No comments:

Post a Comment