ADO NET Programming-Designing the Web Service

Designing the Web Service

Before we rush in and start programming, we need to first plan and design the Web Service. The first step is to name our Web Service. For this case study, we will call our Web Service OrderProcessingWS.

OrderProcessingWS

Now that we know what we are going to call our Web Service, we need to decide what data retrieval methods we are going to expose to the clients. This is important because we will also need to use appropriate names for our methods. One thing to remember about the method names in Web Service is that they must be unique.

Note

The method name of a Web Service can be different from the name of the method that implements it in the class. This is useful when you have polymorphic methods.

Data Retrieval Methods

Class Method Name	Web Service Method Name	Description
GetOrders	GetOrders_By_Customer	Parameter: CustomerID (String) Return: Dataset Returns a summary of orders for the given customer
GetOrders	GetOrders_By_Date	Parameter: FromDate (Date), ToDate (Date) Return: Dataset Returns a summary of the different orders for a given date range
GetOrders	GetOrders_By_Customer_Date	Parameter: CustomerID(String), FromDate (Date), ToDate (Date) Return: Dataset Returns a summary of orders for a given customer within a given date range
GetOrder-Details	GetOrderDetails	Parameter: OrderID (Integer) Return: Dataset Returns the line entries for a given order
Get Full-Orders	GetFullOrders	Parameter: CustomerID (String) Return: Dataset Returns detailed information about an order
Get Full-Orders	GetFullOrders_By_Customer	Parameter: CustomerID (String) Return: Dataset Returns detailed information about orders for the given customer

Implementing OrderProcessingWS

Now that we have designed the skeleton for OrderProcessingWS, we can start the next phase. We will use Visual Studio .NET for the implementation and as the development environment. You will also need to have Internet Information Server (IIS) installed and accessible to your development machine. For development purposes, it is always better to have IIS installed locally. After developing and debugging, you can always move the Web Service to a production machine.

Setting Up IIS

Before you begin, first set up a virtual directory that you will use on IIS. This is not necessary, but if you let Visual Studio .NET set up the directory for you, your files will be stored in a directory under your default web site directory. Usually, this is in c:\inetpub\wwwroot.

To set up your virtual directory, use the following steps:

Open the IIS management console from administrative tools.
Expand web sites for your local machine by right-clicking Default Web Site.
Choose New on the context menu and select Virtual Directory..., as shown in Figure 6-1.

Figure 6-1: Creating a virtual directory in IIS between Categories and Products

Follow the instructions for the wizard. For the purpose of this case study, we will use a virtual directory called OrderProcessingWS. You can point the virtual directory to the appropriate directory where you plan to store your source code. Make sure you have the appropriate access permission to the directory.

Note

ASP .NET code will not work unless the ASP .NET run time is installed. This is usually done when you install the .NET Framework. This may not be the case if IIS was not installed at the time the .NET Framework was installed. To check, ensure that you have the “Aspnet_Client” folder installed under Default Web Sites. If it is not installed, reinstall the .NET Framework.

Creating OrderProcessingWS Project

The next step is to actually create the project that you will be using for the development of OrderProcessingWS. Create a new Visual Basic ASP .NET Web Service project. Call it OrderProcessingWS. This should point to the virtual directory you created before (e.g., “http://localhost/OrderProcessingWS,” if you are using IIS locally). See Figure 6-2.

Figure 6-2: Creating the OrderProcessingWS project

Warning

Make sure you have the right level of permission on the virtual directory and you have write enabled. Otherwise, you will get an access denied error when you try to create the project.

Visual Studio creates the necessary files and directories that you will need to write the Web Service. ASP .NET application files have file extension .aspx, whereas Web Services have file extension .asmx. Visual Studio creates a default Web Service file called Service1.asmx. This file should be renamed to OrderProcessingWS.asmx. You should then set the page as the start page by right-clicking on it and choosing Set As Start Page on the context menu.

Open the OrderProcessingWS.asmx file, if it is not already open. This can be done by double-clicking the file in the Solution Explorer. This should open the file in the design view. Double-click in the design view to get to the code.

Tip

You can right-click on the file in Solution Explorer and choose View Code to access the code directly and faster.

As you can see, Visual Studio has generated some code for you. Change the class name to OrderProcessingWS. You can uncomment the suggested “Hello World” example if you wish and try it out. Delete that section of the code once you are done.

Web Service Namespace

For each Web Service you create, you will need to give it a namespace. This namespace is different from the .NET namespaces and is used to uniquely identify the Web Service on the Internet. Usually, you would use a namespace that you have control over, such as the web site of your company (for example, http://mycompany. com/mywebservice/OrderProcessingWS/). We will use http://ProgrammingADODotNET/OrderProcessingWS/ as our namespace, so go ahead and change the default http://tempuri.org/ to http://ProgrammingADODotNET/OrderProcessingWS/ . You should have a code section like that shown in the following figure.

Figure 6-3: The skeleton of the class and namespace

Initialization Code

Before we add our methods, we first need to add our initialization codes. We will be using MS SQL Server, so we need to define a connection string to the server. We do not want to hard-code this data, so we will put it in the application configuration file. For Web Services, the configuration file is usually called web.config. Open the web.config file and create an <appSettings> section.

Note

Please be aware that XML tags are case-sensitive. <appSettings> must be entered in exactly the same case shown here.

Then create a key called SQLConnectionString whose value is the connection string to your database. In the example, we are using the local database, so the data source is 127.0.0.1. The user name, password, and default catalog is also provided. See the code listing below as an example. As you probably have noticed, the web.config file is actually an XML file. Here is the start section of this file:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <appSettings>
   <add key="SQLConnectionString"
    value="data source=127.0.0.1;user id=sa;password=sa;
      Initial Catalog=northwind" />
  </appSettings>
...

You will now need to modify your code so that you can access the configuration file. For this, you need to use the methods of the ConfigurationSettings class. The ConfigurationSettings class is found in the System.Configuration namespace. You can reference the namespace, or you can use the imports construct so that you do not have to type the full name of the class (i.e., System.Configuration.ConfigurationSettings). We are also going to use classes in the System.Data.SqlClient namespace, so import that as well.

Expand the Web Services Designer Generated Code region in the code section of OrderProcessingWS. In the class, you need to declare a private string variable to hold the connection string. You then initialize the variable in the class constructor. The constructor is the New() method inside the class. To get the value of the SQLConnectionString key that you defined earlier in the web.config file, use the ConfigurationSettings.AppSettings method.

...
SQLConnectionString =_
configurationSettings.AppSettings("SQLConnectionString")
...

See the following code sample for a detailed listing:

Imports System.Web.Services
Imports System.Configuration
Imports System.Data.SqlClient

<WebService( _
  Namespace:= _
    "http://ProgrammingADODotNET/OrderProcessingWS/", _
  Description:= _
    "Provides access to the order details of customers")>
Public Class OrderProcessingWS
    Inherits System.Web.Services.WebService

#Region " Web Services Designer Generated Code "
    Private SQLConnectionString As String

    Public Sub New()
        MyBase.New()

        'This call is required by the Web Services
        'Designer.
        InitializeComponent()

        'Initialize connection string based on AppSettings
        'defined in web.config
        SQLConnectionString = _
          ConfigurationSettings.AppSettings
          ("SQLConnectionString")

    End Sub
...

To test that the application setting is working, create a method that will return the connection string. Here is an example of one:

  <WebMethod( _
       MessageName:="ConnectionString" _
    )> _
    Public Function ConnectionString() As String
        ConnectionString = SQLConnectionString
    End Function

Once your application is complete, you can delete this function, or you can set it to private and remove the <WebMethod> attribute.

For easier manageability and access, create regions for the different methods that you are going to use, as shown below:

#Region " GetOrders Code Section "

#End Region

#Region " GetOrderDetails Code Section "

#End Region

#Region " GetFullOrders Code Section "

#End Region

We are now ready to start coding our first method. Let’s start with the GetOrders methods.

GetOrders Methods

Put the GetOrders methods in the GetOrders Code Section region. The source code for GetOrders_By_Customer is available on the companion CD.

Let’s go through the different sections of the code for GetOrders. The first step is to declare the function. The function is declared with the <WebMethod> attribute in order to tell .NET to expose it as a web method of the Web Service. Because we are going to use polymorphism to declare additional GetOrders() methods, we must explicitly declare the name that the method will be exposed as. For this, we use the “MessageName:=” property of the <WebMethod>:

<WebMethod( _
  MessageName:="GetOrders_By_Customer" _
  )> _
  Public Function GetOrders( _
       ByVal CustomerID As String _
  ) As DataSet
      '***************************************'
      '* Return a summary orders             *'
      '* (order header) for the              *'
      '* given customer                      *'
      '***************************************'

Notice that after the creation of the function, I have also written some header comments that describe what the function will do. This is a good habit to get into and, though tedious at first, will help you and your colleagues later when it comes to maintenance.

We then declare the DataSet, the SQL connection object, and the query string that we will use:

  'Declare dataset to store results
  Dim OrderDetailsDS As New DataSet()
  OrderDetailsDS.DataSetName = "OrderDetailsDS"

  'Declare SqlConnection object for connection to database
  'Use the Global Private string initialized in
  'MyBase.New()
  Dim objConn As New SqlConnection(SQLConnectionString)

  'Declare the select query string to be
  'used to select the data.
  'including parameters in T-SQL format and for
  'optimization
  'tell the database engine not to issue locks
  Dim SelectQuerySTR As String = _
      "SELECT * " & _
      "FROM Orders WITH (NOLOCK)" & _
      "WHERE CustomerID = @CustomerID"

Since we are using DataSet to hold our data from the SQL Server, it is easier to use a SqlDataAdapter to form the bridge between the DataSet and SQL Server. Define the adapter and create an SqlCommand object in the adapter using the query string and the connection that we declared earlier.

  'Declare the DataAdapter that will be
  'used to populate the DataSet.
  Dim OrderDataAdapter As New SqlDataAdapter()

  'Define the selectCommand for the DataAdapter
  OrderDataAdapter.SelectCommand = _
    New SqlCommand(SelectQuerySTR, objConn)

In the query string, notice that we use a T-SQL parameter, @CustomerID. The SQLCommand object needs to know what value to substitute for this parameter before sending the query to the database engine. This is done by adding a parameter to the SqlCommand.

  'Define the parameter used in the SqlCommand
  'and also set its value.
  OrderDataAdapter.SelectCommand.Parameters.Add _
   ("@CustomerID", CustomerID)

We are now ready to connect to the database and populate our DataSet using the fill method of the SqlDataAdapter. Since there is a possibility of having a connection error, we will also catch the error gracefully, if any. Once done, we clean up and close the connection. All that remains afterward is to return the DataSet.

  'Catch possible errors
  Try
    'Open the Connection to data base
    objConn.Open()

    'Populate DataSet using the DataAdapter fill method.
    OrderDataAdapter.Fill(OrderDetailsDS, "Orders")

  Catch Err As Exception
    Throw New ApplicationException( _
      "Exception encountered when executing command.",
        Err)
  Finally

    'Close the Connection to data base
    objConn.Close()

  End Try

  'Return the DataSet
  GetOrders = OrderDetailsDS

Note

You do not have to worry about converting the DataSet to XML for communication over the Internet. This is done transparently by the .NET Framework.

The rest of the GetOrders methods should follow a similar pattern to the one we have just seen. The main difference is the query string and the parameters. For GetOrders_By_Date, we have:

  'Declare the select query string to
  'be used to select the data.
  'including parameters in T-SQL format and for
  'optimization
  'tell the database engine not to issue locks
  Dim SelectQuerySTR As String = _
      "SELECT * " & _
      "FROM Orders WITH (NOLOCK)" & _
      "WHERE OrderDate >= @FromDate and OrderDate <=
        @ToDate"

  '(Code omitted for clarity)

  'Define the parameter used in the SqlCommand
  'and also set its value.
  OrderDataAdapter.SelectCommand.Parameters.Add _
    ("@FromDate", FromDate)
  OrderDataAdapter.SelectCommand.Parameters.Add _
    ("@ToDate", ToDate)

For GetOrders_By_Customer_Date, we have:

  'Declare the select query string
  'to be used to select the data.
  'including parameters in T-SQL format and for
  'optimization
  'tell the database engine not to issue locks
  Dim SelectQuerySTR As String = _
      "SELECT * " & _
      "FROM Orders WITH (NOLOCK)" & _
      "WHERE CustomerID = @CustomerID AND " & _
      "OrderDate >= @FromDate AND " & _
      "OrderDate <= @ToDate"

  '(Code omitted for clarity)

  'Define the parameters used in the SqlCommand
  'and also set its value.
  OrderDataAdapter.SelectCommand.Parameters.Add _
    ("@CustomerID", CustomerID)
  OrderDataAdapter.SelectCommand.Parameters.Add _
    ("@FromDate", FromDate)
  OrderDataAdapter.SelectCommand.Parameters.Add _
    ("@ToDate", ToDate)

The full source code for the other GetOrders methods is available on the companion CD.

To test the code, you can choose Start from the Debug menu or press F5. A web page should start listing all the available web methods for the Web Service. See Figure 6-4.

Figure 6-4: Web page generated by IIS for the OrderProcessingWS Web Service

Choose any of the hyperlinks to see more information about the web methods. This will take you to another page, which will allow you to test the method. See Figure 6-5.

Figure 6-5: Test page for GetOrders_By_Customer

Note

You can only test methods that support HTTP get. That means, if you pass objects like DataSet as parameters you will not be able to test the method using IIS auto-generated test page. In such a case, you will have to write your own test methods.

Enter some test data in the parameter and click the Invoke button to run the test. A new page is opened that shows the result in XML format of running the method with the given data. The resulting XML is shown here:

  <?xml version="1.0" encoding="utf-8" ?>
- <DataSet xmlns="http://ProgrammingADODotNET/
    OrderProcessingWS/">
- <xs:schema id="OrdersDS" xmlns=""
    xmlns:xs="http://www.w3.org/2001/XMLSchema"
    xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
- <xs:element name="OrdersDS" msdata:IsDataSet="true"
    msdata:Locale="en-GB">
- <xs:complexType>
- <xs:choice maxOccurs="unbounded">
- <xs:element name="Orders">
- <xs:complexType>
- <xs:sequence>
  <xs:element name="OrderID" type="xs:int"
    minOccurs="0" />
  <xs:element name="CustomerID" type="xs:string"
    minOccurs="0" />
  <xs:element name="EmployeeID" type="xs:int"
    minOccurs="0" />
  <xs:element name="OrderDate" type="xs:dateTime"
    minOccurs="0" />
  <xs:element name="RequiredDate" type="xs:dateTime"
    minOccurs="0" />
  <xs:element name="ShippedDate" type="xs:dateTime"
    minOccurs="0" />
  <xs:element name="ShipVia" type="xs:int" 
    minOccurs="0" />
  <xs:element name="Freight" type="xs:decimal"
    minOccurs="0" />
  <xs:element name="ShipName" type="xs:string"
    minOccurs="0" />
  <xs:element name="ShipAddress" type="xs:string"
    minOccurs="0" />
  <xs:element name="ShipCity" type="xs:string"
    minOccurs="0" />
  <xs:element name="ShipRegion" type="xs:string"
    minOccurs="0" />
  <xs:element name="ShipPostalCode" type="xs:string"
    minOccurs="0" />
  <xs:element name="ShipCountry" type="xs:string"
    minOccurs="0" />
  </xs:sequence>
  </xs:complexType>
  </xs:element>
  </xs:choice>
  </xs:complexType>
  </xs:element>
  </xs:schema>
- <diffgr:diffgram xmlns:msdata=
    "urn:schemas-microsoft-com:xml-msdata"
    xmlns:diffgr="urn:schemas-microsoft-com:
    xml-diffgram-v1">
- <OrdersDS xmlns="">
- <Orders diffgr:id="Orders1" msdata:rowOrder="0">
  <OrderID>10248</OrderID>
  <CustomerID>VINET</CustomerID>
  <EmployeeID>5</EmployeeID>
  <OrderDate>1996-07-04T00:00:00.0000000+04:00</OrderDate>
  <RequiredDate>1996-08-01T00:00:00.0000000+04:00</
   RequiredDate>
  <ShippedDate>1996-07-16T00:00:00.0000000+04:00</
   ShippedDate>
  <ShipVia>3</ShipVia>
  <Freight>32.38</Freight>
  <ShipName>Vins et alcools Chevalier</ShipName>
  <ShipAddress>59 rue de l'Abbaye</ShipAddress>
  <ShipCity>Reims</ShipCity>
  <ShipPostalCode>51100</ShipPostalCode>
  <ShipCountry>France</ShipCountry>
  </Orders>
- <Orders diffgr:id="Orders2" msdata:rowOrder="1">
  <OrderID>10274</OrderID>
  <CustomerID>VINET</CustomerID>
  <EmployeeID>6</EmployeeID>
  <OrderDate>1996-08-06T00:00:00.0000000+04:00</OrderDate>
  <RequiredDate>1996-09-03T00:00:00.0000000+04:00</
   RequiredDate>
  <ShippedDate>1996-08-16T00:00:00.0000000+04:00</
   ShippedDate>
  <ShipVia>1</ShipVia>
  <Freight>6.01</Freight>
  <ShipName>Vins et alcools Chevalier</ShipName>
  <ShipAddress>59 rue de l'Abbaye</ShipAddress>
  <ShipCity>Reims</ShipCity>
  <ShipPostalCode>51100</ShipPostalCode>
  <ShipCountry>France</ShipCountry>
  </Orders>
- <Orders diffgr:id="Orders3" msdata:rowOrder="2">
  <OrderID>10295</OrderID>
  <CustomerID>VINET</CustomerID>
  <EmployeeID>2</EmployeeID>
  <OrderDate>1996-09-02T00:00:00.0000000+04:00</OrderDate>
  <RequiredDate>1996-09-30T00:00:00.0000000+04:00</
   RequiredDate>
  <ShippedDate>1996-09-10T00:00:00.0000000+04:00</
   ShippedDate>
  <ShipVia>2</ShipVia>
  <Freight>1.15</Freight>
  <ShipName>Vins et alcools Chevalier</ShipName>
  <ShipAddress>59 rue de l'Abbaye</ShipAddress>
  <ShipCity>Reims</ShipCity>
  <ShipPostalCode>51100</ShipPostalCode>
  <ShipCountry>France</ShipCountry>
  </Orders>
- <Orders diffgr:id="Orders4" msdata:rowOrder="3">
  <OrderID>10737</OrderID>
  <CustomerID>VINET</CustomerID>
  <EmployeeID>2</EmployeeID>
  <OrderDate>1997-11-11T00:00:00.0000000+04:00</OrderDate>
  <RequiredDate>1997-12-09T00:00:00.0000000+04:00</
   RequiredDate>
  <ShippedDate>1997-11-18T00:00:00.0000000+04:00</
   ShippedDate>
  <ShipVia>2</ShipVia>
  <Freight>7.79</Freight>
  <ShipName>Vins et alcools Chevalier</ShipName>
  <ShipAddress>59 rue de l'Abbaye</ShipAddress>
  <ShipCity>Reims</ShipCity>
  <ShipPostalCode>51100</ShipPostalCode>
  <ShipCountry>France</ShipCountry>
  </Orders>
- <Orders diffgr:id="Orders5" msdata:rowOrder="4">
  <OrderID>10739</OrderID>
  <CustomerID>VINET</CustomerID>
  <EmployeeID>3</EmployeeID>
  <OrderDate>1997-11-12T00:00:00.0000000+04:00</OrderDate>
  <RequiredDate>1997-12-10T00:00:00.0000000+04:00</
   RequiredDate>
  <ShippedDate>1997-11-17T00:00:00.0000000+04:00</
   ShippedDate>
  <ShipVia>3</ShipVia>
  <Freight>11.08</Freight>
  <ShipName>Vins et alcools Chevalier</ShipName>
  <ShipAddress>59 rue de l'Abbaye</ShipAddress>
  <ShipCity>Reims</ShipCity>
  <ShipPostalCode>51100</ShipPostalCode>
  <ShipCountry>France</ShipCountry>
  </Orders>
  </OrdersDS>
  </diffgr:diffgram>
  </DataSet>

The result shows both the XML Schema and the XML data.

GetOrderDetails Methods

The GetOrderDetails methods follow the same principle as the GetOrders methods. Again, the only difference is the query and parameter, so I will not go into detail about each section of the code.

#Region " GetOrderDetails Code Section "

    <WebMethod()> _
    Public Function GetOrderDetails( _
           ByVal OrderID As Integer _
     ) As DataSet
        '***************************************'
        '* Return the item entries             *'
        '* for the given order                 *'
        '***************************************'

        'Declare dataset to store results
        Dim OrderEntriesDS As New DataSet()
        OrderEntriesDS.DataSetName = "OrderEntriesDS"

        'Declare SqlConnection object for connection to
        'database
        'Use the Global Private string initialized in 
        'MyBase.New()
        Dim objConn As New SqlConnection
            (SQLConnectionString)

        'Define the select query string to be used
        'to select the data.
        'including parameters in T-SQL format and for
        'optimization
        'tell the database engine not to issue locks
        Dim SelectQuerySTR As String = _
            "SELECT * " & _
            "FROM [Order Details] WITH (NOLOCK)" & _
            "WHERE OrderID = @OrderID"

        'Declare the DataAdapter that will be
        'used to populate the DataSet.
        Dim OrderDataAdapter As New SqlDataAdapter()

        'Define the selectCommand for the DataAdapter
        OrderDataAdapter.SelectCommand = _
          New SqlCommand(SelectQuerySTR, objConn)

        'Define the parameter used in the SqlCommand
        'and also set its value.
        OrderDataAdapter.SelectCommand.Parameters.Add _
          ("@OrderID", OrderID)

        'Catch possible errors
        Try
            'Open the Connection to database
            objConn.Open()

            'Populate the DataSet using the DataAdapter
            'fill method.
            OrderDataAdapter.Fill(OrderEntriesDS, "Orders
              Details")

        Catch Err As Exception
            Throw New ApplicationException( _
             "Exception encountered when executing
               command.", Err)
        Finally

            'Close the Connection to database
            objConn.Close()

        End Try

        'Return the DataSet
        GetOrderDetails = OrderEntriesDS



    End Function


#End Region

Note that we only have one GetOrderDetails method, and we want to use the same name for the web method. Therefore, we do not have to specify MessageName:=" GetOrderDetails " for the <WebMethod> attribute.

GetFullOrders Methods

So far, we have used the DataSet as a transporter for a single table. We have not yet used multiple tables or added relations to the DataSet. With GetFullOrders, we need to get a snapshot of the database into the DataSet. This means that we will also have to allow for relationships between the two tables. Figure 6-6 shows the tables, columns, and relationships that we will need in the DataSet.

Figure 6-6: Tables, columns, and relationships for GetFullOrders methods

To help with the coding, it would be useful to create a typed DataSet. This will keep us from having to write code to create the proper schema. To do this, add a DataSet to the project and call it “ OrdersDs.xsd.” The design should replicate what is shown in Figure 6-7.

Figure 6-7: Design view of OrdersDs.xsd

The OrdersDs.xsd file is provided on the companion CD. You can use it instead of having to create your own. Once you have created the file, ensure that the Schema menu option Generate DataSet is selected; if not, select it. This will generate the DataSet for you from the XSD file, which, if you remember, is an XML Schema file. However, if you want to manually create the schema yourself, it is shown here:

<?xml version="1.0" encoding="utf-8" ?>
<xs:schema id="Dataset1" targetNamespace=
"http://ProgrammingADODotNET/OrderProcessingWS/
  OrdersDS.xsd"
 elementFormDefault="qualified"
   attributeFormDefault="qualified"
xmlns="http://ProgrammingADODotNET/OrderProcessingWS/
  OrdersDS.xsd"
xmlns:mstns=
"http://ProgrammingADODotNET/OrderProcessingWS/
  OrdersDS.xsd"
 xmlns:xs="http://www.w3.org/2001/XMLSchema"
 xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
 <xs:element name="OrdersDS" msdata:IsDataSet="true">
  <xs:complexType>
   <xs:choice maxOccurs="unbounded">
    <xs:element name="Orders">
     <xs:complexType>
      <xs:sequence>
       <xs:element name="OrderID" type="xs:int"
        minOccurs="0" />
       <xs:element name="CustomerID" type="xs:string"
        minOccurs="0" />
       <xs:element name="EmployeeID" type="xs:int"
        minOccurs="0" />
       <xs:element name="OrderDate" type="xs:dateTime"
        minOccurs="0" />
       <xs:element name="RequiredDate"
        type="xs:dateTime" minOccurs="0" />
       <xs:element name="ShippedDate"
        type="xs:dateTime" minOccurs="0" />
       <xs:element name="ShipVia" type="xs:int"
        minOccurs="0" />
       <xs:element name="Freight" type="xs:decimal"
        minOccurs="0" />
       <xs:element name="ShipName" type="xs:string"
        minOccurs="0" />
       <xs:element name="ShipAddress"
        type="xs:string" minOccurs="0" />
       <xs:element name="ShipCity"
        type="xs:string" minOccurs="0" />
       <xs:element name="ShipRegion"
        type="xs:string" minOccurs="0" />
       <xs:element name="ShipPostalCode"
        type="xs:string" minOccurs="0" />
       <xs:element name="ShipCountry"
        type="xs:string" minOccurs="0" />
      </xs:sequence>
     </xs:complexType>
    </xs:element>
    <xs:element name="Order_x0020_Details">
     <xs:complexType>
      <xs:sequence>
       <xs:element name="OrderID" type="xs:int"
        minOccurs="0" />
       <xs:element name="ProductID" type="xs:int"
        minOccurs="0" />
       <xs:element name="UnitPrice"
        type="xs:decimal" minOccurs="0" />
       <xs:element name="Quantity" type="xs:short"
        minOccurs="0" />
       <xs:element name="Discount" type="xs:float"
        minOccurs="0" />
      </xs:sequence>
     </xs:complexType>
    </xs:element>
    <xs:element name="Customers">
     <xs:complexType>
      <xs:sequence>
       <xs:element name="CustomerID"
        type="xs:string" minOccurs="0" />
       <xs:element name="CompanyName"
        type="xs:string" minOccurs="0" />
       <xs:element name="ContactName"
        type="xs:string" minOccurs="0" />
       <xs:element name="ContactTitle"
        type="xs:string" minOccurs="0" />
       <xs:element name="Address" type="xs:string"
        minOccurs="0" />
      </xs:sequence>
     </xs:complexType>
    </xs:element>
    <xs:element name="Employees">
     <xs:complexType>
      <xs:sequence>
       <xs:element name="EmployeeID" type="xs:int"
        minOccurs="0" />
       <xs:element name="Title" type="xs:string"
        minOccurs="0" />
       <xs:element name="FirstName" type="xs:string"
        minOccurs="0" />
       <xs:element name="LastName" type="xs:string"
        minOccurs="0" />
      </xs:sequence>
     </xs:complexType>
    </xs:element>
    <xs:element name="Products">
     <xs:complexType>
      <xs:sequence>
       <xs:element name="ProductID" type="xs:int"
        minOccurs="0" />
       <xs:element name="ProductName"
        type="xs:string" minOccurs="0" />
      </xs:sequence>
     </xs:complexType>
    </xs:element>
   </xs:choice>
  </xs:complexType>
  <xs:key name="OrdersPK" msdata:PrimaryKey="true">
   <xs:selector xpath=".//mstns:Orders" />
   <xs:field xpath="mstns:OrderID" />
  </xs:key>
  <xs:key name="EmployeesPK" msdata:PrimaryKey="true">
   <xs:selector xpath=".//mstns:Employees" />
   <xs:field xpath="mstns:EmployeeID" />
  </xs:key>
  <xs:key name="OrdersDetailsPK" msdata:PrimaryKey="true">
   <xs:selector xpath=".//mstns:Order_x0020_Details" />
   <xs:field xpath="mstns:OrderID" />
   <xs:field xpath="mstns:ProductID" />
  </xs:key>
  <xs:key name="CustomersPK" msdata:PrimaryKey="true">
   <xs:selector xpath=".//mstns:Customers" />
   <xs:field xpath="mstns:CustomerID" />
  </xs:key>
  <xs:key name="ProductsPK" msdata:PrimaryKey="true">
   <xs:selector xpath=".//mstns:Products" />
   <xs:field xpath="mstns:ProductID" />
  </xs:key>
  <xs:keyref name="ProductsOrder_x005F_x0020_Details"
   refer="ProductsPK" msdata:ConstraintOnly="true">
   <xs:selector xpath=".//mstns:Order_x0020_Details" />
   <xs:field xpath="mstns:ProductID" />
  </xs:keyref>
  <xs:keyref name="OrdersOrder_x005F_x0020_Details"
   refer="OrdersPK" msdata:ConstraintOnly="true"
   msdata:DeleteRule="Cascade"
   msdata:UpdateRule="Cascade">
   <xs:selector xpath=".//mstns:Order_x0020_Details" />
   <xs:field xpath="mstns:OrderID" />
  </xs:keyref>
  <xs:keyref name="CustomersOrders"
   refer="CustomersPK" msdata:ConstraintOnly="true">
   <xs:selector xpath=".//mstns:Orders" />
   <xs:field xpath="mstns:CustomerID" />
  </xs:keyref>
  <xs:keyref name="EmployeesOrders"
   refer="EmployeesPK" msdata:ConstraintOnly="true">
   <xs:selector xpath=".//mstns:Orders" />
   <xs:field xpath="mstns:EmployeeID" />
  </xs:keyref>
 </xs:element>
</xs:schema>

The DataSet based on OrderDS will act as a snapshot of tables and records in the database. As you can see in Figure 6-7, the tables that we need to populate in the DataSet are Customers, Employees, Products, Orders, and Order Details. OrderDS also defines a few relationships, as shown here:

Table and Columns	Relationship
Customers	Primary key is CustomerID
Employees	Primary key is EmployeeID
Orders	Primary key is OrdersID
Orders.CustomerID	Foreign key references Customers.CustomerID
Orders.EmployeeID	Foreign key references Employees.EmployeeID
Products	Primary key is ProductID
Order Details	Primary key is a compound key of OrderID and ProductID
Order Details.ProductID	Foreign key references Products.ProductID

To maintain the relationship, the DataSet must be populated in a certain order:

The Customers and Employees tables must be populated before the Orders table.
The Orders table must be populated before the Order Details table.
The Products table must be populated before the Order Details table.

With the above in mind, let’s go through the different sections of the GetFullOrders code.

GetFullOrders Code

First, let’s look at the simplest of the GetFullOrders methods, the GetFullOrders using OrderID. First, declare the method and required variables:

    <WebMethod(MessageName:="GetFullOrders")> _
    Public Function GetFullOrders(ByVal OrderID As _
    Integer)
    As DataSet

        '***************************************'
        '* Return a snapshot of tables         *'
        '* related to the given order          *'
        '***************************************'

        'Declare dataset to hold tables and relations
        Dim OrderDS As New OrdersDS()

        'Declare SqlConnection object for connection to
        'database
        Dim objConn As New SqlConnection
          (SQLConnectionString)

        'Declare the DataAdapter that will be used
        'to populate the DataTables.
        Dim OrderDataAdapter As New SqlDataAdapter()

Notice that OrderDS is declared as type OrdersDS, which is our typed DataSet. We need to define query strings for each of the tables we are going to populate:

     'Declare the select query string to be used
     'to select the data from Orders  table
     'including parameters in T-SQL format and for
     'optimization
     'tell the database engine not to issue locks
     Dim SelectOrderSTR As String = _
      "SELECT OrderID, CustomerID, " & _
      "EmployeeID, OrderDate, " & _
      "RequiredDate, ShippedDate, " & _
      "ShipVia, Freight, " & _
      "ShipName, ShipAddress, " & _
      "ShipCity, ShipRegion, " & _
      "ShipPostalCode, ShipCountry " & _
      "FROM Orders with (NOLOCK) " & _
      "WHERE Orders.OrderID = @OrderID"

     'Declare the select query string to be used
     'for Order details
     Dim SelectOrderDetailsSTR As String = _
      "SELECT [Order Details].OrderID, " & _
      "[Order Details].ProductID, " & _
      "[Order Details].UnitPrice, " & _
      "[Order Details].Quantity, " & _
      "[Order Details].Discount " & _
      "FROM [Order Details] WITH (NOLOCK) " & _
      "WHERE [Order Details].OrderID = @OrderID"


     'Declare the select query string to be used
     'for Products. Note that we do not retrieve
     'all products as this will make the dataset
     'load unused data
     Dim SelectProductsSTR As String = _
      "SELECT DISTINCT Products.ProductID, " & _
      "Products.ProductName " & _
      "FROM [Order Details] JOIN " & _
      "Products ON " & _
      "[Order Details].ProductID = " & _
      "Products.ProductID " & _
      "WHERE [Order Details].OrderID = @OrderID"

     'Declare the select query string to be used
     'for Employees.
     Dim SelectEmployeesSTR As String = _
      "SELECT DISTINCT Employees.EmployeeID, " & _
      "Employees.Title, Employees.FirstName, " & _
      "Employees.LastName " & _
      "FROM Orders JOIN Employees ON " & _
      "Orders.EmployeeID = Employees.EmployeeID " & _
      "WHERE Orders.OrderID = @OrderID"

     'Declare the select query string to be used
     'for Customers.
     Dim SelectCustomersSTR As String = _
      "SELECT DISTINCT Customers.CustomerID, " & _
      "Customers.CompanyName, Customers.ContactName, " & _
      "Customers.ContactTitle, Customers.Address " & _
      "FROM Orders JOIN Customers ON " & _
      "Orders.CustomerID = Customers.CustomerID " & _
      "WHERE Orders.OrderID = @OrderID"

The query string makes sure that, as far as possible, only the required records are retrieved. For example, only the products found on that particular order are retrieved. This helps to reduce the size of the DataSet that will be returned to the clients. In a full-blown system, the Products table could be returned by a separate method. The client could keep a read-only copy of the list updated periodically or on demand. You will also notice that not all columns in the supporting table (Customers, Employees, and Products) are returned. Again, this is again to reduce the size of the DataSet.

Once we have defined the query string, all that is left is to fill the respective tables:

     'Define the selectCommand for the DataAdapter
     OrderDataAdapter.SelectCommand = New SqlCommand()
     OrderDataAdapter.SelectCommand.Connection =
       objConn

     'Define the parameter used in the SqlCommand
     'and also set its value.
     OrderDataAdapter.SelectCommand.Parameters.Add _
          ("@OrderID", OrderID)

        Try
            'Open the Connection to database
            objConn.Open()

            OrderDataAdapter.SelectCommand.CommandText = _
              SelectProductsSTR
            'Populate the DataSet using the DataAdapter
            'fill method.
            OrderDataAdapter.Fill(OrderDS.Products)

            OrderDataAdapter.SelectCommand.CommandText = _
              SelectEmployeesSTR
            'Populate the DataSet using the DataAdapter
            'fill method.
            OrderDataAdapter.Fill(OrderDS.Employees)

            OrderDataAdapter.SelectCommand.CommandText = _
              SelectCustomersSTR
            'Populate the DataSet using the DataAdapter
            'fill method.
            OrderDataAdapter.Fill(OrderDS.Customers)

            OrderDataAdapter.SelectCommand.CommandText = _
              SelectOrderSTR
            'Populate the DataSet using the DataAdapter
            'fill method.
            OrderDataAdapter.Fill(OrderDS.Orders)

            OrderDataAdapter.SelectCommand.CommandText = _
             SelectOrderDetailsSTR
            'Populate the DataSet using the DataAdapter
            'fill method.
            OrderDataAdapter.Fill(OrderDS.Order_Details)

        Catch Err As Exception
            Throw New ApplicationException( _
             "Exception encountered when executing
               command.", Err)
        Finally

            'Close the Connection to database
            objConn.Close()

        End Try


        'Return the dataset
        GetFullOrders = OrderDS

End Function

We only need to use one DataAdapter. The key to this technique is to change the CommandText property of the SqlCommand object for each table that we retrieve. If you look carefully, you will notice also that we are referencing the tables in the DataSet using the dot notation. For example, we use OrderDS.Order_Details to refer to the Order Details table. Since spaces are not allowed in the names of objects in VB .NET syntax, the space in the table name is replaced by an underscore, “_”. Therefore, “Order Details” becomes “Order_Details”. As you would expect, the parameters used in the script must also be added to the SqlCommand object. Since I have chosen the same name for that parameter in my script, I only have to define it once.

GetFullOrders_By_Customer Code

For GetFullOrders_By_Customer, the only difference is the query string and parameter:

       'Declare the select query string to be used
        'to select the data from Orders table
        'including parameters in T-SQL format and for
        'optimization
        'tell the database engine not to issue locks
        Dim SelectOrderSTR As String = _
         "SELECT OrderID, CustomerID, " & _
         "EmployeeID, OrderDate, " & _
         "RequiredDate, ShippedDate, " & _
         "ShipVia, Freight, " & _
         "ShipName, ShipAddress, " & _
         "ShipCity, ShipRegion, " & _
         "ShipPostalCode, ShipCountry " & _
        "FROM Orders with (NOLOCK) " & _
        "WHERE Orders.CustomerID = @CustomerID"

       'Declare the select query string to be used
       'for Order details
       Dim SelectOrderDetailsSTR As String = _
        "SELECT [Order Details].OrderID, " & _
        "[Order Details].ProductID, " & _
        "[Order Details].UnitPrice, " & _
        "[Order Details].Quantity, " & _
        "[Order Details].Discount " & _
        "FROM Orders JOIN [Order Details]  " & _
        "ON Orders.OrderID = [Order Details].OrderID " & _
        "WHERE Orders.CustomerID = @CustomerID"


       'Declare the select query string to be used
       'for Products. Note that we do not retrieve
       'all products as this will make the dataset
       'load unused data
       Dim SelectProductsSTR As String = _
        "SELECT DISTINCT Products.ProductID, " & _
        "Products.ProductName " & _
        "FROM Orders JOIN [Order Details] " & _
        "ON Orders.OrderID = [Order Details].OrderID " & _
        "JOIN Products ON " & _
        "[Order Details].ProductID = " & _
        "Products.ProductID " & _
        "WHERE Orders.CustomerID = @CustomerID"

       'Declare the select query string to be used
       'for Employees.
       Dim SelectEmployeesSTR As String = _
        "SELECT DISTINCT Employees.EmployeeID, " & _
        "Employees.Title, Employees.FirstName, " & _
        "Employees.LastName " & _
        "FROM Orders JOIN Employees ON " & _
        "Orders.EmployeeID = Employees.EmployeeID " & _
        "WHERE Orders.CustomerID = @CustomerID"

       'Declare the select query string to be used
       'for Customers.
       Dim SelectCustomersSTR As String = _
        "SELECT Customers.CustomerID, " & _
      "Customers.CompanyName, Customers.ContactName, " & _
      "Customers.ContactTitle, Customers.Address " & _
      "FROM Customers " & _
      "WHERE Customers.CustomerID = @CustomerID"

The SELECT query used to filter the rows in this case is slightly more complicated because we always have to join with the Orders table to be able to get the CustomerID. This is particularly apparent in the Products table where the join is two levels deep. In this case, the Products table is joined to the Order Details table via the ProductID column. In turn, the Order Details table is joined to the Orders table via the OrderID column. Order is then restricted using the CustomerID column. Since the same products may be on different orders for the same customer, we must also use the DISTINCT qualifier in the query so that we do not get duplicate products.