How to get stored procedure OUTPUT Parameters in Entity Framework

Calling stored procedure from Entity framework code first is not an issue. Its explained very well in Entity Framework tutorials as well [Here] , Let see how to call custom stored procedure from C#


This is my stored procedure to return total number of employes from database

USE [AdventureWorksDW2008R2]
GO
/****** Object:  StoredProcedure [dbo].[getEmployeeCount] 
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Raju Melveetilpurayil
-- Create date: 
-- Description: Get total number of employes from AdventureWorksDW2008R2 database
-- =============================================
ALTER PROCEDURE [dbo].[getEmployeeCount] 
AS
BEGIN
 SET NOCOUNT ON;

 SELECT COUNT(*)AS EmpCount FROM dbo.DimEmployee
END

--
--

Now we can check how to call above stored procedure from C#

using (var db = new EmployeeContext())
{
 var totalNumberOfEmployes = db.Database.SqlQuery<int>("getEmployeeCount").ToList();
 int totalNumber = totalNumberOfEmployes.FirstOrDefault<int>();
}  

So  we can call a stored procedure with some parameter including OUTPUT parameter

USE [AdventureWorksDW2008R2]
GO
/****** Object:  StoredProcedure [dbo].[getEmployeeByPageNumber]    Script Date: 08/10/2014 22:25:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:  <Raju Melveetilpurayil>
-- Create date: <Create Date,,>
-- Description: <Get rows by row between limit>
-- =============================================
ALTER PROCEDURE [dbo].[getEmployeeByPageNumber]
 -- Add the parameters for the stored procedure here
 @Start INT,
 @End INT,
 @TotalCount INT OUTPUT
AS
BEGIN
 -- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.
 SET NOCOUNT ON;
SELECT @TotalCount=COUNT(*) FROM dbo.DimEmployee
SELECT [EmployeeKey]
      ,[FirstName]
      ,[LastName]
      ,[Title]
      ,[EmailAddress]
      ,[DepartmentName] FROM 
(
 SELECT ROW_NUMBER() OVER(ORDER BY EmployeeKey) ROW_NUM, * FROM DimEmployee
 ) AS K
 WHERE ROW_NUM >@Start AND ROW_NUM <=@End
  
--
--
END

Actually the above script I am using for to get data between some limit.. I will use this same script in my later articles for How to do paging in MVC 5 We need to pass one SqlParameter parameter and set its direction to System.Data.ParameterDirection.Output, after executing the function SqlParameter hold the OUTPUT data.

var spOutput = new SqlParameter {
                    ParameterName = "@TotalCount",
                    SqlDbType = System.Data.SqlDbType.BigInt,
                    Direction = System.Data.ParameterDirection.Output 
                };

we pass other parameter like normal SqlParameter

var start = new SqlParameter("@Start", limitStart);
var end = new SqlParameter("@End", limitEnd);

db.Database.SqlQuery help us to call raw Sql from entity framework The whole code

//
// calling stored procedure to get the total result count

var start = new SqlParameter("@Start", limitStart);
var end = new SqlParameter("@End", limitEnd);
               
//
// setting stored procedure OUTPUT value
// This return total number of rows, and avoid two database call for data and total number of rows 
var spOutput = new SqlParameter {
                    ParameterName = "@TotalCount",
                    SqlDbType = System.Data.SqlDbType.BigInt,
                    Direction = System.Data.ParameterDirection.Output 
                };
//
//calling stored procedure to get paged data.
  List<DimEmployee> AllEmpoyees = db.Database.SqlQuery<DimEmployee>(
                               "getEmployeeByPageNumber @Start,@End,@TotalCount out",
                                start, end, spOutput)
                                .ToList();
//
// setting total number of records
totalCount = int.Parse(spOutput.Value.ToString());


//
//

Happy programming ..