Card image cap

Working with Stored Procedures in ASP.NET Core / EF Core

ASP.NET Core  • Posted 9 days ago

Stored Procedures encapsulate complex database queries or command which can be called to execute on a database for respective results. These programming structures enable developers to write a query only once and then use it as many times as needed providing code re-usability. While writing Stored Procedures is out of scope for our discussion, let's talk about how we can invoke existing Stored Procedures in a database from our ASP.NET Core API using Entity Framework Core. We shall also look at how we can maintain Stored Procedures inside our API code-base and add them to a new database through Migrations.

Getting Started - API Project that uses Stored Procedures

Let's create a new ASP.NET Core Web API that does a simple GetOrCreate functionality on a UserProfiles table in our database. To better understand about how we do this, let's work on a completely new database and use migrations to push our models into database schema - a Code First approach.

> dotnet new webapi --name SpReadersApi

Once created, let's delete the boilerplate classes to keep our project clean and clutter free. Let's add the things needed for us to use Entity Framework Core in this project by installing EFCore nuget packages. We shall use SQL Server as our database, so let's add necessary support for the same. The csproj looks like this (you can update your project with the contents of and do a restore):

<Project Sdk="Microsoft.NET.Sdk.Web">
  <PropertyGroup>
    <TargetFramework>netcoreapp3.1</TargetFramework>
  </PropertyGroup>
  <ItemGroup>
    <!-- EFCore tools needed for Migrations -->
    <PackageReference Include="Microsoft.EntityFrameworkCore.Design" Version="3.1.3">
      <IncludeAssets>runtime; build; native; contentfiles; analyzers; buildtransitive</IncludeAssets>
      <PrivateAssets>all</PrivateAssets>
    </PackageReference>
    <!-- EFCore support for SQL Server -->
    <PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer" Version="3.1.3" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.Tools" Version="3.1.3">
      <IncludeAssets>runtime; build; native; contentfiles; analyzers; buildtransitive</IncludeAssets>
      <PrivateAssets>all</PrivateAssets>
    </PackageReference>
  </ItemGroup>
</Project>

Once this is done, let's add our Code First schema, which shall be created in our database.

using System.ComponentModel.DataAnnotations;
using Microsoft.EntityFrameworkCore;

namespace SpReadersApi.Models.Context
{
    public class AppDbContext : DbContext
    {
        public AppDbContext(DbContextOptions<AppDbContext> options)
             : base(options)
        {
        }
        public DbSet<UserProfile> UserProfiles { get; set; }
    }

    public class UserProfile
    {
        [Key]
        public int Id { get; set; }
        public string EmailAddress { get; set; }
        public string PasswordHash { get; set; }
        public string OIdProvider { get; set; }
        public string OId { get; set; }
    }
}

The property Id of the UserProfile doubles as the PrimaryKey inside the database Table, and so we decorate it with [Key] attribute.

Now that we have our Model ready, let's configure EFCore to use this DbContext while connecting to the SQL Server database inside the ConfigureServices method of the Startup class.

services.AddScoped<IUserRepository, UserRepository>();
            
services.AddDbContext<AppDbContext>(options =>
{
    options.UseSqlServer(
        Configuration.GetConnectionString("DefaultConnection"));
});

I've also added another Service UserRepository which encapsulates the database logic which we shall see in a moment.

The IUserRepository declares a single method that validates if a user exists in the database and if not exists just creates it and returns the Id.

namespace SpReadersApi.Providers.Repositories
{
    public interface IUserRepository
    {
        Task<int> GetOrCreateUserAsync(UserProfile id);
    }
}

The implementation of this abstract type IUserRepository invokes two Stored Procedures sp_GetUser and sp_CreateUser to query user records from the database table and run an INSERT command into the UserProfiles table.

The Stored Procedure sp_GetUser looks like below:

#SQL#

CREATE OR ALTER PROCEDURE sp_GetUsers(@emailAddress VARCHAR(100), @passwordHash VARCHAR(100))
AS
BEGIN
SELECT * FROM UserProfiles WHERE EmailAddress = @emailAddress AND PasswordHash = @passwordHash;
END

