Data sent to the database
When calling Recordset.SaveChanges(), the runtime will go through all the records in the resultsets of the recordset to collect modified, new and marked-for-deletion data.
The modification flags are not used to determine the column values that will be send to the database.
A new record results in an INSERT statement executed on the database.
INSERT INTO <tablename> VALUES <non-null updateable column values>
A new record results in an INSERT statement executed on the database. The database engine will automatically set column values that are not specified to null. That is why null values are never send to the database for a New record.
All modified updateable columns are send to the database.
An existing record results in an UPDATE statement executed on the database.
UPDATE <modified updateable column values> WHERE <primary key values>
When original value tracking is enabled, then modified updateable primary key values will be included in the "modified updateable column values" part of UPDATE statement. See Original value tracking and primary key values below.
This is a record that is marked-for-deletion. Only the primary key column values are send to the database.
An existing record marked for deletion results in a DELETE statement executed on the database.
DELETE FROM <tablename> WHERE <primary key values>
Eventual modified updateable column values in the record are ignored and never send to the database.
Original value tracking and primary key values
When original value tracking is enabled, the record registers both original and new primary key values.
This allows the UPDATE statement to modify primary key values in the database table:
UPDATE <new primary key values> WHERE <original primary key values>
The original name is John and we want to change it to Pete. The Name column is a primary-key.
UPDATE Name = "Pete" WHERE Name = "John"
var customers = new CustomersRecordset();
customers.ExecSql(); // the Name is set to John
customers.Name = "Pete";
customers.SaveChanges(); // UPDATE Name = "Pete" WHERE Name = "John"