ADO.Net Get Inserted Row's ROW ID without designer wizard

Question

I have a generic update function that takes a datatable, select query and uses this to update the database tables.It is working fine. I need to know is there a way to get back the inserted row's ID (identity field) by changing something in the below code.

Public Function UpdateDataTable_GetID(ByVal dt As DataTable, ByVal SQL As String, ByVal connString As String) As Integer

        Dim conn As SqlConnection = Nothing
        Dim cmd As SqlCommand
        Dim adp As SqlDataAdapter = Nothing
        Dim cmdBuilder As SqlCommandBuilder = Nothing
        Dim UpdatedID As Integer
        If SQL.Length <= 0 Then
            Return False
        End If

        conn = New Data.SqlClient.SqlConnection(connString)
        cmd = New Data.SqlClient.SqlCommand
        cmd.Connection = conn
        cmd.CommandText = SQL
        cmd.CommandType = CommandType.Text
        adp = New Data.SqlClient.SqlDataAdapter(cmd)
        cmdBuilder = New Data.SqlClient.SqlCommandBuilder(adp)

        Try
            UpdatedID = Convert.ToInt32(adp.Update(dt)) ' What to do here to get the just inserted ID instead of number of records updated
            adp.Dispose()
            cmdBuilder.Dispose()
            Return UpdatedID

        Catch ex As System.Data.SqlClient.SqlException
            ' Closing connection
            Return -1
        Finally
End try
End function

I am aware of solutions wherein I can append "select scope_identity()" to the insert Command of data adapter's query using designer as well as editing the adapter's insertcommand text and then doing an ExecuteScalar(). I want to know if the generic adapter.Update() can be tweaked to get the inserted row's ID.

Answer 1

you can subscribe to this event in code like this : (C# I dont know VB)

adp.RowUpdated += adapter_RowUpdated;

and write the event yourself :

void adapter_RowUpdated(object sender, SqlRowUpdatedEventArgs e)
{
   if (e.StatementType == StatementType.Insert)
   {
       object id = e.Command.Parameters["@ID"].Value;
       e.Row[_identityFieldName] = id;
   }
}

In this example the following has been added to the commandtext first :

 SET @ID = SCOPE_IDENTITY()  

and a private variable _identityFieldName has been filled.

Maybe this can help you.

EDIT: I noticed you also use an SqlCommandBuilder that makes things easier to add the Scope identity :

SqlCommand inserter = new SqlCommand();
inserter = cmdBuilder.GetInsertCommand(true).Clone();
inserter.CommandText += " SET @ID = SCOPE_IDENTITY()";
SqlParameter param = new SqlParameter();
param.Direction = ParameterDirection.Output;
param.Size = 4;
param.DbType = DbType.Int32;
param.ParameterName = "@ID";
inserter.Parameters.Add(param);

adp.InsertCommand = inserter;