HOW TO: Update Parent-Child Data with an Identity Column from a Windows Forms Application by Using a Web Service in Visual C# .NET

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 object 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 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 key values that are changed 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 additional information about how to update a single table by using a Web service, click the article number below to view the article in the Microsoft Knowledge Base:

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
• ADO.NET fundamentals and syntax
• ASP.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 the Web Service

1. To create a new Visual C# ASP.NET Web Service project, follow these steps:
   1. Start Visual Studio .NET.
   2. On the File menu, point to New, and then click Project.
   3. Click Visual C# Projects under Project Types, and then click ASP.NET Web Service under Templates.
   4. In the Location box, the default location appears as http://localhost/WebService1. Type the URL for your server (for example, http://localhost runs the Web service on your local Web server). Replace WebService1 with CSharpUpdateData. The URL in the Location box should appear as follows:
      http://localhost/CSharpUpdateData
   5. To close the New Project dialog box, click OK.
2. On the Service1.asmx.cs[Design] page, switch to Code view. Notice that the Code window for the Web service appears.
3. At the top of the Code window, add the following using statement:

   using System.Data.SqlClient;

4. Add the following to the class Service1 implementation:

           [WebMethod]
           public DataSet GetData()
           {
               SqlConnection conn = new SqlConnection ("server=vcdb02;uid=sa;pwd=ricka;database=northwind");
               //Pull back the recent orders for the parent rows.
               SqlDataAdapter daOrder = new SqlDataAdapter("SELECT * FROM Orders WHERE OrderDate >= '05/01/1998'",conn);
               //Get only the appropriate child rows for the parent rows.
               SqlDataAdapter daDetails = new SqlDataAdapter("SELECT * FROM [Order Details] WHERE OrderID in ( SELECT OrderID FROM Orders WHERE OrderDate >= '05/01/1998')",conn);
   
               DataSet ds = new DataSet();
               try
               {
   				
                   //Fill DataSet, and then set DataRelation to move through the DataGrid.
                   conn.Open();
   
                   daOrder.FillSchema(ds,SchemaType.Mapped,"Orders");
                   daOrder.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"]);
   			
                   DataColumn dc = ds.Tables["Orders"].Columns["OrderID"];
                   dc.AutoIncrement = true;
                   dc.AutoIncrementSeed = -1;
                   dc.AutoIncrementStep = -1;
               }
               catch(SqlException ex)
               {
                   Console.Write (ex.Message.ToString ());
                   Console.Write(ex.InnerException.ToString ());
   
               }
   
               return ds;
           }
   		
           [WebMethod]
           public DataSet UpdateData(DataSet ds)
           {
               SqlConnection conn = new SqlConnection ("server=vcdb02;uid=sa;pwd=ricka;database=northwind");
               //Pull back the recent orders for the parent rows.
               SqlDataAdapter daOrders = new SqlDataAdapter("SELECT * FROM Orders WHERE OrderDate >= '05/01/1998'",conn);
               //Get only the appropriate child rows for the parent rows.
               SqlDataAdapter daDetails = new SqlDataAdapter("SELECT * FROM [Order Details] WHERE OrderID in ( SELECT OrderID FROM Orders WHERE OrderDate >= '05/01/1998')",conn);
   
   			
               try
               {
                   conn.Open();
                   // Get commands for the Orders table.
                   // Reselect record after insert to get new Identity value.
                   // You must get the schema, which you did in GetData(), before you get commands; 
                   // otherwise, the Command builder tries to insert new rows, based 
                   // on the Identity column.
                   SqlCommandBuilder cb = new SqlCommandBuilder(daOrders);
                   daOrders.DeleteCommand = cb.GetDeleteCommand();
                   daOrders.UpdateCommand = cb.GetUpdateCommand();
                   daOrders.InsertCommand = cb.GetInsertCommand();
                   daOrders.InsertCommand.CommandText = String.Concat(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 = null;
           
   
                   // Get commands for the Order Details table.
                   // Must set the QuotePrefix and QuoteSuffix; 
                   // otherwise, the CommandBuilder does not put brackets ([])
                   // around the table name.
   
                   SqlCommandBuilder cb1 = new SqlCommandBuilder(daDetails);
                   cb1.QuotePrefix = "[";
                   cb1.QuoteSuffix = "]";
                   daDetails.DeleteCommand = cb1.GetDeleteCommand();
                   daDetails.InsertCommand = cb1.GetInsertCommand();
                   daDetails.UpdateCommand = cb1.GetUpdateCommand();
   
   
                   // Create a new DataAdapter based on the original one to prevent the
                   // CommandBuilder from modifying the SQL statements, 
                   // specifically the custom InsertCommand.
                   // You do not need this if you roll your own commands and parameters 
                   // or if you use the Visual Tools to do it.
              
                   SqlDataAdapter daOrd2 = new SqlDataAdapter();
                   daOrd2.DeleteCommand = daOrders.DeleteCommand;
                   daOrd2.InsertCommand = daOrders.InsertCommand;
                   daOrd2.UpdateCommand = daOrders.UpdateCommand;
   
                   // Use a delegate to prevent AcceptChanges from occurring on Deletes and Inserts.
                   // This is for a limitation of the DataAdapter; see Q313540. 
   
                   daOrd2.RowUpdated += new SqlRowUpdatedEventHandler(OnOrd1RowUpdated);
                   daDetails.RowUpdated += new SqlRowUpdatedEventHandler(OnDetailsRowUpdated);
   
   
                   daDetails.Update(GetDeletedRows(ds.Tables["Details"]));
                   daOrd2.Update(GetDeletedRows(ds.Tables["Orders"]));
                   DataRow [] dsArray = ds.Tables["Orders"].Select("", "", DataViewRowState.ModifiedCurrent);
                   daOrd2.Update(ds.Tables["Orders"].Select("", "", DataViewRowState.ModifiedCurrent));
                   daDetails.Update(ds.Tables["Details"].Select("", "", DataViewRowState.ModifiedCurrent));
   
                   daOrd2.Update(ds.Tables["Orders"].Select("", "", DataViewRowState.Added));
   
                   ds.EnforceConstraints = false;
                   daDetails.Update(ds.Tables["Details"].Select("","", DataViewRowState.Added));
                   ds.EnforceConstraints = true;
   
                   conn.Close();
   
   				
   
               }
               catch(SqlException ex)
               {
                   Console.Write (ex.Message.ToString ());
                   Console.Write(ex.InnerException.ToString ());
               }
               return ds;
           }
   
   
           protected static void OnOrd1RowUpdated(object sender, SqlRowUpdatedEventArgs args)
           {
               if(args.StatementType == StatementType.Insert || args.StatementType == StatementType.Delete )
                   args.Status = UpdateStatus.SkipCurrentRow;
   
           }
           protected static void OnDetailsRowUpdated(object sender, SqlRowUpdatedEventArgs args)
           {
               if(args.StatementType == StatementType.Insert )
               {
                   // Do not allow the AcceptChanges to occur on this row.
                   args.Status = UpdateStatus.SkipCurrentRow;
   
                   // Get the current, actual primary key value so that you can plug it back
                   // in after you get the correct original value that was generated for the child row.
                   int currentkey = (int)args.Row["OrderID"];
                   // This is where you get a correct original value key that is stored to the child row. 
                   // You pull the original, pseudo key value from the parent, plug it in as the child row's primary key
                   // field, and then accept changes on it. Specifically, this is why you turned off EnforceConstraints.
                   args.Row["OrderID"] = args.Row.GetParentRow("OrdDetail")["OrderID",DataRowVersion.Original];
                   args.Row.AcceptChanges();
                   // Store the actual primary key value in the foreign key column of the child row.
                   args.Row["OrderID"] = currentkey;
               }
   
               if(args.StatementType == StatementType.Delete )
                   args.Status = UpdateStatus.SkipCurrentRow;
   
           }
           private DataRow [] GetDeletedRows(DataTable dt)
           {
               DataRow [] dr ;
               if(dt == null)
                   return null;
               dr = dt.Select("","",DataViewRowState.Deleted );
               if(dr.Length ==0 || dr[0] != null)
                   return dr;
               // Workaround:
               // With a remoted DataSet, Select returns the array elements
               // that are filled with Nothing/null instead of DataRow objects.
   					
               for(int i=0; i < (int)dt.Rows.Count; i++)
               {
                   if(dt.Rows[i].RowState ==DataRowState.Deleted )
                       dr[i]=dt.Rows[i];
               }
               return dr;
   						
   
           } 
   					

5. Modify the SqlConnection strings to connect to the server that is running SQL Server.

Test the Web Service

1. Press F5 to compile and to run the Web service. Notice that a Web page is returned with the URL http://localhost/CSharpUpdateData/Service1.asmx. In this Web page, you can interact with the Web service from Microsoft Internet Explorer.
2. On the Service1.asmx Web page, click GetData. Notice that a Web page is returned that displays details about the GetData Web method.
3. Close the Web pages.

Create the Client Application

1. To create a new Visual C# Windows Application project, follow these steps:
   1. On the File menu in Visual Studio .NET, point to New, and then click Project.
   2. In the New Project dialog box, click Visual C# Projects under Project Types, and then click Windows Application under Templates. By default, Form1 is added to the project.
2. Drag a Button control and a DataGrid control from the toolbox to Form1. By default, the controls are named button1 and dataGrid1 respectively.
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/CSharpUpdateData/Service1.asmx), press ENTER, and then click Add Reference. Notice that the entry for this Web reference appears in Solution Explorer.
5. Add the following code to the Form1 class to declare a form-level member for the DataSet:

          private System.Data.DataSet ds;
   					

