• To avoid conflicts with keys generated on the server, set the AutoIncrementSeed and AutoIncrementStep properties to -1. (or don't do auto-increment in the dataset).
• When added rows from tables with autogenerated primary keys updated to the database, the rows will get new key values. We need to get those values back.
• We saw one example previously. That involved:
  • A parameter
  • An INSERT query designed to retrieve the key value
  • The UpdatedRowSource property on the DbCommand for the insert. Possible values are:

• The default is Both unless the update command was built by a CommandBuilder; the latter default is None.
• Our earlier example used FirstReturnedRecord.
• The following MS example uses a stored procedure in the database that sets an output variable to the new identity value. This is picked up by an output parameter.

  CREATE PROCEDURE InsertCategory
    @CategoryName nchar(15),
    @Identity int OUT
  AS
  INSERT INTO Categories (CategoryName) VALUES(@CategoryName)
  SET @Identity = SCOPE_IDENTITY()
  

 

// Assumes that connection is a valid SqlConnection object.
SqlDataAdapter adapter = new SqlDataAdapter(
  "SELECT CategoryID, CategoryName FROM dbo.Categories", connection);

adapter.InsertCommand = new SqlCommand("InsertCategory", connection);
adapter.InsertCommand.CommandType = CommandType.StoredProcedure;

adapter.InsertCommand.Parameters.Add(
  "@CategoryName", SqlDbType.NChar, 15, "CategoryName");

SqlParameter parameter = adapter.InsertCommand.Parameters.Add(
  "@Identity", SqlDbType.Int, 0, "CategoryID");
parameter.Direction = ParameterDirection.Output;

connection.Open();

DataSet categories = new DataSet();
adapter.Fill(categories, "Categories");

DataRow newRow = categories.Tables["Categories"].NewRow();
newRow["CategoryName"] = "New Category";
categories.Tables["Categories"].Rows.Add(newRow);

adapter.Update(categories, "Categories");

connection.Close();

• If you working against Microsoft Access, you have to work a little harder.
• MS Access 2000 and later support the @@IDENTITY property for retrieving the value of an Autonumber field after an INSERT.
• Use the RowUpdated event to determine if an INSERT has occurred, retrieve the value and insert it in the new row.
• More MS code:

  // Assumes that connection is a valid OleDbConnection object.
  OleDbDataAdapter adapter = new OleDbDataAdapter(
    "SELECT CategoryID, CategoryName FROM Categories ORDER BY CategoryID", 
    connection);
  
  adapter.InsertCommand = new OleDbCommand(
    "INSERT INTO Categories (CategoryName) Values(?)", connection);
  adapter.InsertCommand.CommandType = CommandType.Text;
  
  adapter.InsertCommand.Parameters.Add( _
    "@CategoryName", OleDbType.Char, 15, "CategoryName");
  
  connection.Open();
   
  // Fill the DataSet.
  DataSet categories = new DataSet();
  adapter.Fill(categories, "Categories");
  
  // Add a new row.
  DataRow newRow = categories.Tables["Categories"].NewRow();
  newRow["CategoryName"] = "New Category";
  categories.Tables["Categories"].Rows.Add(newRow);
  
  // Include an event to fill in the Autonumber value.
  adapter.RowUpdated += new OleDbRowUpdatedEventHandler(OnRowUpdated);
  
  // Update the DataSet.
  adapter.Update(categories, "Categories");
  
  connection.Close();
  
  // Event procedure for OnRowUpdated
  protected static void OnRowUpdated(object sender, OleDbRowUpdatedEventArgs args)
  {
      // Include a variable and a command to retrieve 
      // the identity value from the Access database.
      int newID = 0;
      OleDbCommand idCMD = new OleDbCommand(
        "SELECT @@IDENTITY", connection);
  
      if (args.StatementType == StatementType.Insert)
      {
        // Retrieve the identity value and store it in the CategoryID column.
        newID = (int)idCMD.ExecuteScalar();
        args.Row["CategoryID"] = newID;
      }
  }