How to update parent-child data with an Identity column from a Windows Forms application by using a Web service in Visual Basic .NET

Article translations Article translations
Article ID: 310350 - View products that this article applies to.
This article was previously published under Q310350
Expand all | Collapse all

On This Page

SUMMARY

This step-by-step article describes how to retrieve a DataSet object from a Web service, how to send DiffGram updates to the Web service, and then how to merge the updated DiffGram back into the client DataSet. This process involves the following steps:
  1. The client application retrieves a DataSet with two DataTable objects in a parent-child relationship from the Web service. The parent DataTable has an Identity/Autonumber column as the Primary Key.
  2. In the client application, the user can add, delete, and modify both parent records and child records. New parent records receive a temporary primary key value that is generated locally.
  3. The client application sends changes back to the Web service as a DiffGram.
  4. The Web service updates the database, retrieves the actual primary key values for new parent records, and then propagates the changed key values to the child records.
  5. The client application receives the updated values from the Web service and then merges the updated values back into the local DataSet.
For more information about how to update a single table by using a Web service, click the following article number to view the article in the Microsoft Knowledge Base:
308056 How to update server data through a Web Service by using ADO.NET and Visual Basic .NET

Requirements

The following list outlines the recommended hardware, software, network infrastructure, and service packs that are required:
  • Microsoft Windows 2000 Professional, Windows 2000 Server, Windows 2000 Advanced Server, or Windows NT 4.0 Server
  • Microsoft Visual Studio .NET
This article assumes that you are familiar with the following topics:
  • Visual Studio .NET
  • ASP.NET fundamentals and syntax
  • ADO.NET fundamentals and syntax
The code samples in this article use http://localhost as the Web server. Additionally, the code samples use the Northwind database as the database. The Northwind database is included with Microsoft SQL Server.

