DotNetCore – Sqlite with Entity Framework Code First and Migration

In last months there were 2 big frameworks for software development published: DotNetCore and Angular 2. They’re new and powerful. I’ve spent most of my free time to learn both of them. The combination of DotNetCore Web Api and Angular 2 give me a powerful development enviroment for building website or building web-view app (based on Apache Cordova). I’ve started to build some web apps with Angular 2 (for example YouTubeApp or my private dictionary EnDeVi). They are all my pet projects but it’s a good start for learning new stuffs. In this blog, I would like to write about DotNetCore and Sqlite like the previous post Sqlite with Entity Framework Code First and Migration. Basically there’s nothing new with this post, I just want to test how I can write a simple .NET core console connecting to Sqlite database.

1. Prerequisite

– Depending on your current system, you have a package to install to set up a DotNetCore project, more instructions at Microsoft .Net Core website. I have a Windows machine with Visual Studio 2015 Update 3. Therefore I don’t have to install any extra things.
– I use Visual Studio Code as my IDE. I recommend installing the C#-Extension to have support for editing C#, such as Syntax Highlighting, IntelliSense, Go to Definition, Find All References, etc…

– Please read this previous post Sqlite with Entity Framework Code First and Migration before reading this one because I’ll skip the part of setting up DbContext and Migrations and focus only on setting project with .NET core.

2. Buiding simple Console app connecting with Sqlite database

– After installing or setting up .NET core environment, you can check the current version of your .NET core by executing this command

dotnet --version

– Create a folder for your project, move to that folder and execute this command to create a .NET core console app.

dotnet new -t console

where t defines the type of the project. Valid values for C# are console, web, lib and xunittest and for F# only console is valid.
– The command will create 2 files in your project folder: Program.cs and project.json. The Program.cs just prints out Hello World! message

public class Program
{
	public static void Main(string[] args)
	{
		Console.WriteLine("Hello World!");
	}
}

– Now we try to build and run the app. In the Terminal, run this command to restore the project dependencies

dotnet restore

– Then build the project

dotnet build

– And run it

dotnet run

A Hello World! message will be printed out to your debug console.
– To debug the app, just press F5 in Visual Studio Code, you’ll be asked for setting up the Debug-Enviroment. Choose .NET Core in popup window, a new launch.json file will be created. In launch.json file, change the option program to your dll. Press F5 again, you’ll be launched in Debug mode.

– The main function will be as same as before. We’ll provide some simple functions to enter data (such as courses or students).

(new CourseraContext()).Initialize();
int option = -1;
while (option != 0)
{
	Console.WriteLine("Choose an option (0, 1, 2, 3, 4):" + Environment.NewLine +
		"1. Enter a course" + Environment.NewLine +
		"2. Enter a student" + Environment.NewLine +
		"3. List all courses" + Environment.NewLine +
		"4. List all students" + Environment.NewLine +
		"0. Exit" + Environment.NewLine);
	if (int.TryParse(Console.ReadLine(), out option))
	{
		switch (option)
		{
			case 1:
				EnterCourse();
				break;

			case 2:
				EnterStudent();
				break;

			case 3:
				ListAllCourses();
				break;

			case 4:
				ListAllStudents();
				break;
		}
	}
}

– The CourseraContext doesn’t have too much changes in compare to previous version. The only difference is how we initialize the DbContext over the ConnectionString. In .Net version, we implicit initialize the context over the settings connectionStrings in app.config. In .Net Core version, we intialize the DbContext by overriding the OnConfiguring function and set ConnectionString with our ConnectionStrings setting from appsettings.json

public class CourseraContext : DbContext
{
	private static int RequiredDatabaseVersion = 1;

	protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
	{
		optionsBuilder.UseSqlite(SettingProvider.Instance.GetConfiguration().GetConnectionString("DefaultConnection"));
	}


	public DbSet<Course> Courses { get; set; }
	public DbSet<Student> Students { get; set; }

	public DbSet<SchemaInfo> SchemaInfoes { get; set; }

	public void Initialize()
	{
		int currentVersion = 0;
		if (SchemaInfoes.Count() > 0)
			currentVersion = SchemaInfoes.Max(x => x.Version);
		CourseraContextHelper mmSqliteHelper = new CourseraContextHelper();
		while (currentVersion < RequiredDatabaseVersion)
		{
			currentVersion++;
			foreach (string migration in mmSqliteHelper.Migrations[currentVersion])
			{
				Database.ExecuteSqlCommand(migration);
			}
			SchemaInfoes.Add(new SchemaInfo() { Version = currentVersion });
			SaveChanges();
		}
	}
}

– The SettingProvider is a singleton object. It parses the setting stored in appsettings.json file into a IConfigurationRoot so that we can read all settings from this object later.

public IConfigurationRoot GetConfiguration()
{
	if (configuration == null)
	{
		var configurationBuilder = new ConfigurationBuilder()
						.SetBasePath(Directory.GetCurrentDirectory())
						.AddJsonFile("appsettings.json", optional: true, reloadOnChange: true);
		configuration = configurationBuilder.Build();
	}

	return configuration;
}

– Remember to publish appsettings.json and database template Coursera.sqlite to our output folder by adding them to includeFiles option in project.json

{
  "version": "1.0.0-*",
  "buildOptions": {
    "debugType": "portable",
    "emitEntryPoint": true,
    "copyToOutput":{
      "includeFiles": ["appsettings.json","Coursera.sqlite"]
    }
  },
  "dependencies": {
    "Microsoft.EntityFrameworkCore":"1.1.0",
    "Microsoft.EntityFrameworkCore.Sqlite":"1.1.0",
    "Microsoft.Extensions.Configuration":"1.1.0",
    "Microsoft.Extensions.Configuration.FileExtensions":"1.1.0",
    "Microsoft.Extensions.Configuration.Json":"1.1.0"
  },
  "frameworks": {
    "netcoreapp1.0": {
      "dependencies": {
        "Microsoft.NETCore.App": {
          "type": "platform",
          "version": "1.0.1"
        }
      },
      "imports": "dnxcore50"
    }
  }
}

– Now run our console app, enter some courses and all data will be saved in our SQLite database. The migration will works on the fly as in .NET version (the required table will be created if not exists).

3. Source code

So we finished setting up a simple console app working with Sqlite in .NET core. Although the syntax is as same as before but the namespaces and libraries are all rewritten. It’s pretty difficult to find out which assembly has to be included to make the code works. However it’s common problem when we get to know with a new framework.

Source code: https://bitbucket.org/hintdesk/dotnetcore-sqlite-with-entity-framework-code-first-and

4. Reference

.NET core API: https://docs.microsoft.com/en-us/dotnet/core/api/

Leave a Reply

Your email address will not be published. Required fields are marked *