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.

Maybe if you subscribe to the adapters RowUpdated event. There you can check for e.StatementType == StatementType.Insert and if that is the case then the new ID might be available there. I did it like this but I did added the select scope_identity first so I am not sure if it will work without it — GuidoG, Aug 29 '16 at 12:30
@GuidoG This function is part of a class library and not a form so I cannot subscribe to an event. Is there any way by which I can get the scope_identity by editing the command object here. Can anyone suggest how I can update the Insert Command of an adapter in the code instead of a designer based solution. — chiku coder, Aug 29 '16 at 13:17

GuidoG · Answer 1 · 2016-08-29T13:43:30.297

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;

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

1 Answers1