Ignoring the typical syntax of a Stored Procedure in PL/SQL, what we would like to observe is the parameters we're passing into the procedure - emailAddress, passwordHash; and what happens inside the Stored Procedure - a SELECT query which shall return matching rows for the passed emailAddress and passwordHash combination. If none matched, no rows shall be returned from the database.

"Since we're dealing with Stored Procedures and by definition Stored Procedures won't have a return type in their syntax - this doesn't return anything specifically"

Now let's see how we shall call this Stored Procedure inside UserRepository.

Invoking a Stored Procedure that executes a Query

To execute a Stored Procedure and read the results of the query it executes inside it, we use a specific function on the Model which it is related to. In this case, we're invoking on the UserProfiles entity and so we shall run it on the same.

The Stored Procedure is executed as below:

var emailAddressParam = new SqlParameter("@emailAddress", id.EmailAddress);
var passwordParam = new SqlParameter("@passwordHash", id.PasswordHash);

var users = context
            .UserProfiles
            .FromSqlRaw("exec sp_GetUsers @emailAddress, @passwordHash", emailAddressParam, passwordParam)
            .ToList();

In EntityFrameworkCore we have two methods for executing Stored Procedures that:

  • Query for records from a database - FromSqlRaw()
  • Execute a command (typically DML) on the database - ExecuteSqlRaw() or the awaitable ExecuteSqlRawAsync()

In the above case, since we're executing a Stored Procedure that queries for results, we use the former - FromSqlRaw(). The method takes two parameters:

  • an SQL string - in our case, it shall be the "exec sp_GetUser along with parameters" SQL statement to execute the Stored Procedure
  • a series of the parameters to pass with this SQL statement - we need to pass objects of type SqlParameter() which map between the parameters we're passing from our project to the actual parameter names which are defined in the SQL.

Note: Be careful about matching the parameter names and their types exactly between the Stored Procedure SQL definition and the names we're passing in our code.

Our UserRepository code for fetching users from database looks like this:

public class UserRepository : IUserRepository
{
    private readonly AppDbContext context;

    public UserRepository(AppDbContext context)
    {
        this.context = context;
    }

    public async Task<int> GetOrCreateUserAsync(UserProfile id)
    {
        if (id != null)
        {
            // create parameters to pass to the exec Stored Procedure statement
            // and bind the parameters with the values passed
            var emailAddressParam = new SqlParameter("@emailAddress", id.EmailAddress);
            var passwordParam = new SqlParameter("@passwordHash", id.PasswordHash);

            // run the statement to exec Stored Procedure inside the database
            // and capture the return values
            var users = context
                        .UserProfiles
                        .FromSqlRaw("exec sp_GetUsers @emailAddress, @passwordHash", 
                            emailAddressParam, passwordParam)
                        .ToList();

            /* 
            // The above Stored Procedure does the same job 
            // as what the below code does
                    
            var users = context
                .UserProfiles
                .Where(x => x.EmailAddress == id.EmailAddress 
                    && x.PasswordHash == id.PasswordHash).ToList(); 
                */

            if (users.Count > 0)
            {
                return users.First().Id;
            }
            else
            {
                // Create a New User with the passed Details
            }
            
            return id;
        }

        return -1;
    }
}

Invoking a Stored Procedure that runs a Command - with Output Parameters:

In our UserRepository, we still need to fill in the scenario where the user doesn't exist and we need to insert a new user into the table. To do this, let's create a Stored Procedure which takes in the passed values and inserts a new row in the table. But we would also need to find out the new Id of the user created. How?

Enter, Output parameters.

Stored Procedures can't return values by definition but we can get a value from the Stored Procedure by means of output parameters - whose state is modified after a Stored Procedure is executed and we can read the changed state of the identifier passed.

The Stored Procedure looks like below:

#SQL#

