Execute SQL Stored Procedures using FromSqlRaw() & ExecuteSqlRawAsync() methods in Entity Framework Core

Execute SQL Stored Procedures using FromSqlRaw() & ExecuteSqlRawAsync() methods in Entity Framework Core

SQL Stored Procedures can be easily executed using the FromSqlRaw() & ExecuteCommand() methods in Entity Framework Core. This tutorial will cover them in details.

On my last tutorial called Execute Raw SQL Queries using FromSqlRaw() method in Entity Framework Core I created the entities and Database Context. I will continue to use them in this tutorial to.
Stored Procedure

Below is the SQL Stored Procedure that returns all Students that have a particular name and standard. The name & standard values are provided through the name & standard input parameters of the Stored Procedure.

CREATE PROCEDURE [dbo].[sp_GetStudents]
    @Name       VARCHAR(50),
    @Standard   INT
AS
BEGIN
    SELECT * FROM Student Where Name=@Name AND Standard=@Standard
END

Entity Framework Core Execute Stored Procedure with “FromSqlRaw()” method

The above stored procedure has 2 input parameters which have to be provided when using the FromSqlRaw() method. Therefore we have to use SqlParameter array to sets these parameters.

The SqlParameter class resides inside Microsoft.Data.SqlClient namespace.

The below code executes the stored procedure.

var param = new SqlParameter[] {
                        new SqlParameter() {
                            ParameterName = "@Name",
                            SqlDbType =  System.Data.SqlDbType.VarChar,
                            Size = 100,
                            Direction = System.Data.ParameterDirection.Input,
                            Value = "Tony"
                        },
                        new SqlParameter() {
                            ParameterName = "@Standard",
                            SqlDbType =  System.Data.SqlDbType.Int,
                            Direction = System.Data.ParameterDirection.Input,
                            Value = 10
                        }};
List<Student> studentList = context.Student.FromSqlRaw("[dbo].[sp_GetStudents] @Name, @Standard", param).ToList();

Stored Procedures that return Mulitple Record Sets

Remember that the .FromSqlRaw() method gets only one record set from a stored procedure. If the Stored procedure returns multiple record sets then use the ExecuteReader() method.

The below procedure returns 2 record sets – one from Student table and other from StudentAddress table.

CREATE PROCEDURE [dbo].[sp_MultiRecordSets]
    @IdStu   INT,
    @IdAdd   INT 
AS
BEGIN
    SELECT * FROM Student Where Id=@IdStu 
    SELECT * FROM StudentAddress WHERE Id=@IdAdd
END

This is how to use ExecuteReader() method to get the values from both the record sets.

var param = new SqlParameter[] {
                        new SqlParameter() {
                            ParameterName = "@IdStu",
                            SqlDbType =  System.Data.SqlDbType.Int,
                            Direction = System.Data.ParameterDirection.Input,
                            Value = 5
                        },
                        new SqlParameter() {
                            ParameterName = "@IdAdd",
                            SqlDbType =  System.Data.SqlDbType.Int,
                            Direction = System.Data.ParameterDirection.Input,
                            Value = 10
                        }};
 
var context = new SchoolContext();
using (var cnn = context.Database.GetDbConnection())
{
    var cmm = cnn.CreateCommand();
    cmm.CommandType = System.Data.CommandType.StoredProcedure;
    cmm.CommandText = "[dbo].[sp_MultiRecordSets]";
    cmm.Parameters.AddRange(param);
    cmm.Connection = cnn;
    cnn.Open();
    var reader = cmm.ExecuteReader();
 
    while (reader.Read())
    {
       // name from student table 
       string studentName= Convert.ToString(reader["Name"]); 
    }
    reader.NextResult(); //move the next record set
    while (reader.Read())
    {
        // city from student address table
        string city = Convert.ToString(reader["City"]); 
    }
}

Note : NextResult() moves to the next record set.

What are the 3 most important topics of Entity Framework Core ?

Execute Stored Procedure using ExecuteSqlRawAsync()

The Entity Framework Core ExecuteSqlRawAsync() method is used to execute Stored Procedures and Raw SQL in asynchronous manner. But unlike FromSqlRaw() method, it returns the number of affected rows. Note that there is also another variant called ExecuteSqlRaw() which is of synchronous type.

var rowsAffected = await context.Database.ExecuteSqlRawAsync("Update Students set Name = 'Donald Trump' where Id = 5");

Working with output parameters of Stored Procedure using ExecuteSqlRawAsync()

Here we have added a new Stored Procedure with one output parameter called @TotalStudents which returns the total students in the table.

CREATE PROCEDURE [dbo].[sp_GetStudentsNew]
    @Name               VARCHAR(50),
    @Standard           INT,
    @TotalStudents      INT            OUTPUT
AS
BEGIN
    SELECT * FROM Student Where Name=@Name AND Standard=@Standard
    SET @TotalStudents= (SELECT COUNT(*) FROM Student)
END

To execute this Stored Procedure in EF Core we will not have to provide an SqlParameter with output direction.

new SqlParameter() {
    ParameterName = "@TotalStudents",
    SqlDbType =  System.Data.SqlDbType.Int,
    Direction = System.Data.ParameterDirection.Output, // direction output
}

Once the stored procedure is executed we can get the value returned by the output parameter like this:

int totalStudents = Convert.ToInt32(Convert.ToString(param[2].Value));

Note : Parameter starts with index 0. Here there are total 3 parameters and the last one is the output parameter.

The complete code is:

var context = new SchoolContext();
 
var param = new SqlParameter[] {
                        new SqlParameter() {
                            ParameterName = "@Name",
                            SqlDbType =  System.Data.SqlDbType.VarChar,
                            Size = 100,
                            Direction = System.Data.ParameterDirection.Input,
                            Value = "Tony"
                        },
                        new SqlParameter() {
                            ParameterName = "@Standard",
                            SqlDbType =  System.Data.SqlDbType.Int,
                            Direction = System.Data.ParameterDirection.Input,
                            Value = 10
                        },
                        new SqlParameter() {
                            ParameterName = "@TotalStudents",
                            SqlDbType =  System.Data.SqlDbType.Int,
                            Direction = System.Data.ParameterDirection.Output,
                        }};
int affectedRows = await context.Database.ExecuteSqlRawAsync("[dbo].[sp_GetStudentsNew] @Name, @Standard, @TotalStudents out", param);
int totalStudents = Convert.ToInt32(param[2].Value); 

SHARE THIS ARTICLE

  • linkedin
  • reddit
yogihosting

ABOUT THE AUTHOR

I hope you enjoyed reading this tutorial. If it helped you then consider buying a cup of coffee for me. This will help me in writing more such good tutorials for the readers. Thank you. Buy Me A Coffee donate