Home Library Learn Downloads Support Community
Sign in | United States - English | |
9 out of 12 rated this helpful - Rate this topic

Walkthrough – Generating RDL Using the .NET Framework

SQL Server 2000

This walkthrough illustrates how to write Report Definition Language (RDL) to a report definition file using the XmlTextWriter class. The writer provides a fast, forward-only way of generating XML, thus RDL, and helps you to build report definition documents that conform to the RDL specification. The XmlTextWriter writes to a stream rather than using an object model such as the XML DOM, and so gives better performance.

Typically, you use an XmlTextWriter if you need to write XML as raw data without the overhead of a DOM. The XmlTextWriter is an implementation of the XmlWriter class that provides the API which writes XML to a file stream. The XmlTextWriter class provides several methods that are useful for creating a report definition file. In particular, the following walkthrough shows you how to construct a report definition file using the WriteStartElement, WriteAttributeString, WriteElementString, and WriteEndElement methods.

During the course of this walkthrough, you will accomplish the following activities:

  • Create an application using the Visual Studio .NET Console Application project template.
  • Add a connection to the AdventureWorks2000 sample database.
  • Write code to retrieve a list of fields for the data source.
  • Write code to generate a simple report definition file that can be used to build a report.

Requirements

To complete the walkthrough, you must have the following:

  • Microsoft® SQL Server™ 2000 Reporting Services
  • Microsoft Visual Studio® .NET 2003 or a similar .NET Framework compatible development tool.
  • The AdventureWorks2000 sample database installed to an instance of SQL Server 2000.

Creating the RDL Generator Visual Studio Project

For this walkthrough, you will create a simple console application. This walkthrough assumes you are developing in Microsoft Visual Studio .NET.

To create a console application

  1. On the File menu, point to New, and then click Project to open the New Project dialog box.
  2. Expand either the Visual Basic Projects or the Visual C# Projects folder.
  3. Click the Console Application icon.
  4. In the Name box, enter a name for your project. Enter the name, SampleRDLGenerator.
  5. In the Location box, enter the path where you want to save your project, or click Browse to navigate to the folder.
  6. Click Open. A collapsed view of your project appears in Project Explorer.

    In Project Explorer, expand the project node. A code file with the default name of Class1.cs (Module1.vb for Visual Basic) has been added to your project.

When you have finished creating the application template, replace the contents of the code file with the following:

Copy 
Imports System
Imports System.Collections
Imports System.Data
Imports System.Data.SqlClient
Imports System.IO
Imports System.Text
Imports System.Xml

Namespace SampleRDLGenerator
   Class RdlGenerator
      Private m_connection As SqlConnection
      Private m_connectString As String
      Private m_commandText As String
      Private m_fields As ArrayList
      
      
      Public Shared Sub Main()
         Dim myRdlGenerator As New RdlGenerator()
         myRdlGenerator.Run()
      End Sub 'Main
      
      
      Public Sub Run()
         Try
            ' Call methods to create the RDL
            Me.OpenConnection()
            Me.GenerateFieldsList()
            Me.GenerateRdl()
            
            Console.WriteLine("RDL file generated successfully.")
         
         Catch exception As Exception
            Console.WriteLine(("An error occurred: " + exception.Message))
         
         Finally
            ' Close the connection string
            m_connection.Close()
         End Try
      End Sub 'Run
      
      
      Public Sub OpenConnection()
      End Sub 'OpenConnection
      
      ' TODO: Open a connection to the sample database
      
      Public Sub GenerateFieldsList()
      End Sub 'GenerateFieldsList
      
      ' TODO: Generate a list of fields for a report query
      
      Public Sub GenerateRdl()
      End Sub 'GenerateRdl
   End Class 'RdlGenerator ' TODO: Generate RDL using XmlTextWriter
End Namespace 'SampleRDLGenerator
Copy 
using System;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Text;
using System.Xml;

namespace SampleRDLGenerator
{
   class RdlGenerator
   {
      SqlConnection m_connection;
      string m_connectString;
      string m_commandText;
      ArrayList m_fields;

      public static void Main()
      {
         RdlGenerator myRdlGenerator = new RdlGenerator();
         myRdlGenerator.Run();
      }

