How to call Stored Procedures in ASP.NET Core

Let's talk about how we can call Stored Procedures from our ASP.NET Core API using Entity Framework Core with an illustrating example.

Table of Contents

  1. Why are Stored Procedures important for Database operations?
  2. Getting Started – API Project that uses Stored Procedures
  3. How to invoke a Stored Procedure that executes a Command
  4. How to call a Stored Procedure using ADO.NET via Entity Framework Core
  5. How to invoke a Stored Procedure with Output Parameters
  6. How to push Stored Procedures via Code First approach
  7. Conclusion

Why are Stored Procedures important for Database operations?

Stored Procedures encapsulate complex queries or programming logic which, when executed on a database produces data results. These programming structures help developers in reusing the scripts whenever required, while creating opportunities for separating database programming from the upper tiers.

I can see two advantages of Stored Procedures –

  1. Stored Procs are created as database objects inside the database directly and are pre-compiled. Database creates an execution plan for these stored procs which results in visible performance.
  2. Since they are separated from the application tier, the database logic is decoupled from the application logic. This makes applications easy to manage in case of any database side issues or optimizations

The point I’m trying to make here is that by having a separate database tier with its scripts separated from the web-server or app-server tiers, we can have a loosely coupled system where when a db operation requires tuning, the application doesn’t need to be modified.

In this article, let us look at how we can invoke Stored Procedures in a database from our ASP.NET Core. We will look at the various approaches available for us to call Stored Procedures, including EntityFrameworkCore.

We shall also look at how we can maintain Stored Procedures inside our API code-base and add them to a new database through code-first approach.

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 the database. We begin by creating a new web api application via dotnet CLI.

> dotnet new webapi --name SpReadersApi

Once created, Let’s add the necessary EntityFrameworkCore 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>net6.0</TargetFramework>
    <Nullable>enable</Nullable>
    <ImplicitUsings>enable</ImplicitUsings>
  </PropertyGroup>
  <ItemGroup>
    <!-- EFCore tools needed for Migrations -->
    <PackageReference Include="Microsoft.EntityFrameworkCore.Design" Version="6.0.0">
      <IncludeAssets>runtime; build; native; contentfiles; analyzers; buildtransitive</IncludeAssets>
      <PrivateAssets>all</PrivateAssets>
    </PackageReference>
    <!-- EFCore support for SQL Server -->
    <PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer" Version="6.0.0" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.Tools" Version="6.0.0">
      <IncludeAssets>runtime; build; native; contentfiles; analyzers; buildtransitive</IncludeAssets>
      <PrivateAssets>all</PrivateAssets>
    </PackageReference>
    <PackageReference Include="Swashbuckle.AspNetCore" Version="6.2.3" />
  </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 UserProfileId { get; set; }
        public string EmailAddress { get; set; }
        public string PasswordHash { get; set; }
        public string OIdProvider { get; set; }
        public string OId { get; set; }
    }

    public class UserRole
    {
        [Key]
        public int UserRoleId { get; set; }
        public string RoleName { get; set; }
        public DateTime CreatedOn { get; set; }

        [ForeignKey("UserProfile")]
        public int UserProfileId { get; set; }
    }
}

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

We will also configure EntityFrameworkCore and DbContext to the Service Collection.

services.AddScoped<IUserRepository, UserRepository>();

// normal style (until .NET 5)
services.AddDbContext<AppDbContext>(options =>
{
    options.UseSqlServer(
        Configuration.GetConnectionString("DefaultConnection"));
});

// .NET 6 Style
services.AddSqlServer<AppDbContext>(
    Configuration.GetConnectionString("DefaultConnection"));

I have 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);

        Task<IEnumerable<UserRolesDTO>> GetUserRolesByProfileId(int profileId);
    }
}

The implementation of this interface IUserRepository internally calls 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_GetUsers looks like below:

-- STORED PROCEDURE
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.

We also have another Stored Procedure that returns all the User Roles assigned to the User. This is as below –

-- STORED PROCEDURE
-- returns data from multiple entities
-- UserProfiles and UserRoles
CREATE OR ALTER PROCEDURE sp_GetUserRoles (
    @profileId int
) AS BEGIN
SELECT *
FROM UserProfiles up
INNER JOIN UserRoles ur ON up.UserProfileId = ur.UserProfileId
WHERE up.UserProfileId = @profileId;
END

The above Stored Procedure doesn’t fit a particular entity (UserProfile or UserRole) and instead returns all the rows joining both the tables.

namespace SpReadersApi.Contracts.DTO
{
    public class UserRolesDTO
    {
        public int UserProfileId { get; set; }
        public int UserRoleId { get; set; }
        public string RoleName { get; set; }
        public DateTime CreatedOn { get; set; }
    }
}

Now let’s see how we shall call these Stored Procedure inside UserRepository.

How to invoke a Stored Procedure that executes a Command

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 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 –

  1. FromSqlRaw() – used to run query statements that return records from the database
  2. ExecuteSqlRaw() / ExecuteSqlRawAsync() – executes a command that can modify data on the database (typically DML commands like INSERT, UPDATE or DELETE)

