34th Friday Fun Session (Part 1) – 22nd Sep 2017
Many a times, we use Entity Framework (EF), Microsoft’s recommended data access technology for an application, to execute (MS SQL Server) Stored Procedure (SP), and retrieve the results emitted by them. Here we discuss different kinds of output that a SP can produce and how we can retrieve them using EF.
SP output
A SP typically provides the following kinds of results:
- Return code (single integer)
- Output parameters (one or more, any data type)
- Result set
- Single result set
- Multiple result set
- All having the same schema
- Having different schema
Return code
SP can return a single integer return code. Return statement without any value (null) would automatically return 0. It is mostly used to exit execution of a SP when certain condition is met.
CREATE Procedure SpReturnCode AS BEGIN RETURN 1; END
Using T-SQL we can execute SP like below.
DECLARE @Success AS INT EXEC @Success = [SpReturnCode]; PRINT @Success
Output parameter
SP can return one or more values, each having its own data type.
CREATE Procedure SpOutputParameter (@InputValue INT, @OutputValue INT OUTPUT) AS BEGIN SET @OutputValue = @InputValue + 1; RETURN 0; END
Using T-SQL we can execute SP like below.
DECLARE @ReturnValue INT; EXECUTE [SpOutputParameter] 2, @ReturnValue OUTPUT; PRINT @ReturnValue
Single result set
Returns a result set having 0 or more rows of a certain schema. The following SP returns a result set with a single column named, Success.
CREATE Procedure SpSingleResultSet AS BEGIN SELECT 3 AS Success RETURN 0; END
Multiple result set, same schema
The following SP returns the result set for Employee schema twice.
CREATE Procedure SpMultipleResultSetSameSchema AS BEGIN SELECT * FROM [Employee] SELECT * FROM [Employee] SELECT [EmployeeId] > 10 RETURN 0; END
The following SP returns a result set that is not associated with any database entity.
CREATE Procedure SpMultipleResultSetNonDbContextEntity
AS
BEGIN
DECLARE @Loop AS INT
SET @Loop = 0
WHILE @Loop < 10
BEGIN
EXEC SpSingleResultSet
SET @Loop = @Loop + 1
END
RETURN 0;
END
Multiple result set, multiple schema
The following SP returns two different result sets: one for Company and another for Employee.
CREATE Procedure SpMultipleResultSetMultipleSchema AS BEGIN SELECT * FROM [Company] SELECT * FROM [Employee] RETURN 0; END
Executing SP using EF
We will use the following different ways in EF to read different kinds of SP output as described earlier:
- ExecuteSqlCommand
- SqlQuery
- CreateCommand/ExecuteReader
ExecuteSqlCommand
This executes a given DDL/DML command. This can be executed when no result set needs to be returned.
Return code
Return code does not require any explicit output parameter to be used in the SP. However, while calling from EF, it should be treated as an output parameter by specifying the direction for the parameter.
SqlParameter returnCode = new SqlParameter("@ReturnCode", SqlDbType.Int);
returnCode.Direction = ParameterDirection.Output;
Db.Database.ExecuteSqlCommand("exec @ReturnCode = [SpReturnCode] ", returnCode);
var returnCodeValue = (int)returnCode.Value;
Output Parameter
For each of the output parameters, we need to declare an output parameter in EF matching the appropriate data type.
SqlParameter inputParam = new SqlParameter("@InputValue", SqlDbType.Int);
inputParam.Direction = ParameterDirection.Input;
SqlParameter outputParam = new SqlParameter("@OutputValue ", SqlDbType.Int);
outputParam.Direction = ParameterDirection.Output;
Db.Database.ExecuteSqlCommand("[SpOutputParameter] @InputValue, @OutputValue OUT", inputParam, outputParam);
var returnValue = (int)outputParam.Value;
SqlQuery
SqlQuery is usually used when SP returns a single result set. However, it can return any data type including primitive types – not necessarily only entity type. If the SP returns multiple result sets, it will only get the first one. However, the complete execution of the entire SP does happen.
public class SuccessSet
{
public int Success { get; set; }
}
var result = Db.Database.SqlQuery("[SpSingleResultSet]").ToList();
CreateCommand/ExecuteReader
When multiple result sets to be returned this method can be used. We need to use IObjectContextAdapter interface that makes use explicit interface implementation.
Db.Database.Initialize(force: false);
var cmd = Db.Database.Connection.CreateCommand();
cmd.CommandText = "[SpMultipleResultSetSameSchema]";
Db.Database.Connection.Open();
var reader = cmd.ExecuteReader();
var employees =
((System.Data.Entity.Infrastructure.IObjectContextAdapter)Db)
.ObjectContext
.Translate<Employee>(reader, "Employee", System.Data.Entity.Core.Objects.MergeOption.AppendOnly);
foreach (var employee in employees)
Console.WriteLine(employee. Name);
While(!reader.NextResult())
{
employees =
((System.Data.Entity.Infrastructure.IObjectContextAdapter)Db)
.ObjectContext
.Translate<Employee>(reader, "Employee", System.Data.Entity.Core.Objects.MergeOption.AppendOnly);
foreach (var employee in employees)
Console.WriteLine(employee. Name);
}
Db.GetDatabase().Connection.Close();
When result set is not in DbContext, slight modification is required.
var successSets = ((System.Data.Entity.Infrastructure.IObjectContextAdapter)Db) .ObjectContext .Translate<SuccessSet>(reader);
When different schema are used we can still use the same, we just need to use the right object while translating.
var companies = ((System.Data.Entity.Infrastructure.IObjectContextAdapter)Db) .ObjectContext .Translate<Company>(reader, "Company", System.Data.Entity.Core.Objects.MergeOption.AppendOnly); reader.NextResult(); var employees = ((System.Data.Entity.Infrastructure.IObjectContextAdapter)Db) .ObjectContext .Translate<Employee>(reader, "Employee", System.Data.Entity.Core.Objects.MergeOption.AppendOnly);
Sharing Experiences 