Using a Web Service to Interact with a Database

As discussed, using a web service, you can create “front-end” code that resides between client programs and a database. For example, the BookInfo web service in Listing 7.5 provides the GetEntries method a client program can use to interact with the Duwamish7vb database. The method returns an array of strings that will contain the names of either the authors or publishers, depending on the parameter the user passes to the method:

string GetEntries(ByVal TableName As String)

Listing 7.5 BookInfo.asmx.vb

Imports System.Web.Services Imports System.Data.SqlClient <WebService(Namespace:="http://tempuri.org/")> _ Public Class Service1     Inherits System.Web.Services.WebService #Region " Web Services Designer Generated Code "    'Generated code not shown. #End Region <WebMethod()> Public Function GetEntries(ByVal TableName As String) _ Ä  As String()    Try      Dim Entries(1) As String      Dim Connection As New SqlConnection("Initial Catalog=" & _ Ä      "Duwamish7vb;Data Source=(local);User ID=sa;password=;")      Connection.Open()      Dim Query As String = "SELECT * From " & TableName      Dim DataSetObj As New DataSet()      Dim Adapter As SqlDataAdapter = New _         SqlDataAdapter(Query, Connection)      Dim CmdBuilder As SqlCommandBuilder = New _ Ä      SqlCommandBuilder(Adapter)      Adapter.Fill(DataSetObj)      Dim I As Integer      For I = 0 To DataSetObj.Tables(0).Rows.Count - 1        ReDim Preserve Entries(I + 1)        Entries(I) = DataSetObj.Tables(0).Rows(I).ItemArray(1)      Next      Connection.Close()      GetEntries = Entries      Catch Ex As Exception        Throw Ex      End Try    End Function End Class

In general, the BookInfo web service eliminates the need for a program to perform operations directly with the database. Instead, programs can call the web service methods to query the database. To retrieve an array that contains the author names, for example, the program can simply call the web service method, passing to the method the string authors, which specifies the table that contains the information the program requires. To create the BookInfo web service, perform these steps:

1. Within Visual Studio .NET, select the File menu New Project option. Visual Studio .NET will display the New Project dialog box.

2. Within the New Project dialog box Project Types list, click Visual Basic Projects. Then, within the Templates field, click ASP.NET Web Service. Finally, within the Location field, specify the folder within which you want to store the program and the program name BookInfo. Select OK. Visual Studio .NET will display a page onto which you can drag and drop the service’s components.

3. Select the View menu Code option. Visual Studio .NET will display the program’s source code.

The web service’s processing is quite similar to the code presented throughout this chapter. To start, the service creates a connection to the database. Then, the code uses the table name passed to the method as a parameter to specify the table the SELECT query should use. Next, the code uses a DataSet object to retrieve, in this case, the list of either authors or publishers. The code then uses a For loop to move the query’s result, assigning each publisher name or author name to the array.

Note 

For simplicity, the BookInfo web service does not test the name of the table the caller passes to the GetEntries method. As such, a programmer could use the service to retrieve information about any table within the Duwamish7vb database. If you are implementing a production web service, you should perform such testing to better secure database access. In addition, the BookInfo web service makes use of the fact that both author names and publisher names appear in column one. To provide a more functional method, you would want the caller to provide a table name and column name.

The Visual Basic .NET program in Listing 7.6, ClientDBAccess.vb, uses the BookInfo web service to interact with the Duwamish7vb database. When you run the program, your screen will display a form that contains two buttons and a text box similar to that shown in Figure 7.5. If you click the Authors button, the program will display a list of the authors the database contains. Likewise, if you click the Publishers button, the program will display the publisher list.

Figure 7.5: Using the BookInfo web service to interact with a database

To create the ClientDBAccess.vb program, perform these steps:

1. Within Visual Studio .NET, select the File menu New Project option. Visual Studio .NET will display the New Project dialog box.

2. Within the New Project dialog box Project Types list, click Visual Basic Projects. Then, within the Templates field, click Windows Application. Finally, within the Location field, specify the folder within which you want to store the program and the program name ClientDBAccess. Select OK. Visual Studio .NET will display a page onto which you can drag and drop the program’s controls.

3. Using the Toolbox, drag and drop the buttons and text box previously shown in Figure 7.5 onto the form.

4. Select the Project menu Add Web Reference option. Visual Studio .NET will display the Add Web Reference dialog box.

5. Within the Address field, type localhost/BookInfo/Service1.asmx?WSDL and press Enter. The dialog box will load the file’s contents. Click the Add Reference button.

6. Select the View menu Code option. Visual Studio .NET will display the program’s source code. Within the source code add the program statements in Listing 7.6.

Listing 7.6 ClientDBAccess.vb

Private Sub Button1_Click(ByVal sender As System.Object, _ ÄByVal e As System.EventArgs) Handles Button1.Click    Dim WebServiceObject As New localhost.Service1()    Dim Results() As String    Try      Results = WebServiceObject.GetEntries("Authors")      TextBox1.Text = ""      Dim I As Integer      For I = 0 To Results.Length - 1        TextBox1.Text = TextBox1.Text & Results(I) & vbCrLf      Next    Catch Ex As Exception      TextBox1.Text = "Exception in Web service: " & Ex.Message    End Try End Sub Private Sub Button2_Click(ByVal sender As System.Object, _ ÄByVal e As System.EventArgs) Handles Button2.Click Dim WebServiceObject As New localhost.Service1()    Dim Results() As String    Try      Results = WebServiceObject.GetEntries("Publishers")      TextBox1.Text = ""      Dim I As Integer      For I = 0 To Results.Length - 1        TextBox1.Text = TextBox1.Text & Results(I) & vbCrLf      Next    Catch Ex As Exception      TextBox1.Text = "Exception in Web service: " & Ex.Message    End Try End Sub

The program’s processing is quite straightforward. Depending on the button the user selects, the program uses the BookInfo web service to retrieve an array of strings that contains either the author names or publisher names. The code then uses a For loop to move through the array.