Lesson 2: Defining a Report Dataset for an ADO.NET DataSet from a Web Service

Article
12/03/2008

New: 17 July 2006

In this lesson you will create a data source connection to a Web Service that returns an XML System.Data.DataSet object. You can create and deploy a Web service that returns an XML System.Data.DataSet object or use an existing Web service available to you. You will define a query string that specifies the data you want to retrieve for a report dataset from the XML System.Data.DataSet returned by the Web service.

For this tutorial, a Web service retrieves employee and manager information calling the stored procedure uspGetEmployeeManagers in the AdventureWorks sample database. This stored procedure accepts an EmployeeID value and produces a result set that shows the employee-manager relationship recursively to the top-level employee.

Procedures

To create a Web service that returns an ADO.NET DataSet (Optional)

In Microsoft Visual Studio, from the File menu, click New, then click Web Site.

The New Web Site dialog box opens.

Note

If you do not see Web Site as an option, check the requirements section for Tutorial: Using XML Data in a Report.
In the Templates pane, select ASP.NET Web Service.
In the Location drop-down list, select HTTP.
In the Language drop-down list, select Visual Basic or C#.
Click the Browse button.

The Choose Location dialog box opens.
In the Open a Web site pane, verify the Local IIS button is selected.
On the Web site tree control, navigate to the parent folder for this new Web site.
Click the Create New Web Application button.
Type in a name for your Web site.
Click Open.
Click OK.

The App_Code/Service code page for the programming language you selected in step 4 opens in Visual Studio, showing the sample WebMethod HelloWorld.

Note

The default namespace is http://tempuri.org/. This namespace will be used in the query when you define a report dataset below. If you change this default, you will need to change the query.

Add the following two namespace references to the top of the source file for Visual Basic or C#.

using System.Data;
using System.Data.SqlClient;

Imports System.Data
Imports System.Data.SqlClient

Replace the HelloWorld WebMethod with the following Visual Basic or C# code.

[WebMethod]
public DataSet  GetEmployeeManagers(String EmployeeID)
{
  Int32 val = Int32.Parse(EmployeeID);
  DataSet ds = new DataSet("Results");
  try
  {
    if (val < 1)
    val = 1;
    else if (val > 290) val = 290;
    SqlDataAdapter da = new 
SqlDataAdapter(@"uspGetEmployeeManagers " + val.ToString(),
  @"Data Source=localhost; Initial Catalog=AdventureWorks; " +
  @"Integrated Security=true");
  da.Fill(ds);
  }
  catch (Exception e)
  {
    throw;
  }
  return ds;
 }

<WebMethod()> _
Public Function GetEmployeeManagers(ByVal EmployeeID As String) As DataSet
  Dim ds As DataSet = New DataSet("Results")
  Try
    Dim val As Integer = Integer.Parse(EmployeeID)
    If (val < 1) Then
       val = 1
    ElseIf (val > 290) Then
       val = 290
    End If
    Dim da As SqlDataAdapter = _
        New SqlDataAdapter("uspGetEmployeeManagers " + _
         val.ToString(), _
       "Data Source=localhost; Initial Catalog=AdventureWorks; " + _
            "Integrated Security=true")
        da.Fill(ds)
    Catch e As Exception
    Throw
  End Try
  Return ds
End Function

On the Build menu, click Build Web Site.
On the Build menu, click Publish Web Site.

To test your published Web service (Optional)

Open Internet Explorer.
In the Address bar, type your Web service URL.

For example, https://localhost/XMLTutorial/Service.asmx.

The Web service default page appears.
Click GetEmployeeManagersFromAdventureWorks.
Type in an EmployeeID value between 1 and 290.
Click Invoke.

The Web service retrieves a dataset with the employee managers from the AdventureWorks database using the stored procedure uspGetEmployeeManagers.

Note

If you get the message "Execute permission denied on object", you need to grant execute permission on the AdventureWorks stored procedure for ASPNETUser, as described in the following procedure.