      public void Run()
      {
         try
         {
            // Call methods to create the RDL
            this.OpenConnection();
            this.GenerateFieldsList();
            this.GenerateRdl();

            Console.WriteLine("RDL file generated successfully.");
         }

         catch (Exception exception)
         {
            Console.WriteLine("An error occurred: " + exception.Message);
         }

         finally
         {
            // Close the connection string
            m_connection.Close();
         }
      }

      public void OpenConnection()
      {
         // TODO: Open a connection to the sample database
      }

      public void GenerateFieldsList()
      {
         // TODO: Generate a list of fields for a report query
      }

      public void GenerateRdl()
      {
         // TODO: Generate RDL using XmlTextWriter
      }
   }
}

Creating a Connection to the Sample Database

The first step is to create a connection to the AdventureWorks2000 sample database in order to generate a list of fields for the report definition.

To create a connection to AdventureWorks2000

  • Replace the code for the OpenConnection() method in your project with the following code:
Copy 
Public Sub OpenConnection()
   ' Create a connection object
   m_connection = New SqlConnection()
   
   ' Create the connection string
   m_connectString = "data source=localhost;initial catalog=AdventureWorks2000;integrated security=SSPI"
   m_connection.ConnectionString = m_connectString
   
   ' Open the connection
   m_connection.Open()
End Sub 'OpenConnection
Copy 
public void OpenConnection()
{
   // Create a connection object
   m_connection = new SqlConnection();
         
   // Create the connection string
   m_connectString = "data source=localhost;initial catalog=AdventureWorks2000;integrated security=SSPI";
   m_connection.ConnectionString = m_connectString; 
         
   // Open the connection
   m_connection.Open();
}

Note  You should replace the connection string used here with a connection string that is valid for your particular configuration. The previous connection string assumes that you have installed the AdventureWorks2000 database to a local instance of SQL Server.

Retrieving a List of Fields for the Report Definition

Since every report definition should have a list of fields that represent the data in the report, you must generate a fields list from your query.

To generate a fields list

  • Replace the code for the GenerateFieldsList() method in your project with the following code:
Copy 
Public Sub GenerateFieldsList()
   Dim command As SqlCommand
   Dim reader As SqlDataReader
   
   ' Executing a query to retrieve a fields list for the report
   command = m_connection.CreateCommand()
   m_commandText = "SELECT CountryRegion.Name AS CountryName, StateProvince.Name AS StateProvince " & _
      "FROM StateProvince " & _
      "INNER JOIN CountryRegion ON StateProvince.CountryRegionCode = CountryRegion.CountryRegionCode " & _
      "ORDER BY CountryRegion.Name"
   command.CommandText = m_commandText
   
   ' Execute and create a reader for the current command
   reader = command.ExecuteReader(CommandBehavior.SchemaOnly)
   
   ' For each field in the resultset, add the name to an array list
   m_fields = New ArrayList()
   Dim i As Integer
   For i = 0 To reader.FieldCount - 1
      m_fields.Add(reader.GetName(i))
   Next i
End Sub 'GenerateFieldsList
Copy 
public void GenerateFieldsList()
{
   SqlCommand command;
   SqlDataReader reader;

   // Executing a query to retrieve a fields list for the report
   command = m_connection.CreateCommand();
   m_commandText = 
      "SELECT CountryRegion.Name AS CountryName, StateProvince.Name AS StateProvince " +
      "FROM StateProvince " +
      "INNER JOIN CountryRegion ON StateProvince.CountryRegionCode = CountryRegion.CountryRegionCode " +
      "ORDER BY CountryRegion.Name";
   command.CommandText = m_commandText;
   
   // Execute and create a reader for the current command
   reader = command.ExecuteReader(CommandBehavior.SchemaOnly);
   
   // For each field in the resultset, add the name to an array list
   m_fields = new ArrayList();
   for (int i = 0; i <= reader.FieldCount - 1; i++)
   {
      m_fields.Add(reader.GetName(i));
   }
}

Creating Code to Generate the Report Definition File

Now that you have created your connection and retrieved a list of fields for the query, you can generate RDL programmatically using XmlTextWriter.

To generate RDL programmatically

  • Replace the code for the GenerateRdl() method in your project with the following code:
