<%@ WebService Class="ProductsService" %>

Imports System
Imports System.Web.Services
Imports System.Data
Imports System.Data.SqlClient
Imports System.Collections

<WebService( Namespace:="http://yourdomain.com/webservices" )> _
Public Class ProductsService : Inherits WebService

<WebMethod()> Public Function GetCategories() As String()
  Dim strSelect As String
  Dim conNorthwind As SqlConnection
  Dim cmdCategories As SqlCommand
  Dim dtrCategories As SqlDataReader
  Dim colCategories As ArrayList

  strSelect = "SELECT CategoryName FROM Categories"
  conNorthwind = New SqlConnection( "Server=localhost;UID=sa;PWD=secret;Database=Northwind" )
  cmdCategories = New SqlCommand( strSelect, conNorthwind )
  conNorthwind.Open()
    dtrCategories = cmdCategories.ExecuteReader()
    colCategories = New ArrayList
    While dtrCategories.Read
      colCategories.Add( dtrCategories( "CategoryName" ) )
    End While
  conNorthwind.Close()
  Return colCategories.ToArray( GetType( System.String ) ) 
End Function

<WebMethod()> Public Function GetProducts( CategoryName As String ) As String()
  Dim strSelect As String
  Dim conNorthwind As SqlConnection
  Dim cmdProducts As SqlCommand
  Dim dtrProducts As SqlDataReader
  Dim colProducts As ArrayList

  CategoryName = Server.UrlDecode( CategoryName )

  strSelect = "SELECT ProductName, UnitPrice " & _
    "FROM Products, Categories WHERE Products.CategoryID = Categories.CategoryID " & _
    "AND CategoryName = @CategoryName"

  conNorthwind = New SqlConnection( "Server=localhost;UID=sa;PWD=secret;Database=Northwind" )
  cmdProducts = New SqlCommand( strSelect, conNorthwind )
  cmdProducts.Parameters.Add( New SQLParameter( "@CategoryName", CategoryName ) )
  conNorthwind.Open()
    dtrProducts = cmdProducts.ExecuteReader()
    colProducts = New ArrayList
    While dtrProducts.Read
      colProducts.Add( _
        dtrProducts( "ProductName" ) & _
        String.Format( " - {0:c}", dtrProducts( "UnitPrice" ) ) )
    End While
  conNorthwind.Close()
  Return colProducts.ToArray( GetType( System.String ) ) 
End Function




End Class