Using RDLC and DataSets to develop ASP.NET Reporting Services Reports

Introduction

While developing on ASP.NET, you may experience some difficulties or hard decisions with Reporting Services. Do you really need a Reporting Services Server installed and working for your ASP.NET applications to access the reports?

The answer is RDLC, which are the Reporting Services report files on ASP.NET applications. These are simple to create, but after creating the layout, when you attempt to load data from a SQL Server 2005 database, you'll find the issue - how can I map DataSets to my report?

On RDL (Common Server Reporting Services), you can do this directly, but on RDLC, you will have to create your DataSets on your Web Application/Project, and send the correct parameters to the report so it can get the respective data from the database and show it on the report.

Sounds simple, right? The problem is trying to find this code..

Background

This article covers the existing gap on how to send DataSets into an RDLC file so it can display a report with data.

Using the code

Even if the introduction or the concept sounds simple, you must apply the code carefully.

In the first place, drag and drop a ReportViewer into your page (in the code, I'll name it as ReportViewer1).

using System.Data.SqlClient;
using Microsoft.ApplicationBlocks.Data;
using Microsoft.Reporting.WebForms;

/// <summary>
/// Loads DataSources to the RDLC Report
/// </summary>
/// <param name="lineNr">Line Number
///  as parameter to get DataSources results from SQL DB</param>
protected void LoadReport(int lineNr)
{
    //  1. Clear Report Data
    ReportViewer1.LocalReport.DataSources.Clear();  

    //  2. Get Connection String from Web.Config
    SqlConnection sqlCon = new SqlConnection();
    sqlCon.ConnectionString = 
      ConfigurationManager.ConnectionStrings
      ["myConnection"].ConnectionString;

    //  3. Create DataSets (In my case I use 2 DataSets,
    //     you can use one or more)
    DataSet dsHeader = new DataSet();
    DataSet dsDetail = new DataSet();


    //  4. Select the DataSource to both DataSets
    // (In my case both SQL Stored Procedures
    //   use the same Sql parameter)

    SqlParameter sqlParm = new SqlParameter("@intNr", 
                                            lineNr.ToString());
    dsHeader = SqlHelper.ExecuteDataset(sqlCon, 
               CommandType.StoredProcedure, 
               "stp_S_LoadHeaderData",sqlParm);
    dsDetail = SqlHelper.ExecuteDataset(sqlCon, 
               CommandType.StoredProcedure, 
               "stp_S_LoadDetailData",sqlParm);

    //  5. The next lines mean that you will fill the datasets
    //     with the data retrieved from the stored procedures.
    //     I had on my SQL database, giving a input parameter
    //     sqlParam as you can see before
    //     Create datasource to the report - this way you associate
    //     these local datasets to your project datasets,
    //     with the correctly loaded data
    //     Notice that DS1_stp_s_LoadHeaderData and
    //     DS1_stp_s_LoadDetailData are my Report DataSets,
    //     so you can Map the local Datasets to the report datasets

    ReportDataSource reportDSHeader = 
      new ReportDataSource("DS1_stp_s_LoadHeaderData", 
                           dsHeader.Tables[0]);
    ReportDataSource reportDSDetail = 
      new ReportDataSource("DS1_stp_s_LoadDetailData", 
                           dsDetail.Tables[0]);

    //  6. Add these DataSources to your ReportViewer, et voilá!

    ReportViewer1.LocalReport.DataSources.Add(reportDSHeader);
    ReportViewer1.LocalReport.DataSources.Add(reportDSDetail);

    ReportViewer1.LocalReport.Refresh();
}

And then, you just need to call the LoadReport method and pass the correct parameter, and you're set.

Points of interest

This way, you avoid using the Reporting Services Server, and you spare a lot of unwanted code. Your report will be together with the Web Application, so once you deploy your project, your report is already set, and there is no need to install it on any server.

You'll just need .NET Framework 2.0 and VS 2005 installed to develop/run this code.