Create a Web service

  1. Start Visual Studio .NET.
  2. Create a new Visual Basic ASP.NET Web Service project by following these steps:
    1. On the File menu, point to New, and then click Project.
    2. In the New Project dialog box, click Visual Basic Projects under Project Types, and then click ASP.NET Web Service under Templates.
    3. In the Location box, replace the existing address with the following address, and then clickOK.
      http://localhost/vbUpdateData
  3. On the Service1.asmx.vb[Design] page, switch to Code view. The Code window for the Web service appears.
  4. At the top of the Code window, add the following Imports statements:
    Imports System.Data
    Imports System.Data.SqlClient
    					
  5. After the following lines of code
    Public Class Service1
               Inherits System.Web.Services
    						
    add the following code:
        <WebMethod()> Public Function GetData() As DataSet
            Dim con As New SqlConnection("server=<SQLServer>;uid=<myid>;pwd=<mypwd>;database=northwind")
            'Pull back the recent orders for the parent rows.
            Dim daOrders As New SqlDataAdapter("SELECT * FROM Orders WHERE OrderDate >= '05/01/1998'", con)
            'Get only the appropriate child rows for the preceding parent rows.
            Dim daDetails As New SqlDataAdapter("SELECT * FROM [Order Details] WHERE OrderID in ( SELECT OrderID FROM Orders WHERE OrderDate >= '05/01/1998')", con)
            Dim ds As New DataSet()
            Dim dt As DataTable
            Dim dc As DataColumn
            Dim cb As SqlCommandBuilder
            '
            ' Fill DataSet and set DataRelation for navigating in the DataGrid.
            '
            con.Open()
            daOrders.FillSchema(ds, SchemaType.Mapped, "Orders")
            daOrders.Fill(ds, "Orders")
            daDetails.FillSchema(ds, SchemaType.Mapped, "Details")
            daDetails.Fill(ds, "Details")
            ds.Relations.Add("OrdDetail", ds.Tables!Orders.Columns!OrderID, ds.Tables!Details.Columns!OrderID)
            '
            ' Set AutoNumber properties in the Orders DataTable.
            '
            dt = ds.Tables!Orders
            dc = dt.Columns!OrderID
            dc.AutoIncrement = True
            dc.AutoIncrementSeed = -1
            dc.AutoIncrementStep = -1
            '
            'Return the DataSet to the client.
            '
            GetData = ds
        End Function
    
        <WebMethod()> Public Function UpdateData(ByVal ds As DataSet) As DataSet
            Dim con As New SqlConnection("server=<SQLServer>;uid=<myid>;pwd=<mypwd>;database=northwind")
            Dim daOrders As New SqlDataAdapter("SELECT * FROM Orders WHERE OrderDate >= '05/01/1998'", con)
            Dim daDetails As New SqlDataAdapter("SELECT * FROM [Order Details] WHERE OrderID in ( SELECT OrderID FROM Orders WHERE OrderDate >= '05/01/1998')", con)
            '
            ' Get commands for Orders table.
            ' Reselect record after insert to get new Identity value.
            ' Must get the schema, which you did in GetData(), before getting commands, 
            ' or the Command Builder will try to insert new rows, based 
            ' on the Identity column.
            '
            con.Open()
            Dim cb As SqlCommandBuilder
            cb = New SqlCommandBuilder(daOrders)
            daOrders.UpdateCommand = cb.GetUpdateCommand
            daOrders.DeleteCommand = cb.GetDeleteCommand
            daOrders.InsertCommand = cb.GetInsertCommand
            daOrders.InsertCommand.CommandText &= "; Select * From Orders Where OrderID = @@IDENTITY"
            '
            ' UpdateRowSource tells the DataAdapter that there will be a re-selected record.
            '
            daOrders.InsertCommand.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord
            cb = Nothing
            '
            ' Get commands for Order Details table.
            '
            ' Must set the QuotePrefix and QuoteSuffix or the CommandBuilder won't put [ ]
            ' around the table name.
            '
            cb = New SqlCommandBuilder(daDetails)
            cb.QuotePrefix = "["
            cb.QuoteSuffix = "]"
            daDetails.UpdateCommand = cb.GetUpdateCommand
            daDetails.DeleteCommand = cb.GetDeleteCommand
            daDetails.InsertCommand = cb.GetInsertCommand
            cb = Nothing
            '
            ' Create a new DataAdapter based on the original one to prevent the
            ' CommandBuilder from modifying the SQL statements, 
            ' specifically the custom InsertCommand.
            '
            ' You don't need this if you roll your own commands and parameters or use 
            ' the visual tools to do it.
            '
            Dim daOrd2 As New SqlDataAdapter()
            daOrd2.DeleteCommand = daOrders.DeleteCommand
            daOrd2.InsertCommand = daOrders.InsertCommand
            daOrd2.UpdateCommand = daOrders.UpdateCommand
            '
            ' Use a delegate to prevent AcceptChanges from occurring on Deletes & Inserts.
     
            ' This is for a limitation of the DataAdapter; see Q313540. 
            '
            AddHandler daOrd2.RowUpdated, AddressOf daOrd2_MyRowUpdated
    					   '
            AddHandler daDetails.RowUpdated, AddressOf dadetails_MyRowUpdated
            '
            'See Q313483 INFO: Roadmap for ADO.NET DataAdapter Objects. 
            daDetails.Update(GetDeletedRows(ds.Tables("details")))
            daOrd2.Update(GetDeletedRows(ds.Tables("orders")))
    
            daOrd2.Update(ds.Tables("orders").Select("", "", DataViewRowState.ModifiedCurrent))
            daDetails.Update(ds.Tables("details").Select("", "", DataViewRowState.ModifiedCurrent))
    
            daOrd2.Update(ds.Tables("orders").Select("", "", DataViewRowState.Added))
     ' Otherwise, you see an integrity violation, because of the parent row's
            ' orphaned child rows. You get an orphaned child row temporarily, because
            ' you store the original pseudo foreign key back to the child row and issue
            ' an AcceptChanges to force it to the OriginalValue property. You then restore
            ' the actual foreign key value back to the row without AcceptChanges(). This
            ' puts the child row in the correct state to be merged with the original DataSet
            ' on the client. All of this functionality is handled in the RowUpdated delegate
            ' of the Order Details DataAdapter (see the daOrd2_MyRowUpdated procedure, which follows this procedure).
            ds.EnforceConstraints = False
            daDetails.Update(ds.Tables("details").Select("", "", DataViewRowState.Added))
            'Turn the integrity checking back on, because you turned it off earlier.
            ds.EnforceConstraints = True
    
            con.Close()
            'Send the DataSet back to the client to have the DataSet merged back in.
            UpdateData = ds
    
        End Function
    
        Private Sub daOrd2_MyRowUpdated(ByVal sender As Object, ByVal e As System.Data.SqlClient.SqlRowUpdatedEventArgs)
            If e.StatementType = StatementType.Insert Then e.Status = UpdateStatus.SkipCurrentRow
            If e.StatementType = StatementType.Delete Then e.Status = UpdateStatus.SkipCurrentRow
        End Sub
    
        Private Sub dadetails_MyRowUpdated(ByVal sender As Object, ByVal e As System.Data.SqlClient.SqlRowUpdatedEventArgs)
      ' When the primary key propagates down to the child row's foreign key field, the field
            ' does not receive an OriginalValue with pseudo key value and a CurrentValue with the 
            ' actual key value. Therefore, when the merge occurs, this row is  appended to the DataSet
            ' on the client tier, instead of being merged with the original row that was added.
            If e.StatementType = StatementType.Insert Then
                'Don't allow the AcceptChanges to occur on this row.
                e.Status = UpdateStatus.SkipCurrentRow
                ' Get the Current actual primary key value, so you can plug it back
                ' in after you get the correct original value that was generated for the child row.
                Dim currentkey As Integer = e.Row("OrderID") '.GetParentRow("OrdDetail")("OrderID", DataRowVersion.Current)
                ' This is where you get a correct original value key stored to the child row. You yank
                ' the original pseudo key value from the parent, plug it in as the child row's primary key
                ' field, and accept changes on it. Specifically, this is why you turned off EnforceConstraints.
                e.Row!OrderID = e.Row.GetParentRow("OrdDetail")("OrderID", DataRowVersion.Original)
                e.Row.AcceptChanges()
                ' Now store the actual primary key value back into the foreign key column of the child row.
                e.Row!OrderID = currentkey
            End If
            If e.StatementType = StatementType.Delete Then e.Status = UpdateStatus.SkipCurrentRow
        End Sub
     Private Function GetDeletedRows(ByVal dt As DataTable) As DataRow()
            Dim Rows() As DataRow
            If dt Is Nothing Then Return Rows
            Rows = dt.Select("", "", DataViewRowState.Deleted)
            If Rows.Length = 0 OrElse Not (Rows(0) Is Nothing) Then Return Rows
            '
            ' Workaround:
            ' With a remoted DataSet, Select returns the array elements
            ' filled with Nothing/null, instead of DataRow objects.
            '
            Dim r As DataRow, I As Integer = 0
            For Each r In dt.Rows
                If r.RowState = DataRowState.Deleted Then
                    Rows(I) = r
                    I += 1
                End If
            Next
            Return Rows
        End Function
    
    End Class
          
  6. Modify the SqlConnection string to correctly connect to the server that is running SQL Server.