CREATE OR ALTER PROCEDURE sp_CreateUser(
    @emailAddress VARCHAR(100), 
    @passwordHash VARCHAR(100), 
    @oidProvider VARCHAR(100), 
    @oid VARCHAR(100), 
    @Id int out)
AS
BEGIN

INSERT INTO UserProfiles(
    EmailAddress, 
    PasswordHash, 
    OIdProvider, 
    OId) 
    VALUES(@emailAddress, @passwordHash, @oidProvider, @oid);

-- the state of the output param is set to the Id of the created user record
SELECT @Id = Id FROM UserProfiles WHERE EmailAddress = @emailAddress;

END

When we execute this Stored Procedure with some parameters to test, we can print the output parameter passed to see the value.

#SQL#

declare @userId as int;
exec sp_CreateUser 'abc@123.com', 'xHefhRAndftKdhdtc', 'Legacy', NULL, @userId out
select @userId

To call this Stored Procedure in our code, we repeat the same steps we followed before: we create SqlParameters, define the SQL Statement that executes the Stored Procedure and finally call the ExecuteSqlRaw() method instead of the FromSqlRaw() method which is for SELECT queries. What changes is how we handle the output parameter we need to pass in our SQL Statement for the Stored Procedure and how we read it.

The code snippet looks like this:

#GetOrCreateUserAsync() method#

if (users.Count > 0)
{
    return users.First().Id;
}
else
{
    /*
        // The above Stored Procedure does the same job 
        // as what the below code does
        var user = id;
        await context.UserProfiles.AddAsync(user);
        await context.SaveChangesAsync();
        return user.Id;
    */

    // the emailAddress and passwordHash params are reused 
    // from their previous declaration in the method

    // define SqlParameters for the other two params to be passed
    var oidProviderParam = new SqlParameter("@oidProvider", id.OIdProvider);
    var oidParam = new SqlParameter("@oid", string.IsNullOrEmpty(id.OId) ? "" : id.OId);

    // define the output parameter that needs to be retained
    // for the Id created when the Stored Procedure executes 
    // the INSERT command
    var userIdParam = new SqlParameter("@Id", SqlDbType.Int);

    // the direction defines what kind of parameter we're passing
    // it can be one of:
    // Input
    // Output
    // InputOutput -- which does pass a value to Stored Procedure and retains a new state
    userIdParam.Direction = ParameterDirection.Output;
    
    // we can also use context.Database.ExecuteSqlCommand() or awaitable ExecuteSqlCommandAsync()
    // which also produces the same result - but the method is now marked obselete
    // so we use ExecuteSqlRawAsync() instead

    // we're using the awaitable version since GetOrCreateUserAsync() method is marked async
    await context.Database.ExecuteSqlRawAsync(
            "exec sp_CreateUser @emailAddress, @passwordHash, @oidProvider, @oid, @Id out", 
            emailAddressParam, 
            passwordParam, 
            oidProviderParam, 
            oidParam, 
            userIdParam);
    
    // the userIdParam which represents the Output param
    // now holds the Id of the new user and is an Object type
    // so we convert it to an Integer and send
    return Convert.ToInt32(userIdParam.Value);
}

Finally, we have our API controller that is the entry point for this functionality as below:

[ApiController]
[Route("api/[controller]")]
public class UsersController : ControllerBase
{
    private readonly IUserRepository _repository;
    public UsersController(IUserRepository repository)
    {
        _repository = repository;
    }

    [HttpPost, Route("GetOrCreate")]
    public async Task<IActionResult> GetOrCreate([FromBody]UserProfile id)
    {
        return Ok(await _repository.GetOrCreateUserAsync(id));
    }
}

When we test this setup with some sample payload, we get the result as below:

POST /api/users/getorcreate HTTP/1.1
Host: localhost:5001
Content-Type: application/json

{
    "emailAddress":"123451@abc.com",
    "passwordHash":"xHefhRAndftKdhdtc",
    "oidProvider":"Legacy"
}

Response:

11

StoredProcedure and Migrations:

In general scenarios, Stored Procedures are completely database related entities and are maintained at the database side. The API is aware of only the Stored Procedure names and its signature, so that it can pass parameters accordingly and look for results.

