In last week’s column,
we completed the initial phase of making product information available via one
or more Web services. The product information is contained in the standard Northwind database available with most SQL Server
installations. We developed the stored procedures to query the desired
information. The next step is developing the Web service code.


The System.Web.Services namespace
provides the necessary classes for creating custom Web services. Specifically,
a Web service is derived from the WebServices class
located within this namespace. In addition, a Web service class file is created
with the asmx file extension.

Web service methods are exposed via the WebMethod
attribute. It immediately precedes the method name. The method marked with this
attribute must be declared as public, so it is available to all as a Web
service method should be.

You may use the .NET language of your choice, including C#,
VB.NET, J#, or so forth. I will utilize both C# and VB.NET in this example. The
remaining aspects of the development follow the normal rules of development.

Let’s create our Web service to interface with SQL Server
and return the applicable information from the Northwind
database. There will be one Web service with six methods corresponding to the
six stored procedures:

  • GetProductsById: Accepts an integer parameter.
    This parameter is used to call the sp_GetProductByID
    stored procedure.
  • GetProductsByName: Accepts a string parameter that
    is passed to the sp_GetProductByName stored
  • GetProductsByCategoryId: Accepts an integer parameter
    that is passed to the sp_GetProductByCategoryID
    stored procedure.
  • GetProductsByCategoryName: Accepts a string parameter that
    is passed to the sp_GetProductByCategoryName
    stored procedure.
  • GetProductsBySupplierId: Accepts an integer parameter
    that is passed to the sp_GetProductBySupplierID
    stored procedure.
  • GetProductsBySupplierName: Accepts a string parameter that
    is passed to the sp_GetProductBySupplierName
    stored procedure.

Each method is public and tagged with the WebMethod attribute. In addition, the SoapDocumentMethod
attribute is assigned to each method as well to control SOAP formattnig.

For overall SOAP body formatting, or style, the Web Services Description Language (WSDL)
provides two choices: RPC and document. The .NET Framework controls these
choices in code using attributes. Document refers to formatting the XML Web
service method according to an XSD schema. The document style refers to
formatting the Body element as a series of one or more message parts following
the Body element.

Listing A
contains the code for the Web service I created. The Web service is simple, and
this simplicity is aided by the Data Access Application Block (DAAB). DAAB is
an excellent addition to a .NET developer’s toolbox. It provides a clean and
elegant method for access data in your .NET applications.

A great aspect of DAAB is the simple approach to creating
methods. You can use it to return almost any data-related object including DataSet in our example. The overloaded method signatures
ensure ease-of-use. They accept the same set of parameters: connection string,
command type, SQL, and parameter object. The parameter object is only necessary
when using stored procedures with parameters as this sample illustrates. I love
interfacing with SQL Server in so few lines of code.
Listing B contains the Web service written with VB.NET.

Does it work?

Web services may communicate over a network using industry
standard protocols. One such approach uses standard HTTP and a browser to
access the service. We may test our service using this approach.

The following
URL will call the service and provide links to the exposed methods (note: I am
using my local development machine signified by localhost):


A quick test of the GetProductsById
method with a value of 5 returns the XML document you’ll see in Listing C.

Notice that the XSD schema is included as specified by the SoapDocument attribute. Let’s take a quick detour to
discuss XSD.

An overview of XSD

An XSD (XML Schema Definition) is also commonly known as an XML Schema. It is an XML language for
describing and constraining the content of XML documents. The schema element
opens our schema. It may contain one or more element elements. Here is a quick
overview of some of the other elements contained in our sample:

  • sequence: The sequence is a composite
    element that defines an ordered sequence of sub-elements.
  • choice: Another composite element
    defining a list of elements. It describes a choice between several
    possible elements or groups of elements.

XML Schema allows you to define the cardinality of an
element with some precision. You can specify both minOccurs
(the minimum number of occurences) and maxOccurs (the maximum number of occurrences). You can see
the minOccurs attribute used in the ProductID, ProductName, CompanyName, and UnitPrice

Also, you may notice that the data type of an element can be
specified. In this specific example, string is used for ProductName
and CompanyName, while ProductID
is an integer (int) and UnitPrice
is decimal. Custom types may be created with the simpleType
and complexType elements. Notice that complexType is used in our example.

What to expect

The .NET Framework simplifies the Web service creation
process; this is especially true if you use Visual Studio .NET, but it isn’t a
requirement. In the next column, we will dive into using the Web service.

TechRepublic’s free .NET newsletter, delivered each Wednesday, contains useful tips and coding examples on topics such as Web services, ASP.NET, ADO.NET, and Visual Studio .NET. Automatically sign up today!