Creating a Useful Web Service

Although a service that adds two numbers together has its uses (although it might be difficult to determine those uses, except for demonstration purposes), you might be more likely to create a Web Service that interacts with a data source and provides information based on that data. In this section, you'll create a Web Service that retrieves information from the Northwind sample database in SQL Server. You'll provide two methods:

• GetInventory. This method returns the current, available inventory for a specific product.

• GetAllInventory. This method returns a DataSet containing the inventory level for all products.

There really isn't much new in these procedures, because the techniques you've already learned for manipulating data all apply here. To get started creating your service, follow these steps:

Imports System.Web.Services

<WebService(Namespace:="http://www.northwind.com/ws")> _
Public Class Inventory
  Inherits System.Web.Services.WebService

End Class

Imports System.Data.OleDb

Private Const CONNECTION_STRING As String = _
 "Provider=sqloledb;" & _
 "Data Source=(local);" & _
 "Initial Catalog=Northwind;" & _
 "User ID=sa;Password="

<WebMethod()> Public Function UnitsInStock( _
 ByVal ProductID As Integer) As Integer
  Dim strSQL As String = _
   "SELECT UnitsInStock FROM Products " & _
   "WHERE ProductID = " & ProductID

  Dim cnn As OleDbConnection
  Dim cmd As OleDbCommand
  Dim intUnits As Integer

  Try
    cnn = New OleDbConnection(CONNECTION_STRING)
    cnn.Open()
    cmd = New OleDbCommand(strSQL, cnn)
    UnitsInStock = cmd.ExecuteScalar()

  Finally
    If cnn.State = ConnectionState.Open Then
      cnn.Close()
    End If
  End Try
End Function

The UnitsInStock method opens a connection to the data source and executes a Command object that returns the single required value—the available units in stock for the requested product. To test your Web Service method, follow these steps:

Returning a DataSet from a Web Service

Every sample method exposed in a Web Service that you've seen so far returns a single value. There's no reason, however, that a Web Service method can't return a complex result, such as a DataSet. To finish this chapter, you'll create a Web Service method that returns a complete DataSet, and then you'll create a simple page that displays the results of calling the Web Service method in a DataGrid control.

Follow these steps to create and consume the Web Service method:

<WebMethod()> Public Function GetAllInventory() As DataSet
  Dim strSQL As String = _
   "SELECT ProductID, ProductName, " & _
   "UnitPrice, UnitsInStock " & _
   "FROM Products ORDER BY ProductName"

  Dim da As OleDbDataAdapter
  Dim ds As New DataSet()

  Try
    da = New OleDbDataAdapter(strSQL, CONNECTION_STRING)
    da.Fill(ds)
  Catch
    Throw
  End Try
  Return ds
End Function

http://localhost/jumpstart/inventoryservice/inventory.asmx

Private Sub Page_Load( _
 ByVal sender As System.Object, _
 ByVal e As System.EventArgs) _
 Handles MyBase.Load

  If Not Page.IsPostBack Then
									Dim ws As New localhost.Inventory()
									grdInventory.DataSource = ws.GetAllInventory()
									grdInventory.DataBind()
									End If
End Sub

What happened here? When your page loaded, it called the Web Service method, which returned a DataSet. Your code set the DataGrid control's DataSource property to the DataSet returned from the method and displayed the data. Couldn't be much easier than that, could it?

Of course, between the Web Service and the consumer application, the .NET Framework serializes the DataSet as XML—you saw that in Figure 29.9. Once the consumer application receives the XML, however, the proxy class indicates that the application should retrieve a DataSet object, and the .NET Framework converts the data back into its original data type, ready to be bound to the DataGrid control's DataSource property.