How to update parent-child data with an identity column from a Windows Forms application by using a Web service in Visual C++ (815711)

INTRODUCTION

This step-by-step article describes how to retrieve a DataSet object from a Web service, how to send DiffGram format updates to the Web service, and then how to merge the updated DiffGram formats back into the client DataSet object. The following steps provide an overview of the sequential process:

The client application retrieves a DataSet object from the Web service that has two DataTable objects that have a parent-child relationship. The parent DataTable object has either an Identity or an Autonumber column that is the primary key.
You can add parent records and child records, you can delete parent records and child records, and you can modify parent records and child records in the client application. When you do this, new parent records receive a temporary primary key value that is generated locally.
The client application sends changes back to the Web service as an updated DiffGram format.
The Web service updates the database, retrieves the actual primary key values for new parent records, and then propagates the primary key values that have changed 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 object.

back to the top

Requirements

This article assumes that you are familiar with the following topics:

Programming with Microsoft Visual C++ .NET or Microsoft Visual C++ 2005
Using ADO.NET fundamentals and ADO.NET syntax
Using ASP.NET fundamentals and ASP.NET syntax

The code samples in this article use http://localhost as the Web server. Additionally, the code samples use the Northwind sample database as the database. The Northwind sample database is included with Microsoft SQL Server. The following list outlines the recommended hardware, software, network infrastructure, and service packs that you need:

Microsoft Windows 2000 Professional, Microsoft Windows 2000 Server, Microsoft Windows 2000 Advanced Server, or Microsoft Windows Server 2003
Microsoft Visual Studio .NET or Microsoft Visaul Studio 2005
Microsoft Internet Information Services
Microsoft SQL Server

back to the top

Create the Web service

To create a new Visual C++ .NET-based or Visual C++ 2005-based ASP.NET Web Service project, follow these steps:
1. Start Visual Studio .NET 2003 or Visual Studio 2005.
2. On the File menu, point to New, and then click Project.
3. Under Project Types, click Visual C++ Projects. Under Templates, click ASP.NET Web Service.
  
  Note In Visual Studio 2005, click Visual C++ under Project Types.
4. In the Name text box, type CppUpdateData.
5. In the Location text box, type C:\Test, and then click OK.
6. On the CppUpdateDataClass.h [Design] page, right-click, and then click View Code.
  
  Notice that the Code window for the Web service appears.
In the Code window, locate the following statement:
```
#pragma once
```
Add the following code after the previous statement:
```
#include <tchar.h>
```
In the Code window, locate the following code:
```
using namespace System::Web::Services;
```
Add the following using statement after the code that you just located:
```
using namespace System::Data;
using namespace System::Data::SqlClient;
```
In the CppUpdateDataClass.h file, locate the following code, and then delete the following code:
```
[System::Web::Services::WebMethod] 
String __gc* HelloWorld();
```

In the CppUpdateDataClass.cpp file locate the following code, and then delete the following code:

String __gc* CppUpdateDataClass::HelloWorld()
{

	// TODO: Add the implementation of your Web Service here

	return S"Hello World!";
    
}

After the InitializeComponent function, add the following code to the CppUpdateDataClass class :

