Wriju's BLOG

.NET, Cloud and everything

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<T> or IMultipleResults<T>, 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);               


}


Namoskar!!!