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

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:

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.
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.
The client application sends changes back to the Web service as a DiffGram.
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.
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

back to the top

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.

back to the top

Create a Web service

Start Visual Studio .NET.
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
On the Service1.asmx.vb[Design] page, switch to Code view. The Code window for the Web service appears.
At the top of the Code window, add the following Imports statements:
```
Imports System.Data
Imports System.Data.SqlClient
					
```

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

Modify the SqlConnection string to correctly connect to the server that is running SQL Server.

back to the top

Test the Web service

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.
On the Service1 Web page, click GetData. A Web page is returned that displays details about the GetData Web method.
Close the Web pages.

back to the top

Create a client application

In Visual Studio .NET, create a new Visual Basic Windows Application project. Form1 is added to the project, by default.
Add one Button control and one DataGrid control to Form1. Button1 and DataGrid1 are added to the project by default.
Change the Name property of Button1 to btnSave, and then change the Text property of Button1 to Save.
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.
To declare a form level member for the DataSet, add the following code after the Inherits statement:
```
Public ds as Dataset
```

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"

Switch to Form view.

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"

back to the top

Test the client application

Press F5 to compile and to run the client application.
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.
Add a child row to an existing parent row, and then note that the child row automatically receives the correct foreign key value.
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

back to the top

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.

back to the top

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

IN THIS TASK

SUMMARY

Requirements

Create a Web service

Test the Web service

Create a client application

Test the client application

Troubleshooting

REFERENCES