Stored procedure return value ISingleResult instead of Integer

Question

If I drag this one stored procedure into my DBML, it assigns the return type of (Auto-generated Type) in the properties window, (which is ISingleResult<>).

The stored procedure is supposed to return an integer.

The basics of the stored procedure are:

BEGIN TRANSACTION;
BEGIN TRY    
   UPDATE...... (a few update statements)    
   EXEC ..... (executes a stored procedure)    
   COMMIT TRANSACTION;    
   RETURN 1;
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION;
    RETURN 0;
END CATCH

When I drag it into the DBML, it sets the return type of ISingleResult instead of Integer. I cannot change it in the properties window. I tried modifying the dbml designer.cs file and change ISingleResult<sp_nameResult> to int, but Visual Studio automatically overwrites it.

I tried deleting the stored proc from the dbml, removing my connection in server explorer, recreating the connection, and readding the SP, but it still does it.

Any ideas?

Thanks.

Answer 1

This is the way how stored procedure returns values in LINQ, if you want to return just integer you'd better use Scalar Valued functions.

For more understanding about how LINQ operates with SQL you can read my blog post about it

http://msguy.net/post/2012/03/20/LINQ-to-SQL-Practices-and-approaches.aspx

Hope this will help you.

---

Please Mark as Reply and Vote as Helpful if I helped.

Also please visit my blog http://msguy.net/

Answer 2

This is normal. You can get your results by iterating over the ISingleResult<> results

foreach (var result in results)
{
   var item = result.Something;
}

where results is an `ISingleResult<>` object. 

Answer 3

Ok, I read your blog post. Under the first "Stored Procedure" heading, you mention:

As you can see, procedure execution returns result of type IExecuteResult which is single value result, function is type of int (because it is visual studio default behavior) 
    and function returns ReturnValue property of result object converted into int, 
    that returns single value from the database.

But the problem is, the function is not type of INT. It is type ISingleResult.

All my other stored procedures RETURN 1 for success and RETURN 0 for failure, and they all show up as INT, not ISingleResult.

The only thing I can possibly think of that is causing this is that the SP is calling another SP inside of it (and all my other SPs don't do this).

Answer 4

I removed the call to the other stored procedure from within this stored procedure, and now it shows up in Visual Studio as an INT. So, how can I call a stored procedure from within a stored procedure, and still have the main stored procedure return an INT to Visual Studio?

Here's my call to the other SP:

IF @CustomerID > 0
   BEGIN
      DECLARE @res INT;
      EXECUTE [spUpdateCustomer] @CustomerID, @CustomerValue, @res OUTPUT;
   END;

Answer 5

Hi,

Do you try this?

Int32 i = (Int32)result.ReturnValue;