Test Driven Development (TDD) with xunit and .NET Core 1.1

It is assumed that you have .NET Core 1.1 installed on your computer. If you do not already have it, you can obtain .NET Core 1.1 from https://www.microsoft.com/net/download/core.
We will create the test cases for a ET Core 1.1 application named FizzBuzz. Here’s how it goes. If a number is divisible by 3 then you will call out Fizz. If a number is divisible by 5 then you will call out Buzz. If a number is divisible by 3 and 5 then you will call out FizzBuzz. Otherwise, you will just call out the number itself.

Here are some examples:

2	12
4	1 2 Fizz 4
5	1 2 Fizz 4 Buzz
15	1 2 Fizz 4 Buzz Fizz 7 8 Fizz Fuzz 11 Fizz 13 14 FizzBuzz

The above table would represent our four test cases.

Directory & file structure

Create the following directory structure in your workspace directory:

Creating the business logic project

There are a number of built-in templates that are available to the developer. You con view these templates by typing the following in a terminal window:

dotnet new template --list

In a command line, while in the src/FizzBuzzLibrary directory, create the library project files by executing the following command:

dotnet new classlib

Similarly, in a command line while the test/FizzBuzzTests directory, create the test project files by executing the following command:

dotnet new xunit

If you open the root FizzBuzz folder in Visual Studio Code, your directories & files will look like this:
 


Rename the src/FizzBuzzLibrary/Class1.cs file to src/FizzBuzzLibrary/FizzBuzzMaster.cs. Open src/FizzBuzzLibrary/FizzBuzzMaster.cs in the editor and change the class name from Class1 to FizzBuzzMaster.

Similarly, rename test/FizzBuzzTests/UnitTest1.cs to test/FizzBuzzTests/FizzBuzzTestsMaster.cs. Open test/FizzBuzzTests/FizzBuzzTestsMaster.cs in the editor and change the class name from UnitTest1 to FizzBuzzTestsMaster.

Open the src/FizzBuzzLibrary/FizzBuzzMaster.cs file in the editor. Make sure the namespace is FizzBuzzLibrary and add the following method to the class:

public string GetResult(int nmbr) {  
  string result = "";
  return result;
}

You will notice that the above method is destined to fail. This is the fundamental principal of test driven development whereby methods are initially built to fail.

We should create our test cases. Open test/FizzBuzzTests/FizzBuzzTestsMaster.cs in the editor and replace the Test1() method with the following four test cases:

[Fact]
public void Given2Result12() {
  FizzBuzzMaster fbm = new FizzBuzzMaster();
  var expected = "1 2 ";
  var actual = fbm.GetResult(2);
  Assert.Equal(expected, actual);
}
[Fact]
public void Given4Result12fizz4() {
  FizzBuzzMaster fbm = new FizzBuzzMaster();
  var expected = "1 2 Fizz 4 ";
  var actual = fbm.GetResult(4);
  Assert.Equal(expected, actual);
}
[Fact]
public void Given5Result12fizz4buzz() {
  FizzBuzzMaster fbm = new FizzBuzzMaster();
  var expected = "1 2 Fizz 4 Buzz ";
  var actual = fbm.GetResult(5);
  Assert.Equal(expected, actual);
}
[Fact]
public void Given15Result12fizz4buzzfizz78fizzbuzz11fizzfizz1314fizzbuzz() {
  FizzBuzzMaster fbm = new FizzBuzzMaster();
  var expected = "1 2 Fizz 4 Buzz Fizz 7 8 Fizz Buzz 11 Fizz 13 14 FizzBuzz ";
  var actual = fbm.GetResult(15);
  Assert.Equal(expected, actual);
}

Import the following namespace at the top of the above file test/FizzBuzzTests/FizzBuzzTestsMaster.cs:

using FizzBuzzLibrary;

We need to make a reference to the Library project from the test project. This is done by adding the following reference to the test/FizzBuzzTests/FizzBuzzTests.csproj file inside the <ItemGroup> XML block:

<ProjectReference Include="..\..\src\FizzBuzzLibrary\FizzBuzzLibrary.csproj" />
The <ItemGroup> XML block in file test/FizzBuzzTests/FizzBuzzTests.csproj now looks like this:
<ItemGroup>
  <PackageReference Include="Microsoft.NET.Test.Sdk" Version="15.0.0" />
  <PackageReference Include="xunit" Version="2.2.0" />
  <PackageReference Include="xunit.runner.visualstudio" Version="2.2.0" />
  <ProjectReference Include="..\..\src\FizzBuzzLibrary\FizzBuzzLibrary.csproj" />
