Evaluate Weigh the pros and cons of technologies, products and projects you are considering.

Update using Web services, datasets and diffgrams

How to use Web services, datasets and diffgrams to update a database.

In this tip, I continue the series on using Web services and Datasets. In the first tip, I explained how to create a Web Service that retrieves data from a stored procedure into a Dataset and transmits it in its XML representation to the consuming application.

In the second tip, I explained how to consume the Web service, and explained how the XML representation gets transformed back into a Dataset. I used that Dataset by DataBinding it to a DataGrid for display in an ASP.NET Web Form.

In this tip, I demonstrate how to make changes to the data in the Dataset (on the client application), then send the modified Dataset back to the Web service provider to update the database. To accomplish this, Microsoft has created the concept of a DiffGram. Due to the disconnected nature of the Dataset, it is possible that many different sources (other applications, or many different users of the same application) could have changed the underlying data after it was sent to our consumer. Also, its possible that our consuming application can do more than just update the data...perhaps it can add new rows of data to the Dataset, or delete rows of data from the Dataset. The Dataset keeps a running log of all changes to the original dataset to know 1) what happened, and 2) when did it happen. Then, when the dataset is passed back into the Web service, the dataset can be reconciled back into the original data source. Updates to the dataset will result in update statements to the data source. Inserts that occurred on the client to the dataset will result in insert statements to the original data source. The same is true for deletes, as well. The key is that the DataAdapter knows how to reconcile inserts, updates and deletes back to the data source and map the appropriate fields, parameters, etc. I use the terms "the DataAdapter knows" accomodatively -- it doesn't just "know." You must tell it, but the way you do this is through the DataAdapter Wizard, as I'll demonstrate in just a moment.

