Pattern to Update Multiple Fields

Introduction

Often times you find yourself needing to update the values from a web form into a database. This is usually a straightforward process. But what happens if you want to issue updates on only those values that have changed in the web form. And what if you want an easy pattern to use given that the set of values to be updated might grow (or, not likely, reduce) in future. In this example, I offer the case of updating personal information.

Background

Probably you have a form where a user can update his personal information. Whether you are using Windows Forms or web forms (I will use web forms in this example) you are using a bunch of Web Controls to capture the data (drop down lists, text boxes, radio buttons, etc..). And maybe you don't want to update every value in the table that stores this information because there exists some trigger that does something when a value is updated. Or maybe you have a lot of hits and just don't want the transaction log to fill. Or maybe your users are intellectually challenged and often hit the Submit button even when they have not updated anything and you don't want the call to be issued carte blanche. The following solution will attempt to provide a pattern to handle this situation.

Using the code

The methodology is essentially to maintain a Hash table of parameter names/parameter values, taking into account only those values that have changed in the web form. By populating this System.Web.UI.Page class instance variable that we declare, we can pass this value into some method that is responsible for issuing the call to update the database. This update method will extract these parameter names and values and issue a call to the stored procedure responsible for the update.

The code to do this is as follows:

1. Create a private instance variable of System.System.Collections.Hashtable. This variable is going to be responsible for associating stored procedure parameter names with the newly created (and guaranteed to be different - more on this later) value from the user.
   C#

   private System.Collections.Hashtable _changeList;

2. Create a private instance method that populates the table with the key/value parameters just mentioned.
   C#

   private void Add2Collection(string name, string newValue) 
   {
       if(null == _changeList) 
       {
           //must issue the constructor here.
           //We want _changeList to be null if nothing changed.
           _changeList = new Hashtable();    
                                                     
       }
       _changeList.Add(name, newValue);
   }

3. Wire up every Web Control's ValueChanged event. For instance:
   C#

   System.Web.UI.WebControls.TextBox = TextChanged
   System.Web.UI.WebControls.DropDownList = SelectedIndexChanged 
   System.Web.UI.WebControls.CheckBox = CheckedChanged

   And now the aspx markup should look like:

   ASP.NET

   Name 
       <asp:TextBox id="txtName" runat="server" 
             OnTextChanged="txtName_Changed"/>
   Age
       <asp:DropDownList id="cmbAge" runat="server" 
           OnSelectedIndexChanged="cmbAge_Changed">
           <asp:ListItem Value="0">13-20</asp:ListItem>
           <asp:ListItem Value="1">21-30</asp:ListItem>
           <asp:ListItem Value="2">31-40</asp:ListItem>
           <asp:ListItem Value="3">41-50</asp:ListItem>
           <asp:ListItem Value="4">>

   Notice that I did not wire up an event for the rbFemale radio button. This is because they are both members of the same group and I am asserting the table that stores this field is defined as:

   SQL

   CREATE TABLE UserData 
   (
       ...
       UserName varchar(50),
       Age int,
       IsMale bit,
       ...
   )

   So, if the user was male and clicks on rbFemale, then that will trigger a call to rbMale_Changed(). If the user was female and clicks on rbMale, again that will issue a call to rbMale_Changed(). Hopefully, no one is getting too many sex changes.

4. In each of those events, issue a call to Add2Collection().
   C#

   public void txtName_Changed(object sender, EventArgs e) 
   {
       Add2Collection("userName", txtName.Text);
   }
   
   public void cmbAge_Changed(object sender, EventArgs e) 
   {
       Add2Collection("userAge", cmbAge.SelectedValue);
   }
   
   public void rbMale_Changed(object sender, EventArgs e) 
   {
       Add2Collection("isMale", rbMale.Checked.ToString());
   }

   This is how we guarantee that _changeList will be null if nothing has changed. Only the calls to Add2Collection will initialize this variable. And if nothing changes, then this variable will remain null and flag us that we don't need to make a call to the database.

5. Finally, there is the button that issues the call to update:
   ASP.NET

   <asp:Button id="btnUpdate" runat="server" 
     Text="Update" OnClick="btnUpdate_Click"/>

   And the code behind it is:

   C#

   public void btnUpdate_Click(object sender, System.EventArgs e)
   {
       Update(_changeList);
   }

   The update method is very simple. It accepts the _changeList, or in this case a class implementing IDictionary. These entries are now guaranteed to contain the parameter names and their new values. We extract these pairs using an enumerator to gather the parameter names and parameter values, load them into a SqlCommand and issue the call. Of course, we just return if IDictionary object is null.

   C#

   private static void Update(IDictionary parameterCollection) 
   {
          //user hit update but didn't update anything - just return
       if(null == parameterCollection) 
       {
           return;
       }
   
       const string sproc = "SomeUpdateSproc";
       using(SqlConnection connection = 
               new SqlConnection(SomeConnectionString))
       using(SqlCommand command = 
               new SqlCommand(sproc, connection)) 
       {
           command.CommandType = CommandType.StoredProcedure;
           
           //get every name/value pair
           foreach(DictionaryEntry entry in parameterCollection) 
           {
             //don't forget to add the '@'
             SqlParameter parameter = 
               command.Parameters.Add("@" + entry.Key, entry.Value);  
             parameter.Value = entry.Value;  //our new value!
           }
   
           command.Connection.Open();
           command.ExecuteNonQuery();
           command.Connection.Close();
       }
   }

   I made this method static and accepted the instance variable as a parameter because this will be wrapped in some Data Access Layer code. Just move this out of the code-behind page and put it into the Data Access Layer.

   Lastly the stored proc would look like:

   SQL

   CREATE PROCEDURE [dbo].[SomeUpdateSproc] 
   
       @userName varchar(50) = null,
       @userAge int = null,
       @isMale bit = null
   
   AS
   
   if @userName <> null begin
       --do some update
   end
   
   if @userAge <> null begin
       --do some update
   end
   
   if @isMale <> null begin
       --do some update
   end
   
   GO

And that is it. As new fields are added to the table that need to be updated in the same manner, just add the appropriate web control, set its Changed event handler, issue the Add2Collection() call and add the appropriate logic to the stored procedure.

History

None yet.