I find TableAdapters to be a very helpful resource so I use them a lot. However, every now and then I stumble upon some scenario where the TableAdapter needs some creativity to work. Imagine the following:

Scenario:

1. Need to insert a Person and an Address in the Database;
2. You populate all the data in just one page;
3. The table PERSON has an primary key named ID which is an Identity, so SQLServer will generate the ID upon the record insertion;
4. You need to Insert a record in the ADDRESS table that will need the ID of the person just inserted;
5. You’re using TableAdapters;

For this you’ll need the SCOPE_IDENTITY function provided by SQLServer in the TableAdapter code.

The TableAdapters code is frequently regenerated so you need to protect this code from being overwritten on regeneration. The best solution for this is extending your TableAdapter and overriding the method you need to protect.

First step

Add a new insert query in the PERSONTableAdapter named InsertQueryReturnID with a code like this:

INSERT INTO [PERSON] ([NAME], [PHONE], [EMAIL]) VALUES (@p1, @p2, @p3);
SELECT CAST(SCOPE_IDENTITY() AS INT);

The key here is the SCOPE_IDENTITY() function which will return the last inserted id in your connection so you’re safe about inserts made by other users.

Second step

Add a new class named PERSONTableAdapterExtended to your project. This class will inhererit from PERSONTableAdapter. Next, copy the code for the InsertQueryReturnID method from the Designer file from your DataSet and paste the method to your extended class. Substitute the method’s virtual modifier by an override modifier (because you want to override the method from the base class).

Another important change is in the way you execute and return the id. Normally the insert would be executed with a command.ExecuteNonQuery() command which would return the numbers of rows affected. Our SQL will return the ID of the record inserted so will replace that code by (int)command.ExecuteScalar(). The final method should look like this:

    public class PERSONTableAdapterExtended : PERSONTableAdapter
    {
        [global::System.Diagnostics.DebuggerNonUserCodeAttribute()]
        [global::System.ComponentModel.Design.HelpKeywordAttribute("vs.data.TableAdapter")]
        [global::System.ComponentModel.DataObjectMethodAttribute(global::System.ComponentModel.DataObjectMethodType.Insert, false)]
        public override int InsertQueryReturnID(string p1, string p2, string p3)
        {
            global::System.Data.SqlServerCe.SqlCeCommand command = this.CommandCollection[1];
            if ((p1 == null))
            {
                command.Parameters[0].Value = global::System.DBNull.Value;
            }
            else
            {
                command.Parameters[0].Value = ((string)(p1));
            }
            if ((p2 == null))
            {
                command.Parameters[1].Value = global::System.DBNull.Value;
            }
            else
            {
                command.Parameters[1].Value = ((string)(p2));
            }
            if ((p3 == null))
            {
                command.Parameters[2].Value = global::System.DBNull.Value;
            }
            else
            {
                command.Parameters[2].Value = ((string)(p3));
            }
            global::System.Data.ConnectionState previousConnectionState = command.Connection.State;
            if (((command.Connection.State & global::System.Data.ConnectionState.Open)
                        != global::System.Data.ConnectionState.Open))
            {
                command.Connection.Open();
            }
            int returnValue;
            try
            {
                returnValue = (int)command.ExecuteScalar();
            }
            finally
            {
                if ((previousConnectionState == global::System.Data.ConnectionState.Closed))
                {
                    command.Connection.Close();
                }
            }
            return returnValue;
        }
    }

Step Three

Now you’ll use only the PERSONTableAdapterExtended class instead of the PERSONTableAdapter generated class. This will protect your code from changes when the DataSet is regenerated.