Using an ADO.NET DataSet as a Reporting Services Data Source

Click here to download the Visual Basic sample code for this article, and here to download the C# code.

Contents

Introduction
Data Processing Extension Basics
Implementing the Extension
Deploying the Extension
Creating the Report
Conclusion
Related Books

Introduction

Reporting Services provides access to SQL Server, Oracle, ODBC, and OLE DB data sources as part of its standard features. For many reporting scenarios, connecting to a database and running a query is all that is needed to get all the information you need to report upon. But what happens if you want to use a DataSet as your data source? For example, maybe you already have a middle tier that processes your data to conform to your business logic and produces a DataSet as a result. Or maybe you don't, but you nonetheless want to manipulate your raw data prior to reporting in ways that are a better fit for a Microsoft Visual Basic or C# implementation than a SQL implementation, and a DataSet would be the logical end result of such processing. Well, fortunately, it is possible to do this. It is even relatively easy, once you work out what parts of the data processing extension interfaces really have to be implemented to wrap up a DataSet in a way that Reporting Services can use.

In this article, we'll take a look at creating and deploying a simple data processing extension that can be used to provide DataSet data to a Reporting Services report.

Data Processing Extension Basics

Reporting Services allows you to expand or customize the data sources available to you by means of data processing extensions. A data processing extension is an assembly containing the implementation of a set of interfaces available in the Microsoft.ReportingServices.DataProcessing namespace. There are seven interfaces that must be implemented in any data processing extension:

Table 1. The seven interfaces in a data processing extension

Interface	Description
IDbConnection	Represents a unique session with a data source. In the case of a client/server database system, the session may be equivalent to a network connection to the server.
IDbTransaction	Represents a local transaction.
IDbCommand	Represents a query or command that is used when connected to a data source.
IDataParameter	Represents a parameter or name/value pair that is passed to a command or query.
IDataParameterCollection	Represents a collection of all parameters relevant to a command or query.
IDataReader	Provides a method of reading a forward-only, read-only stream of data from your data source.

There are also several optional interfaces that provide additional functionality for connections, transactions, and so on, but the enhancements they provide are not needed in a DataSet data processing extension. If you'd like to know more about these additional interfaces, take a look at the Preparing to Implement a Data Processing Extension topic in the Reporting Services documentation.

From looking at the interfaces listed above, you can see that building a data processing extension is rather similar to building a Microsoft .NET Framework data provider. Many of the interfaces are named the same and provide similar but often not identical functionality, so if you have worked with the System.Data interfaces before, make sure to keep the differences in mind.

For the purposes of accessing a DataSet, half of the required interfaces need only the most minimal implementation, and the remaining interfaces can be implemented with fairly basic functionality. Let's take a look at what really needs to happen to get this extension working.

Implementing the Extension

The sample application that this article is based on reads data from two or more XML files, then aggregates the data from these files into one Table in one DataSet. The file names are provided as the command text. The schema to verify the file structure is read from the Report Server config file using a Connection property. This file and schema information is used to create a DataSet that provides the data source for the DataReader. The Report Designer will use DataReader.Read to access the DataSet data for the report.

Paying Your Dues

Open a new Class Library project in Microsoft Visual Studio, and add a reference to Microsoft.ReportingServices.Interfaces.dll. It contains the Microsoft.ReportingServices.DataProcessing namespace that you'll need to reference for the data processing extension interfaces.

Our first order of business is to take care of the classes that implement the IDbTransaction, IDataParameter, and IDataParameterCollection interfaces. These classes are included only because they are required in any data processing extension. In this implementation, since we don't make a database connection, do any data modification, or use any kind of structured language to communicate with a relational database, they are not used for anything. They can all be implemented in a skeletonized form, as illustrated in the sample code accompanying this article.

Note   It is possible that you might want to implement these interfaces more fully, depending on your application. In cases where you want to retrieve relational data, process it, create a DataSet, and then report on that, clearly these would be of use.

The Heart of the Matter

Once the required-but-unnecessary classes are out of the way, let's take a look at the IDbConnection, IDbCommand, and IDataReader implementations that actually do the work in this extension.