To grant execute permissions for the stored procedure (Optional)

Open Microsoft SQL Server Management Studio.
Connect to the Database Engine where the AdventureWorks sample database is installed.
In Object Explorer, expand the Databases node, then the AdventureWorks node, then the Programmability node, and then the Stored Procedures node.
Right-click dbo.uspGetEmployeeManagers, and choose Properties.
In the Select a page pane, click Permissions.
For the Users or roles pane, click the Add button.
Click the Browse button. Select [ASPNETUser].
Click OK.

You have added permission for anyone running ASP.NET to execute the uspGetEmployeeManagers stored procedure.

To define a report dataset for the Web service

Start Report Designer and open the report server project created for this tutorial.
In Data view, select New Dataset. Type a name for the dataset (for example, XMLWebDataSet).
In the Dataset dialog box, in Data source, select New Data Source. The Data Source dialog box appears.
Type a name for the data source (for example, XMLWebDataSource).
In Type, select XML.
In Connection string, type the following URL to the Report Server Web service:

https://localhost/XMLTutorial/Service.asmx
In the Credentials tab, select Use Windows Authentication (Integrated Security).
Click OK to save your changes and close the Data Source dialog box.
On the Report menu, click Report Parameters. The Report Parameters dialog box opens.
Click Add.
In the Properties section, in the Name text box, type EmployeeID.
In the Prompt text box, type Employee ID.
Click OK.
On the Data tab, click the Edit Selected Dataset (…) button on the toolbar. The Dataset dialog box opens.
Click the Parameters tab.
In the Name column, type EmployeeID.
Click in the Value text box to enable the drop-down list, and select =Parameters!EmployeeID.Value. This maps the dataset parameter to the report parameter.
Click OK.

In the Dataset dialog box, type the following query using the namespace version information that you verified in step 1:

<Query>
    <SoapAction>
        http://tempuri.org/GetEmployeeManagers
    </SoapAction>
    <Method Namespace="http://tempuri.org/" 
        Name="GetEmployeeManagers">
    </Method>
</Query>

Click Run (!) button on the toolbar. The Define Query Parameters dialog box opens.
In the Parameter Value column, enter an EmployeeID number between 1 and 290.
Click OK. The dataset is added to the Datasets window.
Examine the result set. By default, a query extracts data from all XML elements and attributes down to the leaf node on the first XML path it finds in the dataset.
Paste the following ElementPath into the Query pane just after the <Query> tag.
```
<ElementPath IgnoreNamespaces="True">
    GetEmployeeManagersResponse {}/
    GetEmployeeManagersResult/diffgram{}/
    Results {}/Table 
</ElementPath>
```
This provides the query with an element path specifying which elements to use for retrieving data in the result set. In this case, the empty brackets ({}) after the nodes GetEmployeeManagersResponse, GetEmployeeManagersResult, diffgram, and Results, all instruct the query to skip data from these nodes. The Table node with no brackets instructs the query to retrieve all nodes and attributes for that node.

Note

To specify which Table elements to retrieve for the dataset fields collection, list them after the Table element inside curly brackets, as shown in the following example: Table {FirstName, LastName, EmployeeID, ManagerID, RecursionLevel}.
Click Run (!) to view the result set. The changes made in the ElementPath element change the result set returned by the query.
When you are satisfied with the result set, click the Refresh Fields () button on the toolbar. This saves the report definition and updates the view of fields in the Report Datasets window to show all the fields you can use.

Next Steps

You have successfully defined a report dataset from an XML System.Data.DataSet object returned by a Web service. When the report is processed, data from each XML Table element and its attributes are retrieved from the Web service. Next, you will create a report dataset from XML embedded in the report. See Lesson 3: Defining a Report Dataset from Embedded XML Data.

Share via

Lesson 2: Defining a Report Dataset for an ADO.NET DataSet from a Web Service

Procedures

Next Steps

See Also

Concepts

Other Resources

Help and Information

Additional resources