Get ID of the newly inserted record, using SqlDataAdapter

Question

BGREX.BGREXDataTable _bRexDataTable = new BGREX.BGREXDataTable();
BGREX.BGREXRow bgRexRow = _bRexDataTable.NewBGREXRow();

bgRexRow.BGRes_TITLE = "abc";
bgRexRow.BGRes_VERSION = 123;

_bRexDataTable.AddBGREXRow(bgRexRow);
int rewEffected = Adapter.Update(_bRexDataTable);

Have beed using above to insert record in Database, workes perfect. Now I need the id (Primary key) of newly inserted record which is auto generated, instead of rows effected to be returned from

Adapter.Update(_bRexDataTable);

It looks like I need SCOPE_IDENTITY() function, but I am not sure where to include this in designer as designers is not happy with following syntax added to the actual insert query. It woks fine in sqlserver Management console though

DECLARE @ROWI_D int

INSERT INTO TABLE
.....
.....
.....         
VALUES
("val1","val2",.......................)

SET ROW_ID = SCOPE_IDENTITY()

Answer 1

How about @@Identity ?

Answer 2

i'm not sure of a way to do this using sql statements with the commands. i'm not saying that it can't be done, i just never work with the statements, always through sprocs.

through the use of stored procedures you can define an output parameter which you can set equal to SCOPE_IDENTITY() in the sproc and then ensure that you pass that parameter into the command while settings it's direction as output.

sorry i don't have a solution for the direct sql call, but maybe someone else with more experience working in this way will chime in.

here's a link to a thread which discusses this same topic and a possible answer. but there's no verification whether the suggested solution worked or not.

Answer 3

Best way to do this is to change to a stored procedure that returns the value you want in a parameter. Post the code for doing the update you have now and I (or someone else) will show you how to refactor it to use a stored procedure. It is not hard.