Refresh the Primary Key Identity Column during Insert Operation

  • [アーティクル]

If you have an identity primary key column defined in a database table, its value will be automatically set by the database engine when you insert a new row to the table. The identity value is determined by the column’s Identity Seed and Identity Increment properties.

While in your client application, you can insert a new row into a corresponding ADO.NET DataTable (DataTable can be seen as the in memory cache of the data table in the database, I will use DataTable as the example in this post). You will not get the true value until you commit the update to the database. What you see before the update is not what you get after the update. For better user experience, you really want to refresh the database to retrieve the correct identity value back right after the insert operation.

In this post, I will describe the way to retrieve the identity value in ADO.NET applications with the help of Visual Studio Database Tools. I will start with a sample Windows Forms application using DataSet. My database server is SQL Server.

First in Visual Studio (2005 and after) server explorer, I establish a data connection connects to a SQL Server database. I create a data table in the database called MyCustomer with three columns: CustId, Name and Company. CustId is defined as primary key and an identity column. Identity Seed and Identity Increment are set as 1 by default as shown in Figure 1 below.

Figure 1

Figure 1 MyCustomer table defined in database

Then I create my DataSet through the Data Source Configuration Wizard, or by adding a DataSet and then dragging and dropping MyCustomer from Server Explorer to the DataSet Designer. By opening the DataSet and selecting the CustId column, you will see that it has the following properties: AutoIncrement=True, and AutoIncrementSeed = -1 , AutoIncrementStep = -1 (Figure 2). These properties are used by ADO.NET to auto generate the CustId’s placeholder values for newly added rows.

Figure 2

Figure 2. CustId properties in the DataTable

The AutoIncrement property corresponds to the Identity property in the database. AutoIncrementSeed corresponds to Identity Seed, and AutoIncrementStep to Identity Increment. You may be curious why both AutoIncrementSeed and AutoIncrementStep are set to -1. This is an approach to ensure that the placeholder values that ADO.NET generates will not conflict with values that already exist in the database. Another benefit is it looks like unreal so users will know that it is just a temporary placeholder value.

Now if you click on the MyCustomerTableAdapter header and show its CommandText for Insert Command, you will see this:

INSERT INTO [MyCustomer] ([Name], [Company]) VALUES (@Name, @Company);

SELECT CustId, Name, Company FROM MyCustomer WHERE (CustId = SCOPE_IDENTITY())

The command text contains two statements and the second one is used to retrieve the primary key value after the insert operation is committed. Notice the use of the SCOPE_IDENTITY function, which is defined so in MSDN:

“SCOPE_IDENTITY (Transact-SQL) Returns the last identity value inserted into an identity column in the same scope. A scope is a module: a stored procedure, trigger, function, or batch. Therefore, two statements are in the same scope if they are in the same stored procedure, function, or batch”

The auto generation of the second statement in Insert command is controlled by the “refresh data table option” in the TableAdapter Configuration Wizard (Figure 3).

 

Figure 3

Figure 3. Refresh the data table advanced option in the TableAdapter Configuration Wizard

 

Now let’s take a look what is the experience when applying this refresh data table feature at run time. Let me open the Form and then show the Data Sources Window. From the Data Sources Window, I drag and drop the MyCustomer table to the Form and I get the following layout (Figure 4):

Figure 4 

Figure 4. At design time: Drag and Drop MyCustomer from the Data Sources window to the form

 

Hit F5 to run the app. Click on the Add New (the + sign) button to add some rows. Notice that I will get values of -1, -2, -3, etc. for CustId column.

Figure 5 

Figure 5. CustForm at run time, before committing

 

Now I click the Save (the disk sign) button and I see that the CustIds are updated to 9,10,11. Oh! I would expect to get 2,3,4; someone may beat me and add some rows before me. You see that using -1,-2,-3 really make sense here to clearly indicate that the values are not committed.

Figure 6 

Figure 6. CustForm at run time, after committing

 

Now if you are using MS Access database or SQL CE, you will see that the above walk through does not work as expected. When you click the save, the primary key -1,-2,-3 keep unchanged. If you check on the TableAdapter Configuration Wizard, the Refresh the data table option is disabled. If you check the generated insert command, there is only one statement. This is because MS Access database and SQL CE do not support batching SQL statements and therefore cannot use the SCOPE_IDENTITY function. The build-in refresh the data table option is not available for these databases.

The good news is there is a sound workaround to fix this by reset the primary key identity value on Adapter.RowUpdated event. Please see Beth Massi’s blog: Using TableAdapters to Insert Related Data into an MS Access Database. Beth promised to write a follow up post for SQL CE as well. J

In summary, SQL server or any database supporting batching operation has a reliable way to retrieve the identity value through the usage of the SCOPE_IDENTITY function. Visual Studio Data Tool provides the auto generated Insert command that leverages this support with the “Refresh the data table” option turn on by default. For databases that do not support batching of SQL statements, resetting the primary key value on row updated event is a good workaround.

Comments

  • Anonymous
    September 15, 2009
    Hi John, As promiced, here's how you can get SQL CE to work: http://blogs.msdn.com/bethmassi/archive/2009/09/15/inserting-master-detail-data-into-a-sql-server-compact-edition-database.aspx Enjoy!

  • Anonymous
    September 23, 2009
    Great! it works fine for me, but why is not defined by default in any application when I generated DataSource?

  • Anonymous
    September 23, 2009
    Hi Humberto, Could you describe what is not defined by default? Thanks! John  

  • Anonymous
    November 04, 2009
    Thank you! I had been trying to figure this out for an ODBCDataSet for days... Beth Massi's MSAccess solution fixed it for me (after swapping in Odbc* for the OleDb* items).

  • Anonymous
    November 05, 2009
    it is nice one...forward lik this useful docs.....

  • Anonymous
    November 14, 2009
    很详细的解释(hen xiangxi de jieshi) 我曾在Channel 9上看到 Beth 采访你。 (wo ceng zai channel 9 shang kandao Beth caifangni) Very detailed explanation thanks!

  • Anonymous
    February 16, 2010
    Are the pictures missing in this post?

  • Anonymous
    February 16, 2010
    Pictures are not missing.

  • Anonymous
    February 27, 2010
    John, This works well when entering all new data.  But, when I pull up data already saved in the database, which loads that dataset, new rows added are ignoring the -1 setting of the autoincrementseed and using that largest loaded values from the rows loaded in select statement.  It IS using the -1 for autoincrementstep so I'm getting the error again, but for the edited rows.  Ugh. For ex:  load in 2 rows with ID values of 2000 and 2001, then add row and get new ID value tries to be 2000 which errors.     Any word of help?

  • Anonymous
    February 28, 2010
    Hi Lori, What you described is really UNEXPECTED. I have never perienced. You can try to do a debug to see what is the autoincrementseed when it happens. Assume your column is in DataSet1.DataTable1.Columns(0), put a code like this after you load the dataset to check: VB:        Dim seed As Integer = Me.DataSet1.DataTable1.Columns(0).AutoIncrementSeed C#:        int seed = this.DataSet1.DataTable1.Columns(0).AutoIncrementSeed; By the way, what kind of database you are using? Regards, John

  • Anonymous
    April 08, 2010
    Thank you so much. Spend hours trying to figure out how to add auto increment to my primary key. Thanks Again. R.Sixfiggaz

  • Anonymous
    April 14, 2010
    Hi. Is there any way to use advanced option "Refresh the data table" programmatically? I'm trying to use SqlCommandBuilder in my project, and i want to update primary keys in datatable after inserting data to DB. But CommandBuilder don't add the "select" statement after insert command :(