Create Reports from Any Data Source Using SQL Server Reporting Services Custom Data Extensions : Page 3

Handling Connections
As noted, this sample CDE doesn't connect to a data source because it gets everything it needs from the query parameters (a serialized dataset or a path to file). But if you build a CDE that needs to connect to data source, you need to flesh out the IDbConnection interface. The most important method of this interface is IDbConnection.Open(). Before calling this method, SSRS will pass the data source properties set at design time to the IDbConnection properties including the connection string (ConnectionString property).

Implementing Command
If you have done any ADO.NET programming, you know that you need to create a command object to use a DataReader. Similarly, the DsCommand class represents the CDE command object. It fulfils two main tasks. First, the IDataParameterCollection.GetParameters() method returns the query parameters defined for the query. If the parameters are defined as placeholders inside the query text, GetParameters would parse the query text. In our case, the query supports only one parameter (DataSource). Therefore, you can just add this parameter to the parameter collection and return the collection to SSRS.

   public IDataParameterCollection GetParameters ()
   {
      DsDataParameterCollection parameters = 
         new DsDataParameterCollection();
      // inform the designer that @DataSource parameter 
      // is needed
      parameters.Add(new DsDataParameter(Util.DATA_SOURCE, 
         m_connection.ConnectionString));
      return parameters;
   }

The second major role of the CDE command object is to prepare and return a DataReader object that implements the IDataReader interface. The ExecuteReader method handles these tasks.

   public IDataReader ExecuteReader()  {
      if (m_connection == null || m_connection.State != 
         System.Data.ConnectionState.Open)
         throw new InvalidOperationException(
         "Connection must be valid and open.");
   
      DsDataReader reader = new DsDataReader(m_connection, 
         m_cmdText, m_parameters);
      reader.LoadDataset();
      return reader;
   }

When SSRS needs the data, it calls the ExecuteReader method, which instantiates a new DsDataReader object and passes the CDE context consisting of the connection string, command text, and parameters. Next, ExecuteReader calls the LoadDataset helper method to load the dataset and returns the reader object to SSRS.

Implementing DataReader
The DataReader object (see the DsDataReader class) is the workhorse of the CDE. It is responsible for loading the dataset and providing forward record navigation. The most interesting method is LoadDataset.

   internal void LoadDataset()   {
      string dataSource = null;
      DsDataParameter parameter = 
         m_parameters.GetByName(Util.DATA_SOURCE) 
         as DsDataParameter;              
      dataSource = parameter.Value.ToString();
   
      m_dataset = GetDataSet(dataSource);           
   
      if (m_cmdText.Trim().ToLower()=="nothing")    
         m_datatable = m_dataset.Tables[0];
      else    
         m_datatable = m_dataset.Tables[m_cmdText];
     
      m_ie = m_datatable.Rows.GetEnumerator();     
   }
   
   private DataSet GetDataSet(string dataSource)   {
      DataSet dataset = new DataSet();
   
      if (dataSource.IndexOf("<") >=0 ) {        
         StringReader reader = new 
            StringReader(dataSource);  
         dataset.ReadXml(reader);
      }
      else {
         FileIOPermission permission = new  
         FileIOPermission(FileIOPermissionAccess.Read, 
            dataSource);
         permission.Assert();
         dataset.ReadXml(dataSource);
      }
   
     return dataset;
   }
   

First, LoadDataset gets the value of the DataSource parameter and passes it to the GetDataSet helper function. GetDataSet uses a simple algorithm to check the dataset format. If the value of the DataSource parameter starts with "<", GetDataSet assumes that DataSource contains a serialized ADO.NET dataset, in which case it deserializes the parameter value back to an ADO.NET dataset. Otherwise, GetDataSet assumes that the parameter value specifies the full path to the dataset file.

Note that I am specifically demanding read CAS (Code Access Security) permission to the physical file. Regardless of the fact that you will configure the CDE assembly for full access, CAS is layered on top of the OS security. For this reason, if you decide to use a persisted dataset, grant the ASP.NET worker process at least read permission to that file.

The rest of the DsDataReader code implements the IDataReader standard property and methods, such as providing a forward-only enumerator (Read method), returning the field count (FieldCount property), getting a field value (GetValue method), etc.

Previous Page

Next Page