Copy 
Public Sub GenerateRdl()
    ' Open a new RDL file stream for writing
    Dim stream As FileStream
    stream = File.OpenWrite("Report1.rdl")
    Dim writer As New XmlTextWriter(stream, Encoding.UTF8)

    ' Causes child elements to be indented
    writer.Formatting = Formatting.Indented

    ' Report element
    writer.WriteProcessingInstruction("xml", "version=""1.0"" encoding=""utf-8""")
    writer.WriteStartElement("Report")
    writer.WriteAttributeString("xmlns", Nothing, "http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefinition")
    writer.WriteElementString("Width", "6in")

    ' DataSource element
    writer.WriteStartElement("DataSources")
    writer.WriteStartElement("DataSource")
    writer.WriteAttributeString("Name", Nothing, "DataSource1")
    writer.WriteStartElement("ConnectionProperties")
    writer.WriteElementString("DataProvider", "SQL")
    writer.WriteElementString("ConnectString", m_connectString)
    writer.WriteElementString("IntegratedSecurity", "true")
    writer.WriteEndElement() ' ConnectionProperties
    writer.WriteEndElement() ' DataSource
    writer.WriteEndElement() ' DataSources
    ' DataSet element
    writer.WriteStartElement("DataSets")
    writer.WriteStartElement("DataSet")
    writer.WriteAttributeString("Name", Nothing, "DataSet1")

    ' Query element
    writer.WriteStartElement("Query")
    writer.WriteElementString("DataSourceName", "DataSource1")
    writer.WriteElementString("CommandType", "Text")
    writer.WriteElementString("CommandText", m_commandText)
    writer.WriteElementString("Timeout", "30")
    writer.WriteEndElement() ' Query
    ' Fields elements
    writer.WriteStartElement("Fields")
    Dim fieldName As String
    For Each fieldName In m_fields
        writer.WriteStartElement("Field")
        writer.WriteAttributeString("Name", Nothing, fieldName)
        writer.WriteElementString("DataField", Nothing, fieldName)
        writer.WriteEndElement() ' Field
    Next fieldName

    ' End previous elements
    writer.WriteEndElement() ' Fields
    writer.WriteEndElement() ' DataSet
    writer.WriteEndElement() ' DataSets
    ' Body element
    writer.WriteStartElement("Body")
    writer.WriteElementString("Height", "5in")

    ' ReportItems element
    writer.WriteStartElement("ReportItems")

    ' Table element
    writer.WriteStartElement("Table")
    writer.WriteAttributeString("Name", Nothing, "Table1")
    writer.WriteElementString("DataSetName", "DataSet1")
    writer.WriteElementString("Top", ".5in")
    writer.WriteElementString("Left", ".5in")
    writer.WriteElementString("Height", ".5in")
    writer.WriteElementString("Width", (m_fields.Count * 1.5).ToString() + "in")

    ' Table Columns
    writer.WriteStartElement("TableColumns")
    For Each fieldName In m_fields
        writer.WriteStartElement("TableColumn")
        writer.WriteElementString("Width", "1.5in")
        writer.WriteEndElement() ' TableColumn
    Next fieldName
    writer.WriteEndElement() ' TableColumns
    ' Header Row
    writer.WriteStartElement("Header")
    writer.WriteStartElement("TableRows")
    writer.WriteStartElement("TableRow")
    writer.WriteElementString("Height", ".25in")
    writer.WriteStartElement("TableCells")

    For Each fieldName In m_fields
        writer.WriteStartElement("TableCell")
        writer.WriteStartElement("ReportItems")

        ' Textbox
        writer.WriteStartElement("Textbox")
        writer.WriteAttributeString("Name", Nothing, "Header" + fieldName)

        writer.WriteStartElement("Style")
        writer.WriteElementString("TextDecoration", "Underline")
        writer.WriteEndElement() ' Style
        writer.WriteElementString("Top", "0in")
        writer.WriteElementString("Left", "0in")
        writer.WriteElementString("Height", ".5in")
        writer.WriteElementString("Width", "1.5in")
        writer.WriteElementString("Value", fieldName)
        writer.WriteEndElement() ' Textbox
        writer.WriteEndElement() ' ReportItems
        writer.WriteEndElement() ' TableCell
    Next fieldName

    writer.WriteEndElement() ' TableCells
    writer.WriteEndElement() ' TableRow
    writer.WriteEndElement() ' TableRows
    writer.WriteEndElement() ' Header
    ' Details Row
    writer.WriteStartElement("Details")
    writer.WriteStartElement("TableRows")
    writer.WriteStartElement("TableRow")
    writer.WriteElementString("Height", ".25in")
    writer.WriteStartElement("TableCells")

    For Each fieldName In m_fields
        writer.WriteStartElement("TableCell")
        writer.WriteStartElement("ReportItems")

        ' Textbox
        writer.WriteStartElement("Textbox")
        writer.WriteAttributeString("Name", Nothing, fieldName)

        writer.WriteStartElement("Style")
        writer.WriteEndElement() ' Style
        writer.WriteElementString("Top", "0in")
        writer.WriteElementString("Left", "0in")
        writer.WriteElementString("Height", ".5in")
        writer.WriteElementString("Width", "1.5in")
        writer.WriteElementString("Value", "=Fields!" + fieldName + ".Value")
        writer.WriteElementString("HideDuplicates", "DataSet1")
        writer.WriteEndElement() ' Textbox
        writer.WriteEndElement() ' ReportItems
        writer.WriteEndElement() ' TableCell
    Next fieldName


    ' End Details element and children   
    writer.WriteEndElement() ' TableCells
    writer.WriteEndElement() ' TableRow
    writer.WriteEndElement() ' TableRows
    writer.WriteEndElement() ' Details
    ' End table element and end report definition file
    writer.WriteEndElement() ' Table
    writer.WriteEndElement() ' ReportItems
    writer.WriteEndElement() ' Body
    writer.WriteEndElement() ' Report
    ' Flush the writer and close the stream
    writer.Flush()
    stream.Close()
