How to Add a command/SQL statement to a strongly typed TableAdapter's Update/Insert command?

Question

See this question. I have the following code that executes against a SQLIte database using a strongly typed dataset.

        messagesAdapter.Update(messages);//messages is a DataTable
        var connection = messagesAdapter.Connection;
        var retrieveIndexCommand= connection.CreateCommand();
        retrieveIndexCommand.CommandText = @"Select last_insert_rowid()";
        connection.Open();
        var index = retrieveIndexCommand.ExecuteScalar();
        connection.Close();

This does not work as the last_inser_rowid() always returns zero. This caused by the fact that it needs to be called during the same connection that is used by the TableAdapter's Update command. How can I change the the TableAdapter's Insert or Update command so that it return the index?

Answer 1

If you are inserting a single row, you can use this:

// cast if necessary
using (var insert = (SQLiteCommand)this.Adapter.InsertCommand.Clone()) { 
    insert.CommandText += "; SELECT last_insert_rowid()";
    foreach (SQLiteParameter parameter in insert.Parameters) {
        parameter.Value = row[parameter.SourceColumn];
    }
}
var index = Convert.ToInt32(insert.ExecuteScalar());

You can also use it to insert multiple rows and assign your id to each one:

using (var insert = (SQLiteCommand)this.Adapter.InsertCommand.Clone())
{
    insert.CommandText += "; SELECT last_insert_rowid()";
    // this filter only added rows
    foreach (MyDataSet.MessageRow row in messages.GetChanges(DataRowState.Added))
    {
        foreach (SQLiteParameter parameter in insert.Parameters)
        {
            parameter.Value = row[parameter.SourceColumn];
        }
        // use the name of your rowid column
        row.ID = Convert.ToInt32(insert.ExecuteScalar());
        row.AcceptChanges();
    }
}
// then you can perfom the other updates
messagesAdapter.Update(messages);

Note: Be sure to open / close your connection