4. Creating the Client Application
For
the client portion of our example, we created a simple Windows Forms
application, called AdventureSales, that calls the Native XML Web
Service endpoint we created and provides the user interface for
searching and viewing data related to the Adventure Works Cycles
customer stores. We will explain some of the important steps for
creating this application in Visual Studio and examine some of the
interesting parts of the code, but we will not provide step-by-step
instructions to create this application. You’ll find a complete
solution—the AdventureSales Visual Studio solution—. You can open this solution to look at the code and run
the application, as long as you have completed the preceding steps to
create the endpoint.
In Visual Studio .NET, we start by creating a
new C# Windows Application project and creating a simple form with two
tabs—one for searching the AdventureWorks database for a store and one for viewing the detailed information for a particular store. Figure 3 shows the Search page of the Adventure Works Sales form of our application.
To enable our application to invoke the Native
XML Web Services endpoint we have exposed, we add a Web reference in the
project to the AdventureSales Web service. To add a Web reference, you
right-click the project in Visual Studio and choose Add Web Reference.
This starts the Add Web Reference Wizard. To point to our endpoint,
enter http://localhost/sql/AdventureSales?wsdl
for the URL and click Go. The wizard downloads the WSDL for the
endpoint and displays the available Web methods. To give our proxy
object a meaningful name, type AdventureSalesWebService
in the Web Reference Name text box, and click Add Reference. The wizard
generates a proxy object that handles all the complexity of invoking
Web services over SOAP. Figure 4 shows the page from the Add Web Reference Wizard where we specify the URL and set the class name for the Web reference proxy.
As a programming convenience, we set up a property called Proxy (see Listing 7) that returns an instance of the proxy object with the security credentials already attached.
Listing 7. Proxy property from AdventureSalesForm.cs
private AdventureSalesWebService.AdventureWorks Proxy
{
get
{
if (_proxy == null)
{
_proxy = new AdventureSalesWebService.AdventureWorks();
_proxy.Credentials = System.Net.CredentialCache.DefaultCredentials;
}
return _proxy;
}
}
private AdventureSalesWebService.AdventureWorks _proxy;
|
When the user clicks the Search button on the Search tab, the code shown in Listing 8 is executed. The GetSearchSQL method, which is called by searchButton_Click, dynamically builds an SQL string based on the values the user enters in the search criteria text boxes. The AddToWhereClause method is called by GetSearchSQL to add the optional sections to the WHERE clause.
Listing 8. searchButton_Click and supporting methods from AdventureSalesForm.cs
private void searchButton_Click(object sender, EventArgs e)
{
string searchSQL = GetSearchSQL();
_CustomerId = 0;
AdventureSalesWebService.SqlParameter[] p = null;
DataSet ds = GetDataSet(Proxy.sqlbatch(searchSQL, ref p));
if (!(ds == null))
{
storesListBox.DataSource = ds.Tables[0];
storesListBox.DisplayMember = "Name";
storesListBox.ValueMember = "CustomerID";
}
else
{
storesListBox.DataSource = null;
}
}
private string GetSearchSQL()
{
string searchSQL =
"SELECT Store.CustomerID, Store.Name, Address.City, " +
" Address.PostalCode as Zip, " +
" StateProvince.StateProvinceCode as State " +
" FROM Sales.Store " +
" INNER JOIN Sales.CustomerAddress " +
" ON Store.CustomerID = CustomerAddress.CustomerID " +
" INNER JOIN Person.Address " +
" ON CustomerAddress.AddressID = Address.AddressID " +
" INNER JOIN Person.StateProvince " +
" ON Address.StateProvinceID = StateProvince.StateProvinceID " +
"AND Address.StateProvinceID = StateProvince.StateProvinceID ";
string whereClause = "";
if (!String.IsNullOrEmpty(this.searchStoreNameTextBox.Text))
whereClause = AddToWhereClause(whereClause, "Store.Name Like '"
+ searchStoreNameTextBox.Text + "%'");
if (!String.IsNullOrEmpty(this.searchCityTextBox.Text))
whereClause = AddToWhereClause(whereClause, "Address.City Like '" + this.search
CityTextBox.Text + "%'");
if (!String.IsNullOrEmpty(this.searchStateTextBox.Text))
whereClause = AddToWhereClause(whereClause, "StateProvince.StateProvinceCode = '
" + this.searchStateTextBox.Text) + "'";
if (!String.IsNullOrEmpty(this.searchZipTextBox.Text))
whereClause = AddToWhereClause(whereClause, "Address.PostalCode Like '" + this.s
earchZipTextBox.Text + "%'");
return searchSQL + whereClause;
}
private string AddToWhereClause(string whereClause, string newSection)
{
if (String.IsNullOrEmpty(whereClause))
return " WHERE " + newSection;
else
return whereClause + " AND " + newSection;
}
|
As
mentioned earlier, the client-side programming challenge when you
invoke Native XML Web Services is that the results are returned as an
array of type object. For this sample, we have created a helper function, shown in Listing 9, to loop through the array and extract the expected DataSet.
Listing 9. GetDataSet from AdventureSalesForm.cs
private DataSet GetDataSet(Object[] results)
{
DataSet data = null;
for (int j = 0; j < results.Length; j++)
{
object result;
result = results[j];
if (result.ToString() == "System.Data.DataSet")
{
data = (System.Data.DataSet)results[j];
break;
}
}
return data;
}
|
After the user has searched for and identified a
store, she can double-click the row in the list box to activate the
Customer Info tab. Listing 10 shows the storesListBox_DoubleClick method that handles this event. This method calls on the LoadStoreInfo, LoadStoreContacts, LoadStoreRecentOrders, and LoadStoreSalesYTD
methods to retrieve the various pieces of information from the database
using the proxy and to bind the data to the appropriate controls in the
user interface.
Listing 10. storesListBox_DoubleClick and supporting methods from AdventureSalesForm.cs
private void storesListBox_DoubleClick(object sender, EventArgs e)
{
if ((storesListBox.SelectedIndex == -1) || (storesListBox.Items.Count==0))
return;
_CustomerId = (int)storesListBox.SelectedValue;
LoadStoreInfo();
LoadStoreContacts();
LoadStoreRecentOrders();
LoadStoreSalesYTD();
this.CustomerTabControl.SelectedIndex = 1;
}
private void LoadStoreInfo() {
System.Xml.XmlElement xmlData = GetXMLElement(Proxy.GetStoreInfo(this._CustomerId));
if (!(xmlData == null))
{
this.nameTextBox.Text = xmlData.SelectSingleNode("//Name").InnerText;
this.address1TextBox.Text = xmlData.SelectSingleNode
("//AddressLine1").InnerText;
this.cityStateZipTextBox.Text = xmlData.SelectSingleNode
("//City").InnerText + ", " +
xmlData.SelectSingleNode
("//State").InnerText + ", " +
xmlData.SelectSingleNode
("//PostalCode").InnerText;
}
else
{
this.nameTextBox.Text = "";
this.address1TextBox.Text = "";
this.cityStateZipTextBox.Text = "";
}
}
private void LoadStoreContacts() {
DataSet ds = GetDataSet(Proxy.GetStoreContacts(this._CustomerId));
if (!(ds == null))
this.contactsDataGridView.DataSource = ds.Tables[0];
else
this.contactsDataGridView.DataSource = null;
}
private void LoadStoreRecentOrders() {
DataSet ds = Proxy.GetStoreRecentOrders(this._CustomerId);
if (!(ds == null))
this.recentOrdersDataGridView.DataSource = ds.Tables[0];
else
this.recentOrdersDataGridView.DataSource = null;
}
private void LoadStoreSalesYTD() {
System.Data.SqlTypes.SqlDecimal yTDSales = Proxy.GetStoreSalesYTD
(this._CustomerId);
if (!yTDSales.IsNull)
this.salesYTDTextBox.Text = String.Format("{0:C}", (Decimal)yTDSales);
else
this.salesYTDTextBox.Text = String.Format("{0:C}", 0.0D) ;
}
|
For LoadStoreContacts and LoadStoreRecentOrders, we simply bind the returned DataSet to the appropriate DataGridView control. Because the GetStoreRecentOrders WEB METHOD clause specifies ROWSET_ONLY for the FORMAT option, we don’t have to use the GetDataSet helper function (shown earlier in Listing 10-10). The GetStoreRecentOrders Web method returns a DataSet directly. Because LoadStoreSalesYTD returns a simple scalar value, we format it as a string using the Currency standard format.
The LoadStoreInfo method in Listing 10-11 shows the result coming back as an XmlElement. In this case, we take advantage of the SelectSingleNode method of the XMLElement, using XPATH expressions to pull out the desired values and populate the controls. A helper method, GetXMLElement, shown in Listing 11, is used to pull back the XMLElement returned by the Web method.
Listing 11. GetXMLElement helper method from AdventureSalesForm.cs
private System.Xml.XmlElement GetXMLElement(Object[] results)
{
System.Xml.XmlElement data=null;
for (int j = 0; j < results.Length; j++)
{
object e1;
e1 = results[j];
if (e1.ToString() == "System.Xml.XmlElement")
{
data = (System.Xml.XmlElement)results[j];
break;
}
}
return data;
}
|
Figure 5
shows the final result, with the Customer Info tab populated with data.
We’ve done it! The sales representatives can now get the data they need
to be more effective on the road, the sales manager is happy, and of
course the IT manager and the junior developer get well-deserved pay
raises.
