Data Retrieval Made Easy

Retrieve Data from a Database Using a Web Service

asp:Feature

 

Data Retrieval Made Easy

Retrieve Data from a Database Using a Web Service

 

By Ricardo D. Sanchez

 

Web services are a great solution when you need to expose data to different applications using XML to transfer data.

 

Although Web services are not .NET-specific, by using Visual Studio 2005, the task of creating Web services and applications to consume them is very easy. In this article I ll show you how to create a Web service that will connect to a SQL database, and an application that will connect to the Web service to retrieve the data.

 

For this example we re going to connect to the Northwind sample database using one of its existing stored procedures.

 

Creating the Web Service

Open Microsoft Visual Studio (any 2005 version) and create a New Web Site ASP.NET Web Service. Make sure the name of your newly created Web service project is WS_Northwind (to maintain consistency with this article). The code added by Visual Studio is a complete, albeit very simple, Web service. It even contains a WebMethod for the famous Hello World .

 

Configuring the Connection to the Database

Let s create the connection to our Northwind database using Visual Studio s new ASP.NET configuration tool. Click on Website in the top menu, then click on ASP.NET Configuration. A Web page will open in a browser window (see Figure 1).

 


Figure1: Web Site Administration Tool.

 

Next, click on Application Configuration. Under Application Settings, click on Create Application Settings. Type the following to create a connection string, then click Save and OK (see Figure 2):

 

Name: NorthwindConnString

Value: server=localhost;database=Northwind;uid=user;password=password; (Replace the values for user and password with a user name and password for your server).

 


Figure 2: Web Site Administration Tool Application setting.

 

Creating Functions to Get the Data from the Northwind Database

We now need to create the functions that will call the Northwind stored procedure using the connection string we ve just created to then create WebMethods that will call the functions that return a data set.

 

With the service.vb page open (this is the page that was created automatically by Visual Studio when you created the WebService project), type the following on the very top:

 

Imports System.Data

Imports System.Data.SqlClient

 

Then, under the Hello World WebMethod function, type the following function (which will retrieve the data from the stored procedure):

 

'Function to get data from database

 Public Function GetSalesByYear(ByVal Beginning_Date As DateTime, ByVal Ending_Date As DateTime) As DataSet

   ' Create Instance of Connection and Command Object

   Dim objSQLConn As New SqlConnection(ConfigurationManager.AppSettings("NorthwindConnString"))

   Dim objSQLAdapter As New SqlDataAdapter("Sales by Year", objSQLConn)

   Dim dsTemp As New DataSet

   ' Mark the Command as a SPROC

   objSQLAdapter.SelectCommand.CommandType = CommandType.StoredProcedure

   objSQLAdapter.SelectCommand.Parameters.Add(New SqlParameter("@Beginning_Date", SqlDbType.DateTime))

   objSQLAdapter.SelectCommand.Parameters("@Beginning_Date").Value = Beginning_Date

   objSQLAdapter.SelectCommand.Parameters.Add(New SqlParameter("@Ending_Date", SqlDbType.DateTime))

   objSQLAdapter.SelectCommand.Parameters("@Ending_Date").Value = Ending_Date

   Try

     ' Execute the command by filling the dataset

     objSQLConn.Open()

     objSQLAdapter.Fill(dsTemp)

     objSQLConn.Close()

   Catch ex As Exception

     dsTemp = Nothing

     Throw ex

   Finally

     'clean up

     If objSQLConn.State = ConnectionState.Open Then objSQLConn.Close()

     objSQLConn = Nothing

      objSQLAdapter.Dispose()

     objSQLAdapter = Nothing

   End Try

    ' Return the dataset

   Return dsTemp

 End Function

 

The above code will allow you to populate the Web service with data from the existing Northwind stored procedure SalesByYear. To do this, we need to modify the existing WebMethod that Visual Studio 2005 created. Replace the public function Hello World with the following:

 

_

Public Function SalesByYear(ByVal Beginning_Date As DateTime, ByVal Ending_Date As DateTime) As DataSet

   Return GetSalesByYear(Beginning_Date, Ending_Date)

End Function

 

As you see, we are calling the GetSalesByYear function, which has two parameters (Beginning_Date and Ending_Date) to get the data from the SQL database.

 

Running the Web Service

Click F5 to run your project; you should see a page in your browser similar to that shown in Figure 3.

 


Figure 3: Web service page.

 

Once this page is up, click on the SalesByYear link; this will display a sample page for you to test the Web service (you can only do this if you are running the Web service in your local machine). Then simply type the following dates:

 

Beginning_Date: 1/1/98

Ending_Date: 12/1/98

 

Click on Invoke (see Figure 4); this will make the call to your database and return the data as XML. Figure 5 shows you the XML output after you clicked on Invoke.

 


Figure 4: Testing the Web service.

 


Figure 5: XML data returned by Web service.

 

That s it; you ve created a Web service that connects to a database, executes a stored procedure, and returns data as XML. This Web service is returning the data as a dataset; this means that you could simply bind it to different objects to display the data in a more elegant way instead of plain XML. Next, I ll show you how to consume a Web service to display the data it returns in a GridView control.

 

Creating a Web Reference to the Web Service

Let s proceed by creating a Web reference to the Web server we created earlier. To do this, simply right click in the Solution name under Solution Explorer and select Add Web Reference. When the Add Web Reference window appears, click on Web Services in this Solution (see Figure 6); the window in Figure 7 will be displayed. In a production environment, you ll instead want to type the actual path of the Web service to which you want to connect.

 


Figure 6: Adding a Web reference to your project.

 

With the window in Figure 6 open, click on the Service link (this is the default name of the Web service we created), then name the Web Reference WS_Northwind and click on Add Reference. Your Solution Explorer tab should look similar to the one shown in Figure 7.

 


Figure 7: Solution Explorer.

 

Consuming a Web Service to Display Data in a GridView Control

With your WS_Northwind project still open, select your project in Solution Explorer, then go to File | New | File to add a new Web Form file (see Figure 8).

 


Figure 8: Adding a Web form to your project.

 

Drag and drop onto the newly created Web form a GridView control and a Button control. Change the text of the Button control to Get Data ; after this, your page in design mode should look like Figure 9.

 


Figure 9: Default.aspx (in design mode).

 

Double click the new button control to switch to Code view. Type the following code:

 

Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click

 Me.GridView1.DataSource = (New WS_Northwind.Service).SalesByYear("1/1/1980", "6/1/2006")

 Me.GridView1.DataBind()

End Sub

 

Run your project; once the Default.aspx page loads in your browser, simply click the Get Data button and you should see a populated datagrid (see Figure 10).

 


Figure 10: Gridview displaying data from WebService.

 

That s it, we re done! We have populated our datagrid with data sent by the Web service.

 

This simple example demonstrates how easy it is to create Web services that return any data you choose and how to use data controls such as the GridView to display data returned by the Web service.

 

Ricardo D. Sanchez is a Web developer who has experience designing and developing Web sites and Web applications using primarily .NET technologies. Contact him at mailto:[email protected]. or visit http://www.gdltec.com.

 

 

 

 

Hide comments

Comments

  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.
Publish