SQL Server 2005 Native XML Web Services : Example Native XML Web Services Project (part 3) - Creating the Client Application

3/23/2013 4:11:38 AM

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.

Figure 3. The Search page of the Adventure Works Sales form

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.

Figure 4. Adding the Web reference to the AdventureSales Web service

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
        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";
        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 '" +
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;
        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];
    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))
    _CustomerId = (int)storesListBox.SelectedValue;
    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
        this.cityStateZipTextBox.Text = xmlData.SelectSingleNode
("//City").InnerText + ", " +
("//State").InnerText + ", " +
        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];
        this.contactsDataGridView.DataSource = null;

private void LoadStoreRecentOrders() {
    DataSet ds = Proxy.GetStoreRecentOrders(this._CustomerId);
    if (!(ds == null))
        this.recentOrdersDataGridView.DataSource = ds.Tables[0];
        this.recentOrdersDataGridView.DataSource = null;

private void LoadStoreSalesYTD() {
    System.Data.SqlTypes.SqlDecimal yTDSales =     Proxy.GetStoreSalesYTD
    if (!yTDSales.IsNull)
        this.salesYTDTextBox.Text = String.Format("{0:C}", (Decimal)yTDSales);
        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];
    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.

Figure 5. The final result—viewing information for the selected store on the Customer Info tab

  •  SQL Server 2005 Native XML Web Services : Exposing SQL Programmability as Web Services (part 2) - Calling Native XML Web Service Endpoints from Client Applications
  •  SQL Server 2005 Native XML Web Services : Exposing SQL Programmability as Web Services (part 1)
  •  Western Digital Black 4TB Hard Drive - 4TB Storage Goes Mainstream
  •  Intel Solid State Drive 335 Series - Better Under The Hood
  •  HDD, SSD and Hybrid Hard Drive Competition
  •  SQL Server 2008 : Index analysis (part 3) - Identifying index fragmentation
  •  SQL Server 2008 : Index analysis (part 2) - Identifying indexes to add
  •  SQL Server 2008 : Index analysis (part 1) - Identifying indexes to drop/disable
  •  ADO.NET Programming : Microsoft SQL Server CE (part 5) - Querying Schema Information
  •  ADO.NET Programming : Microsoft SQL Server CE (part 4) - Updating a SQL Server CE Database, The SqlCeDataAdapter Class
    Top 10
    3 Tips for Maintaining Your Cell Phone Battery (part 2) - Discharge Smart, Use Smart
    3 Tips for Maintaining Your Cell Phone Battery (part 1) - Charge Smart
    OPEL MERIVA : Making a grand entrance
    FORD MONDEO 2.0 ECOBOOST : Modern Mondeo
    BMW 650i COUPE : Sexy retooling of BMW's 6-series
    BMW 120d; M135i - Finely tuned
    PHP Tutorials : Storing Images in MySQL with PHP (part 2) - Creating the HTML, Inserting the Image into MySQL
    PHP Tutorials : Storing Images in MySQL with PHP (part 1) - Why store binary files in MySQL using PHP?
    Java Tutorials : Nested For Loop (part 2) - Program to create a Two-Dimensional Array
    Java Tutorials : Nested For Loop (part 1)
    - First look: Apple Watch

    - 3 Tips for Maintaining Your Cell Phone Battery (part 1)

    - 3 Tips for Maintaining Your Cell Phone Battery (part 2)
    - How to create your first Swimlane Diagram or Cross-Functional Flowchart Diagram by using Microsoft Visio 2010 (Part 1)

    - How to create your first Swimlane Diagram or Cross-Functional Flowchart Diagram by using Microsoft Visio 2010 (Part 2)

    - How to create your first Swimlane Diagram or Cross-Functional Flowchart Diagram by using Microsoft Visio 2010 (Part 3)
    Popular Tags
    Microsoft Access Microsoft Excel Microsoft OneNote Microsoft PowerPoint Microsoft Project Microsoft Visio Microsoft Word Active Directory Biztalk Exchange Server Microsoft LynC Server Microsoft Dynamic Sharepoint Sql Server Windows Server 2008 Windows Server 2012 Windows 7 Windows 8 Adobe Indesign Adobe Flash Professional Dreamweaver Adobe Illustrator Adobe After Effects Adobe Photoshop Adobe Fireworks Adobe Flash Catalyst Corel Painter X CorelDRAW X5 CorelDraw 10 QuarkXPress 8 windows Phone 7 windows Phone 8 BlackBerry Android Ipad Iphone iOS