Exposing product information via Web services

Overview
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 utilise 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.

Listing A
using Microsoft.ApplicationBlocks.Data; using System; using System.Collections; using System.ComponentModel; using System.Data; using System.Data.SqlClient; using System.Diagnostics; using System.Web; using System.Web.Services; using System.Web.Services.Protocols; namespace WebServiceExample { public class Service1 : WebService { private DataSetds; private SqlParameter[] sp = new SqlParameter[1]; private const string connString = "server=(local);UID=Chester;PWD=Tester;INITIAL CATALOG=Northwind"; private string strSQL; public Service1() { } [SoapDocumentMethod(Action="GetProductsById")] [WebMethod] public DataSetGetProductsById(intproductID) { strSQL = "sp_GetProductById"; sp[0] = new SqlParameter("@ProductID", productID); ds = SqlHelper.ExecuteDataset(connString, CommandType.StoredProcedure, strSQL, sp); return ds; } [SoapDocumentMethod(Action="GetProductsByName")] [WebMethod] public DataSetGetProductsByName(string productName) { strSQL = "sp_GetProductByName"; sp[0] = new SqlParameter("@ProductName", productName); ds = SqlHelper.ExecuteDataset(connString, CommandType.StoredProcedure, strSQL, sp); return ds; } [SoapDocumentMethod(Action="GetProductsByCategoryId")] [WebMethod] public DataSetGetProductsByCategoryId(intcategoryID) { strSQL = "sp_GetProductByCategoryId"; sp[0] = new SqlParameter("@CategoryID", categoryID); ds = SqlHelper.ExecuteDataset(connString, CommandType.StoredProcedure, strSQL, sp); return ds; } [SoapDocumentMethod(Action="GetProductsByCategoryName")] [WebMethod] public DataSetGetProductsByCategoryName(string categoryName) { strSQL = "sp_GetProductByCategoryName"; sp[0] = new SqlParameter("@CategoryName", categoryName); ds = SqlHelper.ExecuteDataset(connString, CommandType.StoredProcedure, strSQL, sp); return ds; } [SoapDocumentMethod(Action="GetProductsBySupplierId")] [WebMethod] public DataSetGetProductsBySupplierId(intsupplierID) { strSQL = "sp_GetProductBySupplierId"; sp[0] = new SqlParameter("@SupplierID", supplierID); ds = SqlHelper.ExecuteDataset(connString, CommandType.StoredProcedure, strSQL, sp); return ds; } [SoapDocumentMethod(Action="GetProductsBySupplierName")] [WebMethod] public DataSetGetProductsBySupplierName(string supplierName) { strSQL = "sp_GetProductBySupplierName"; sp[0] = new SqlParameter("@SupplierName", supplierName); ds = SqlHelper.ExecuteDataset(connString, CommandType.StoredProcedure, strSQL, sp); return ds; } } }

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.

Listing B
Imports System.Data Imports System.Data.SqlClient Imports Microsoft.ApplicationBlocks.Data Imports System.Web.Services Imports System.Web.Services.Protocols <System.Web.Services.WebService(Namespace := "http://tempuri.org/WebServiceExample/Service1")> _ Public Class Service1 Inherits WebService Dim ds As DataSet Dim sp(1) As SqlParameter Const connString As String = "server=(local);UID=Chester;PWD=Tester;INITIAL CATALOG=Northwind" Dim strSQL As String <SoapDocumentMethod(ResponseElementName:="GetProductsById"), _ WebMethod()> _ Public Function GetProductsById(ByValproductID As Integer) As DataSet strSQL = "sp_GetProductById" sp(0) = New SqlParameter("@ProductID", productID) ds = SqlHelper.ExecuteDataset(connString, CommandType.StoredProcedure, strSQL, sp) Return ds End Function <SoapDocumentMethod(ResponseElementName:="GetProductsByName"), _ WebMethod()> _ Public Function GetProductsByName(ByValproductName As String) As DataSet strSQL = "sp_GetProductByName" sp(0) = New SqlParameter("@ProductName", productName) ds = SqlHelper.ExecuteDataset(connString, CommandType.StoredProcedure, strSQL, sp) Return ds End Function <SoapDocumentMethod(ResponseElementName:="GetProductsByCategoryId"), _ WebMethod()> _ Public Function GetProductsByCategoryId(ByValcategoryID As Integer) As DataSet strSQL = "sp_GetProductByCategoryId" sp(0) = New SqlParameter("@CategoryID", categoryID) ds = SqlHelper.ExecuteDataset(connString, CommandType.StoredProcedure, strSQL, sp) Return ds End Function <SoapDocumentMethod(ResponseElementName:="GetProductsByCategoryName"), _ WebMethod()> _ Public Function GetProductsByCategoryName(ByValcategoryName As String) As DataSet strSQL = "sp_GetProductByCategoryName" sp(0) = New SqlParameter("@CategoryName", categoryName) ds = SqlHelper.ExecuteDataset(connString, CommandType.StoredProcedure, strSQL, sp) Return ds End Function <SoapDocumentMethod(ResponseElementName:="GetProductsBySupplierId"), _ WebMethod()> _ Public Function GetProductsBySupplierId(ByValsupplierID As Integer) As DataSet strSQL = "sp_GetProductBySupplierId" sp(0) = New SqlParameter("@SupplierID", supplierID) ds = SqlHelper.ExecuteDataset(connString, CommandType.StoredProcedure, strSQL, sp) Return ds End Function <SoapDocumentMethod(ResponseElementName:="GetProductsBySupplierName"), _ WebMethod()> _ Public Function GetProductsBySupplierName(ByValsupplierName As String) As DataSet strSQL = "sp_GetProductBySupplierName" sp(0) = New SqlParameter("@SupplierName", supplierName) ds = SqlHelper.ExecuteDataset(connString, CommandType.StoredProcedure, strSQL, sp) Return ds End Function End Class