Partilhar via

LINQ to SQL : Returning Scalar Value from Stored Procedure

  • Artigo

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!!!

Comments

  • Anonymous
    April 09, 2009
    PingBack from http://microsoft-sharepoint.simplynetdev.com/linq-to-sql-returning-scalar-value-from-stored-procedure/

  • Anonymous
    June 16, 2009
    The comment has been removed

  • Anonymous
    November 10, 2009
    Thanks a lot ur brilliant iam trying this from 2 days

  • Anonymous
    December 02, 2009
    Bru, why can't the other blogs get to the point like you do...and effectively! Thanks.

  • Anonymous
    January 03, 2010
    like the using thing, did not 'get it' to start but now realise what it does, makes things easier to read too !

  • Anonymous
    March 15, 2010
    How do use resolve this: Cannot implicitly convert 'int?' to 'string' Seriously now

  • Anonymous
    April 05, 2010
    Thanks for this post! So many other posts online sending me off 10 different directions, your solution was simple and direct, awesome.

  • Anonymous
    October 17, 2010
    This was EXACTLY the info I was looking for:) www.freedownloadsatellitetv.com

  • Anonymous
    July 10, 2011
    Thanks for your support. Iwas looking for this only. I know how to return without using Linq to Sql,i.e.in Sql which returns single value. I was knowing that in Linq to Sql returns ISingleResult<T> or IMultipleResults<T> kinds of value but i wasn't know how to get it in Integer. Thanks a lot.

  • Anonymous
    October 27, 2011
    wriiju, Wil this same approach work for bit values? Thanks Steve SteveStacel@gmail.com

  • Anonymous
    August 30, 2014
    However, TSQL Functions can not be used to perform CRUD operations, so if you are trying to return @@ROWCOUNT to know how many records a delete or an insert changed, this method fails and there is no other good way to do it.

  • Anonymous
    November 29, 2016
    Thank you for sharing your info. I really appreciate your efforts and I am waiting for your further post thank you once again.