</ItemGroup>

Running our tests

We have built our preliminary business logic and test cases. In addition, we referenced our business logic application into our test cases application. Now let us test things out.

To restore and build our Library application, execute the following commands from within the root FizzBuzz directory:

dotnet restore src/FizzBuzzLibrary
dotnet build src/FizzBuzzLibrary
dotnet restore test/FizzBuzzTests
dotnet build test/FizzBuzzTests

It is now time to run the actual tests. This is done by executing the following command also from within the root FizzBuss directory:

dotnet test test/FizzBuzzTests/FizzBuzzTests.csproj

The test execution shows the following results:

Build started, please wait...
Build completed.
Test run for F:\4870\FizzBuzz\test\FizzBuzzTests\bin\Debug\netcoreapp1.1\FizzBuzzTests.dll(.NETCoreApp,Version=v1.1)
Microsoft (R) Test Execution Command Line Tool Version 15.0.0.0
Copyright (c) Microsoft Corporation.  All rights reserved.
Starting test execution, please wait...
[xUnit.net 00:00:00.5936585]   Discovering: FizzBuzzTests
[xUnit.net 00:00:00.7169039]   Discovered:  FizzBuzzTests
[xUnit.net 00:00:00.7640234]   Starting:    FizzBuzzTests
[xUnit.net 00:00:00.9084551]     FizzBuzzTests.FizzBuzzTestsMaster.Given4Result12fizz4 [FAIL]
[xUnit.net 00:00:00.9105612]       Assert.Equal() Failure
[xUnit.net 00:00:00.9107578]                 ↓ (pos 0)
[xUnit.net 00:00:00.9108391]       Expected: 1 2 Fizz 4
[xUnit.net 00:00:00.9109069]       Actual:  
[xUnit.net 00:00:00.9109578]                 ↑ (pos 0)
[xUnit.net 00:00:00.9125774]       Stack Trace:
[xUnit.net 00:00:00.9143555]         F:\4870\FizzBuzz\test\FizzBuzzTests\FizzBuzzTestsMaster.cs(22,0): at FizzBuzzTests.FizzBuzzTestsMaster.Given4Result12fizz4()
[xUnit.net 00:00:00.9308443]     FizzBuzzTests.FizzBuzzTestsMaster.Given2Result12 [FAIL]
[xUnit.net 00:00:00.9309901]       Assert.Equal() Failure
[xUnit.net 00:00:00.9310410]                 ↓ (pos 0)
[xUnit.net 00:00:00.9310890]       Expected: 1 2
[xUnit.net 00:00:00.9311260]       Actual:  
[xUnit.net 00:00:00.9312167]                 ↑ (pos 0)
[xUnit.net 00:00:00.9313206]       Stack Trace:
[xUnit.net 00:00:00.9314166]         F:\4870\FizzBuzz\test\FizzBuzzTests\FizzBuzzTestsMaster.cs(14,0): at FizzBuzzTests.FizzBuzzTestsMaster.Given2Result12()
[xUnit.net 00:00:00.9319873]     FizzBuzzTests.FizzBuzzTestsMaster.Given5Result12fizz4buzz [FAIL]
[xUnit.net 00:00:00.9321133]       Assert.Equal() Failure
[xUnit.net 00:00:00.9321913]                 ↓ (pos 0)
[xUnit.net 00:00:00.9322648]       Expected: 1 2 Fizz 4 Buzz
[xUnit.net 00:00:00.9323297]       Actual:  
[xUnit.net 00:00:00.9323876]                 ↑ (pos 0)
[xUnit.net 00:00:00.9324578]       Stack Trace:
[xUnit.net 00:00:00.9325473]         F:\4870\FizzBuzz\test\FizzBuzzTests\FizzBuzzTestsMaster.cs(30,0): at FizzBuzzTests.FizzBuzzTestsMaster.Given5Result12fizz4buzz()
[xUnit.net 00:00:00.9327846]     FizzBuzzTests.FizzBuzzTestsMaster.Given15Result12fizz4buzzfizz78fizzbuzz11fizzfizz1314fizzbuzz [FAIL]
[xUnit.net 00:00:00.9328761]       Assert.Equal() Failure
[xUnit.net 00:00:00.9329459]                 ↓ (pos 0)
[xUnit.net 00:00:00.9330050]       Expected: 1 2 Fizz 4 Buzz Fizz 7 8 Fizz Buzz 11 Fiz···
[xUnit.net 00:00:00.9331245]       Actual:  
[xUnit.net 00:00:00.9332037]                 ↑ (pos 0)
[xUnit.net 00:00:00.9332891]       Stack Trace:
[xUnit.net 00:00:00.9333733]         F:\4870\FizzBuzz\test\FizzBuzzTests\FizzBuzzTestsMaster.cs(38,0): at FizzBuzzTests.FizzBuzzTestsMaster.Given15Result12fizz4buzzfizz78fizzbuzz11fizzfizz1314fizzbuzz()
[xUnit.net 00:00:00.9356137]   Finished:    FizzBuzzTests
Failed   FizzBuzzTests.FizzBuzzTestsMaster.Given4Result12fizz4
Error Message:
Assert.Equal() Failure
          ↓ (pos 0)
