Data Retrieval Made Easy

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 exposedata to different applications using XML to transfer data.

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

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

Creating the Web Service

Open Microsoft Visual Studio (any 2005 version) and createa New Web Site ASP.NET Web Service. Make sure the name of your newly created Webservice 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 databaseusing Visual Studio s new ASP.NET configuration tool. Click on Website in thetop menu, then click on ASP.NET Configuration. A Web page will open in abrowser window (see Figure 1).

Figure1: Web Site AdministrationTool.

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

Name: NorthwindConnString

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

Figure 2: Web Site AdministrationTool Application setting.

Creating Functions to Get the Data from the Northwind Database

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

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

Imports System.Data

Imports System.Data.SqlClient

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

'Function to get data from database

 Public FunctionGetSalesByYear(ByVal Beginning_Date As DateTime, ByVal Ending_Date As DateTime)As DataSet

   ' Create Instance ofConnection and Command Object

   Dim objSQLConn As NewSqlConnection(ConfigurationManager.AppSettings("NorthwindConnString"))

   Dim objSQLAdapter AsNew SqlDataAdapter("Sales by Year", objSQLConn)

   Dim dsTemp As NewDataSet

   ' Mark the Command as aSPROC

   objSQLAdapter.SelectCommand.CommandType = CommandType.StoredProcedure

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

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

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

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

   Try

     ' Execute the commandby 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 servicewith 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

   ReturnGetSalesByYear(Beginning_Date, Ending_Date)

End Function

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

Running the Web Service

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

Figure 3: Web service page.

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

Beginning_Date: 1/1/98

Ending_Date: 12/1/98

Click on Invoke (see Figure 4); this will make the call toyour database and return the data as XML. Figure 5 shows you the XML outputafter 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 adatabase, executes a stored procedure, and returns data as XML. This Webservice is returning the data as a dataset; this means that you could simplybind it to different objects to display the data in a more elegant way insteadof plain XML. Next, I ll show you how to consume a Web service to display thedata it returns in a GridView control.

Creating a Web Reference to the Web Service

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

Figure 6: Adding a Web reference toyour project.

With the window in Figure 6 open, click on the Servicelink (this is the default name of the Web service we created), then name theWeb Reference WS_Northwind and click on Add Reference. Your Solution Explorertab 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 yourproject in Solution Explorer, then go to File | New | File to add a new WebForm file (see Figure 8).

Figure 8: Adding a Web form to yourproject.

Drag and drop onto the newly created Web form a GridViewcontrol and a Button control. Change the text of the Button control to GetData ; 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 Codeview. Type the following code:

Protected Sub Button1_Click(ByVal sender As Object, ByVal e AsSystem.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 yourbrowser, simply click the Get Data button and you should see a populateddatagrid (see Figure 10).

Figure 10: Gridview displaying datafrom WebService.

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

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

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