In part 1 I built a VB6 program to insert incoming caller id information into an SQL server database. In order to have the information available online, I'm going to have to write some kind of Web page to retrieve the data from the database.

I could write a simple .asp page and be done with it but, instead, I'm going to build a asp.net Web service that will expose methods for getting and manipulating the data.

<%@ WebService Language="vb" Class="Caller_Id" %>  

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Web.Services  
Imports System.Configuration

'// When declaring a namespace for your Web service
'// Choose something that will be a unique identifier
'// Notice how I used the domain name that I own

<WebService(Namespace:="www.allpaul.com")> _
Public Class Caller_Id

<WebMethod> Public Function Get_Callers() As Dataset
		    	try
					Dim myConnection As SqlConnection
					Dim myCommand As SqlDataAdapter
					myConnection = New _
						SqlConnection(ConfigurationSettings.AppSettings("MyDSN"))
					
					myCommand = New _
						SqlDataAdapter("sp_GetCallers", myConnection)
					
					myCommand.SelectCommand.CommandType = _
						CommandType.StoredProcedure
					
					myConnection.Open()
											
					Dim myDataSet As New DataSet()
		
					myCommand.Fill(myDataSet, "tbl_Caller_Id")
					
					myConnection.Close
					
					Return myDataSet
				
				catch exp As Exception
					
					Return nothing
					
				End try
  			 End Function


<WebMethod>	Public Sub Delete_Caller(Caller_Id as Integer)
				
				Dim myConnection As _
					New SqlConnection(ConfigurationSettings.AppSettings("MyDSN"))
				
				Dim myCommand As _
					New SqlCommand("sp_DeleteCaller", myConnection)

				myCommand.CommandType = CommandType.StoredProcedure
				
				Dim parameterCaller_Id As _
					New SqlParameter("@Caller_ID", SqlDbType.Int, 4)
				
				parameterCaller_Id.Value = Caller_Id
				myCommand.Parameters.Add(parameterCaller_Id)

				myConnection.Open()
					myCommand.ExecuteNonQuery()
				myConnection.Close()
			
			End Sub
End Class
   

Any method in a class can be called via a Web service request as long as <WebMethod> appears before the declaration of the method.

This service has two WebMethods:

Get_Callers

This method accepts no parameters and returns all the callers from the database as a dataset to the page that calls the method. It uses a stored procedure, sp_GetCallers , that simply retrieves all the records from the table tbl_Caller_id.

Delete_Caller

This method used to delete a specific caller from the database. The method expects an integer Caller_ID and uses the stored procedure sp_DeleteCaller passing it the input parameter Caller_ID.

To test the service navigate to the URL using any Web browser. Conveniently, Web services are served to Web browsers as html interfaces. So, if you navigate to the service you should see a page similar to the following screenshot.



And, clicking on the links named for each of the services Web methods allows you to invoke the method (if parameters are expected you will be prompted to provide them) and view the results.

Get_Callers


The resulting xml from invoking the method



Because the Delete_Caller method is expecting a parameter (Caller_ID) the service asks for the Caller_ID in the form of a input form element.



Now that we have a fully functioning Web service, all that's left is to build an asp.net page to consume the service. I'll do that in part 3.