Expected: 1 2 Fizz 4
Actual:  
          ↑ (pos 0)
Stack Trace:
   at FizzBuzzTests.FizzBuzzTestsMaster.Given4Result12fizz4() in F:\4870\FizzBuzz\test\FizzBuzzTests\FizzBuzzTestsMaster.cs:line 22
Failed   FizzBuzzTests.FizzBuzzTestsMaster.Given2Result12
Error Message:
Assert.Equal() Failure
          ↓ (pos 0)
Expected: 1 2
Actual:  
          ↑ (pos 0)
Stack Trace:
   at FizzBuzzTests.FizzBuzzTestsMaster.Given2Result12() in F:\4870\FizzBuzz\test\FizzBuzzTests\FizzBuzzTestsMaster.cs:line 14
Failed   FizzBuzzTests.FizzBuzzTestsMaster.Given5Result12fizz4buzz
Error Message:
Assert.Equal() Failure
          ↓ (pos 0)
Expected: 1 2 Fizz 4 Buzz
Actual:  
          ↑ (pos 0)
Stack Trace:
   at FizzBuzzTests.FizzBuzzTestsMaster.Given5Result12fizz4buzz() in F:\4870\FizzBuzz\test\FizzBuzzTests\FizzBuzzTestsMaster.cs:line 30
Failed   FizzBuzzTests.FizzBuzzTestsMaster.Given15Result12fizz4buzzfizz78fizzbuzz11fizzfizz1314fizzbuzz
Error Message:
Assert.Equal() Failure
          ↓ (pos 0)
Expected: 1 2 Fizz 4 Buzz Fizz 7 8 Fizz Buzz 11 Fiz···
Actual:  
          ↑ (pos 0)
Stack Trace:
   at FizzBuzzTests.FizzBuzzTestsMaster.Given15Result12fizz4buzzfizz78fizzbuzz11fizzfizz1314fizzbuzz() in F:\4870\FizzBuzz\test\FizzBuzzTests\FizzBuzzTestsMaster.cs:line 38
Total tests: 4. Passed: 0. Failed: 4. Skipped: 0.
Test Run Failed.
Test execution time: 2.1205 Seconds

Let’s fix all four failed tests by fixing our GetResult() method in src/FizzBuzzLibrary/FizzBuzzMaster.cs. Replace the GetResult() method with the following code:

public string GetResult(int nmbr) {  
    string result = "";
    for (int ndx=1; ndx<nmbr+1; ndx++) {
      if (ndx % 3 == 0 && ndx % 5 ==0) {
      result += "FizzBuzz ";
      } else if (ndx % 5 ==0 ) {
      result += "Buzz ";
      } else if (ndx % 3 ==0 ) {
      result += "Fizz ";                  
      }
      else
      result += ndx.ToString() + " ";
    }
    return result;
}

Build and run your tests again. This should be the outcome:

Build started, please wait...
Build completed.
Test run for F:\4870\FizzBuzz\test\FizzBuzzTests\bin\Debug\netcoreapp1.1\FizzBuzzTests.dll(.NETCoreApp,Version=v1.1)
Microsoft (R) Test Execution Command Line Tool Version 15.0.0.0
Copyright (c) Microsoft Corporation.  All rights reserved.
Starting test execution, please wait...
[xUnit.net 00:00:00.5944016]   Discovering: FizzBuzzTests
[xUnit.net 00:00:00.7260788]   Discovered:  FizzBuzzTests
[xUnit.net 00:00:00.7727980]   Starting:    FizzBuzzTests
[xUnit.net 00:00:00.9098086]   Finished:    FizzBuzzTests
Total tests: 4. Passed: 4. Failed: 0. Skipped: 0.
Test Run Successful.
Test execution time: 1.9927 Seconds

Rejoice that all our tests have successfully passed.

