Creating the InformationRetrievalWebService Web Service | NET InstantCode. UML with Visio and Visual Studio .NET

The InformationRetrievalService class defines the InformationRetrievalWebService Web service.

Listing 7-11 shows the code for the InformationRetrievalService.asmx.vb file:

Listing 7-11: The InformationRetrievalService.asmx.vb File

 Imports System.Web.Services Imports System.IO Imports System.Collections Imports System.Data.SqlClient Imports System.Data <System.Web.Services.WebService(Namespace:= "http://tempuri.org/InformationRetrievalWebService/InformationRetrievalService")> _ Public Class InformationRetrievalService    Inherits System.Web.Services.WebService    'A string that contains the connection string property of SQLConnection object    Dim strSQLConncetion As String = ""    'An object reference to sqlClient.SqlConnection    Dim objSQLConnection As New SqlConnection    'Required by the Web Services Designer    Private components As System.ComponentModel.IContainer    'NOTE: The following procedure is required by the Web Services Designer    'It can be modified using the Web Services Designer.      'Do not modify it using the code editor.    <System.Diagnostics.DebuggerStepThrough()>     Private Sub InitializeComponent()       components = New System.ComponentModel.Container          Try             Dim sr As New StreamReader(Server.MapPath(".") & "\Settings.txt")             strSQLConncetion = sr.ReadLine             sr.Close()             Catch ex As Exception             strSQLConncetion = "SERVER=localhost;UID=sa;PWD=sa;Initial Catalog=services_info"          End Try          'Try establising connection to services_info database          Try             'Specifying the connection string for opening the connection             objSQLConnection.ConnectionString = strSQLConncetion             Catch ex As Exception             'Error establishing connection                      End Try       End Sub       'This method returns a data set containing the information of all the locations.       'If there is an error retrieving the locations then a blank data set is returned       <WebMethod()> _       Public Function GetLocations() As DataSet          Dim objds As New DataSet          'Retrieve the information about locations.          Try             Dim objSqlCmd As New SqlCommand("SELECT * FROM Location", objSQLConnection)             Dim objSqlAdapter As New SqlDataAdapter(objSqlCmd)             'Filling DataSet from Data Adapter             objSqlAdapter.Fill(objds)             Catch ex As Exception             'If there is an error retrieving the locations.           End Try          GetLocations = objds       End Function       'This method returns a data set containing the information of all the        'nearby locations for a specified location       <WebMethod()> _       Public Function GetNearByLocations(ByVal strLocation As String) As DataSet          Dim objds As New DataSet          'Retrieve the information about nearby locations for a specified location          Try             Dim objSqlCmd As New SqlCommand("SELECT dbo.nearByLocations.nbLId, _             Location_1.LocationName, dbo.NearByLocations.Distance FROM  dbo.Location _             INNER JOIN dbo.NearByLocations ON dbo.Location.LId = dbo.NearByLocations.LId _             INNER JOIN dbo.Location Location_1 ON dbo.NearByLocations.NbLId = Location_1.LId _             WHERE Location.LocationName = '" & strLocation & "'", objSqlConnection)             Dim objSqlAdapter As New SqlDataAdapter(objSqlCmd)             objSqlAdapter.Fill(objds)             Catch ex As Exception             'An error retrieving the near by locations from the database.           End Try          GetNearByLocations = objds       End Function       'This method returns a data set containing the information of the service categories.       <WebMethod()> _       Public Function GetServiceCategory() As DataSet          Dim objds As New DataSet          'Retrieve the information about service categories.          Try             Dim objSqlCmd As New SqlCommand("SELECT * FROM services", objSQLConnection)             Dim objSqlAdapter As New SqlDataAdapter(objSqlCmd)             objSqlAdapter.Fill(objds)             Catch ex As Exception             'An error retrieving the service categories from the database          End Try          GetServiceCategory = objds       End Function       'This method returns a data set containing the Company names.       <WebMethod()> _       Public Function GetCompany() As DataSet          Dim objds As New DataSet          'Retrieve the company names from the database          Try             Dim objSqlCmd As New SqlCommand("SELECT distinct CompanyName FROM CompanyService", objSQLConnection)             Dim objSqlAdapter As New SqlDataAdapter(objSqlCmd)             objSqlAdapter.Fill(objds)             Catch ex As Exception             'An error retrieving the company names from the database              End Try          GetCompany = objds       End Function       'This method returns a data set containing the services and their addresses in a specified location.       <WebMethod()> _       Public Function GetServicesByLocation(ByVal strLocation As String) As DataSet          Dim objds As New DataSet          Try             'Retrieve the services located in a specific location.             Dim objSqlCmd As New SqlCommand _             ("SELECT Services.SId,ServiceCategory,CompanyName,Address,Phone FROM _             Services,CompanyService where Services.SId=CompanyService.SId and LId= _             (select LId from location where locationName='" & strLocation & "') _             ", objSqlConnection)             Dim objSqlAdapter As New SqlDataAdapter(objSqlCmd)             objSqlAdapter.Fill(objds)             Catch ex As Exception             'An error retrieving the services in a particular location.          End Try          GetServicesByLocation = objds       End Function       'This method returns a data set containing the information of the services        'provided by a specified company.       <WebMethod()> _       Public Function GetServicesBycompany(ByVal strCompany As String) As DataSet          Dim objds As New DataSet          Try             'Retrieve the information regarding services provided by a specified company.             Dim objSqlCmd As New SqlCommand("SELECT ServiceCategory,Address,Phone  FROM _             Services,CompanyService where Services.SId=CompanyService.SId and CompanyName='" _             & strCompany & "'", objSqlConnection)             Dim objSqlAdapter As New SqlDataAdapter(objSqlCmd)             objSqlAdapter.Fill(objds)             Catch ex As Exception             'An error retrieving the information regarding services provided by a specified company          End Try          GetServicesBycompany = objds       End Function       'This method returns a data set containing the information of specified        'service provided by a specified company.       <WebMethod()> _       Public Function GetSpecServiceByComp(ByVal strService As String, _       ByVal strCompany As String) As DataSet          Dim objds As New DataSet          Try             Dim objSqlCmd As New SqlCommand("SELECT ServiceCategory,Address,Phone  FROM _             Services,CompanyService where Services.SId=CompanyService.SId and CompanyName='" _             & strCompany & "' and ServiceCategory='" & strService & "'", _             objSqlConnection)             Dim objSqlAdapter As New SqlDataAdapter(objSqlCmd)             objSqlAdapter.Fill(objds)             Catch ex As Exception             'An error retrieving the information of specified service provided by a specified company          End Try          GetSpecServiceByComp = objds       End Function       'This method returns a data set containing the information of        'services provided by a specific company in a specific location.       <WebMethod()> _       Public Function GetServicesByLocAndComp(ByVal strCompany As String, _       ByVal strLocation As String) As DataSet          Dim objds As New DataSet          Try             'Retrieve the information of services provided by a specific company in a specific location.             Dim objSqlCmd As New SqlCommand("SELECT ServiceCategory, CompanyName, Address, Phone _             FROM Services,CompanyService where Services.SId=CompanyService.SId and _             CompanyName='" & strCompany & "' and LId= _             (select LId from Location where LocationName='" & strLocation & "')", objSqlConnection)             Dim objSqlAdapter As New SqlDataAdapter(objSqlCmd)             objSqlAdapter.Fill(objds)             Catch ex As Exception             'An error retrieving the information of services provided by a              'specific company located in a specific location          End Try       GetServicesByLocAndComp = objds    End Function    'This method returns a data set containing the information of specified service in a specific location.    <WebMethod()> _    Public Function GetSpecServiceByLocation(ByVal strService As String, _    ByVal strLocation As String) As DataSet       Dim objds As New DataSet       Try          'Retrieve the information of specified service in a specific location.          Dim objSqlCmd As New SqlCommand("SELECT ServiceCategory, CompanyName, Address, Phone _          FROM Services,CompanyService where Services.SId=CompanyService.SId and _          ServiceCategory='" & strService & "' and LId=(select LId from Location _          where LocationName='" & strLocation & "')", objSqlConnection)          Dim objSqlAdapter As New SqlDataAdapter(objSqlCmd)          objSqlAdapter.Fill(objds)          Catch ex As Exception          'An error retrieving the information of specific service in a specific location       End Try       GetSpecServiceByLocation = objds    End Function    'This method returns a data set containing the Locations and Company Name providing a specified Service    <WebMethod()> _    Public Function GetCompLocByService(ByVal strService As String) As DataSet       Dim objds As New DataSet       Try          'Retrieve the Locations and Companies providing specified service          Dim objSqlCmd As New SqlCommand("SELECT LocationName,CompanyName FROM _          Location,CompanyService where Location.LId=CompanyService.LId and Sid= _          (select sid from services where ServiceCategory='" & strService & "') ", _          objSqlConnection)          Dim objSqlAdapter As New SqlDataAdapter(objSqlCmd)          objSqlAdapter.Fill(objds)          Catch ex As Exception          'An error retrieving the Locations and Companies providing specified service       End Try       GetCompLocByService = objds    End Function    'This method returns a data set containing the information of a     'specific Service in a specific Location provided by a specific Company    <WebMethod()> _    Public Function GetSpecServiceByCompLoc(ByVal strService As String, _    ByVal strLocation As String, ByVal strCompany As String) As DataSet       Dim objds As New DataSet       Try          'Retrieve the information of a specific Service in a specific           'Location provided by a specific Company          Dim objSqlCmd As New SqlCommand("SELECT Address,Phone FROM CompanyService where _          Sid=(select sid from services where ServiceCategory='" & strService & "') and _          LId=(select LId from Location where LocationName='" & strLocation & "' ) and _          CompanyName='" & strCompany & "'", objSqlConnection)          Dim objSqlAdapter As New SqlDataAdapter(objSqlCmd)          objSqlAdapter.Fill(objds)          Catch ex As Exception          'An error retrieving the information of specific service,           'located in a specific location, provided by specific company       End Try       GetSpecServiceByCompLoc = objds    End Function End Class

Download this Listing .

The above listing implements the following methods :

GetLocations() : Returns a data set that contains the information on all locations.
GetNearbyLocations() : Returns a data set that contains the information on all near by locations for a specified location.
GetServiceCategory() : Returns a data set that contains information about service categories.
GetCompany() : Returns a data set that contains information about the companies that provide particular services.
GetServicesByLocation() : Returns a data set that contains the information on services and company addresses sorted by location.
GetServicesByCompany() : Returns a data set that contains information about the services provided by a specified company.
GetSpecServiceByComp() : Returns a data set that contains information about a specific service provided by a specific company.
GetSpecServiceByLocation() : Returns a data set that contains the information about a specific service in a specific location.
GetServicesByLocAndComp() : Returns a data set that contains information about services provided by a specific company in a specific location.
GetCompLocByService() : Returns a data set that contains information on locations and companies that provide a specific service in these locations.
GetSpecServiceByCompLoc() : Returns a data set that contains information on a specific service in a specific location provided by a specific company.