Although we like Entity Framework Core, there are occasions when the appropriate tool to work with the logic of data access in our application is ADO.NET. Given this, it is important that we know how to handle the stored procedures.

Stored procedures allow us to encapsulate operations that we will perform in a database. Whether they are writings, deletions, complex operations that involve several tables, and even simple readings; stored procedures allow us to place this logic in an easily accessible place for our applications.

In this post we will see how to work with stored procedures in the context of ASP.NET Core. The stored procedures will be created in SQL Server.

Code on github: https://github.com/gavilanch/ASPNETCORE_StoredProcs

ASP.NET Core

We will start by creating the application in ASP.NET Core 2.2. We’ll choose the API template, although what we will learn in this entry also applies to Web Application (Razor Pages) and MVC.

When we create our project, a controller called ValuesController is created for us. We will work with this controller. Through this controller is that we will interact with our database using ADO.NET.

The idea is that we will have a table in our database, and we want to be able to read and insert data in that table.

We will start by creating a model which will represent the information of said table. We’re going to create a class called Value in a folder called Models:

We are going to create a class in which we will place the data access logic with our database. The name of the class will be ValuesRepository.

As we can see, our class has methods to obtain all the values, obtain a value from its Id, insert new values, and delete a value from its Id. In addition, we are returning Tasks in each function, this because we will use asynchronous programming to communicate with the database.

We will use dependency injection to inject this class into the ValuesController controller. Let’s head to the startup class, to the ConfigureServices method, and place the following line:

We are not using an interface just to keep the tutorial focused on what interests us.

Let’s now work with our ValuesController class:

We see that each action uses the repository sent by the constructor. Again, we are keeping the code simple and focused on purpose. In production code you may want to place some try-catch, and validations.

If we execute our project and go to the endpoint /api/values, we will see that we get a NotImplementedException error, this is because we have not implemented the code of our ValuesRepository class. Before that, we need a database with which to work.

SQL Server

We will create a database with a table with which we will work (If you already have your database with your stored procedures you can skip this part of the tutorial).

Here I put the creation script of the database, the table and the stored procedures: : https://gist.github.com/gavilanch/59a011c18fa59a98ef71d9fc775b7730

You just have to run the previous script in your MSSQL to have a database with what is necessary to work on this tutorial.

Then, we need the connection string of this database in our application. We can place it in our appsettings.json in the following way:

"connectionStrings": {
"defaultConnection": "CONNECTIONSTRING"
},

Where it says CONNECTIONSRING you must place your connection string.

Then, back in our ASP.NET Core application, we must inject IConfiguration into ValuesRepository to access this connection string:

ADO.NET

Now with the database and the architecture of our application, we only need to use ADO.NET to communicate with our database.

Stored procedure to read data

We will start by implementing the GetAll method of the ValuesRepository class to obtain all the records in the Values table of our database:

Here we can see that what we do is open a connection by passing the connection string to the SqlConnection class. Then, we instantiate a SqlCommand passing as arguments the name of the stored procedure and the connection.

Below we use ExecuteReaderAsync to execute the stored procedure and read the information it brings us. This information is in the reader variable. Then, what I did was to create a MapToValue function which takes the reader and extracts the values from the columns of the reader, places the values in a new Value object, and returns that object.

In the end, we return a list of Values.

Stored procedure for reading data passing a parameter

Let’s now implement the GetById function:

As we can see, the only difference between this code and the previous one,is the use of SqlParamater:

cmd.Parameters.Add(new SqlParameter(“@Id”, Id));

We use this to send parameters to the stored procedure.

Inserting registries with stored procedures

We are now going to implement our Insert function which is used to insert a new value in the database:

Here we do not use ExecuteDataReader because we do not intend to read data, we want to perform an operation. That’s why we use ExecuteNonQueryAsync. Notice also that we passed two parameters to the stored procedure, Value1 and Value2.

Finally, we can also implement the DeleteById function:

We see that this code is quite similar to the Insert function.

Summary

We can use stored procedures in ASP.NET Core in a very direct way. This is useful when for some reason we can not or do not want to use Entity Framework Core.

We saw that we can use ExecuteReaderAsync to read data from a query, and ExecuteNonQueryAsync to execute a stored procedure that does not return a result set.

Regards!