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

In this post we will look at building a simple command line .NET Core application that works with SQL Server. The database is created using the Code First development paradigm. The database table will look like this:

1) Create a working directory named InstituteSqlServer.

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

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

4) Open Program.cs , change the namespace to InstituteSqlServer and save the file.

5) To build the “Hello World” command-line app and run it, execute the following commands from the command prompt:

dotnet restore
dotnet build
dotnet run

6) Since we will be accessing SQL Server 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.SqlServer": "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": [
      "portable-net45+win8+dnxcore50",
      "portable-net45+win8"
    ]
  }
}

8) At this stage, it is appropriate to restore all the additional dependencies that we will be using. Therefore, run the following command at the command prompt:

dotnet restore

9) Add a folder named “Models” and add to it a C# class file named Student.cs. Add the following code to Student.cs:

using System;
using System.Collections.Generic;
namespace InstituteSqlServer.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 sample 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": "Server=(localdb)\\mssqllocaldb;Database=InstituteDB;Trusted_Connection=True;"
  }
}

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 InstituteSqlServer.Models {
    public class Utility {
        public static string GetConnectionString(string key) {
            // Defines the sources of configuration information for the
            // application.
            var builder = new ConfigurationBuilder()
                .AddJsonFile("appsettings.json");
            // 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 InstituteSqlServer.Models {
    public class InstituteContext : DbContext {
        public DbSet<Student> Students { get; set; }
        protected override void OnModelCreating(ModelBuilder builder) {
            builder.Entity<Student>().HasKey(m => m.Id);
            base.OnModelCreating(builder);
        }
        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)  {
            string constr = Utility.GetConnectionString("ConnectionStrings:DefaultConnection");
            optionsBuilder.UseSqlServer(constr);
        }
    }
}

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 by the application. 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 command:

dotnet build

15) It is now possible for us to 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 are now in a position to add and retrieve data to and from the database. Add the following two methods to your Program.cs file:

private static void addStudents() {
    using (var db = new InstituteContext()) {
        db.Students.AddRange(Student.GetSampleStudents());
        var count = db.SaveChanges();
        Console.WriteLine("{0} records saved to database", count);
    }
}
private static void displayStudents() {
    using (var db = new InstituteContext()) {
        Console.WriteLine();
        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 in Program.cs.

18) Replace the Main() method on Program.cs with the following:

public static void Main(string[] args) {
    addStudents();
    displayStudents();
}

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 these commands from the command prompt:

dotnet build
dotnet run

20) You can celebrate if you see the following results:

Project InstituteSqlServer (.NETCoreApp,Version=v1.0) was previously compiled. Skipping compilation.
5 records saved to database
All students in database:

1 Ann Lee
2 Bob Doe
3 Sue Douglas
4 Tom Brown
5 Joe Mason

In order to use this application on Linux or the Mac, it is advisable to use the SQLite database instead of SQL Server. This is the topic for my next post.

Related Posts:

SQLite DB and Code-First .NET Core console application
PostgreSQL DB and Code-First .NET Core console application