Click here to Skip to main content
11,487,721 members (73,926 online)
Click here to Skip to main content

Pattern to Update Multiple Fields

, 14 Apr 2005 32.8K 32
Rate this:
Please Sign up or sign in to vote.
An article describing a pattern for updating only certain values that have changed in a web form.

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.
    private System.Collections.Hashtable _changeList;
  2. Create a private instance method that populates the table with the key/value parameters just mentioned.
    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:
    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:

    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:

    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().
    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:Button id="btnUpdate" runat="server" 
      Text="Update" OnClick="btnUpdate_Click"/>

    And the code behind it is:

    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.

    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:

    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.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Share

About the Author

Mark

United States United States
No Biography provided

Comments and Discussions

 
QuestionHow do you set a constant variable? Pin
New_Jack4-Mar-09 17:39
memberNew_Jack4-Mar-09 17:39 
GeneralPatterns Pin
norm.net14-Apr-05 23:48
membernorm.net14-Apr-05 23:48 
GeneralRe: Patterns Pin
Anonymous21-Apr-05 20:00
sussAnonymous21-Apr-05 20:00 
GeneralRe: Patterns Pin
Mark Siffer22-Apr-05 5:00
memberMark Siffer22-Apr-05 5:00 
GeneralRe: Patterns Pin
Anonymous25-Apr-05 21:22
sussAnonymous25-Apr-05 21:22 
GeneralRe: Patterns Pin
hexcoder27-Apr-05 5:10
memberhexcoder27-Apr-05 5:10 
GeneralRe: Patterns Pin
Mark Siffer27-Apr-05 6:03
memberMark Siffer27-Apr-05 6:03 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.150520.1 | Last Updated 14 Apr 2005
Article Copyright 2005 by Mark
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid