Linq to SQL using StoredProcedure - How to return a value (insert, update, delete)

Question

How do we return a value from a stored procedure

here is my sp looks like:

create proc dbo.spInsertGroup
@ID uniqueidentifier
@GroupName varchar(100),
@IsActive bit
AS
BEGIN



insert into tblGroup
values(@ID, @GroupName, @IsActive)

Select @@IDENTITY AS ID

END

based on the return value i want to show the user some kind of feedback wether the save was successfull or failed.

public void AddInquiry(Inquiry inq)
{
   using (var transaction = new TransactionScope())
   {
      using (MyDataContext dc = conn.GetContext())
      {
         var results =  dc.spInquiry_Insert(......).ToList();

         transaction.Complete();

         var returnValue = (int)results.ReturnValue; 
         // note that ReturnValue is of type object and must be cast. 
      }
   }
}

error:

Error 39 'System.Collections.Generic.List' does not contain a definition for 'ReturnValue' and no extension method 'ReturnValue' accepting a first argument of type 'System.Collections.Generic.List' could be found (are you missing a using directive or an assembly reference?)

Answer 1

For a scalar value like the ID, I believe you just say "RETURN @ID"

For a rowset, I believe you select it, similar to your example, and Linq2Sql imports it as a IEnumerable<>.

Update:

Since you used SELECT, L2S created a List<T>. Since the value in the select was an integer, it was a List<int>, with only one element. To retrieve that, it's just results[0].

Or you could change the store proc to return @ID, and use:

var id =  dc.spInquiry_Insert(......);

Answer 2

try:

using (MyDataContext dc = conn.GetContext())
{
    var returnValue = (int)dc.spInsertGroup(.......).ReturnValue;
}

You are getting that error because you are calling the .ToList() so the var results is of type List<> that has no ReturnValue property.

If you can't see the ReturnValue property, maybe you need to update the method generated by LINQ

Answer 3

You cannot return any but an INT from a stored proc, however, you're trying to send out a GUID - that won't work. You need to rewrite your stored proc to something like:

CREATE PROCEDURE dbo.spInsertGroup
    @GroupName varchar(100),
    @IsActive bit,
    @ID uniqueidentifier OUTPUT
AS .......

Then it's good enough to simply have the SET @ID = NEWID() in your stored proc - that works just fine.

Your C# code would now look something like:

using(YourDataContext ctx = new YourDataContext())
{
    Guid? newID = new Guid();
    bool? active = true;

    int retVal = ctx.spInsertGroup2("test", active, ref newID);
}

and newID will contain the new ID after the call.