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 run6) 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": {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:
"Microsoft.EntityFrameworkCore.Tools": {
"version": "1.0.0-preview2-final",
"imports": [
"portable-net45+win8+dnxcore50",
"portable-net45+win8"
]
}
}
dotnet restore9) Add a folder named “Models” and add to it a C# class file named Student.cs. with the following code:
using System;The above code defines the properties of a Student class and adds a static method GetSampleStudents() that retrieves some dummy data.
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;
}
}
}
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:
{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:
"ConnectionStrings": {
"DefaultConnection": "Data Source=institute.sqlite"
}
}
using Microsoft.Extensions.Configuration;12) Add another C# class file to the Models folder named InstituteContext.cs with the following code:
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;
}
}
}
using Microsoft.EntityFrameworkCore;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:
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);
}
}
}
"copyToOutput": {14) It is time to build our application. At a command-prompt in the project folder, type the following:
"include": [ "appsettings.json" ]
}
dotnet build15) 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 FirstMigrationUpon completion of the above EF commands, the database will have been created.
dotnet ef database update
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() {17) Resolve the namespace for the Student and InstituteContext classes.
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);
}
}
}
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:
addStudents();
displayStudents();
}
dotnet build20) If all goes well, you see the following results:
dotnet run
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 applicationPostgreSQL DB and Code-First .NET Core console application
No comments:
Post a Comment