Returning Identity Column of Autoincrement Fields

Issuing an Insert command on an ObjectDataSource typically requires two database calls:

1) INSERT INTO mytable ….
2) SELECT @@IDENTITY

If you are prototyping against Access databases, one problem that becomes quickly apparent is that OleDb providers do not support multiple SQL statements. So you can’t do set the following commandtext in a DataTableAdapter method:

INSERT INTO mytable ….;
SELECT @@IDENTITY

However, this can be worked around by adding another method to the TableAdapter using partial classes that wraps the original Insert method.

Here’s some sample code:

namespace EventTableAdapters
{
    partial class EVENTTableAdapter
    {
        public int InsertQuery2(string EQUIPMENT, string PART, out System.Nullable EVENTID)
        {
            this.Connection.Open(); // If connection.Open() called after Insert() we lose the @@identity
            EVENTID = null;
            int RowsAffected = this.Insert(EQUIPMENT, PART);
            if ( RowsAffected > 0)
            {
                try
                {
                    IDbCommand cm = this.Connection.CreateCommand();
                    cm.CommandText = "SELECT @@IDENTITY";
                    EVENTID = Convert.ToInt32(cm.ExecuteScalar());
                }
                finally
                {
                    this.Connection.Close();
                }
            }

            return RowsAffected;
        }
    }
}

Then configure an ObjectDataSource to call InsertQuery2() instead.

Hat Tip to Bruce L for showing this technique.