Execute Stored Procedure using Entity Framework Core in .NET Core Class Library Project

Introduction

In this article, I would like to show you how to execute the stored procedure in data access layer library core project.

Prerequisites

Make sure you have installed all the prerequisites in your computer. If not, then download and install all, one by one.

First, download and install Visual Studio 2015 with Update 3 from this link.

If you have Visual Studio 2015 and have not yet updated with update 3, download and install the Visual Studio 2015 Update 3 from this link.

Download and install .NET Core 1.0.1

Problem

Entity Framework Core 1.0.0 RTM still does not support migrations for class library projects. Please refer to the following link for the above problem:

• https://docs.microsoft.com/en-us/ef/core/miscellaneous/cli/dotnet#targeting-class-library-projects-is-not-supported

Solution

Step 1

Create a sample ASP.NET core application project using .NET Core template after installing all the prerequisites component.

We can use our class library project in this web application. First, you need to create two .NET core library projects - one for business logic and another for data access layer.

Follow the same step for creating a data access layer library project. Finally, our project will be as follows:

Remove the existing code and add the following piece of code in your business library project‘s project.json file:

{
 "version": "1.0.0-*",

 "dependencies": {
   "NETStandard.Library": "1.6.0"
 },
 "frameworks": {
   "netcoreapp1.0": {}
 }
}

We need to create our class library as a .NET Core app project. For this, you need to the following code in your data access layer library’s project.json file.

{
 "buildOptions": {
   "emitEntryPoint": true
 },
 "frameworks": {
   "netcoreapp1.0": {}
 },
 "dependencies": {
   "Microsoft.NETCore.App": {
     "version": "1.0.1",
     "type": "platform"
   },
   "Microsoft.EntityFrameworkCore.Design": "1.0.0-preview2-final",
   "Microsoft.EntityFrameworkCore.SqlServer": "1.0.0",
   "Microsoft.EntityFrameworkCore.SqlServer.Design": "1.0.0",
   "Microsoft.AspNetCore.Identity.EntityFrameworkCore": "1.0.0"
 },
 "tools": {
   "Microsoft.EntityFrameworkCore.Tools": {
     "version": "1.0.0-preview2-final"
   }
 }
}

You’ll also need to add a static void main() to complete the .NET Core app. For this, you need to add an empty program.cs to your class library project. Please refer to the following code:

public class Program
   {
       public static void Main(string[] args)
       {

       }
   }

Step 2

Create a database and tables with stored procedure in SQL Server. Please run the following script in your SQL Server:

 USE [master] 

 GO 

 /****** Object:  Database [ContactDB]    Script Date: 2/10/2017 12:22:09 PM ******/ 

 CREATE DATABASE [ContactDB]
