Microsoft KB Archive/310350

= 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 ID: 310350

Article Last Modified on 3/28/2005

-

APPLIES TO


 * Microsoft Visual Basic .NET 2002 Standard Edition
 * Microsoft Visual Basic .NET 2003 Standard Edition
 * Microsoft .NET Framework 1.1 Service Pack 1
 * Microsoft ASP.NET 1.1
 * Microsoft .NET Framework 1.1 Service Pack 1
 * Microsoft ADO.NET 1.1

-



This article was previously published under Q310350





For a Microsoft Visual C# .NET version of this article, see 320301.

IN THIS TASK

 * SUMMARY
 * Requirements
 * Create a Web service
 * Test the Web service
 * Create a client application
 * Test the client application
 * Troubleshooting
 * References



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

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:  On the File menu, point to New, and then click Project. In the New Project dialog box, click Visual Basic Projects under Project Types, and then click ASP.NET Web Service under Templates. In the Location box, replace the existing address with the following address, and then clickOK.

http://localhost/vbUpdateData  </li> On the Service1.asmx.vb[Design] page, switch to Code view. The Code window for the Web service appears.</li>  At the top of the Code window, add the following Imports statements: Imports System.Data Imports System.Data.SqlClient </li>  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(&quot;server=<SQLServer>;uid= ;pwd= ;database=northwind&quot;) 'Pull back the recent orders for the parent rows. Dim daOrders As New SqlDataAdapter(&quot;SELECT * FROM Orders WHERE OrderDate >= '05/01/1998'&quot;, con) 'Get only the appropriate child rows for the preceding parent rows. Dim daDetails As New SqlDataAdapter(&quot;SELECT * FROM [Order Details] WHERE OrderID in ( SELECT OrderID FROM Orders WHERE OrderDate >= '05/01/1998')&quot;, 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, &quot;Orders&quot;) daOrders.Fill(ds, &quot;Orders&quot;) daDetails.FillSchema(ds, SchemaType.Mapped, &quot;Details&quot;) daDetails.Fill(ds, &quot;Details&quot;) ds.Relations.Add(&quot;OrdDetail&quot;, 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(&quot;server=<SQLServer>;uid= ;pwd= ;database=northwind&quot;) Dim daOrders As New SqlDataAdapter(&quot;SELECT * FROM Orders WHERE OrderDate >= '05/01/1998'&quot;, con) Dim daDetails As New SqlDataAdapter(&quot;SELECT * FROM [Order Details] WHERE OrderID in ( SELECT OrderID FROM Orders WHERE OrderDate >= '05/01/1998')&quot;, 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 &= &quot;; Select * From Orders Where OrderID = @@IDENTITY&quot; '       ' 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 = &quot;[&quot; cb.QuoteSuffix = &quot;]&quot; 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(&quot;details&quot;))) daOrd2.Update(GetDeletedRows(ds.Tables(&quot;orders&quot;)))

daOrd2.Update(ds.Tables(&quot;orders&quot;).Select(&quot;&quot;, &quot;&quot;, DataViewRowState.ModifiedCurrent)) daDetails.Update(ds.Tables(&quot;details&quot;).Select(&quot;&quot;, &quot;&quot;, DataViewRowState.ModifiedCurrent))

daOrd2.Update(ds.Tables(&quot;orders&quot;).Select(&quot;&quot;, &quot;&quot;, 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(&quot;details&quot;).Select(&quot;&quot;, &quot;&quot;, 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(&quot;OrderID&quot;) '.GetParentRow(&quot;OrdDetail&quot;)(&quot;OrderID&quot;, 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(&quot;OrdDetail&quot;)(&quot;OrderID&quot;, 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(&quot;&quot;, &quot;&quot;, 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 </li> Modify the SqlConnection string to correctly connect to the server that is running SQL Server.</li></ol>

back to the top

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.

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.</li> Add one Button control and one DataGrid control to Form1. Button1 and DataGrid1 are added to the project by default.</li> Change the Name property of Button1 to btnSave, and then change the Text property of Button1 to Save .</li> 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.</li>  To declare a form level member for the DataSet, add the following code after the Inherits statement: Public ds as Dataset </li>  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 = &quot;Orders&quot; </li> Switch to Form view.</li>  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 = &quot;&quot; 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 = &quot;Orders&quot; </li></ol>

back to the top

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.
 * 1) Add a child row to an existing parent row, and then note that the child row automatically receives the correct foreign key value.
 * 2) 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:
 * 3) * Orders.CustomerID=RATTC
 * 4) * Orders.EmployeeID=1
 * 5) * Orders.OrderDate=2/2/2002
 * 6) * Details.ProductID=1
 * 7) * Details.UnitPrice=18
 * 8) * Details.Quantity=1
 * 9) * 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

<div class="references_section">