Setting Up The Database

By and large the posts I went through when setting this up relied on Entity Framework to build migrations. Code first migrations are admittedly pretty cool — you get to avoid SQL and quickly build a database from the C# models you’ve already written. My main problems with this are twofold:

You’ve relinquished control over how your database is created to an outside tool. You should take time to plan your database schema for committing to one; your database should not be something that it volatile like a model.

I’d rather spend some extra time to write out the SQL that designs my database by hand. When creating and altering tables I’d much rather know exactly what code is being run to generate them. One of the downsides to this approach is updating a table in your database means you have to update the corresponding model (and I suppose anything that is tightly coupled to it), though with enough planning at a database level these occurrences should be minimal.

So let’s get started. Before adding any of the scripts that will create and modify the database, we need to create a new folder in the root directory of our project.

Database Migration folder setup

The new folder structure looks like this.

As I mentioned earlier, I will be doing my database work in pgAdmin3 so the coming instructions will be written from the perspective of having that tool at your disposal. The application is relatively straightforward, but poking around in it for a few moments or looking over the documentation couldn’t hurt.

First we need to create the user that the server will use to connect to the database. Open a new query window connected to the default postgres database and run the following.

CREATE ROLE testuser LOGIN

ENCRYPTED PASSWORD 'test'

NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;

Find the Login Roles section in the menu on the left, expand it, and then click on the big refresh button to verify the new user has appeared. Next we need to create the database that all our information will be stored in.

CREATE DATABASE "TestDatabase"

WITH OWNER = testuser

ENCODING = 'SQL_ASCII'

TABLESPACE = pg_default

LC_COLLATE = 'C'

LC_CTYPE = 'C'

CONNECTION LIMIT = -1;

Change the user/database names and password as you see fit. The important things here are that the the backend for server has it’s own user to access the database and that the new user has ownership of the new database.

While we’re at it lets create a table in our new database and populate it with a record to play around with later. There’s a few extra things that need to happen this time.

A new query window that is connected to our new TestDatabase needs to be used for any SQL commands that need to effect it. Any scripts that modify TestDatabase need to be saved to our project because they are now migration scripts. I typically timestamp my migration scripts so the order in which things happened to my database is very apparent.

Run the following to make the new table.

CREATE TABLE "user"(

Id serial PRIMARY KEY,

FirstName varchar(255) NOT NULL,

LastName varchar(255) NOT NULL,

Email varchar(255) NOT NULL,

Password varchar(255) NOT NULL

); ALTER TABLE "user"

OWNER TO testuser;

'test', 'user', '

); INSERT INTO "user"(FirstName, LastName, Email, Password) VALUES('test', 'user', ' test@user.com ', 'test');

I save this file under the migrations folder named 08_31_2016_1500_Create-User-Table.sql — the timestamp consists of the date and time (expressed in a 24–hour format) the script was written followed by a brief description of what the script is doing.

Now that we’re done configuring our database it’s time to head back to C# land to make our server aware of this wonderful new toy we’ve created for it.

Connecting The Server To The Database

At this point Medium’s awesome code block has begun to fail me. I apologize in advance for poorly formatted code that follow.

We need to create a configuration file that details how the server should connect to the database. Create appsettings.json at the same level as your Program.cs and Startup.cs files and give it the following.

{

"Logging": {

"IncludeScopes": false,

"LogLevel": {

"Default": "Debug",

"System": "Information",

"Microsoft": "Information"

}

},

"DbContextSettings" :{

"ConnectionString" : "User ID=testuser;Password=test;Host=localhost;Port=5432;Database=TestDatabase;Pooling=true;"

}

}

The parameters to this connection string (User ID, Password, and Database) should change based on whatever you used when setting up your database in the previous section.

We’ll need a DatabaseContext for the next step, so we’ll create a barebones one now. It won’t do much, but it should let us run our server without errors before moving on to the next section. Make a new folder called Models in the Core folder, then a Database folder in that Models folder, then create DatabaseContext.cs with the following.

using Microsoft.EntityFrameworkCore;



namespace Core.Models.Database

{

public class DatabaseContext : DbContext

{

public DatabaseContext(DbContextOptions<DatabaseContext> options) : base(options) { }

}

}

You should have a folder structure similar to what I have here.

