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

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 da; // . . . private void UpdatePrimaryKeyForm_Load(object sender, System.EventArgs e) { // Define the table. dt = new DataTable(TABLENAME); DataColumnCollection cols; cols = dt.Columns; DataColumn col = cols.Add("Id", typeof(Int32)); dt.PrimaryKey = new DataColumn[] {col}; cols.Add("Field1", typeof(String)).MaxLength = 50; cols.Add("Field2", typeof(String)).MaxLength = 50; // Create the DataAdapter and connection. da = new SqlDataAdapter( ); SqlConnection conn = new SqlConnection( ConfigurationSettings.AppSettings["Sql_ConnectString"]); // Build the select command. String sqlText = "SELECT Id, Field1, Field2 FROM " + TABLENAME; da.SelectCommand = new SqlCommand(sqlText, conn); // Build the delete command. sqlText = "DELETE FROM " + TABLENAME + " WHERE Id=@Id"; SqlCommand deleteCommand = new SqlCommand(sqlText, conn); deleteCommand.Parameters.Add("@Id", SqlDbType.Int, 0, "Id"); da.DeleteCommand = deleteCommand; // Build the insert command. sqlText = "INSERT " + TABLENAME + " (Id, Field1, Field2) VALUES " + "(@Id, @Field1, @Field2)"; SqlCommand insertCommand = new SqlCommand(sqlText, conn); insertCommand.Parameters.Add("@Id", SqlDbType.Int, 0, "Id"); insertCommand.Parameters.Add("@Field1", SqlDbType.NVarChar, 50, "Field1"); insertCommand.Parameters.Add("@Field2", SqlDbType.NVarChar, 50, "Field2"); da.InsertCommand = insertCommand; // Build the update command. sqlText="UPDATE " + TABLENAME + " SET " + "Id=@Id, Field1=@Field1, Field2=@Field2 WHERE Id=@IdOriginal"; SqlCommand updateCommand = new SqlCommand(sqlText, conn); updateCommand.Parameters.Add("@Id", SqlDbType.Int, 0, "Id"); updateCommand.Parameters.Add("@Field1", SqlDbType.NVarChar, 50, "Field1"); updateCommand.Parameters.Add("@Field2", SqlDbType.NVarChar, 50, "Field2"); updateCommand.Parameters.Add("@IdOriginal", SqlDbType.Int, 0, "Id"); updateCommand.Parameters["@IdOriginal"].SourceVersion = DataRowVersion.Original; da.UpdateCommand = updateCommand; // Fill the table from the data source. da.Fill(dt); // Bind the default view for the table to the grid. dataGrid.DataSource = dt.DefaultView; } private void updateButton_Click(object sender, System.EventArgs e) { // Update the table to the data source. da.Update(dt); }

Discussion

ADO.NET maintains up to three versions of each DataRow in a DataTable : the current, original, and proposed. The current version is accessed by default. All versions can be accessed using an overloaded DataRow indexer (C#) or an overload of the Item( ) property (VB.NET). Table 4-4 describes the different values of the DataRowVersion enumeration.

Table 4-4. DataRowVersion enumeration

To change the primary key in the table in the database, the UpdateCommand of the DataAdapter needs to locate the row based on the original primary key and update the primary key value with the current value of the primary key in addition to updating the other row values with their current values. In the sample, this is done using the following SQL update command:

sqlText="UPDATE " + TABLENAME + " SET " + "Id=@Id, Field1=@Field1, Field2=@Field2 WHERE Id=@IdOriginal";

The primary key Id fieldis updated with the current value of the Id field, where the Id field of the row matches the original value of the Id field.

The current value for the Id field is set with the following code:

updateCommand.Parameters.Add("@Id", SqlDbType.Int, 0, "Id");

The original value for the Id field is set by the following two lines of code in the sample:

updateCommand.Parameters.Add("@IdOriginal", SqlDbType.Int, 0, "Id"); updateCommand.Parameters["@IdOriginal"].SourceVersion = DataRowVersion.Original;

The first line is the same as for the current version. The second line sets the SourceVersion property of the parameter so that the original value for the Id field is used when loading the value. The UpdateCommand correctly identifies the row to be updated based on the original value of the Id field and updates the row with the current Id value.

Updating the primary key in a database is not normally necessary. Some RDBMSs do not support updating the primary key. Additionally, if a data relation is based on the primary key, related foreign key fields in the child tables will have to be updated to maintain referential integrity.