Create Your First ASP.NET Web Service | 2

Create Your First ASP.NET Web Service [con't]

Integrating the Web Service with a Database

Before we create clients, however, we need to make our service a little bit more sophisticated. Let's add a database. Our service is going to act as a lookup service that users anywhere in the world will use to look up a person's age.

Let's take a look at the database. The name of the database is friends and it has only one table (contacts). Create the table using the following SQL code:

I'm using SQL Server 2008. If you are using any other database system, simply modify the SQL code accordingly. After you run the code, you should have a database called friends that has a table called contacts with some sample data. Now, remove the current Web method and replace it with the following code:

Public Function searchname(ByVal srctrm As System.String) As System.String
        Dim con As SqlConnection
        Dim sql As SqlCommand
        Dim dr As SqlDataReader
        Dim name, age, res As String
        con = New SqlConnection("server=cbs-ws006\JNAMSTAT;uid=sa;pwd=Password1;database=friends")
        Try
            con.Open()
            sql = New SqlCommand("SELECT cname,age FROM contacts WHERE cname='" & srctrm & "'", con)
            dr = Sql.ExecuteReader
            'CHECK IF THE QUERY RETURNED ROWS
            If dr.HasRows = False Then
                res = "Name could not be found"
            Else
                'STORE THE INFORMATION IN  VARIABLES
                While dr.Read
                    name = dr(0).ToString
                    age = dr(1).ToString
                    res = name + " " + age
                End While
            End If
        Catch ex As Exception
            res = ex.ToString
        Finally
            'dr.Close()
            con.Close()
        End Try
        Return res
    End Function

Your entire page should now look like this:

Imports System.Web.Services
Imports System.Web.Services.Protocols
Imports System.ComponentModel
Imports System.Data.SqlClient
' To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line.
' <System.Web.Script.Services.ScriptService()> _
<System.Web.Services.WebService(Namespace:="https://localhost/")> _
<System.Web.Services.WebServiceBinding(ConformsTo:=WsiProfiles.BasicProfile1_1)> _
<ToolboxItem(False)> _
Public Class Service1
    Inherits System.Web.Services.WebService
    <WebMethod()> Public Function searchname(ByVal srctrm As System.String) As System.String
        Dim con As SqlConnection
        Dim sql As SqlCommand
        Dim dr As SqlDataReader
        Dim name, age, res As String
        con = New SqlConnection("server=cbs-ws006\JNAMSTAT;uid=sa;pwd=Password1;database=friends")
        Try
            con.Open()
            sql = New SqlCommand("SELECT cname,age FROM contacts WHERE cname='" & srctrm & "'", con)
            dr = Sql.ExecuteReader
            'CHECK IF THE QUERY RETURNED ROWS
            If dr.HasRows = False Then
                res = "Name could not be found"
            Else
                'STORE THE INFORMATION IN  VARIABLES
                While dr.Read
                    name = dr(0).ToString
                    age = dr(1).ToString
                    res = name + " " + age
                End While
            End If
        Catch ex As Exception
            res = ex.ToString
        Finally
            'dr.Close()
            con.Close()
        End Try
        Return res
    End Function
End Class

Substitute this line:

...with the appropriate username, password and server name for your connection. This code basically takes a name from the enquiring client and returns the name and age.

Conclusion

A word of caution: using databases in a service like ours does open up the possibility of SQL injection attacks. So, please use some validation if you are going to implement this on a live site. In my next article, we will look at how to access or "consume" our Web service using various clients.

Original: June 23, 2010

[prev]

Create Your First ASP.NET Web Service | 2

Create Your First ASP.NET Web Service [con't]

Integrating the Web Service with a Database

Conclusion

Find a programming school near you