Begin integrating with database file structure

Next we need to update Startup.cs to utilize appsettings.json.

using System;

using Microsoft.AspNetCore.Builder;

using Microsoft.AspNetCore.Http;

using Microsoft.AspNetCore.Hosting;

using Microsoft.EntityFrameworkCore;

using Microsoft.Extensions.DependencyInjection;

using Microsoft.Extensions.Configuration;

using Core.Models.Database; namespace Core

{

public class Startup

{

public Startup(IHostingEnvironment env)

{

var builder = new ConfigurationBuilder()

.SetBasePath(env.ContentRootPath)

.AddJsonFile("appsettings.json", optional: true, reloadOnChange: true)

.AddEnvironmentVariables();

Configuration = builder.Build();

} public IConfigurationRoot Configuration { get; } public void ConfigureServices(IServiceCollection services)

{

services.AddMvc(); var connectionString = Configuration["DbContextSettings:ConnectionString"];

services.AddDbContext<DatabaseContext>(opts => opts.UseNpgsql(connectionString));

} public void Configure(IApplicationBuilder app)

{

app.UseMvc();

}

}

}

The final step is to add the new dependencies to project.json. The updated sections should look like this.

{

"dependencies": {

"Microsoft.AspNetCore.Server.Kestrel": "1.0.0",

"Microsoft.AspNetCore.Mvc": "1.0.0",

"Microsoft.Extensions.Configuration.FileExtensions": "1.0.0",

"Microsoft.Extensions.Configuration.Json": "1.0.0-rc2-final",

"Microsoft.EntityFrameworkCore": "1.0.0",

"Microsoft.EntityFrameworkCore.Design": "1.0.0-preview2-final",

"Npgsql.EntityFrameworkCore.PostgreSQL": "1.0.0"

},

"frameworks": {...},

"tools": {

"Microsoft.EntityFrameworkCore.Tools": {

"version": "1.0.0-preview2-final",

"imports": "portable-net45+win8+dnxcore50"

},

"Microsoft.AspNetCore.Razor.Tools": "1.0.0-preview2-final"

}

}

Install the dependencies and run the project just to make sure nothing is failing catastrophically. You should just get the same output you had before we set up the database.

Getting Information From the Database

Now that we have a our DatabaseContext set up and we’re able to connect to our database, we need to make models that interface with the tables we’ve built. Right now we just have the user table, so we need a model for it.

Create User.cs in the same directory as DatabaseContext.cs

using System.ComponentModel.DataAnnotations.Schema; namespace Core.Models.Database

{

[Table("user")]

public class User

{

[Column("id")]

public int Id { get; set; }

[Column("firstname")]

public string FirstName { get; set; }

[Column("lastname")]

public string LastName { get; set; }

[Column("email")]

public string Email { get; set; }

[Column("password")]

public string Password { get; set; }

}

}

Here we’ve dictated what table this model is related to, as well as what column each field is associated with.

We need to update DatabaseContext.cs so it knows about the user table via our new model.

using Microsoft.EntityFrameworkCore;



namespace Core.Models.Database

{

public class DatabaseContext : DbContext

{

public DatabaseContext(DbContextOptions<DatabaseContext> options) : base(options) { }



public DbSet<User> Users{ get; set; }

}

}

Now that we have a DatabaseContext that knows how to access our user table, we need to make a repository to hold all the functions that operate on this table. Create a new Repositories folder under Core and then add UserRepository.cs with the following.

using System.Linq;

using Core.Models.Database;



namespace Core.Respositories

{

public class UserRepository

{

private readonly DatabaseContext _databaseContext;



public UserRepository(DatabaseContext databaseContext)

{

_databaseContext = databaseContext;

}



public User GetByEmail(string email)

{

return _databaseContext.Users.SingleOrDefault(x => x.Email == email);

}



public void Save(User user)

{

var userFromDb = _databaseContext.Users.SingleOrDefault(x => x.Id == user.Id);

if(userFromDb != null)

{

userFromDb = user;

}

else

{

_databaseContext.Users.Add(user);

}

_databaseContext.SaveChanges();

}

}

}

This repository hassome basic functions that allow us to get and save information about users via the injected DatabaseContext. The save method is more or less a re-implementation of the previous version of Entity Framework’s AddOrUpdate() method — it seems that Entity Framework Core is currently lacking that functionality, but is likely to have it reintegrated in the near future.

