CODEWITHSUNDEEP
c#sqlsql-serverlinqstored-procedures
Jamesla
Jamesla

Reputation: 1408

Stored Procedure return values with linq data context

I am trying to access the return value of a stored procedure with Linq 

DECLARE @ValidToken int = 0 //I have also tried using a bit instead of an int here.

IF EXISTS(SELECT 1 FROM Tests WHERE TestToken = @Token)
    select @ValidToken = 1

return @ValidToken

This works when running the SP through sql studio. However I am trying to run it with linq using the datacontext class and it is always returning -1.

using (DataEntities dataEntities = new DataEntities())
    {
        int query = data.ValidateToken(id);
        Response.Write(query.ToString());
    }

query will always equal -1 and I am not sure why.

I have looked online and it would appear that there are easier ways to get the return value however I would rather stick to Linq as that is what the rest of the program is using.

Upvotes: 12

Views: 18945

Answers (6)

Abhi
Abhi

Reputation: 2013

NOTE - This reference code is useful only when Stored Procedure returning one row.

If you are using "select" in Stored procedure here is the working code -

ALTER PROCEDURE [dbo].[sp_GetData]
-- Add the parameters for the stored procedure here
@Id int
AS
BEGIN
    DECLARE @Ret INT 
    SELECT @Ret = COUNT(*) FROM Test 
    set @Ret =@Id+1;
    set @Ret1 =@Id+2; 
    select @Ret as Id, @Ret1 as Id2
END

C# code -

using (DataAccessDataContext db = new DataAccessDataContext())
{
   Cls_Response rtn = new Cls_Response();
   try
   { 
     var SP_data = db.sp_GetData(....params...);
     foreach (var c in SP_data)
     {
        rtn.Id = c.Id;
        rtn.Id2 = c.Id2;
        rtn.Status = true;
        rtn.Message = "success";
    }
}
catch (Exception ex)
{
    rtn.Id = null;
    rtn.Id2 = null;
    rtn.Status = false;
    rtn.Message = ex.Message;
  }
  return rtn;
}

public class Cls_Response
{
    public bool Status { get; set; }
    public string Message { get; set; }
    public long? Id { get; set; }
    public long? Id2 { get; set; }
}

Here is the be response -

{ 
    "Status": true,
    "Message": "success",
    "Id": 1,
    "Id2": 2
 }

Upvotes: 0

Chintu
Chintu

Reputation: 525

CREATE PROCEDURE [dbo].[MyProc]         
AS
BEGIN
    DECLARE @Ret INT

    // use of IF EXISTS instead of IF
    IF EXISTS(SELECT * from Tests WHERE TestToken = @Token)
        SELECT @Ret =  0
    ELSE
        SELECT @Ret =  1

    RETURN @Ret
END

Upvotes: 0

ZZZ
ZZZ

Reputation: 2812

Why are you all using a stored procedure as function?

Though a stored procedure has return, it is not a function, so you should not use return to return data,

Rather, you should use Output parameters and other ways to return data, as documented at MSDN

And the use of return is documented at MSDN

misusing return is probably the reason why LINQ does not understand your stored procedures.

Upvotes: 5

sexta13
sexta13

Reputation: 1568

this was I did and it worked.

In Sqlserver:

   ALTER PROCEDURE [dbo].[ValidateToken]
-- Add the parameters for the stored procedure here
@Id int
AS
BEGIN
DECLARE @Ret INT

SELECT
    @Ret = COUNT(*)
FROM Test

set @Ret =@Id+1;

select @Ret

END

In C#:

 static void Main(string[] args)
    {
        using (SergioEntities dm = new SergioEntities()) 
        {
            int? validToken = 1;

            int? a = dm.ValidateToken(validToken).First();

        }

    }

Give it a try :)

Upvotes: 1

Pranay Rana
Pranay Rana

Reputation: 176896

The problem with you code is you use IF(query) instead of that you need to user it like IF EXISTS(query) so you code will be like as below 

CREATE PROCEDURE [dbo].[MyProc]         
AS
BEGIN
      DECLARE @Ret INT

      //use of IF EXISTS instead of IF
      IF EXISTS(SELECT * from Tests WHERE TestToken = @Token)
        SELECT @Ret =  0
      ELSE
        SELECT @Ret =  1

      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.MyProc();
    Console.WriteLine(q);               

}

If this doesnt work than go for the other solutions

  • Output Parameter
  • Using Scalar-Values Functions

Which are discuss here : LINQ to SQL : Returning Scalar Value from Stored Procedure

Upvotes: 0

Shai Aharoni
Shai Aharoni

Reputation: 1957

Try to change the RETURN 0/1 statements to SELECT 0/1

Upvotes: 0

Related Questions