Test the Web service

  1. Press F5 to compile and to run the Web service. A Web page is returned. With this Web page, you can interact with the Web service in Microsoft Internet Explorer. Note that the URL of the returned page is http://localhost/vbUpdateData/Service1.asmx.
  2. On the Service1 Web page, click GetData. A Web page is returned that displays details about the GetData Web method.
  3. Close the Web pages.

Create a client application

  1. In Visual Studio .NET, create a new Visual Basic Windows Application project. Form1 is added to the project, by default.
  2. Add one Button control and one DataGrid control to Form1. Button1 and DataGrid1 are added to the project by default.
  3. Change the Name property of Button1 to btnSave, and then change the Text property of Button1 to Save.
  4. On the Project menu, click Add Web Reference. Type the URL for your Web service (in this case, type http://localhost/vbUpdateData/Service1.asmx), press ENTER, and then click Add Reference. The newly-added Web reference appears in Solution Explorer.
  5. To declare a form level member for the DataSet, add the following code after the Inherits statement:
    Public ds as Dataset
  6. Add the following code to the Form Load method:
            Dim MyWS As localhost.Service1
            MyWS = New localhost.Service1()
            ds = MyWS.GetData()
            DataGrid1.DataSource = ds
            DataGrid1.DataMember = "Orders"
    					
  7. Switch to Form view.
  8. Open the Code window for the Save button. Paste the following code into the BtnSave_Click (Save) event procedure:
            Dim myWS As localhost.Service1
            myWS = New localhost.Service1()
            Dim MyChangedRows As DataSet
    
            DataGrid1.DataMember = ""
            DataGrid1.DataSource = Nothing
    
            'Pull out only what you must send over the wire.
            MyChangedRows = ds.GetChanges
            MyChangedRows = myWS.UpdateData(MyChangedRows)
    
            'You must accept changes on the Dataset because of a known problem. See KB 313540.
    
            ds.AcceptChanges()
            ds.EnforceConstraints = False
            'Merge in the parent rows first, and then the child rows.
            ds.Merge(MyChangedRows.Tables!Orders, False, MissingSchemaAction.Ignore)
            ds.Merge(MyChangedRows.Tables!details, False, MissingSchemaAction.Ignore)
            'AcceptChanges that you have made to the DataSet.
            ds.AcceptChanges()
            'Turn on the integrity constraints that you turned off earlier.
            ds.EnforceConstraints = True
    
            DataGrid1.DataSource = ds
            DataGrid1.DataMember = "Orders"
    					

Test the client application

  1. Press F5 to compile and to run the client application.
  2. In DataGrid1, modify some of the data, and then click Save.

    NOTE: Do not change the key field. If you change the key field, you receive an error message that states that you are breaking referential integrity on the server.
  3. Add a child row to an existing parent row, and then note that the child row automatically receives the correct foreign key value.
  4. Add the following values for new parent rows and child rows, and then click Save. Note the key values before and after you click Save:
    • Orders.CustomerID=RATTC
    • Orders.EmployeeID=1
    • Orders.OrderDate=2/2/2002
    • Details.ProductID=1
    • Details.UnitPrice=18
    • Details.Quantity=1
    • Details.Discount=0

Troubleshooting

If you use Integrated Security in your database connection string, you must make sure that the Web service account has rights to the database. If the Web service account does not have rights to the database, you receive an HTTP 500 error when you test the Web service in the browser.

REFERENCES

For more information, click the following article numbers to view the articles in the Microsoft Knowledge Base:
308056 How to update server data through a Web service by using ADO.NET and Visual Basic .NET
313483 Roadmap for ADO.NET DataAdapter objects

Properties

Article ID: 310350 - Last Review: March 28, 2005 - Revision: 4.2
APPLIES TO
  • Microsoft Visual Basic .NET 2002 Standard Edition
  • Microsoft Visual Basic .NET 2003 Standard Edition
  • Microsoft ASP.NET (included with the .NET Framework)
  • Microsoft ASP.NET 1.1
  • Microsoft ADO.NET (included with the .NET Framework)
  • Microsoft ADO.NET 1.1
Keywords: 
kbhowtomaster KB310350

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com