For our UserRepository to be any kind of use to us we should create a new instance of it in the HomeController and then use it to get some data from the database. This works by injecting a DatabaseContext and using that to initialize the repository as seen below.

using System;

using Microsoft.AspNetCore.Mvc;

using Core.Models.Database;

using Core.Respositories; namespace Core.Controllers

{

public class HomeController : Controller

{

private readonly UserRepository _userRepository; public HomeController(DatabaseContext databaseContext)

{

_userRepository = new UserRepository(databaseContext);

}

public ActionResult Index()

{

var email = "

var user = _userRepository.GetByEmail(email);



return View(user);

}

}

} [HttpGet("/")]public ActionResult Index()var email = " test@user.com ";var user = _userRepository.GetByEmail(email);return View(user);

The email we’re searching for is currently hardcoded in for the sake of example. The next step is to update Index.cshtml so that it is displaying data off the model we’re now passing it rather than relying on the Viewbag.

If you run the project and navigate to it (like you’ve likely done several times now) you should see some data for the user we added a bit ago.

I do not recommend using your database model to populate views of any kind — you should have a dedicated view model that is used for displaying data (or returning data to anywhere that is going to be user-facing, like with a JSON result). We’ll make some changes to do that now.

The easiest, but possibly most tedious change, is to first rename the current User.cs file to UserDatabaseModel.cs and update the class name to match. Make sure you update everywhere the User model was previously referenced in the project.

Next we want to make the view model. Make a View folder in the Models directory and add UserViewModel.cs.

namespace Core.Models.View

{

public class UserViewModel

{

public string FirstName { get; set; }

public string LastName { get; set; }

public string Email { get; set; }

}

}

Currently we have a database model and view model, but no way to move from the former to latter. So let’s make one — create a Mappers folder under Core and add UserViewModelMapper.cs.

using Core.Models.Database;

using Core.Models.View;



namespace Core.Mappers

{

public static class UserViewModelMapper

{

public static UserViewModel MapFrom(UserDatabaseModel user)

{

return new UserViewModel

{

FirstName = user.FirstName,

LastName = user.LastName,

Email = user.Email

};

}

}

}

Mappers should be static classes that serve to take a type in and spit a new type out. These classes should use as little logic as possible and avoid using the any kind of database interaction.

We now want to use this new mapper in the HomeController to pass a UserViewModel to the view rather than a UserDatabaseModel. To do this we’ll use our UserRepository to get information about a user and then map that database model into a view model.

using System;

using Microsoft.AspNetCore.Mvc;

using Core.Models.Database;

using Core.Respositories;

using Core.Mappers; namespace Core.Controllers

{

public class HomeController : Controller

{

private readonly UserRepository _userRepository; public HomeController(DatabaseContext databaseContext)

{

_userRepository = new UserRepository(databaseContext);

}

public ActionResult Index()

{

var email = "

var user = _userRepository.GetByEmail(email); [HttpGet("/")]public ActionResult Index()var email = " test@user.com ";var user = _userRepository.GetByEmail(email); var userViewModel = UserViewModelMapper.MapFrom(user);

return View(userViewModel);

}

}

}

The final step is to update Index.cshtml to use UserViewModel over UserDatabaseModel.

A note about view models are that should try and be flat, meaning they should have simple types as much as possible (string, int, double, bool, etc). Obviously this just isn’t possible all the time and there are certainly cases where more complex types make more sense, but generally flatter view models are easier to pull data off of and serialize.

Now that project is finished, let’s take a look the final file structure for the Core project.

Final project file structure

As with last time I ended a major section, I’ll also list out what the final states of all the files in the project are (even the ones that we haven’t touched in this section of the post). Apologies again for weird code block formatting.

HomeController.cs

using System;

using Microsoft.AspNetCore.Mvc;

using Core.Models.Database;

using Core.Respositories;

using Core.Mappers; namespace Core.Controllers

{

public class HomeController : Controller

{

private readonly UserRepository _userRepository; public HomeController(DatabaseContext databaseContext)

{

_userRepository = new UserRepository(databaseContext);

}

public ActionResult Index()

{

var email = "

var user = _userRepository.GetByEmail(email); [HttpGet("/")]public ActionResult Index()var email = " test@user.com ";var user = _userRepository.GetByEmail(email); var userViewModel = UserViewModelMapper.MapFrom(user);

return View(userViewModel);

}

}

}