To illustrate this, I've created a new Web service and a new Client application that retrieves an Affiliate's information, allows the user to change it in a Web Form, then return the updated information back to the Web service server where the data source is updated with the changes. I've provided the source code, and I've provided a screen cam video that walks through the entire creation process. These tools should be used in conjunction with this tip to get the complete picture of what is going on. In the rest of this article, I'll outline the steps at a high level that were used to accomplish this.

  1. First, I create a new ASP.NET Web services project.
  2. Next, I drag-and-drop the affiliate table from the Server Explorer to the Web service designer surface. This creates a SqlDataConnection and a SqlDataAdapter object on the designer surface. I select the SqlDataAdapter and click the "Configure Data Adapter" option under the Properties window. This pops open the "Configure Data Adapter Wizard".
  3. In the Wizard, I opt to create stored procedures, add a where clause to the generated SQL statement to make sure I am only getting the affiliate record for a specific AffiliateID, name the four stored procedures appropriately, then allow the Wizard to create the appropriate stored procedures for me.
  4. When returned to the designer surface, I select the SqlDataAdapter and select Generate Dataset option from under the Properties window. I name this new Dataset dsAffiliate. This creates a new instance of a Dataset on the designer surface called dsAffiliate1.
  5. Now I access the code-behind for the Web service and create two Web Methods, one called GetAffiliateDetail and the other UpdateAffiliateDetail. GetAffiliateDetail requires the affiliate to pass an AffiliateID and returns a Dataset in the format of dsAffiliate:
    <WebMethod()> _
    Public Function GetAffiliateDetail( _
      ByVal xintAffiliateID As Integer) As dsAffiliate
      SqlDataAdapter1.SelectCommand.Parameters.Item("@affiliate_id").Value = xintAffiliateID
      SqlDataAdapter1.Fill(DsAffiliate1, "affiliate")
      GetAffiliateDetail = DsAffiliate1
    End Function

    The UpdateAffiliateDetail function merely accepts an instance of dsAffiliate and returns an integer for confirmation.

    <WebMethod()> _
    Public Function UpdateAffiliateDetail( _
      ByVal xdsAffiliate1 As dsAffiliate) As Integer
      UpdateAffiliateDetail = _
        SqlDataAdapter1.Update(xdsAffiliate1, "affiliate")
      Return 1
    End Function

    I build the application.

  6. The next step was to build the consuming application. I create a new ASP.NET Web Form application, then add two buttons (Get and Update) and about six textboxes (for the different properties of the Affiliate.)

  7. I create a Web Reference to the Web service I created in steps 1 through 5. This will add an instance of the dsAffiliate schema to the Web Reference -- this is important for the next step to work!

  8. I drag-and-drop a Dataset object from the Toolbox to the designer surface, and set it as a Typed Dataset to the dsAffiliate schema that appears in the drop-down box. By default, it will name this instance of the Dataset as dsAffiliate1.

  9. For each of the textboxes, I set the DataSource property to the appropriate field in the dsAffiliate1 Dataset. This allows me to retrieve the values without writing code to do it.

  10. Now, I code the cmdGet button's click event. It should look something like this:
    Private Sub cmdGet_Click(ByVal sender As System.Object, ByVal e As
     System.EventArgs) Handles Button1.Click
      Dim ws As localhost.Service1 = New localhost.Service1()
      DsAffiliate1 = ws.GetAffiliateDetail(1)
      ' Since the web is a stateless environment, we must save
      ' off the contents of the dsAffiliate1 Dataset, or else
      ' it will lose its contents.
      Session.Add("myDataset", DsAffiliate1)
    End Sub

    Notice that after I call the Web service to retrieve the DsAffiliate1, I then call DataBind. This refreshes the textboxes with the values from the database. Also, notice that I save off the contents of the DsAffiliate1 into the session. As the comments above denote, this is to maintain state across a typically stateless environment.

  11. Next, I code the cmdUpdate button's click event. It should look like this:
    Private Sub cmdUpdate_Click(ByVal sender As System.Object, ByVal e As
     System.EventArgs) Handles Button2.Click
      Dim ws As localhost.Service1 = New localhost.Service1()
      ' As noted above, we COULD have pulled from a 
      ' database from a persisted file, or any other 
      ' data store.  In this case, we use the Session 
      ' for sheer convenience.
      DsAffiliate1 = Session("myDataset")
      ' We'll use not just any DataRow, but specifically
      ' an affiliateRow that has knowledge of the 
      ' specifical columns in our DataSet.
      Dim dr As localhost.dsAffiliate.affiliateRow
      dr = DsAffiliate1.affiliate(0)
      dr.affiliate_name = txtName.Text
      dr.affiliate_address = txtAddress.Text
      dr.affiliate_city = txtCity.Text
      dr.affiliate_state = txtState.Text
      dr.affiliate_zip = txtZip.Text
      dr.affiliate_phone = txtPhone.Text
      dr.affiliate_email = txtEmail.Text
      Dim iRet = ws.UpdateAffiliateDetail(DsAffiliate1)
    End Sub

    As you'll notice, I retrieve the DsAffiliate1 from the Session object, then begin to update its values with the new values the user typed into the Web Form. Then, I send the DsAffiliate1 into the UpdateAffiliateDetail Web Service. This in turn will save the values back into the data source (as explained in step 5 above).

Compile both the Web service and the Web Form. Then run the Web Form application and watch the magic happen. This is a great model for how Web services and Dataset were designed to operate. You can expand this model to do as much processing on the client (consumer) as possible, then when you are finished upload the changes back to the Web service data provider and allow it to rectify the changes back into the database. You can even be clever about how you save information back, alerting the user that the underlying data has changed and asking them if they are sure they want to overwrite these changes. Try adding this level of functionality in your applications before .NET -- especially considering how few lines of code we actually wrote!

I hope you enjoy these tips, and stay tuned for the next one where I will show the exact structure of the DiffGram and the DataSet that is being passed back and forth between the consumer and provider through the use of SOAP Extensions.

About the Author

Robert Tabor is a Microsoft Certified Professional in Visual Basic with over six years of experience developing n-tier Microsoft-centric applications for some of the world's most prestigious companies and consulting organizations, such as Ernst & Young, KPMG, Cambridge Technology Partners, Sprint, American Heart Association, and the Mary Kay Corporation. Bob is the author of Microsoft .NET XML Web services by Sams Publishing, and contributes to SoapWebservices.com and LearnVisualStudio.NET. He is currently working on initiatives within Mary Kay, the second largest eCommerce site in retail volume on the net, of how to utilize .NET within their e-business group.

Dig Deeper on Topics Archive

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.