public:
    [System::Web::Services::WebMethod]
	DataSet* GetData()
	{
		SqlConnection *conn = new SqlConnection ("server=10.150.144.125;database=northwind;user id=myuser;password=mypassword");
			
		//Pull back the recent orders for the parent rows.
		SqlDataAdapter *daOrder = new SqlDataAdapter(S"SELECT * FROM Orders WHERE OrderDate >= '05/01/1998'",conn);
		//Get only the appropriate child rows for the parent rows.
		SqlDataAdapter *daDetails = new SqlDataAdapter(S"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,S"Orders");
			daOrder->Fill(ds,S"Orders");

			daDetails->FillSchema(ds,SchemaType::Mapped,S"Details");
			daDetails->Fill(ds,S"Details");

			ds->Relations->Add("OrdDetail", ds->Tables->get_Item(S"Orders")->get_Columns()->get_Item(S"OrderID"), ds->Tables->get_Item(S"Details")->get_Columns()->get_Item(S"OrderID"));
		
			DataColumn *dc = ds->Tables->get_Item(S"Orders")->get_Columns()->get_Item(S"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;
	}

	[System::Web::Services::WebMethod]
	DataSet* UpdateData(DataSet *ds)
	{
		SqlConnection *conn = new SqlConnection ("server=10.150.144.125;database=northwind;user id=myuser;password=mypassword");
		//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.
			// Re-select the record after insert to get the new Identity value.
			// You must get the schema that you did in GetData() before you get commands. 
			// Otherwise, the Command builder tries to insert new rows that are 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;
			
			// Get commands for the Order Details table.
			// You must set the QuotePrefix and the 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 that is based on the original one to prevent the
			// CommandBuilder from modifying the SQL statements, 
			// specifically the custom InsertCommand.
			 
		
			SqlDataAdapter *daOrd2 = new SqlDataAdapter();
			daOrd2->DeleteCommand = daOrders->DeleteCommand;
			daOrd2->InsertCommand = daOrders->InsertCommand;
			daOrd2->UpdateCommand = daOrders->UpdateCommand;

			// Use a delegate to prevent AcceptChanges on Deletes and Inserts.
			// This is a limitation of the DataAdapter. See Q313540. 
			daOrd2->RowUpdated += new SqlRowUpdatedEventHandler(this, OnOrd1RowUpdated);
			daDetails->RowUpdated += new SqlRowUpdatedEventHandler(this, OnDetailsRowUpdated);

			daDetails->Update(GetDeletedRows(ds->Tables->get_Item(S"Details")));     
			daOrd2->Update(GetDeletedRows(ds->Tables->get_Item(S"Orders")));
			DataRow *dsArray[] = ds->Tables->get_Item(S"Orders")->Select(S"",S"",DataViewRowState::ModifiedCurrent); 
			daOrd2->Update(ds->Tables->get_Item(S"Orders")->Select(S"",S"",DataViewRowState::ModifiedCurrent));
			daDetails->Update(ds->Tables->get_Item(S"Details")->Select(S"",S"",DataViewRowState::ModifiedCurrent));
			
			daOrd2->Update(ds->Tables->get_Item(S"Orders")->Select(S"",S"", DataViewRowState::Added));
			
			ds->EnforceConstraints = false;
			daDetails->Update(ds->Tables->get_Item(S"Details")->Select(S"",S"", 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 OnDetailsRowUpdated(Object *sender, SqlRowUpdatedEventArgs *args)
	{
		if(args->StatementType == StatementType::Insert )
		{
			// Do not permit AcceptChanges to occur in 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.
			Object *currentkey =  (args->get_Row()->get_Item("OrderID"));


			// At this point, you get a correct original primary key value that is stored in the child row. 
			// You pull the original, pseudo primary key value from the parent, plug it in as the primary key field for the child row,
			// and then accept changes on it. Specifically, this is why you turned off EnforceConstraints.				
			args->get_Row()->set_Item("OrderID",(args->get_Row()->GetParentRow(S"OrdDetail"))->get_Item(S"OrderID",DataRowVersion::Original));
			
			args->Row->AcceptChanges();
			// Store the actual primary key value in the foreign primary key column of the child row.
			args->get_Row()->set_Item("OrderID",currentkey);
		}

		if(args->StatementType == StatementType::Delete )
			args->Status = UpdateStatus::SkipCurrentRow;

	}

protected:
	static void OnOrd1RowUpdated(Object *sender, SqlRowUpdatedEventArgs *args)
	{
		if(args->StatementType == StatementType::Insert || args->StatementType == StatementType::Delete )
			args->Status = UpdateStatus::SkipCurrentRow;

	}

private:
	DataRow* GetDeletedRows(DataTable *dt)[]
	{
		DataRow *dr[] ;
		if(dt == NULL)
			return NULL;
		dr = dt->Select(S"",S"",DataViewRowState::Deleted );
		if(dr->get_Length()== 0 || dr->get_Item(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->get_Item(i)->RowState == DataRowState::Deleted )
				dr->set_Item(i,dt->Rows->get_Item(i));
		}
		return dr;
	}

Note You must add the common language runtime support compiler option (/clr:oldSyntax) in Visual C++ 2005 to successfully compile this code sample. To do this, follow these steps:

Click Project, and then click ProjectName Properties.

Note ProjectName represents the name of the project.
Expand Configuration Properties, and then click General.
Click to select Common Language Runtime Support, Old Syntax (/clr:oldSyntax) in the Common Language Runtime support project setting on the right pane, click Apply, and then click OK.

For more information about the common language runtime support compiler options, visit the following Microsoft Web site:

/clr (Common Language Runtime Compilation)
http://msdn2.microsoft.com/en-us/library/k8d11d4s.aspx

These steps apply to the whole article.

Modify the SqlConnection strings to connect to the server that is hosting a Microsoft SQL Server Database.
Press CTRL+SHIFT+S to save the project.

back to the top

Test the Web service

Press CTRL+SHIFT+B to build the solution.
Press CTRL+F5 to run the Web service.

Notice that a Web page is returned with the following URL:

http://localhost/CppUpdateData/CppUpdateData.asmx

On this Web page, you can interact with the Web service from Microsoft Internet Explorer.
On the CppUpdateData.asmx Web page, click GetData.

Notice that a Web page is returned that displays details about the GetData Web service method.
Close the Web pages.

back to the top

Create the client application

To create a Visual C++ .NET or Visual C++ 2005 project by using the Windows Forms Application (.NET) project creation template, follow these steps:

Note In Visual C++ 2005, you must use the Windows Forms Application project creation template.
1. Start Visual Studio .NET 2003 or Visual Studio 2005.
2. On the File menu, point to New, and then click Project.
3. Under Project Types, click Visual C++ Projects. Under Templates, click Windows Forms Application (.NET).
  
  Note In Visual Studio 2005, click Visual C++ under Project Types, and then click Windows Forms Application under Templates.
4. In the Name text box, type Client.
5. In the Location text box, type C:\Test, and then click OK.
Add a Button control and a DataGrid control to the form that is named Form1.

By default, the controls are named button1 and dataGrid1, respectively.
Change the Name property of the button1 control to btnSave, and then change the Text property of the button1 control to Save.
On the Project menu, click Add Web Reference.
In the Add Web Reference dialog box, type the URL for your Web service in the URL text box, and then click Go.

Note For the example in this article, type the following URL:

http://localhost/CppUpdateData/CppUpdateData.asmx
Click Add Reference.

Notice that the entry for this Web reference appears in Solution Explorer.
In Solution Explorer, right-click the Form1.h file in the Header Files folder, and then click View Code.
Add the following code to the Form1 class to declare a form-level member for the DataSet class:
```
private:
	System::Data::DataSet *ds;
```
In Solution Explorer, right-click the Form1.h file in the Header Files folder, and then click View Designer.
Double-click the Form1 form to switch to Code view.

Notice that Visual Studio .NET or Visual Studio 2005 creates the Form1_Load event handler.

Add the following code to the Form1_Load event handler:
```
localhost::CppUpdateDataClass *sv = new localhost::CppUpdateDataClass();
ds = sv->GetData();

dataGrid1->DataSource = ds;
dataGrid1->DataMember = "Orders";
```
In Solution Explorer, right-click the Form1.h file in the Header Files folder, and then click View Designer.

Double-click Save, and then add the following code to the btnSave_Click event handler:

localhost::CppUpdateDataClass *sv = new localhost::CppUpdateDataClass();
System::Data::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->Item["Orders"], false, MissingSchemaAction::Ignore);
ds->Merge(MyChangedRows->Tables->Item["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";

Press CTRL+SHIFT+S to save the project.

back to the top

Test the client application

Press CTRL+SHIFT+B to build the solution.
Press CTRL+F5 to run the client application.
In the DataGrid control, modify some data, and then click Save.

Note Do not change the primary key field. If you change the primary key field, you may receive an error message.
Add the following values for a new parent row and a new child row, and then 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
  
  The DataGrid control is reloaded with the data that you have added.
Quit the application, and then run the application again.

Notice that the primary key values from step 4 are added.

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 C++ (815711)

SUMMARY

IN THIS TASK

INTRODUCTION

Requirements

Create the Web service

Test the Web service

Create the client application

Test the client application

REFERENCES