CONTAINMENT = NONE
ON  PRIMARY 

 ( NAME = N'ContactDB', _
   FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\ContactDB.mdf' , _
   SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON 

 ( NAME = N'ContactDB_log', _
   FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\ContactDB_log.ldf',_
   SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%) 

 GO 

 ALTER DATABASE [ContactDB] SET COMPATIBILITY_LEVEL = 110 

 GO 

 IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled')) 

 begin 

 EXEC [ContactDB].[dbo].[sp_fulltext_database] @action = 'enable' 

 end 

 GO 

 ALTER DATABASE [ContactDB] SET ANSI_NULL_DEFAULT OFF 

 GO 

 ALTER DATABASE [ContactDB] SET ANSI_NULLS OFF 

 GO 

 ALTER DATABASE [ContactDB] SET ANSI_PADDING OFF 

 GO 

 ALTER DATABASE [ContactDB] SET ANSI_WARNINGS OFF 

 GO 

 ALTER DATABASE [ContactDB] SET ARITHABORT OFF 

 GO 

 ALTER DATABASE [ContactDB] SET AUTO_CLOSE ON 

 GO 

 ALTER DATABASE [ContactDB] SET AUTO_SHRINK OFF 

 GO 

 ALTER DATABASE [ContactDB] SET AUTO_UPDATE_STATISTICS ON 

 GO 

 ALTER DATABASE [ContactDB] SET CURSOR_CLOSE_ON_COMMIT OFF 

 GO 

 ALTER DATABASE [ContactDB] SET CURSOR_DEFAULT  GLOBAL 

 GO 

 ALTER DATABASE [ContactDB] SET CONCAT_NULL_YIELDS_NULL OFF 

 GO 

 ALTER DATABASE [ContactDB] SET NUMERIC_ROUNDABORT OFF 

 GO 

 ALTER DATABASE [ContactDB] SET QUOTED_IDENTIFIER OFF 

 GO 

 ALTER DATABASE [ContactDB] SET RECURSIVE_TRIGGERS OFF 

 GO 

 ALTER DATABASE [ContactDB] SET  ENABLE_BROKER 

 GO 

 ALTER DATABASE [ContactDB] SET AUTO_UPDATE_STATISTICS_ASYNC OFF 

 GO 

 ALTER DATABASE [ContactDB] SET DATE_CORRELATION_OPTIMIZATION OFF 

 GO 

 ALTER DATABASE [ContactDB] SET TRUSTWORTHY OFF 

 GO 

 ALTER DATABASE [ContactDB] SET ALLOW_SNAPSHOT_ISOLATION OFF 

 GO 

 ALTER DATABASE [ContactDB] SET PARAMETERIZATION SIMPLE 

 GO 

 ALTER DATABASE [ContactDB] SET READ_COMMITTED_SNAPSHOT OFF 

 GO 

 ALTER DATABASE [ContactDB] SET HONOR_BROKER_PRIORITY OFF 

 GO 

 ALTER DATABASE [ContactDB] SET RECOVERY SIMPLE 

 GO 

 ALTER DATABASE [ContactDB] SET  MULTI_USER 

 GO 

 ALTER DATABASE [ContactDB] SET PAGE_VERIFY CHECKSUM  

 GO 

 ALTER DATABASE [ContactDB] SET DB_CHAINING OFF 

 GO 

 ALTER DATABASE [ContactDB] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF ) 

 GO 

 ALTER DATABASE [ContactDB] SET TARGET_RECOVERY_TIME = 0 SECONDS 

 GO 

 USE [ContactDB] 

 GO 

 /****** Object:  Table [dbo].[Contacts]    Script Date: 2/10/2017 12:22:09 PM ******/ 

 SET ANSI_NULLS ON 

 GO 

 SET QUOTED_IDENTIFIER ON 

 GO 

 SET ANSI_PADDING ON 

 GO 

 CREATE TABLE [dbo].[Contacts](
               [ContactID] [int] IDENTITY(1,1) NOT NULL,
               [FirstName] [varchar](100) NOT NULL,
               [LastName] [varchar](100) NULL,
               [ContactNo1] [varchar](20) NOT NULL,
               [ContactNo2] [varchar](20) NULL,
               [EmailID] [varchar](200) NULL,
               [Address] [varchar](300) NULL,
CONSTRAINT [PK_Contacts] PRIMARY KEY CLUSTERED 

 (
               [ContactID] ASC 

 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, _
        ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] 

 ) ON [PRIMARY]
 

 GO 

 SET ANSI_PADDING OFF 

 GO 

 /****** Object:  StoredProcedure [dbo].[GetContactList]    Script Date: 2/10/2017 12:22:10 PM ******/ 

 SET ANSI_NULLS ON 

 GO 

 SET QUOTED_IDENTIFIER ON 

 GO 

 CREATE PROCEDURE [dbo].[GetContactList]
             

 AS                

 SET NOCOUNT ON;                       
BEGIN  
                 SELECT
                                                [ContactID]
                                               ,[FirstName]
                                               ,[LastName]
                                               ,[ContactNo1]
                                               ,[ContactNo2]
                                               ,[EmailID]
                                               ,[Address]
                FROM    Contacts
END 

 GO 

 USE [master] 

 GO 

 ALTER DATABASE [ContactDB] SET  READ_WRITE 

 GO

Step 3

Now we can generate the entities using the following dotnet ef command.

ef command now no longer exists, you need to use the following dotnet ef command:

dotnet ef dbcontext scaffold -c ContactDbContext -o Models -f "Data Source=yourdatasource; 
Initial Catalog=ContactDB;User ID=youruserid;Password=yourpassword" 
Microsoft.EntityFrameworkCore.SqlServer

where Models - Folder name that you want to create in data layer project which contains all the entities.

To execute the above command, you need open your command prompt. Go to your data access layer project path and use the above command. Please see the following screenshot for more details:

When you see the “Done” in the above command prompt after executing the above command, then one folder name “Models” is created in our data access layer project which contains the database entities. See the following screenshot:

Open the “ContactDbContext.cs” file, you will see your SQL connection string that you specified in the above ef command prompt and relevant contact entity:

Ok, we are done with our data access layer.

Step 4

Add your data access reference in business layer project and add your business layer reference in your web project.

Step 5

Now I have created one extension class which is used to map the database object to our entity framework entities.

Step 6

Create an interface name “IContactRepository.cs” in business layer class library:

Step 7

Create a repository name “ContactRepository.cs” and implement the above interface:

That’s it! We are now going to use our stored procedure using the .NET core class library project.

Hope you liked the article.