Countries/Cities of US States (Webservice/ADO.NET Demo)

Introduction

Here in this example, I'll demonstrate the use of ADO.NET through a web service and make you realize that its not a monster replacement to ADO 2.5 after all.... though it is monstrous in terms of its features and the power it exudes :). I have included a database along with the zip called LIS (Location Information Services), which has two tables viz. statemaster (all states in the US) and countymaster (counties and cities for a particular state). This web service accepts the abbreviation of a US state and returns the counties/cities within that state.

Note: You have to create a DSN called LIS, mapping it to the MS-Access db provided along as zip along with article.

Source Code

Imports System.ComponentModel
Imports System.Configuration
Imports System.Web.Services
Imports System.Diagnostics
Imports System.Data
Imports System.Data.SQL
Imports System.Data.ADO
Public Class Service1
Inherits System.Web.Services.WebService
Public Function <WebMethod()>
getCounties(ByVal stateAbr As String) As DataSet
Dim strConnString As String
strconnstring = "DSN=LIS"
'Create an ADOConnection instance
Dim objConn As ADOConnection
objconn = New ADOConnection
'Set the ADOCOnnection object's connection string property
objconn.ConnectionString = strconnstring
objconn.Open()
'Create a SQL string
Dim strSQL As String = "select countyname,gslid from countymaster where stateid=(select stateid from statemaster where abbr='" & stateAbr & "')"
'Create an instance of the ADODatasetcommand and Dataset objects
Dim objDSCommand As ADODataSetCommand
Dim objDataset As DataSet
objdataset = New DataSet
objdscommand = New ADODataSetCommand(strsql, objconn)
objdscommand.FillDataSet(objdataset, "Counties")
Return objDataset
objconn.Close()
objconn = Nothing
End Function
Public Sub New()
MyBase.New()
InitializeComponent()
'Add your own initialization code after the InitializeComponent call
End Sub
End Class