Introduction to Entity Framework 6.x

Watch my video that introduces Entity Framework 6.x to beginners.

PostgreSQL DB and Code-First .NET Core console application

In previous posts I showed how to build console .NET Core applications that use the EF Code First model to connect with SQL Server (http://blog.medhat.ca/2016/09/codefirst-with-net-core-command-line.html) and SQLite (http://blog.medhat.ca/2016/10/codefirst-with-net-core-console.html). In this post we will look at building a simple console .NET Core application that works with database server PostgreSQL. The database is created using the Code First development paradigm.
This is taken from https://www.postgresql.org/about/:

PostgreSQL is a powerful, open source object-relational database system. It has more than 15 years of active development and a proven architecture that has earned it a strong reputation for reliability, data integrity, and correctness. It runs on all major operating systems, including Linux, UNIX (AIX, BSD, HP-UX, SGI IRIX, Mac OS X, Solaris, Tru64), and Windows. It is fully ACID compliant, has full support for foreign keys, joins, views, triggers, and stored procedures (in multiple languages). It includes most SQL:2008 data types, including INTEGER, NUMERIC, BOOLEAN, CHAR, VARCHAR, DATE, INTERVAL, and TIMESTAMP.

You can download PostgreSQL from https://www.postgresql.org/download/. Make sure you remember the password you entered because it belongs to the default super user named “postgres”.
After you download and install the software you can run the PostgreSQL management console by finding and launching “pgAdmin 4”.



When you double click on “PostgreSQL 9.6”, you will be prompted for the “postgres” password which you entered during installation. You can save the password if you enable the “Save Password” checkbox.



In the real world, you would not save the password in a production environment. I am saving the password for convenience as I am, essentially, in a test environment.

You can create a database named “test” and experiment with these SQL queries:

CREATE  TABLE employee (id SERIAL PRIMARY KEY, name VARCHAR(30) );
INSERT INTO employee (name) VALUES ('SAM');
SELECT * FROM employee;

This would create an employee table in the test database:



The command-line utility of PostgreSQL is psql. Find and launch SQL Shell (psql). Choose default values for Server [localhost], Database [postgres], Port [5432] and Username [postgres]. However, when prompted for “Password for user postgres:”, put the password you entered during installation.
To use the test database, enter “\c test;”. Remember to terminate all psql commands with the semicolon (;).

Next, let us view the contents of our employee table. Enter the following in the psql shell:

SELECT * FROM employee;

You should see the following:

id | name
----+------
  1 | SAM
(1 row)

To exit psql type “\q”.

Let is start building a simple console .NET Core app that uses the following Student entity:


1) Create a working directory named InstitutePostgreSQL.

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 InstitutePostgreSQL folder to open the project.

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

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

dotnet restore
dotnet build        (optional)
dotnet run

6) Since we will be accessing PostgreSQL using Entity Framework, we will need to add the following to the dependencies block of the project.json file:

"Microsoft.EntityFrameworkCore": "1.0.1",
"Npgsql.EntityFrameworkCore.PostgreSQL": "1.0.2",
"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 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. Add the following code to Student.cs:

using System;
using System.Collections.Generic;
namespace InstitutePostgreSQL.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": "User ID=postgres;Password=password;Host=localhost;Port=5432;Database=InstituteDB;Pooling=true;"
  }
}

Ensure that you enter the correct password for username postgres.

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 InstitutePostgreSQL.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 InstitutePostgreSQL.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.UseNpgsql(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 appsettings.json 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 are now in a position to 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()) {
        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.

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

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

19) This final step is the most revealing. If you run the application, it should add data to the Students table in the database and retrieve its contents. Excited … 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

Since we are using PostgreSQL with .NET Core, this application can run equally well on Windows, Linux and the Mac. This is also true of SQLite discussed in a previous post.

Related Posts:

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

SQLite DB and Code-First .NET Core console application

In a previous post (http://blog.medhat.ca/2016/09/codefirst-with-net-core-command-line.html), 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: http://sqlitestudio.pl/?act=download. 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": [
        "portable-net45+win8+dnxcore50",
        "portable-net45+win8"
      ]
    }
  }

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()
                .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 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);
            base.OnModelCreating(builder);
        }
        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)  {
            string constr = Utility.GetConnectionString("ConnectionStrings:DefaultConnection");
            string path = System.IO.Directory.GetCurrentDirectory();
            constr = constr.Replace("=", "=" + path + "\\");
            optionsBuilder.UseSqlite(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. 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()) {
        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.

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

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

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