Click here to Skip to main content
15,867,756 members
Articles / Programming Languages / C# 7.0

Build Persisting Layer with ASP.NET Core and EF Core using Postgresql and SQL Server 2016

Rate me:
Please Sign up or sign in to vote.
4.25/5 (8 votes)
13 Oct 2016CPOL3 min read 18.5K   4   2
This post is about developing ASP.NET Core application using EF Core, we will demonstrate how we can connect in different ways to SQL Server Database and Postgresql Database.

Prerequisites

  • Visual Studio 2015 update 3
  • .NET Core 1.0
  • SQL Server 2014 / 2016 Server Management Studio with a default localdb
  • Postgresql

Contents

  1. Create Database: samplecoreEF
  2. Create an ASP.NET Core Web Application: Samples.AspCoreEF
  3. Add Class Library Core to the solution: Samples.AspCoreEF.DAL.EF
  4. Create model classes: Task and Person
  5. Add Context: TaskSystemDbContext
  6. Register the context class with Dependency Injection
  7. Auto-Create the database
  8. Add Web API controller and test using Postman

Background

Have some knowledge in ASP.NET and Entity Framework.

Create Database: samplecoreEF

Create a new database “sampleCoreEF” in PostgreSQL:

Image 1

Image 2

Create a new database “sampleCoreEF” in SQL server 2016:

Image 3

Create an ASP.NET Core Web Application: Samples.AspCoreEF

We open Visual Studio > File > New Project> Select “ASP.NET Core Web Application” > Enter Name “Samples.AspCoreEF” & Location > OK.

Image 4

Then, we select the temple “Web Application” and we confirm the choice by clicking on OK.

Add Class Library Core to the Solution: Samples.AspCoreEF.DAL.EF

Image 5

Now, we take a look at Samples.AspCoreEF project structure, as shown in the screenshot, given below:

Image 6

The project Samples.AspCoreEF.DAL.EF will contain our EntityFramework models, migrations, context … (This is the reason for the name DAL: Data Access Layer).

Create Model Classes: Task and Person

We will add to the Samples.AspCoreEF.DAL.EF project the Models folder that will contain two models: Person.cs and Task.cs.

Image 7Image 8

Image 9

Image 10

In project.json, we install the Entity Framework Core package for the database provider from NuGet Package Manager Console in Visual Studio 2015.

Image 11

Image 12

Image 13

Image 14

Image 15

 

So “project.json” should have this content to be sure that we have added the right version:

Image 16

Add Context: TaskSystemDbContext

We have to add a new folder called EntityFramework where we will add our context like below:

Image 17Image 18

So we will have this structure:

Image 19

Register the Context Class with Dependency Injection

We will work now in the ASP.NET Web Application to be able to register our context.

We will start by adding Samples.AspCoreEF.DAL.EF as a reference to Samples.AspCoreEF.

Image 20

After we will add needed references to generate our database as before, so, our project.json will have this look:

Image 21

In the Startup.cs, we will add two blocks to show you how we can register context class in two different ways: So in this method:

C#
public void ConfigureServices(IServiceCollection services)

After:

C#
services.AddMvc();

We will add this block related to the connection to the SQL Server Database.

We will add this block related to the connection to the SQL Server Database:

C#
//Using SQL Server
var sqlconnection = @"Server=(localdb)\v11.0;Database=samplecoreEF;Trusted_Connection=True;";
services.AddDbContext<TaskSystemDbContext>
(dbcontextoption => dbcontextoption.UseSqlServer(sqlconnection));

sqlconnection is a hardcoded string and we use UseSqlServer to connect to the database.

But, it’s better to add this string in configuration file, before we can add these string in web.config but in Core, this file doesn’t exist anymore but we can use: appsettings.json file where you can add all settings related to your web application so:

Image 22

sqlconnection is a hardcoded string and we use UseSqlServer to connect to the database.

But, it’s better to add this string in configuration file, before we can add these string in web.config but in Core, this file doesn’t exist anymore but we can use: appsettings.json file where you can add all settings related to your web application as shown below:

Image 23

So we will call it in Startup.cs in this way:

C#
//Using Postgresql
var connectionString = Configuration["DbContextSettings:ConnectionString"];
services.AddDbContext<TaskSystemDbContext>(
    opts => opts.UseNpgsql(connectionString)
);

Now, we will create our database:

Tools->NuGet Package Manager and then we click on Package Manager Console menu:

Image 24

Type Add-Migration CoreMigration and enter:

Image 25

And we will have Migration folder added in the solution:

Image 26

And if we check the databases, we will find that tables are added automatically.

And if the database exists, we use Update-Database.

Sometimes, we get some error like below:

Image 27

So, to resolve it, we have to restart Visual Studio as an administrator and enable the migration.

PM> Enable-Migrations

Image 28

And if you get this message, you have to update the PowerShell version:

Add Web API Controller and Test using Postman

Now, we will add new API Controller called PersonController.

Image 29

And test your API using Postman.

References

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Technical Lead
Canada Canada
I am Rebaï Hamida, Microsoft MVP in Visual Studio and Development Technologies.
I'm a software architect and developer who like to build open source projects, writing articles, learning and teaching best practices. you can find out in my blog : http://hamidarebai.blogspot.com/

Innovative Software engineer : hands-on, competent software engineer, passionate and proficient C# developer, offering more than seven years of experience in the full software development lifecycle – from concept through delivery of next-generation applications and customized solutions, producing code to a consistently high standard and testing software across a variety of platforms.
Expert in advanced development methodologies, tools and processes contributing to the design and roll-out of cutting-edge software applications.
Known for excellent troubleshooting skills – able to analyze code and engineer well-researched, cost-effective and responsive solutions.


Success is only a consequence of a continuous work and remarkable intelligence.
I believe that I have to make a change in my country, I can’t live without leaving my footprint.

Comments and Discussions

 
Praisenice Pin
BillW333-Aug-17 2:43
professionalBillW333-Aug-17 2:43 
PraiseVery nice Pin
Member 271004217-Oct-16 7:40
professionalMember 271004217-Oct-16 7:40 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.