Using a DataTable with minimal/partial Update stored procedures

• Download sample app and SQL Server script - 15 Kb
• Download sample project and SQL Server script - 16.6 Kb

Introduction

Sometimes it is desirable to minimise the updates that occur to a database table, for example, if some values in some rows are effectively locked under certain conditions, or if it is desirable to keep an audit trail to a minimum of log entries.

The technique outlined in this article allows partial updates to be carried out on rows in a database table, driven by changes to a System.Data.DataSet. What is meant by this is that not all columns in each row will be updated - only the ones where the value of the data has actually been modified in the System.Data.DataSet.

This is achieved using a combination of DataSets with a correctly chosen DataSet.PrimaryKey property, stored procedures that are written specifically to ensure minimal updates, and the UpdateHelper class as detailed below.

Background

The principal way of dealing with relational data in .NET is to use System.Data.DataSet. DataSet coupled with the System.Data.SqlClient.SqlDataAdapter is a powerful way of fetching and storing data to and from a SQL Server instance. However, the underlying mechanism of the SqlDataAdapter.Update() method means that any change to a row in a DataTable will cause the entire row to be updated in the database table.

An example of where this causes problems is when some values must not be changed once the row is in use. If the application is written in such a way that it deals directly with the database, then this is not a problem; the application code can be adapted to deal with the specific cases. However, if you are passing data between multiple application layers or dealing with many tables, then using the standard SqlDataAdapter.Update() would be preferable, but since Update() will attempt to update all columns, your database table is effectively locked.

That is where this technique comes in, because it automates the update process in much the same way as the SqlDataAdapter does.

Using the code

UpdateHelper.Update() is intended to be used in place of the SqlDataAdapter.Update() and in conjunction with stored procedures that are written specifically for this purpose.

The first thing to note is that in this code, NULL is passed to any stored procedure parameters that are not to be updated - the obvious caveat is that you cannot set any values to null using the stored procedure.

Another point to watch out for is that the DataSet will not be re-populated from the database table, it is assumed that the changes that are made to the DataSet are the changes that will appear in the database table - if you were using UpdateHelper.Update() to insert new rows into a table that uses an Identity column or any other automatic numbering mechanism, then it would be advisable to re-fill the DataSet using the SqlDataAdapter once the UpdateHelper.Update() has been performed.

Populating the DataSet:

This is done as normal, the following example uses the strongly typed DataSet used in the sample project:

PartialUpdateDS ds = new PartialUpdateDS();
SqlConnection conn = new SqlConnection("Server=SERVERNAME;" + 
                     "Integrated Security=sspi;" + 
                     "Database=PartialUpdateTest");
da.SelectCommand = new SqlCommand("Select configId," + 
                   "forUnitType,data from UnitConfig",conn);
da.Fill(ds.UnitConfig);

Updating the database:

When we want to save the changes to the database, we simply replace calls to the SqlDataAdapter with calls to the UpdateHandler. The example below uses the strongly typed DataSet used in the sample project:

UpdateHelper updh = new UpdateHelper();
updh.UpdateCommand = GetUnitConfigUpdateCommand();
updh.UpdateCommand.Connection.Open();
updh.Update(ds.UnitConfig);

Running the Sample Application

This is a trivial Visual Studio 2005 project that is used to demonstrate the use of the UpdateHelper for the stored procedure that is listed below.

Sample Application Setup:

A number of steps need to be carried out to run the sample application.

1. Download the Zip file and extract it to a local drive.
2. Run the SQL script <installation dir>\PartialUpdatesFromDataSet_app\PartialUpdateDB.sql - this will create the test database.
3. If necessary, modify the ConnectionString attribute in <installation dir>\PartialUpdatesFromDataSet_app\PartialUpdateTest.exe.config to point to the newly created database (for example, if your SQL Server instance is on another machine or does not use SSPI).
4. Execute the application in <installation dir>\PartialUpdatesFromDataSet_app\PartialUpdateTest.exe.

Sample Project Setup:

The steps are the same as above except that the folder will be PartialUpdatesFromDataSet_vs2003, and App.config needs to be changed instead of PartialUpdateTest.exe.config.

Execution:

When the application is running, you will see the UnitConfig table. To see the problem when SqlDataAdapter.Update() is used, modify the data column for configId 1 or 2, then press "Update". Event though you should only have modified the data column, we will still get an SqlException triggered by the stored procedure telling you that UnitConfig.forUnitType cannot be changed.

Now do the same thing using UpdateHelper.Update() - check the "Use UpdateHelper" CheckBox before pressing "Update", the new value will be successfully saved to the database.

UpdateHelper will successfully update data values for configurations 1 and 2 but will not update the forUnitType column as it is already in a relationship.

SqlDataAdapter cannot update configurations 1 and 2 at all.

Both SqlDataAdapter and UpdateHelper can update configurations 3 and 4 as they are unassigned (i.e., not referenced in the Unit table).

Code Explanation

Sample stored procedure:

Below is the stored procedure that is used in the sample application. The points to note are:

1. It checks that each parameter is not null before carrying out the related update.
2. Under some circumstances (namely if the UnitConfig row is referenced in the Unit table), it will not allow updates to the UnitConfig.forUnitType column.

