If the tables from your data source have auto-incrementing columns, you can fill the columns in your DataSet with the values generated by the data source by returning the auto-increment value as an output parameter of a stored procedure and mapping that to a column in a table, or by using the RowUpdated event of the DataAdapter. For an example of this, see Retrieving Identity or Autonumber Values.

However, the values in your DataSet can become out-of-sync with the values at the data source and result in unexpected behavior. For example, consider a table with an auto-incrementing primary key column of CustomerID. If you add two new customers within the DataSet, they receive auto-incremented CustomerId values of 1 and 2. When the second customer row is passed to the Update method of the DataAdapter, the newly added row receives an auto-incremented CustomerID value of 1 at the data source, which does not match the value, 2, in the DataSet. When the DataAdapter fills the row in the DataSet with the returned value, a constraint violation occurs because the first customer row already has a CustomerID of 1.

To avoid this behavior, it is recommended that, when working with auto-incrementing columns at a data source and auto-incrementing columns in a DataSet, you create the column in the DataSet with an AutoIncrementStep of -1 and an AutoIncrementSeed of 0, as well as ensuring that your data source generates auto-incrementing identity values starting from 1 and incrementing with a positive step value. As a result, the DataSet will generate negative numbers for auto-incremented values that will not conflict with the positive auto-increment values generated by the data source. Another option is to use columns of type Guid instead of auto-incrementing columns. The algorithm that generates Guid values should never generate the same Guid in the DataSet as is generated by the data source. For more information about defining columns in a DataTable, see Defining the Schema of a DataTable.

Updating the Database with a DataAdapter and the DataSet

Retrieving Identity or Autonumber Values