Since we aren't connecting to a database, we're using the connection class for two things, really. The first is to use the SetConfiguration method to retrieve information from the config file about what schema should be used to validate the XML data. Storing the schema info makes the app a bit more flexible, as you can always just update it to conform to the kind of data you want to use. There are actually two config files used. Since a report developer may not have access to the target Report Server, the Report Designer and the Report Server need separate sources of configuration information. RSReportServer.config accesses the extension information for deployed reports, and RSReportDesigner.config accesses the extension information that is used in the designer UI. These two config files should have identical entries identifying the data processing extension.

The second thing we use the Connection class for is the CreateCommand method, to create a new Command object using the Connection object overload. This will provide access to the configuration info in the context of the Command object. All the other members of IDbConnection are given the minimum required implementation.

using System;using System.Data;using System.Configuration;using System.Xml;using Microsoft.ReportingServices.DataProcessing;namespace Microsoft.Samples.ReportingServices.DataSetExtension{   public class DSXConnection       : Microsoft.ReportingServices.DataProcessing.IDbConnection   {      private string _connString;      // IDbConnection.ConnectionTimeout defaults to 15 seconds.      private int _connTimeout = 15;      private ConnectionState _state = ConnectionState.Closed;      private string _locName = "DataSet Data Extension";      internal string _xmlSchema;      // Default constructor.      public DSXConnection()      {      }      // Connection string constructor overload.      public DSXConnection(string connString)      {         _connString = connString;      }      public string ConnectionString      {         get         {            return _connString;         }         set         {            _connString = value;         }      }      public int ConnectionTimeout      {         get         {            return _connTimeout;         }      }      public ConnectionState State      {         get          {            return _state;         }      }      // Not used.      public          Microsoft.ReportingServices.DataProcessing.IDbTransaction          BeginTransaction()      {         return (null);      }      // Not used.      public void Open()      {         _state = ConnectionState.Open;         return;      }      // Not used.      public void Close()      {         _state = ConnectionState.Closed;         return;      }      // Implemented.      public          Microsoft.ReportingServices.DataProcessing.IDbCommand          CreateCommand()      {         // Create a Command object and pass in the         // Connection object to provide config info.           return new DSXCommand(this);      }            public string LocalizedName      {         get         {            return _locName;         }      }      // Implemented. Inherited from       // IExtension through IDbConnection.      public void SetConfiguration(string configuration)      {         // Get the XML schema file          // from the config file settings.         XmlDocument schemaDoc = new XmlDocument();         schemaDoc.LoadXml(configuration);         if (schemaDoc.DocumentElement.Name             == "XSDConfiguration")         {            foreach (XmlNode schemaChild in                schemaDoc.DocumentElement.ChildNodes)            {               if(schemaChild.Name == "XSDFile")               {                  _xmlSchema = schemaChild.InnerText;               }               else               {                  throw new Exception                     ("Cannot find XSD configuration element.");               }            }         }         else         {            throw new Exception               ("Error returning data from the configuration file.");         }      }      public void Dispose()       {      }      }}

Next up is the implementation of IDbCommand. As with the Connection class, there are only a handful of members in the Command class that we need to use to accomplish our goal. In this case, those are the Connection object overload of the constructor, the CommandText property, and the CommandBehavior overload of the ExecuteReader method.

The overloaded constructor gives us a reference to the Connection object. All we want from that is access to the XML schema information that is set as an internal variable in DSXConnection. This variable is subsequently passed to the DataReader implementation in the call to ExecuteReader so that it can be used in processing the XML data sources.

The CommandText property takes the comma delimited string that identifies the XML files to pull data from, and is entered in Report Designer when you are setting up a new report.

The ExecuteReader call creates the DataReader, creates a DataSet to serve as its data source, and returns the reader to the caller, which in this case is our report. Note that you must implement the CommandBehavior overload to support the SchemaOnly CommandBehavior value. Report Designer and Report Server both use this overload rather than the parameterless version to obtain field information in addition to the data.

using System;using System.Data;using System.ComponentModel;using Microsoft.ReportingServices.DataProcessing;namespace Microsoft.Samples.ReportingServices.DataSetExtension{   public class DSXCommand       : Microsoft.ReportingServices.DataProcessing.IDbCommand   {      private string _cmdText;           private DSXConnection _connection;      // IDbCommand.CommandTimeout defaults to 30 seconds.      private int _cmdTimeout = 30;      private Microsoft.ReportingServices.DataProcessing.CommandType          _cmdType;       private DSXParameterCollection _parameters =          new DSXParameterCollection();                           // Default constructor.      public DSXCommand()      {      }      // Command text constructor overload.      public DSXCommand(string cmdText)      {         _cmdText = cmdText;      }      // Connection object constructor overload.      public DSXCommand(DSXConnection connection)      {         _connection = connection;      }       public string CommandText      {         get { return _cmdText;  }         set  { _cmdText = value;  }      }      public int CommandTimeout      {         get  {return _cmdTimeout;}         set  {_cmdTimeout = value;}      }      public Microsoft.ReportingServices.DataProcessing.CommandType          CommandType      {         get { return _cmdType; }         set { _cmdType = value; }      }      public          Microsoft.ReportingServices.DataProcessing.IDataParameterCollection          Parameters      {         get  { return _parameters; }      }      public          Microsoft.ReportingServices.DataProcessing.IDbTransaction          Transaction      {         get { return (null); }         set { throw new NotSupportedException(); }      }      // Not used.      public void Cancel()      {         throw new NotSupportedException();      }      // Not used.      public          Microsoft.ReportingServices.DataProcessing.IDataParameter          CreateParameter()      {         return (null);      }      // Implemented.      public          Microsoft.ReportingServices.DataProcessing.IDataReader          ExecuteReader         (Microsoft.ReportingServices.DataProcessing.CommandBehavior behavior)      {         try         {            // Create the DataReader.            DSXDataReader testReader =                new DSXDataReader(_cmdText);            // Call the custom method that             // populates the DataSet.            testReader.CreateDataSet(_connection._xmlSchema);            // Return the DataReader.            return testReader;         }         catch(Exception e)         {            throw new Exception(e.Message);         }      }      public void Dispose()       {      }           }}

Finally, there is the DataReader class implementing IDataReader. This class is the most fully fleshed out, as most of its members are used by Report Designer to retrieve the data to be displayed. There are also two custom functions, CreateDataSet and ParseCmdText that provide additional functionality. ParseCmdText parses the input string of comma-delimited XML source files, and CreateDataSet merges them into a single DataSet for reporting. The resulting DataSet is then used as the source for the data that the DataReader returns.

using System;using System.Data;using System.Xml;using System.Collections;using Microsoft.ReportingServices.DataProcessing;namespace Microsoft.Samples.ReportingServices.DataSetExtension{   public class DSXDataReader       : Microsoft.ReportingServices.DataProcessing.IDataReader   {      private string _cmdText;      private int _currentRow = 0;      private int _fieldCount = 0;      private string _fieldName;      private int _fieldOrdinal;      private Type _fieldType;           private object _fieldValue;      private DataSet _ds = null;      // Default constructor      internal DSXDataReader()      {      }      //Command text constructor overload.      internal DSXDataReader(string cmdText)      {            _cmdText = cmdText;      }            // Implemented. Will be called      // by the Report Server to       // return DataSet data.      public bool Read()      {         _currentRow++;         if (_currentRow >= _ds.Tables[0].Rows.Count)          {            return (false);         }          else          {            return (true);         }      }      public int FieldCount      {         get          {             _fieldCount = _ds.Tables[0].Columns.Count;               return _fieldCount;          }      }      public string GetName(int i)      {         _fieldName = _ds.Tables[0].Columns[i].ColumnName;         return _fieldName;      }      public Type GetFieldType(int i)      {         _fieldType =             _ds.Tables[0].Columns[i].DataType;         return _fieldType;      }      public Object GetValue(int i)      {         _fieldValue =             _ds.Tables[0].Rows[this._currentRow][i];         return _fieldValue;      }      public int GetOrdinal(string name)      {         _fieldOrdinal =             _ds.Tables[0].Columns[name].Ordinal;         return _fieldOrdinal;      }      // Input parameter should be the path       // to the .xsd file that was retrieved       // from the Connection.SetConfiguration call.        internal void CreateDataSet(string schemaFile)      {                  // Open an XML doc to hold the data.         XmlDocument xmlDoc = new XmlDocument();         // Create the DataSet.         DataSet ds = new DataSet("Customers");         // Create the schema for the DataSet.         ds.ReadXmlSchema(schemaFile);         // Parse the command text string for the files.            string[] parameters = this.ParseCmdText();         // Get the XML data and          // merge it into the DataSet.            try         {            for(int i=0;i<parameters.GetLength(0);i++)            {               DataSet tempDs = new DataSet();               tempDs.ReadXml(parameters[i]);               ds.Merge(tempDs);            }                  }         catch (Exception e)         {            throw new Exception(e.Message);         }         // Set the DataSet variable used in         // the rest of the DataReader members         // to the one just produced.         _ds = ds;         // Set the current row to -1         // to prepare for reading.         _currentRow = -1;      }      private string[] ParseCmdText()      {         // Check format of command text.         if (_cmdText.IndexOf(",") != -1)         {            string[] dsParams =                _cmdText.Split(new Char[]{','});            // In production code, you'd             // want more error handling here             // confirming that the string values             // are appropriate XML file names, etc.            return dsParams;         }         else            throw new ArgumentException               ("The CommandText value is not in the appropriate format.");      }      public void Dispose()       {      }   }}

Of course, it is possible that you already have a middle-tier component that does some processing and produces a DataSet for you, and you'd like to use that rather than putting together a new DataSet in the extension code. That's easy enough—just add a reference to the assembly and call the method that returns the DataSet from there, like:

using System;using System.Data;using System.Xml;using System.Collections;using Microsoft.ReportingServices.DataProcessing;using ThisCompany.ThisAssembly;namespace ThisCompany.ThisNamespace.DataSetExtension{   public class ExtensionDataReader : Microsoft.ReportingServices.DataProcessing.IDataReader   {         private DataSet _ds = null;      // More variables...      internal ExtensionDataReader(thisParameter)       {         // Get the DataSet from the middle-tier assembly.         this._ds = new ThisCompany.ThisAssembly.Customer.IntegrateDataSources(thisParameter);                  // Set the current row.         currentRow = -1;      }   ...

To recap by making the workflow explicit here, what the Report Server will do when running this report is:

• Call the DSXConnection default constructor.
• Call DSXConnection.CreateCommand, which uses the DSXCommand Connection overload constructor to create the Command object.
• Call DSXCommand.ExecuteReader(CommandBehavior), which in turn:
  • Calls the DSXDataReader command text overload constructor to create the DataReader object.
  • Calls DSXDataReader.CreateDataSet to create the DataSet data source. This method in turn calls DSXDataReader.ParseCmdText to verify the input.
• Processing returns to DSXCommand.ExecuteReader(CommandBehavior), which then hands the DataReader back to Report Server.

And that's it. Really.

You can go ahead and build the solution, and then we're on to deployment.

Deploying the Extension

First we'll need to register the extension with Reporting Services by copying the extension DLL to the Report Server and Report Designer directories and adding the appropriate entries for it to their config files.

Copy RSCustomData.DLL and paste it into the Report Server bin directory (C:/Program Files/Microsoft SQL Server/MSSQL/Reporting Services/ReportServer/bin by default) and the Report Designer directory (C:/Program Files/Microsoft SQL Server/80/Tools/Report Designer by default). Then open the Report Server config file, RSReportServer.config, located by default in C:/Program Files/Microsoft SQL Server/MSSQL/Reporting Services/ReportServer. You'll need to modify this file to add a new <Extension> child node in the <Data> node to document any settings that the extension will use. The <Extension> element requires two attributes; Name, which is the unique name you choose for your extension, and Type, which is the fully qualified name of your connection class plus the name (minus the .dll extension) of your assembly, separated by a comma. That is all that is required—you can skip the <Configuration> node if you don't use it, although we are making use of it in this sample. Your new node should look like this:

<Data>   <Extension Name="DataSet"Type="Microsoft.Samples.ReportingServices.DataSetExtension.DSXConnection,RSCustomData">      <Configuration>         <XSDConfiguration><XSDFile>C:/customer.xsd</XSDFile>         </XSDConfiguration>      </Configuration>   </Extension></Data>

Save and close the Report Server config file, and open the Report Designer one, RSReportDesigner.config in C:/Program Files/Microsoft SQL Server/80/Tools/Report Designer. Add an identical <Extension> node to the <Data> section in this file as well. You will also need to add a slightly different <Extension> element to the <Designer> node as well. This <Extension> element also contains Name and Type attributes. The Name attribute should contain the same unique extension name that you supplied in the <Extension> element in the <Data> node. The Type attribute is the fully qualified name of the generic query designer class plus the name (minus the .dll extension) of the assembly that contains it, separated by a comma.

<Extension Name="DataSet" Type="Microsoft.ReportDesigner.Design.GenericQueryDesigner,Microsoft.ReportingServices.Designer"/>

The generic interface makes it possible for the designer UI to work with the extension using simple query and results panes, rather than the full-blown visual query designer best suited to relational databases.

Code Access Security

The next thing we'll want to do is add entries to the Report Server and Report Designer policy files explicitly setting the code access permissions for the extension. Data processing extensions require FullTrust permissions to run properly.

Open the Report Server policy file, rssrvpolicy.config, located by default in C:/Program Files/Microsoft SQL Server/MSSQL/Reporting Services/ReportServer, and create a new <CodeGroup> node that matches the following:

<CodeGroup class="UnionCodeGroup"version="1"PermissionSetName="FullTrust"Name="DataSetExtensionGroup"Description="This code group grants data extensions full trust."><IMembershipCondition class="UrlMembershipCondition"version="1"           Url="C:/Program Files/Microsoft SQL Server/MSSQL/Reporting Services/ReportServer/bin/RSCustomData.dll"      /></CodeGroup>

Then add an identical <CodeGroup> node to the Report Designer policy file rspreviewpolicy.config, located by default in C:/Program Files/Microsoft SQL Server/80/Tools/Report Designer.

These entries grant the DataSet data processing extension FullTrust permissions, based on URL evidence that identifies the extension assembly. For more information on managing code access security, see Code Access Security in SQL Server 2000 Reporting Services.

Creating the Report

Now that we've created and deployed the DataSet extension, it's a snap to use it in a report. Open Visual Studio and start a new report project. Right-click on the Shared Data Sources folder and select Add New Data Source. In the Type drop-down, you should see an entry for the DataSet data processing extension:

Figure 1. The Shared Data Source folder

Select the extension entry, and on the Credentials pane, select the No Credentials radio button—since there is no database connection to make, no credentials are necessary. Save this data source.

Right-click on the Reports folder and select Add New Report. Start to step through the Report Wizard that comes up. On the Select the Data Source screen, accept the default selection on the DataSet extension data source you just created:

Figure 2. The Select the Data Source screen

On the Design the Query screen, enter in the comma-delimited string containing the full paths to all of your XML source files:

Figure 3. The Design the Query Screen

Finish out the report using whatever settings you like—for the purposes of this sample I just accepted the defaults. When previewed, the sample report should look similar to the following—I monkeyed with the field layout a bit for space considerations:

Figure 4. Reporting Services sample report

To deploy your report, specify your target report server (usually http://MachineName/ReportServer) in your project properties, then select Debug, Start to deploy the solution and view your report in the browser.

Conclusion

So, as you can see, it is easy to access your DataSets from Reporting Services, once you work out the salient points of the APIs and the configuration requirements. Give it a whirl the next time you want to leverage some existing data processing functionality in one of your reporting apps.