Web Service in Visual Basic .NET Calling a Stored Procedure using Typed Dataset

This web service was created to interact with hosted IVR solution to create an automated interactive routing script where  clients can check the status of their records via telephone. Essentially, the IVR makes a single element SOAP request over HTTP, the web service generates a Typed Dataset from a stored procedure and builds the formatted SOAP response with additional data elements. The technologies used are: Web Services, VB.NET, Stored Procedures, SQL Queries, Typed Datasets, SOAP, SchemaSerializationMode. To get started create a new Visual Basic ASP.NET Web Service Application project. This will create the necessary files to run your web service. First import the namespaces from .NET framework.

Imports System.Web.Services
Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Web.Services.Protocols
Imports System.ComponentModel

Next define the public properties class and that the VarSearch class is a WebService class type.

<system.web.services.webservice(namespace:="http: microsoft.com="" webservices=""> _
<system.web.services.webservicebinding(conformsto:=wsiprofiles.basicprofile1_1)> _
<toolboxitem(false)> _
Public Class VarSearch
Inherits System.Web.Services.WebService

The section is normal Visual Basic programming. WebMethod is used to indicate this program will be used as a web service. You will notice the infoDataSet is a Typed Dataset. Read this article to learn how to create Typed DataSets.

<webmethod( _="" based="" description:=""Returns" number="" on="" records=""> _
Public Function GetInfo(ByVal Number As String) As infoDataSet
</webmethod(>

Here we open the database connection. ADO.NET has many different ways to connect.

Dim connectionString As String = "server=127.0.0.1;database=YourDB;uid=YourUser;pwd=YourPW"
Dim connection As SqlConnection = New SqlConnection(connectionString)
connection.Open()

This begins the Try for error handling and calls a stored procedure. Read this article for creating stored procedures.

Try
Dim command As SqlCommand = New SqlCommand("spYourProcedure", connection)
command.CommandType = CommandType.StoredProcedure
command.Parameters.AddWithValue("@parameter", "" & Number & "")

Here we are creating a SQLAdapter and Filling the results into a new DataTable. This will give flexibility later.

Dim adapter As SqlDataAdapter = New SqlDataAdapter(command)
Dim results As DataTable = New DataTable("TableName")
adapter.AcceptChangesDuringFill = False
adapter.Fill(results)

Additional data was needed in the SOAP response. In this case it was a URL that wasn't contained in the database. It was added to the DataTable.

results.Columns.Add("url")
results.Rows(0).Item("url") = "http://yoursite.com/SearchDetails.asp?par=" & Number & ""

Now the Typed DataSet is built and returned with all of the information needed and the extra schema on the XML output is disabled using SchemaSerializationMode.

Dim dsDataSet As New infoDataSet
dsDataSet.Tables.Add(results)
dsDataSet.SchemaSerializationMode = SchemaSerializationMode.ExcludeSchema
Return dsDataSet

Finalize the error handling and close out the function and class.

Catch ex As Exception
Console.WriteLine(ex.Message)
Throw
Finally
connection.Close()
End Try
End Function
End Class

Don't forget to compile the service before testing. When you're ready to test you will need to create a new application on IIS  to host the web service. Not all files from the project are needed. In this example only the .asmx, .vb, .xsd and bin/.dll were required. If you don't create a typed dataset you won't need the .xsd file. When you navigate to your web service http://yoursite.com/ws/ws.asmx you will see a link to your function. You will also notice a service description http://yoursite.com/ws/ws.asmx ?WSDL. This WSDL file will come in handy. To enable post from HTTP you will need to add the following to your web.config file.

Once you enter a parameter value and invoke the web service you should see a diffgram output of your dataset. By excluding schema from the SchemaSerializationMode the output is much lighter. To further test the SOAP request and response I use soapUI 2.0.2 and pointed it to the WSDL. Heres the full source:

Imports System.Web.Services
Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Web.Services.Protocols
Imports System.ComponentModel
<system.web.services.webservice(namespace:="http: microsoft.com="" webservices=""> _
<system.web.services.webservicebinding(conformsto:=wsiprofiles.basicprofile1_1)> _
<toolboxitem(false)> _

Public Class VarSearch
Inherits System.Web.Services.WebService
<webmethod( _="" based="" description:=""Returns" number="" on="" records=""> _
Public Function GetInfo(ByVal Number As String) As infoDataSet

Dim connectionString As String = "server=127.0.0.1;database=YourDB;uid=YourUser;pwd=YourPW"
Dim connection As SqlConnection = New SqlConnection(connectionString)
connection.Open()

Try
Dim command As SqlCommand = New SqlCommand("spYourProcedure", connection)
command.CommandType = CommandType.StoredProcedure
command.Parameters.AddWithValue("@variable", "" & Number & "")

Dim adapter As SqlDataAdapter = New SqlDataAdapter(command)
Dim results As DataTable = New DataTable("TableName")
adapter.AcceptChangesDuringFill = False
adapter.Fill(results)
results.Columns.Add("url")
results.Rows(0).Item("url") = "http://yoursite.com/SearchDetails.asp?par=" & Number & ""

Dim dsDataSet As New infoDataSet
dsDataSet.Tables.Add(results)
dsDataSet.SchemaSerializationMode = SchemaSerializationMode.ExcludeSchema
Return dsDataSet

Catch ex As Exception
Console.WriteLine(ex.Message)
Throw
Finally
connection.Close()
End Try
End Function
End Class
</webmethod(>
</toolboxitem(false)>
</system.web.services.webservicebinding(conformsto:=wsiprofiles.basicprofile1_1)>
</system.web.services.webservice(namespace:="http:>