ADO.NET 3.5 Cookbook, 2nd Edition

5.12. Updating a Primary Key Value

Problem

You changed a primary key value in a DataTable and updated the change back to the underlying data source, but the value in the data source remained unchanged. You need to update a primary key value in the data source underlying the DataTable.

Solution

Use the SourceVersion property of SqlParameter to update the primary key value in the data source.

The solution uses a single table named UpdatePrimaryKey with the schema shown in Figure 5-20.

Figure 5-20. Schema for table UpdatePrimaryKey

The T-SQL DDL to create the table is shown in Example 5-21.

Example 5-21. Create table UpdatePrimaryKey

USE AdoDotNet35Cookbook
GO
CREATE TABLE UpdatePrimaryKey (
    Id int NOT NULL,
    Field1 nvarchar(50) NULL,
    Field2 nvarchar(50) NULL,
  CONSTRAINT PK_UpdatePrimaryKey PRIMARY KEY CLUSTERED
    ( Id ASC )
)

The solution requires some initial data in the UpdatePrimaryKey table. The T-SQL to add three records to the table UpdatePrimaryKey is shown in Example 5-22.

Example 5-22. Insert initial records into table UpdatePrimaryKey

USE AdoDotNet35Cookbook
GO
DELETE FROM UpdatePrimaryKey;
INSERT INTO UpdatePrimaryKey VALUES (1, 'field 1.1', 'field 2.1');
INSERT INTO UpdatePrimaryKey VALUES (2, 'field 1.2', 'field 2.2');
INSERT INTO UpdatePrimaryKey VALUES (3, 'field 1.3', 'field 2.3');

The solution creates a DataTable containing an integer primary key called Id and two string fields called ...