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 [email protected] AND [email protected]
END

Executing the above Stored Procedure using “FromSqlRaw()” method

The above stored procedure has 2 input parameters which have to be provided when using the FromSqlRaw() method. Therefore I 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 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
                        }};
List<Student> studentList = context.Student.FromSqlRaw("[dbo].[sp_GetStudents] @Name, @Standard", param).ToList();

Stored Procedures that return Mulitple Record Sets

You have to 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 [email protected] 
    SELECT * FROM StudentAddress WHERE [email protected]
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 ExecuteSqlRawAsync() is another method to execute Stored Procedures and Raw SQL in Entity Framework Core asynchronously. 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 context = new SchoolContext(); 
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 I have add 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 [email protected] AND [email protected]
    SET @TotalStudents= (SELECT COUNT(*) FROM Student)
END

To execute this Stored Procedure in your EF Core you 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 you 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); 

Download the source codes:

DOWNLOAD

Share this article -

yogihosting

ABOUT THE AUTHOR

This article has been written by the Technical Staff of YogiHosting. Check out other articles on "ASP.NET Core, jQuery, EF Core, SEO, jQuery, HTML" and more.