понедельник, 3 января 2011 г.

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);               
}

Комментариев нет: