C# Winform - How to display real time reports by passing 3 variables to ReportViewer namely Reportname(RDL file), SQLstring and Connectionstring

Question

What is the most elegant way to pass RDL file, SQL and Connection string to ReportViewer Control on Winform C# project for displaying report at run time.

Introduction:

• I recently started working on Report creation on Windows. I found that RDLC files act as static files with no need for Connection string or SQL statements, so we need to pass DataSource to it in order to fill it with data at run time. This does not serve our purpose.
• RDL files however can take SQL statements and Connection String which they save in XML tags and are used for generating Dynamic reports.

Given that I have

1. RDL report template
2. SQL statement
3. Connection String to Database

What is the best way to pass this information to a ReportViewer in C# Winform, to generate report?

1) For an RDL used for a library management system. (we have no control over these files, these are used by the users of our application).

  <Query>
    <DataSourceName>DS1</DataSourceName>
    <CommandText>select bk_book_details.id, bk_book_details.book_id, bk_book_details.book_no, bk_book_details.book_name, bk_book_details.edition_id, bk_book_details.condition_id, bk_book_details.publication_year, bk_book_details.price, bk_book_details.purchase_price, bk_book_details.reference_no, bk_book_details.book_status, bk_book_details.purchase_id, bk_book_details.purchase_date from bk_book_details</CommandText>
  </Query>

2) Here is the SQL statement

select bk_book_details.id, bk_book_details.book_id, bk_book_details.book_no, bk_book_details.book_name, bk_book_details.edition_id, bk_book_details.condition_id, bk_book_details.publication_year, bk_book_details.price, bk_book_details.purchase_price, bk_book_details.reference_no, bk_book_details.book_status, bk_book_details.purchase_id, bk_book_details.purchase_date from bk_book_details

3) Here is the Connection string

server=localhost;User Id=root;password=root;Persist Security Info=True;database=lms

Current output: As seen on the RDLReader.exe application

Code used to get this output

    private void btnReport_Click(object sender, EventArgs e) {
        string sql = "select bk_book_details.id, bk_book_details.book_id, bk_book_details.book_no, bk_book_details.book_name, bk_book_details.edition_id, bk_book_details.condition_id, bk_book_details.publication_year, bk_book_details.price, bk_book_details.purchase_price, bk_book_details.reference_no, bk_book_details.book_status, bk_book_details.purchase_id, bk_book_details.purchase_date from bk_book_details";
        string RDLReaderApplication = @"""""""" + Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location) + @""\ReaderPath\RDLReader.exe"" + @"""""""";
        string reportFile = @"""""""" + Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location) + @""\Reports\ReportFile.RDL"" + @"""""""";
        string connectionString = @"""""""" + "server=localhost;User Id=root;password=root;Persist Security Info=True;database=lms" + @"""""""";
        Process.Start(RDLReaderApplication, reportFile + "" "" + @"""""""" + sql + @"""""""" + "" "" + connectionString);
    }

Expected output:

Work so far:

The form ReportForm contains the ReportViewer Control which is docked to the form.

Additional information:

1) I want the 3 data to be sent to ReportViewer Control, that is docked in a different form and load the report on the ReportViewer instead.

2) Our users do not share connection with us, so we cannot hardcode it in the RDL file. The SQL is generally a constant and RDL file is generally designed once per need for a particular form.

3) We use MySQL database for now. But a solution in any popular RDBMS will help a lot. I hope the query above gives a good picture of the schema of the table being accessed (the query contains all the columns in the table).

Note: Please include answer with RDL files and not RDLC files.

What finally solved the problem for me, in addition to the answer given by @Reza Aghaei

The Report file has tags for datasource and dataset, right at the beginning of the RDL file like the following snippet

  <DataSets>
    <DataSet Name="Data">
      <Query>
        <DataSourceName>DS1</DataSourceName>

The line from @Reza Aghaei, asking for the name of DataSet (Data) and not DataSourceName (DS1). Making this distinction was crucial in making the report file work.

var rds = new Microsoft.Reporting.WinForms.ReportDataSource("DataSet1", this.ReportData);

changed this to:

var rds = new Microsoft.Reporting.WinForms.ReportDataSource("Data", this.ReportData);

Fichtean

Need help making this solution accept multiple Datasets in the question: #40447144 – Authors

You should pay attention the name of DataSet in your report should be the same name which you use when creating a new ReportDataSource. For example if the name of DataSet is DataSet1, you should use such code: var rds = new ReportDataSource("DataSet1", data);. – Killing

Good point @RezaAghaei, I have put the exact same name as it is used in the RDL file – Authors

Answer 1

You can use a single ReportForm like below and pass data and report name to it. The report form should contain a ReportViewer control and such code:

public partial class ReportForm : Form
{
    public ReportForm()
    {
        InitializeComponent();
        this.Load+=new EventHandler(ReportForm_Load);
    }

    public Object ReportData { get; set; }
    public string ReportName { get; set; }        

    private void ReportForm_Load(object sender, EventArgs e)
    {
        var rds = new Microsoft.Reporting.WinForms.ReportDataSource("DataSet1",
            this.ReportData);
        this.reportViewer1.LocalReport.DataSources.Clear();
        this.reportViewer1.LocalReport.DataSources.Add(rds);         
        var path = System.IO.Path.Combine(Application.StartupPath, 
            "Reports", this.ReportName);
        reportViewer1.LocalReport.ReportPath = path;
        this.reportViewer1.RefreshReport();
    }
}

Usage

You can use the ReportForm this way:

var f = new ReportForm();
DataTable table = new DataTable(); 
var command = Properties.Settings.Default.Command;        /*SELECT Statement*/
var connection = Properties.Settings.Default.Connection;  /*Connection String*/
using (var adapter = new SqlDataAdapter(command, connection))
    adapter.Fill(table)
f.ReportName = "Report1.rdlc" /*Or any other name*/
f.ReportData = table;
f.ShowDialog();

Note

1. ReportViewer control shows RDLC reports. RDL Reports should be hosted on SQL Server Reporting Service. It seems you want to have report on client machines and not on a SSRS. If this is the case you need RDLC report. Although RDL and RDLC have the same XML schema but technically it seems you need RDLC.

2. You said RDL file is generally designed once So clients can have report files on their machines and you can simply load a report into report view by its address, or even you can have those reports in solution and embed them as resources. You can load report by its name when you set it as embedded resource:

   reportViewer1.LocalReport.ReportEmbeddedResource = "Sample.Reports.Report1.rdlc";
   

   Or load reports by path:

   var path = System.IO.Path.Combine(Application.StartupPath, "Reports", "Report1.rdlc");
   reportViewer1.LocalReport.ReportPath = path;
   

3. You said The SQL is generally a constant. and Our users do not share connection with us So you can use a Settings.settings and add 2 properties, Command with Application scope, Connection with user scope. So you can let the users change the connection string at run-time and then load data this way and pass the data to your ReportForm:

   DataTable table = new DataTable(); 
   var command = Properties.Settings.Default.Command;
   var connection = Properties.Settings.Default.Connection;
   using (var adapter = new SqlDataAdapter(command, connection))
       adapter.Fill(table)
   //Pass table to ReportForm
   

4. In fact the sql command can be dynamic, but it should keep a constant result schema. Result column name should not be changed because the report engine uses query column names to show data in report fields. So you can create your Command property as User settings too.

5. About But a solution in any popular RDBMS, it's better to use dependency injection to inject libraries which load data for you. This way you can have different dll for different DBMS and inject suitable dll when you need.

Killing

Nice breakdown of answer @reza Aghaei, could you include a bit more code please, or a simple working example? The above explanation is really good, and includes nice snippets. However I need little help in using the snippets in real code and would appeciate some more help in this direction (^_^) – Authors

I am getting an error which i am not able to rootcause: An error occured during local report processing. The definition of the report "<ReportName.rdl>" is invalid. The Value expression for the textrun 'Textbox14.Paragraphs[0].TextRuns[0]' contains an error: [BC30456] 'book_name' is not a member of 'Microsoft.ReportingServices.ReportProcessing.ReportObjectModel.Fields'. – Authors

that same report works when it is passed to the executable I have posted in the question. So I am suspecting something wrong with the query, let me check and get back – Authors

Let us continue this discussion in chat. – Authors

It seems somewhere in report there is a problem: Should be: <Textbox Name="Textbox14"> <Value>=Fields!book_name.Value</Value> <Style> – Killing