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.
