My scenario is simple. I am trying to persist a DataSet and have the identity column filled in so I can add child records.

Here's what I've got so far:

    using (SqlConnection connection = new SqlConnection(connStr))
    {
        SqlDataAdapter adapter = new SqlDataAdapter("select * from assets where 0 = 1", connection);
        adapter.MissingMappingAction = MissingMappingAction.Passthrough;
        adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;

        SqlCommandBuilder cb = new SqlCommandBuilder(adapter);

        var insertCmd = cb.GetInsertCommand(true);
        insertCmd.Connection = connection;

        connection.Open();
        adapter.InsertCommand = insertCmd;

        adapter.InsertCommand.CommandText += "; set ? = SCOPE_IDENTITY()";
        adapter.InsertCommand.UpdatedRowSource = UpdateRowSource.OutputParameters;

        var param = new SqlParameter("RowId", SqlDbType.Int);
        param.SourceColumn = "RowId";
        param.Direction = ParameterDirection.Output;
        adapter.InsertCommand.Parameters.Add(param);

        SqlTransaction transaction = connection.BeginTransaction();
        insertCmd.Transaction = transaction;
        try
        {
            assetsImported = adapter.Update(dataSet.Tables["Assets"]);

            transaction.Commit();
        }
        catch (Exception ex)
        {
            transaction.Rollback();
            // Log an error
        }

        connection.Close();
      }

The first thing that I noticed, besides the fact that the identity value is not making its way back into the DataSet, is that my change to add the scope_identity select statement to the insert command is not being executed. Looking at the query using Profiler, I do not see my addition to the insert command.

Questions:

1) Why is my addition to the insert command not making its way to the sql being executed on the database?

2) Is there a simpler way to have my DataSet refreshed with the identity values of the inserted rows?

3) Should I use the OnRowUpdated callback to add my child records? My plan was to loop through the rows after the Update() call and add children as needed.

Thank you in advance.

Rick