This stored procedure can be used with both the SqlDataAdapter and the UpdateHandler, however, if the row in question is in use in the Unit table, the SqlDataAdapter.Update() method will always fail even if only the data column has been modified.

ALTER PROCEDURE dbo.p_UnitConfig_Update
    (
        @configId INT,
        @forUnitType INT = NULL,
        @data NVARCHAR(50) = NULL
    )
AS
    SET NOCOUNT ON
    
    BEGIN TRANSACTION
    
    -- deal with changes to the forUnitType column 

    IF @forUnitType IS NOT NULL
    BEGIN
        -- we can only update UnitConfig.forUnitType

        -- if this entry is not 

        -- referenced by any entry in the Unit table

        -- note that this check would be

        -- better placed in a trigger

        IF EXISTS 
            (
            SELECT * FROM Unit
                INNER JOIN UnitConfig 
                    ON Unit.configId = UnitConfig.configId
                AND Unit.configId = @configId
            )
        BEGIN
            RAISERROR('UnitConfig.forUnitType cannot be changed 
                       while the UnitConfig is in use',16,1)
            ROLLBACK TRANSACTION
            RETURN -100
        END
        
        UPDATE UnitConfig
        SET forUnitType = @forUnitType
        WHERE configId = @configId
        
        IF @@Error <> 0
        BEGIN
            ROLLBACK TRANSACTION
            RAISERROR('Error updating into 
                       UnitConfig.forUnitType',16,1)
            RETURN -100
        END        
    END
    
    -- we can update UnitConfig.data regardless

    -- of wether this entry is used in the Unit table

    IF @data IS NOT NULL
    BEGIN
        UPDATE UnitConfig
        SET data = @data
        WHERE configId = @configId
        
        IF @@Error <> 0
        BEGIN
            ROLLBACK TRANSACTION
            RAISERROR('Error updating UnitConfig.data',16,1)
            RETURN -100
        END                
    END
    
    COMMIT TRANSACTION
    
    RETURN 

The UpdateHelper class:

The class used to achieve this is pretty straightforward, and simply consists of SqlCommand properties and the Update() procedure.

For new and deleted rows, UpdateHelper.Update() executes the InsertCommand and DeleteCommand in much the same way as SqlDataAdapter.Update() would (though it should be noted that this is by no means a full implementation of the SqlDataAdapter.Update() method's functionality).

When it encounters a modified DataRow, it substitutes DBNull.Value for any DataRow value where the DataRowVersion.Original entry is the same as the DataRowVersion.Current entry.

In order to ensure that the correct database entries are updated, it uses the DataTable.PrimaryKey property to ensure that the Primary Key for the database table is always set in the stored procedure. For this reason, it is always necessary to ensure that the DataTable has been constructed with a Primary Key that accurately reflects the database table's primary key.

public class UpdateHelper
{
  private SqlCommand _upd;
  private SqlCommand _ins;
  private SqlCommand _del;

  public SqlCommand UpdateCommand{get{return _upd;}set{_upd=value;}}
  public SqlCommand InsertCommand{get{return _ins;}set{_ins=value;}}
  public SqlCommand DeleteCommand{get{return _del;}set{_del=value;}}

  public void Update(DataTable dt)
  {
    DataColumn [] pk = dt.PrimaryKey;

    DataRowCollection drc = dt.Rows;
        
    SqlCommand cmd;

    foreach (DataRow currRow in drc)
    {
      if(currRow.RowState == DataRowState.Modified)
      {
        #region Update using minimal updates
        cmd=_upd;
        foreach (SqlParameter param in cmd.Parameters)
        {
          object current = currRow[param.SourceColumn];

          //if this column has changed

          if(!current.Equals(currRow[param.SourceColumn,DataRowVersion.Original]))
          {
            //update it

            param.Value=current;
          }
          else
          {
            //if it is the same check if it is in the primary key

            foreach ( DataColumn c in pk )
            {
              //if it is in the PK

              if(c.ColumnName.Equals(param.SourceColumn))
              {
                //include the value

                param.Value=current;
                break;
              }
              else
              {
                //otherwise trigger the stored procedure 

                //to ignore it by setting it to DBNull.Value

                param.Value=DBNull.Value;
              }
            }
          }
        }
        #endregion
      }
      else
      {
        #region Update using all parameters
        switch ( currRow.RowState )
        {
          case DataRowState.Deleted:
            cmd=_del;
            break;

          case DataRowState.Added:
            cmd=_ins;
            break;

          case DataRowState.Unchanged:
            continue;

          default:
            throw new DataAccessException(currRow.RowState.ToString()+ 
                                          " is not handled here.");
        }
        foreach (SqlParameter param in cmd.Parameters)
        {
          param.Value=currRow[param.SourceColumn];
        }
        #endregion
      }
      cmd.ExecuteNonQuery();
    }
  }
}

Notes

It would be better to have the additional constraint on the forUnitType column in a trigger so that no-one could directly manipulate the table and cause problems; for the sake of clarity I have included the check in the stored procedure for this example.