Execute SQL Stored Procedures using FromSql() & ExecuteCommand() methods in Entity Framework Core

Execute SQL Stored Procedures using FromSql() & ExecuteCommand() methods in Entity Framework Core

SQL Stored Procedures can also be easily executed using the .FromSQL() & .ExecuteCommand() methods in Entity Framework Core. The result from Stored Procedure is returned back to the code.

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 .FromSql() method

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

The SqlParameter class resides inside System.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
                        }};
var students = context.Student.FromSql("[dbo].[sp_GetStudents] @Name, @Standard", param);

Stored Procedures that return Mulitple Record Sets

You have to remember that the .FromSql() 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())
    {
       string studentName= Convert.ToString(reader["Name"]); // name from student table
    }
    reader.NextResult(); //move the next record set
    while (reader.Read())
    {
        string city = Convert.ToString(reader["City"]); // city from student address table
    }
}

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

Execute Stored Procedure using ExecuteSqlCommand()

The ExecuteSqlCommand() is another method to execute Stored Procedures and Raw SQL in Entity Framework Core. But unlike FromSql() method, it returns the number of affected rows.

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

Working with output parameters of Stored Procedure using ExecuteSqlCommand()

Output parameters are provided with out keyword in .FromSql() method.

Here I have updated the Stored Procedure with one output parameter – ‘@TotalStudents’ which returns the total students in the table.

CREATE PROCEDURE [dbo].[sp_GetStudents]
	@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 add a new SqlParameter with direction output:

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. There are total 3 parameters and the last one is the output parameter here.

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=context.Database.ExecuteSqlCommand("[dbo].[sp_GetStudents] @Name, @Standard, @TotalStudents out", param);
int totalStudents = Convert.ToInt32(param[2].Value); 

DOWNLOAD

Share this article -

yogihosting

ABOUT THE AUTHOR

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