In the above case, since we’re executing a Stored Procedure that queries for results, we use the former – FromSqlRaw().

The method FromSqlRaw() takes two parameters –

  1. An SQL string – in our case, it shall be the “exec sp_GetUser along with parameters” SQL statement to execute the Stored Procedure
  2. 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;
    }
}

Observe that we are calling our Stored Procedure sp_GetUsers on top of the DbSet UserProfiles. This works because the return type of the Stored Procedure is UserProfile.

On the other hand if we try to invoke the other Stored Procedure sp_GetUserRoles over UserProfile DbSet, we get an Exception. This is because Entity Framework Core cannot map the result to a particular Entity mapped to it.

So how should we invoke this stored procedure? We have two options to do this –

  1. Use the old school ADO.NET SqlCommand approach; or
  2. Use any micro ORM such as Dapper

How to call a Stored Procedure using ADO.NET via Entity Framework Core

Calling a Stored Procedure using SqlCommand or SqlDataAdapter via Entity Framework Core is simple. You just need to pull the instance of SqlDatabase from the DbContext and from there on its all old school route.

We need to first install Microsoft.Data.SqlClient package which provides the extension method GetDbConnection() that returns an SqlCommand.

<PackageReference Include="Microsoft.Data.SqlClient" Version="5.1.1" />

The fully solved method looks like below –

public class UserRepository : IUserRepository
{
    private readonly AppDbContext context;

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

    public async Task<int> GetOrCreateUserAsync(UserProfile id)
    {
        // implementation mentioned above
    }

    public IEnumerable<UserRolesDTO> GetUserRolesByProfileId(int userProfileId)
    {
        // you must not CLOSE this connection, as it is used by Entity Framework Core
        SqlConnection dbConnection = (SqlConnection)context.Database.GetDbConnection();

        using (SqlCommand cmd = new SqlCommand("sp_GetUserRoles", dbConnection))
        {
            SqlDataAdapter adapt = new SqlDataAdapter(cmd);
            adapt.SelectCommand.CommandType = CommandType.StoredProcedure;
            adapt.SelectCommand.Parameters.Add(new SqlParameter("@ProfileId", SqlDbType.Int));
            adapt.SelectCommand.Parameters["@ProfileId"].Value = userProfileId;

            // fill the data table - no need to explicitly call `conn.Open()` -
            // the SqlDataAdapter automatically does this (and closes the connection, too)
            DataTable dt = new DataTable();
            adapt.Fill(dt);

            var userRolesResult = new List<UserRolesDTO>();

            if (dt.Rows.Count > 0)
            {
                foreach (DataRow row in dt.Rows)
                {
                    var UserProfileId = row["UserProfileId"].ToString();
                    var UserRoleId = row["UserRoleId"].ToString();
                    var RoleName = row["RoleName"].ToString();
                    var CreatedOn = row["CreatedOn"].ToString();

                    userRolesResult.Add(
                        new UserRolesDTO
                        {
                            UserProfileId = Convert.ToInt32(UserProfileId),
                            UserRoleId = Convert.ToInt32(UserRoleId),
                            CreatedOn = Convert.ToDateTime(CreatedOn),
                            RoleName = RoleName
                        }
                    );
                }
            }

            return userRolesResult;
        }
    }
}

The other approach will be using Dapper ORM, as mentioned above. I have written a detailed article on how to use Dapper ORM for your ASP.NET Core application, please do take a look at it.

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. We do this by creating a Stored Procedure which takes in the passed values and inserts a new row in the table.

What if we need to find out the Id of the newly created user, How?

Answer is, Output parameters.

How to invoke a Stored Procedure with 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:

  1. We create SqlParameters, define the SQL Statement that executes the Stored Procedure and finally
  2. 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 –

namespace SpReadersApi.Providers.Repositories;

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();

            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 obsolete
                // 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);
            }

            return id;
        }

        return -1;
    }
}

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

How to push Stored Procedures via Code First approach

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 was created when we first pushed our model into creating a database table.

> dotnet ef migrations add InitialCreate

We can then 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

Conclusion

Stored Procedures are well known programming structures in the world of PL/SQL which encapsulate a set of SQL statements and provide reusability.

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 alongside tables while applying a migration using the Code First approach.

Some thoughts I wish to share with you –

  1. Stored Procedures are ideal choices for scenarios where you need to run a complex SQL query or command on the database, where working with LINQ seems difficult and tedious.
  2. They also offer a better performance over LINQ-to-SQL approach that EFCore applies when we use LINQ syntax for database operations.
  3. These are typically under a Database developer’s bucket – so an API 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.
  4. 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.

You can refer to the Official Documentation for more information on these methods I mentioned above.

Buy Me A Coffee

Found this article helpful? Please consider supporting!

Tags
Ram
Ram

I'm a full-stack developer and a software enthusiast who likes to play around with cloud and tech stack out of curiosity. You can connect with me on Medium, Twitter or LinkedIn.

Related Posts

One comment

Leave a Reply

Your email address will not be published. Required fields are marked *