Execute Stored Procedure using Entity Framework Core in .NET Core Class Library Project
4.80/5 (8 votes)
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:
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.