Get SQL INSERT OUTPUT value in c#

In a SQL stored proc i'm inserting a row and hoping to return the IDENTITY INT for the new row, then get it in my C# code.

ALTER PROCEDURE the_proc @val_2 VARCHAR(10), @val_3 VARCHAR(10)
AS
BEGIN
SET NOCOUNT ON
INSERT INTO the_table (field_2, field_3)
  OUTPUT INSERTED.field_1
  VALUES (@val_2, @val_3)
END

In C# i'm using LINQ but am fuzzy on how to retrieve that OUTPUT value. I tried including it as an OUTPUT parameter in the SQL proc d开发者_StackOverflow社区eclaration, but couldn't get that working either (exceptions complaining about it not being supplied in the call). The closest i've gotten is in walking into the Db.designer.cs code, where IExecuteResult result ReturnValue contains 0 (not correct) but inspecting the contained Results View (result.ReturnValue Results View) DOES have the outputed value.

key = (int)(db.TheProc(val2,val3).ReturnValue);

key is coming back as 0. I want the IDENTITY INT value from the INSERT.

OUTPUT INSERTED.* 

is basically the same thing as doing a select. So this isn't going to show up as an output parameter but rather come back as a result set.

BTW, the ReturnValue should actually be zero which is what you are seeing in this case.

You'll need to change your linq statement so that you capture that result set.

Try this instead (assuming SQL Server) :

ALTER PROCEDURE the_proc 
@val_2 VARCHAR(10), 
@val_3 VARCHAR(10), 
@newKey int OUTPUT
AS
BEGIN
  SET NOCOUNT ON
  INSERT INTO the_table (field_2, field_3) VALUES (@val_2, @val_3)
  SET @newKey = SCOPE_IDENTITY()
END

Once you define your the_proc stored procedure to your LINQ to SQL dbml you can do this:

        int? newKey = null;
        dataContext.the_proc("val1", "val2", ref newKey);
        // newKey.Value now contains your new IDENTITY value

Chris Lively nudged me in the right direction, which is to say re-examining the C# Linq code. The following pulls Field_1 out of the results. Maybe it's a weird way to get there and not the normal idiom, so if anyone has suggestions for something more "correct" please add a comment or answer.

var o = from res in db.MyProc( Val1, Val2 )
           select res.Field_1;
int key = o.ToArray()[0];

Thanks.