ASP.NET Core Entity Framework call stored procedure

I am using ASP.NET Core Entity Framework and I would like to call a simple stored procedure.

I created the stored procedure in my migrations like this:

public partial class spGetAvailableCourses : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        var sp = @"CREATE PROCEDURE [dbo].[GetAvailableCourses]
            AS
            BEGIN
                SELECT COUNT(courses.Enrolled) FROM Courses WHERE Courses.Capacity > Courses.Enrolled;
            END";

        migrationBuilder.Sql(sp);
    }

I can call the stored procedure in SQL Server with the following command.

EXEC dbo.GetAvailableCourses

But when I try and call the stored procedure in in my ICourseRepository, it doesn't work, I get minus one returned.

Can someone please tell me the correct of calling the stored procedure? Thanks

public class CourseRepository : ICourseRepository
{
    private readonly DataContext _context;

    public CourseRepository(DataContext context)
    {
        _context = context;
    }
   
    public Task<CoursesAvailableCount> CoursesAvailableCount()
    { 
        var ss = _context.Database.ExecuteSqlRaw("GetAvailableCourses");
        return null;
    }

I also tried

    public async Task<CoursesAvailableCount> CoursesAvailableCount()
    {
        var s = await _context.Database.ExecuteSqlCommandAsync("GetAvailableCourses");
    }

In addition to the ExecuteSqlCommand method, the DbContext.Database property provides an API that allows you to perform ADO.NET operations directly. The GetDbConnection method returns a DbConnection object representing the context's underlying connection. From that point, you can revert to the familiar ADO.NET APIs:

using (var command = _context.Database.GetDbConnection ().CreateCommand ())
{
   command.CommandText = "SP_NAME";
   command.CommandType = CommandType.StoredProcedure;
   _context.Database.OpenConnection ();
   using (var result = command.ExecuteReader ())
   {
      if (result.HasRows)
      {
         result.Read ();
         var x = result.GetInt32 (0); // x = your sp count value
      }
   }
}

EDIT (extension example):

public static class EfCoreExtensions
{
    public static int? Execute_SingleValue_SP_AsInt(this AppDbContext context,string SpName)
    {
        using (var command = context.Database.GetDbConnection().CreateCommand())
        {
            command.CommandText = SpName;
            command.CommandType = System.Data.CommandType.StoredProcedure;
            context.Database.OpenConnection();
            using (var result = command.ExecuteReader())
            {
                if (result.HasRows)
                {
                    result.Read();
               var x = result.GetInt32(0); // x = your sp count value
                    return x;
                }
            return null;
            }
        
        }
    }
}

For .net core 3.1, using stored procedures with parameters. Use the below code. It works for me. Enjoy!!!

using (var command = DbContext.Database.GetDbConnection().CreateCommand())
                {
                    command.CommandText = "Users_SP";
                    command.CommandType = CommandType.StoredProcedure;
                    command.Parameters.Add(new SqlParameter(“@usernme”, username));
                    command.Parameters.Add(new SqlParameter("@TransDate", DateTime.Now));
                    DbContext.Database.OpenConnection();
                    using (var result = command.ExecuteReader())
                    {
                        if (result.HasRows)
                        {
                            while (result.Read())
                            {
                                response = Convert.ToString(result["Amount"]);
                            }
                        }
                    }
                }

ASP.NET Core Entity Framework call stored procedure

Related

Recent Posts