ADO.NET Cookbook

4.8. 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 schema of table TBL0408 used in this solution is shown in Table 4-3.

Table 4-3. TBL0408 schema

Column name	Data type	Length	Allow nulls?
Id	int	4	No
Field1	nvarchar	50	Yes
Field2	nvarchar	50	Yes

The sample code contains two event handlers:

Form.Load

Sets up the sample by creating a single DataTable containing an integer primary key called Id and two string fields called Field1 and Field2. A DataAdapter is created and the select, delete, insert, and update commands are defined for it. Finally, the table is filled using the DataAdapter and the default view of the table is bound to the data grid on the form.

Update Button.Click

Calls the Update( ) method of the DataAdapter defined in the Form.Load event to reconcile the changes made, including those made to the primary key, with the SQL Server database.

The C# code is shown in Example 4-11.

Example 4-11. File: UpdatePrimaryKeyForm.cs

// Namespaces, variables, and constants using System; using System.Configuration; using System.Data; using System.Data.SqlClient; private const String TABLENAME = "TBL0408"; private DataTable dt; private SqlDataAdapter ...