End Sub 'GenerateRdl
Copy 
public void GenerateRdl()
{
   // Open a new RDL file stream for writing
   FileStream stream;
   stream = File.OpenWrite("Report1.rdl");
   XmlTextWriter writer = new XmlTextWriter(stream, Encoding.UTF8);

   // Causes child elements to be indented
   writer.Formatting = Formatting.Indented;

   // Report element
   writer.WriteProcessingInstruction("xml", "version=\"1.0\" encoding=\"utf-8\"");
   writer.WriteStartElement("Report");
   writer.WriteAttributeString("xmlns", null, "http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefinition");
   writer.WriteElementString("Width", "6in");

   // DataSource element
   writer.WriteStartElement("DataSources");
   writer.WriteStartElement("DataSource");
   writer.WriteAttributeString("Name", null, "DataSource1");
   writer.WriteStartElement("ConnectionProperties");
   writer.WriteElementString("DataProvider", "SQL");
   writer.WriteElementString("ConnectString", m_connectString);
   writer.WriteElementString("IntegratedSecurity", "true");
   writer.WriteEndElement(); // ConnectionProperties
   writer.WriteEndElement(); // DataSource
   writer.WriteEndElement(); // DataSources

   // DataSet element
   writer.WriteStartElement("DataSets");
   writer.WriteStartElement("DataSet");
   writer.WriteAttributeString("Name", null, "DataSet1");

   // Query element
   writer.WriteStartElement("Query");
   writer.WriteElementString("DataSourceName", "DataSource1");
   writer.WriteElementString("CommandType", "Text");
   writer.WriteElementString("CommandText", m_commandText);
   writer.WriteElementString("Timeout", "30");
   writer.WriteEndElement(); // Query

   // Fields elements
   writer.WriteStartElement("Fields");
   foreach (string fieldName in m_fields)
   {
      writer.WriteStartElement("Field");
      writer.WriteAttributeString("Name", null, fieldName);
      writer.WriteElementString("DataField", null, fieldName);
      writer.WriteEndElement(); // Field
   }

   // End previous elements
   writer.WriteEndElement(); // Fields
   writer.WriteEndElement(); // DataSet
   writer.WriteEndElement(); // DataSets

   // Body element
   writer.WriteStartElement("Body");
   writer.WriteElementString("Height", "5in");

   // ReportItems element
   writer.WriteStartElement("ReportItems");

   // Table element
   writer.WriteStartElement("Table");
   writer.WriteAttributeString("Name", null, "Table1");
   writer.WriteElementString("DataSetName", "DataSet1");
   writer.WriteElementString("Top", ".5in");
   writer.WriteElementString("Left", ".5in");
   writer.WriteElementString("Height", ".5in");
   writer.WriteElementString("Width", (m_fields.Count * 1.5) + "in");

   // Table Columns
   writer.WriteStartElement("TableColumns");
   for (int i = 0; i < m_fields.Count; i++)
   {
      writer.WriteStartElement("TableColumn");
      writer.WriteElementString("Width", "1.5in");
      writer.WriteEndElement(); // TableColumn
   }
   writer.WriteEndElement(); // TableColumns

   // Header Row
   writer.WriteStartElement("Header");
   writer.WriteStartElement("TableRows");
   writer.WriteStartElement("TableRow");
   writer.WriteElementString("Height", ".25in");
   writer.WriteStartElement("TableCells");

   foreach (string fieldName in m_fields)
   {
      writer.WriteStartElement("TableCell");
      writer.WriteStartElement("ReportItems");

      // Textbox
      writer.WriteStartElement("Textbox");
      writer.WriteAttributeString("Name", null, "Header" + fieldName);

      writer.WriteStartElement("Style");
      writer.WriteElementString("TextDecoration", "Underline");
      writer.WriteEndElement(); // Style

      writer.WriteElementString("Top", "0in");
      writer.WriteElementString("Left", "0in");
      writer.WriteElementString("Height", ".5in");
      writer.WriteElementString("Width", "1.5in");
      writer.WriteElementString("Value", fieldName);
      writer.WriteEndElement(); // Textbox

      writer.WriteEndElement(); // ReportItems
      writer.WriteEndElement(); // TableCell
   }

   writer.WriteEndElement(); // TableCells
   writer.WriteEndElement(); // TableRow
   writer.WriteEndElement(); // TableRows
   writer.WriteEndElement(); // Header

   // Details Row
   writer.WriteStartElement("Details");
   writer.WriteStartElement("TableRows");
   writer.WriteStartElement("TableRow");
   writer.WriteElementString("Height", ".25in");
   writer.WriteStartElement("TableCells");

   foreach (string fieldName in m_fields)
   {
      writer.WriteStartElement("TableCell");
      writer.WriteStartElement("ReportItems");

      // Textbox
      writer.WriteStartElement("Textbox");
      writer.WriteAttributeString("Name", null, fieldName);

      writer.WriteStartElement("Style");
      writer.WriteEndElement(); // Style

      writer.WriteElementString("Top", "0in");
      writer.WriteElementString("Left", "0in");
      writer.WriteElementString("Height", ".5in");
      writer.WriteElementString("Width", "1.5in");
      writer.WriteElementString("Value", "=Fields!" + fieldName + ".Value");
      writer.WriteElementString("HideDuplicates", "DataSet1");
      writer.WriteEndElement(); // Textbox

      writer.WriteEndElement(); // ReportItems
      writer.WriteEndElement(); // TableCell
   }   

   // End Details element and children   
   writer.WriteEndElement(); // TableCells
   writer.WriteEndElement(); // TableRow
   writer.WriteEndElement(); // TableRows
   writer.WriteEndElement(); // Details

   // End table element and end report definition file
   writer.WriteEndElement(); // Table
   writer.WriteEndElement(); // ReportItems
   writer.WriteEndElement(); // Body
   writer.WriteEndElement(); // Report

   // Flush the writer and close the stream
   writer.Flush();
   stream.Close();
}

Running the Application

Visual Studio offers several methods to build and run a console application from the IDE, such as:

  • Start (with Debugging)
  • Start without Debugging

To build and run SampleRdlGenerator

  1. From the Debug menu, click Start Without Debugging. This ensures that the console window remains open after the program has finished executing.

    The application prints the following output to the console:

    Copy 
    RDL file generated successfully.

    Note  In Visual Basic, you may receive a compiler error that states that 'Sub Main' was not found in SampleRDLGenerator.SampleRDLGenerator.Module1. If you receive this error, double-click the error message in the Task list. The Startup Object dialog appears. Select SampleRDLGenerator.SampleRDLGenerator.RdlGenerator and click the OK button. Rebuild the project.

  2. Press any key to close SampleRdlGenerator.

    Note  Any errors that occur are written to the console.

A file named Report1.rdl is written to the directory from which the console application is run.

Learning More

For more information about RDL and to view the RDL schema, see Report Definition Language. For more information about generating RDL, see Generating Report Definition Language Programmatically.

Did you find this helpful?
(1500 characters remaining)