6. Double-click the form to switch to Code view, and notice that Visual Studio .NET creates the Form1_Load method. Add the following code to the Form1_Load method:

       localhost.Service1 sv = new localhost.Service1();
       ds = sv.GetData ();
          
       dataGrid1.DataSource = ds;
       dataGrid1.DataMember = "Orders";
   					

7. Switch to Form view.
8. Open the Code window for the Save button, and then add the following code to the btnSave_Click event handler:

       localhost.Service1 sv = new localhost.Service1();
   
       DataSet MyChangedRows;
   
       dataGrid1.DataMember = "";
       dataGrid1.DataSource = null;
       //Pull out only what you must send over the wire.
       MyChangedRows = ds.GetChanges();
       MyChangedRows = sv.UpdateData(MyChangedRows);
   
       //You must accept changes on the DataSet because of a known problem. See Q313540.
   
       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);
   
       //Accept changes 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, which states that you are breaking referential integrity on the server.
3. Add a child row to an existing parent row. Notice that the child row automatically receives the correct foreign key value.
4. Add the following values for a new parent row and a new child row, and then click Save. Note the following key values:
   • Orders.CustomerID=RATTC
   • Orders.EmployeeID=1
   • Orders.OrderDate=2/2/2002
   • Details.ProductID=1
   • Details.UnitPrice=18
   • Details.Quantity=1
   • Details.Discount=0

For additional information, click the article numbers below to view the articles in the Microsoft Knowledge Base: