Enterprise Software

Exposing product information via Web services

The .NET Framework simplifies the Web service creation process. Learn how to create a Web service that interfaces with SQL Server and returns the applicable information from the Northwind database.

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 procedure.
  • 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 elements.

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!

About Tony Patton

Tony Patton has worn many hats over his 15+ years in the IT industry while witnessing many technologies come and go. He currently focuses on .NET and Web Development while trying to grasp the many facets of supporting such technologies in a productio...

Editor's Picks