But scenarios of a CodeFirst approach, where the database is freshly created (although this is true for Proof-of-Concept kind of applications) from the application, we can push Stored Procedures via Migrations. To do this, let's take the InitialCreate migration in our API which is created when we first pushed our model into creating a database table. We can add our Stored Procedure script inside the migration as an entry in the Up() method.

public partial class InitialCreate : Migration
{
    private readonly string sp_GetUsers = @"CREATE OR ALTER PROCEDURE sp_GetUsers(
        @emailAddress VARCHAR(100), @passwordHash VARCHAR(100))
AS
BEGIN
SELECT * FROM UserProfiles 
WHERE EmailAddress = @emailAddress AND PasswordHash = @passwordHash;
END";
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.CreateTable(
            name: "UserProfiles",
            columns: table => new
            {
                Id = table.Column<int>(nullable: false)
                        .Annotation("SqlServer:Identity", "1, 1"),
                EmailAddress = table.Column<string>(nullable: true),
                PasswordHash = table.Column<string>(nullable: true),
                OIdProvider = table.Column<string>(nullable: true),
                OId = table.Column<string>(nullable: true)
            },
            constraints: table =>
            {
                table.PrimaryKey("PK_UserProfiles", x => x.Id);
            });
        
        // runs an SQL statement while applying the Migration
        // in this case - creates a new Stored Procedure
        migrationBuilder.Sql(sp_GetUsers);
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.DropTable(
        name: "UserProfiles");

        // runs an SQL statement while applying the Migration
        // in this case - drops the existing Stored Procedure
        migrationBuilder.Sql("drop procedure dbo.sp_GetUsers");
    }
}

To apply this migration we just run:

> dotnet ef database update

Some Final Thoughts:

Stored Procedures are well known programming structures in the world of PL/SQL which encapsulate a set of SQL statements and provide re-usability. We have seen how we can execute an already existing Stored Procedure in the database our application connects to by using the features of EFCore and also looked at how we can use output parameters while invoking them. Finally we've seen how to create Stored Procedures along side tables while applying a migration using the Code First approach.

  • Stored Procedures are ideal choice for scenarios where you need to run a complex SQL query or command on the database and working with LINQ seems difficult and tedious.
  • They also offer high-performance over LINQ-to-SQL approach that EFCore applies when we use LINQ syntax for database operations.
  • These are typically under a Database Programmer's bucket - so a web developer doesn't need to worry about any performance aspects of a Stored Procedure's execution and it also provides a clean separation of responsibilities between the domain layer and the application.
  • On the other hand, LINQ provides a quick solution for scenarios where we use simpler SQL statements and queries in our application and one application entity relates to only one domain entity - although we still have a little performance disadvantage over having the database run the SQL queries through Stored Procedures.

These are some of my thoughts on Stored Procedures. What do you think?

asp.net core stored procedure example asp.net core stored procedures asp.net core call stored procedure asp.net core execute stored procedure asp net core execute stored procedure with parameters asp.net core ef stored procedure asp net core web api stored procedure asp net core web api call stored procedure asp net core with stored procedure example using stored procedure in asp.net core how to execute stored procedure in asp.net core stored procedure in asp.net core asp net core mvc call stored procedure asp.net core entity framework call stored procedure using stored procedure in .net core stored procedure in .net core call stored procedure in asp.net core asp.net core dbcontext stored procedure asp net core entity framework database first stored procedure how to use stored procedure in asp.net core asp.net core entity framework stored procedure asp net core entity framework execute stored procedure call stored procedure from asp.net core how to call stored procedure in asp.net core how to call stored procedure in .net core stored procedure in asp.net core mvc stored procedure in asp.net core web api execute stored procedure in asp.net core asp.net core mvc stored procedure asp.net core run stored procedure asp.net core sql server stored procedure asp net core use stored procedure asp net core 3.1 stored procedure

We use cookies to provide you with a great user experience, analyze traffic and serve targeted promotions.   Learn More   Accept