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
.
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 |
|
4 |
No |
Field1 |
|
50 |
Yes |
Field2 |
|
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 calledId
and two string fields calledField1
andField2
. ADataAdapter
is created and the select, delete, insert, and update commands are defined for it. Finally, the table is filled using theDataAdapter
and the default view of the table is bound to the data grid on the form.- Update
Button.Click
Calls the
Update( )
method of theDataAdapter
defined in theForm.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); }
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
Value |
Description |
---|---|
|
The current values in the row, representing the latest edits. This value is always available. |
|
The default row version. If the row is being edited, this is the
|
|
The original values for the row. Not available for rows that have
been added since data was last retrieved from data source or since
|
|
The proposed values for the row. Only available after
|
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
field—is 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.
Get ADO.NET Cookbook now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.