LINQ to SQL : Returning Scalar Value from Stored Procedure

In LINQ to SQL it is not that easy thing to achieve as compared to other features. Let us assume you have a Stored Procedure like,

Case 1: With Output Parameter

CREATE PROCEDURE [dbo].[GetEmployeeCount]

      @OutVal DateTime OUTPUT

AS

BEGIN

      SELECT @OutVal = GetDate()

END

You need to write code which will look like,

using (TestDBDataContext db = new TestDBDataContext())

{

    //Need a Nullable type here

    //and you need to have some value to it

    DateTime? dt = null;

    var q = db.GetEmployeeCount(ref dt);

    Console.WriteLine(dt);               

}

Case 2: With Return (only for Integers)

CREATE PROCEDURE [dbo].[GetEmployeeCountRet]         

AS

BEGIN

      DECLARE @Ret INT

      SELECT @Ret = COUNT(*) FROM Emp

      RETURN @Ret

END

Your code may look like,

using (TestDBDataContext db = new TestDBDataContext())

{

    //For Stored Procedure with Return value (for Integer)

    //returns Int

    var q = db.GetEmployeeCountRet();

    Console.WriteLine(q);               

}

You cannot simply say

SELECT COUNT(*) FROM Emp and capture the value in a variable. Because in LINQ to SQL a Stored Procedure either returns ISingleResult or IMultipleResults, so capturing single value becomes very tricky.

So when you have to do it go for Scalar-Valued function

Case 3: Using Scalar-Values Functions

ALTER FUNCTION [dbo].[fn_GetEmployeeCount]()

RETURNS int

AS

BEGIN

      DECLARE @ResultVar int

     

      SELECT @ResultVar = Count(*) FROM Emp

     

      RETURN @ResultVar

END

You code,

using (TestDBDataContext db = new TestDBDataContext())

{

    var q = db.fn_GetEmployeeCount();

    Console.WriteLine(q);               

}