UserViewModelMapper.cs

using Core.Models.Database;

using Core.Models.View; namespace Core.Mappers

{

public static class UserViewModelMapper

{

public static UserViewModel MapFrom(UserDatabaseModel user)

{

return new UserViewModel

{

FirstName = user.FirstName,

LastName = user.LastName,

Email = user.Email

};

}

}

}

DatabaseContext.cs

using Microsoft.EntityFrameworkCore; namespace Core.Models.Database

{

public class DatabaseContext : DbContext

{

public DatabaseContext(DbContextOptions<DatabaseContext> options) : base(options) { } public DbSet<UserDatabaseModel> Users{ get; set; }

}

}

UserDatabaseModel.cs

using System.ComponentModel.DataAnnotations.Schema; namespace Core.Models.Database

{

[Table("user")]

public class UserDatabaseModel

{

[Column("id")]

public int Id { get; set; }

[Column("firstname")]

public string FirstName { get; set; }

[Column("lastname")]

public string LastName { get; set; }

[Column("email")]

public string Email { get; set; }

[Column("password")]

public string Password { get; set; }

}

}

UserViewModel.cs

namespace Core.Models.View

{

public class UserViewModel

{

public string FirstName { get; set; }

public string LastName { get; set; }

public string Email { get; set; }

}

}

UserRepository.cs

using System.Linq;

using Core.Models.Database; namespace Core.Respositories

{

public class UserRepository

{

private readonly DatabaseContext _databaseContext; public UserRepository(DatabaseContext databaseContext)

{

_databaseContext = databaseContext;

} public UserDatabaseModel GetByEmail(string email)

{

return _databaseContext.Users.SingleOrDefault(x => x.Email == email);

} public void Save(UserDatabaseModel user)

{

var userFromDb = _databaseContext.Users.SingleOrDefault(x => x.Id == user.Id);

if(userFromDb != null)

{

userFromDb = user;

}

else

{

_databaseContext.Users.Add(user);

}

_databaseContext.SaveChanges();

}

}

}

Index.cshtml

appsettings.json

{

"Logging": {

"IncludeScopes": false,

"LogLevel": {

"Default": "Debug",

"System": "Information",

"Microsoft": "Information"

}

},

"DbContextSettings" :{

"ConnectionString" : "User ID=testuser;Password=test;Host=localhost;Port=5432;Database=TestDatabase;Pooling=true;"

}

}

Program.cs

using System.IO;

using Microsoft.AspNetCore.Builder;

using Microsoft.AspNetCore.Hosting; namespace Core

{

public class Program

{

public static void Main(string[] args)

{

var host = new WebHostBuilder()

.UseContentRoot(Directory.GetCurrentDirectory())

.UseKestrel()

.UseStartup<Startup>()

.Build();

host.Run();

}

}

}

project.json

{

"version": "1.0.0-*",

"buildOptions": {

"debugType": "portable",

"emitEntryPoint": true,

"preserveCompilationContext": true

},

"dependencies": {

"Microsoft.AspNetCore.Server.Kestrel": "1.0.0",

"Microsoft.AspNetCore.Mvc": "1.0.0",

"Microsoft.Extensions.Configuration.FileExtensions": "1.0.0",

"Microsoft.Extensions.Configuration.Json": "1.0.0-rc2-final",

"Microsoft.EntityFrameworkCore": "1.0.0",

"Microsoft.EntityFrameworkCore.Design": "1.0.0-preview2-final",

"Npgsql.EntityFrameworkCore.PostgreSQL": "1.0.0"

},

"frameworks": {

"netcoreapp1.0": {

"dependencies": {

"Microsoft.NETCore.App": {

"type": "platform",

"version": "1.0.0"

}

},

"imports": "dnxcore50"

}

},

"tools": {

"Microsoft.EntityFrameworkCore.Tools": {

"version": "1.0.0-preview2-final",

"imports": "portable-net45+win8+dnxcore50"

},

"Microsoft.AspNetCore.Razor.Tools": "1.0.0-preview2-final"

}

}

Startup.cs