Beginner's guide for creating standalone .rdlc reports with ssrs

Introduction

The information in this article was born out of a need to develop new standalone SSRS reports (to replace a stack of Crystal reports) where the report designer couldn't see the data sources involved due to various account, security, and other restrictions. Therefore I developed a process whereby I could set up dummy data sources (even on my local PC) to provide the fields for the report. Once the report was finished it could then be tested against the real data source.

In this article, I demonstrate how to create a standalone .rdlc report and how to access it via C# code only. I also demonstrate how to set up the report fields for the report designer using a very simple technique based on stored procedures. Additionally, I also demonstrate how to map a dataset consisting of two tables (ds. Tables[0] and ds.Tables[1] - a 3 & 4 column table respectively) to the standalone report. I also cover the setting up of a simple Test Harness C# project in Visual Studio 2005 to test your standalone .rdlc report.

This article is deliberately written with the beginner in mind, hence it is rather lengthy as I try to explain in detail what to do, so I apologize if you find it slow-going. It is also for those who have a particular need to create standalone .rdlc reports that are unbound at design time but have their data added at runtime. In many examples, I have seemed to assume that you will be running your report against the same data source that the report was originally designed against. For me, this was not the case and I needed an alternative, hence the procedure outlined in this article. I hope what follows will be of some help to you.

Acknowledgments

I would like to thank Justin Saraceno whose article The ReportViewer control, a local SSRS 2005 report, and data from a Web Service got me going in the right direction. I use the same type of code approach that he uses. His article can be found on the http://www.c-sharpcorner.com website.

Requirements

My .rdlc reports had to be able to do the following

  • Be 100% standalone and capable of being accessed in code only i.e. there should be no need to add the .rdlc file to your Visual Studio project.
  • Some of our current production report queries run Stored Procedures that have multiple SELECT statements in them. These SPROCS return a single dataset containing multiple tables. If ds is our dataset, then we get back ds. Tables[0] ds.Tables[1], ds.Tables[2] etc. These tables had to be able to be wired into the report in code only.

By the way, with the following technique, you can also wire in Tables from different data sources as well. So if I had 2 datasets ds1 & ds2, then you can wire in ds1.Tables[0] and ds2.Tables[0]. The ds1 object might be an SQL Server query result. ds2 might be a mainframe query result etc. The report viewer control doesn't care where these data tables are coming from they are all just tables as far as the viewer control is concerned.

Prerequisites

To use the following technique that I am about to describe you should make sure you meet the following prerequisites:

  • Visual Studio 2005 with SSRS on your local PC.
  • SQL Server 2005 Management Studio (with NorthwindCS or similar database) installed on your local PC.
  • Sufficient permissions within SQL Server 2005 Management Studio to CREATE and DROP Stored Procedures from the NorthwindCS (or similar) database.

We are using NorthwindCS here just for simplicity because it gets installed when you install SQL Server 2005 Management Studio. We just want somewhere to park our Stored Procedures that we are going to create soon. The Sprocs we create have nothing to do with the NorthwindCS database In a real Development environment you would probably want to store the Sprocs in a meaningful location.

Values for this example

  • Our standalone .rdlc file will be called MyTestReport.rdlc
  • It will reside here C:\Reports\MyTestReport.rdlc for standalone testing.
  • It will require 2 data sources to be added to it called Reportdata_Table0 consisting of 3 columns
    (ID, DESCRIPTION and QUANTITY) and Reportdata_Table1 consisting of 4 columns
    (CUSTOMER_ID, NAME, ADDRESS and POSTAL_CODE).
  • Our design version .rdlc file will be called TestReport.rdlc

The report-friendly name will be "TESTREPORT" Here is a summary of the data source/dataset mapping.

Report Datasource name Data coming from the dataset Table type
Reportdata_Table0 dsMyDataSet.Tables[0] 3 column table
Reportdata_Table1 dsMyDataSet.Tables[1] 4 column table

We will hard-code some test data into the test harness (by building a 2-tabled dataset in code) so that there is something to show on the report itself, but in your application, you can derive the data from anywhere (a query, or a .xml file, web-service call, etc.)

The first step though is to create a simple test harness.

Create a simple TestHarness application

Our test harness will be where we design our .rdlc reports and test them also.

The basic approach will be to add a .rdlc file to our Visual Studio test harness project called TestReport.rdlc, merely so that we can get access to the designer, but when the report is finished it will be copied from the Visual Studio project location to the C:\Reports folder and then renamed to MyTestReport.rdlc to prove absolutely that we are dealing with a standalone file. The project has no knowledge of a file called MyTestReport.rdlc and this file is accessed in code only.

Step 1. Create a test harness

For this, we want the following

  • A standard C# Windows application consisting of a single dialog.
  • One SSRS report viewer control (reportViewer1).
  • One Exit button (btnExit).
  • One View Report button (btnViewReport).
  • One combo drop-down control (cmbReport).
  • A label for the combo (Report).

Here is what mine looks like

Exit

Anchor the buttons etc. to the Bottom and Right. Anchor the Report viewer to the Top, Bottom, Left, and Right.

The code behind the Exit button click event is.

private void btnExit_Click(object sender, EventArgs e)
{
    this.Close();
}

Let's say our new report user-friendly name will be "TESTREPORT". Therefore in the Form_Load event for this dialog put the following.

this.cmbReport.Items.Add("TESTREPORT");

As you design more reports keep adding them to the combo in the Form_Load event so you can easily select the report you want to test.

I found I needed the following using directives.

using System.Data.SqlClient;
using System.Xml;
using System.Xml.Serialization;
using System.Data.OleDb;
using System.IO;

I have been running SQL and Mainframe queries in my test harness. Depending on what you will be designing you may not need all of them. For this walk, you do not need any of them.

Add the following module variable dsMyDataSet this will be our ds. Tables[0] and ds.Tables[1] object I alluded to earlier.

public partial class Form1 : Form
{
    // Module scope dataset variable
    DataSet dsMyDataSet = new DataSet();
}

Now in the click event for btnViewReport button add the following code.

private void btnViewReport_Click(object sender, EventArgs e)
{
    // Reset the report viewer
    this.reportViewer1.Reset();
    // Set processing to local mode
    this.reportViewer1.ProcessingMode = Microsoft.Reporting.WinForms.ProcessingMode.Local;
    // Check which report to test
    if (this.cmbReport.Text.Trim() == "TESTREPORT")
    {
        // Load .rdlc file and add a datasource
        this.reportViewer1.LocalReport.ReportPath = @"C:\Reports\MyTestReport.rdlc";
        // Loop through each table in our dataset
        for (int i = 0; i < this.dsMyDataSet.Tables.Count; i++)
        {
            this.reportViewer1.LocalReport.DataSources.Add(this.GetMyDataTable(i));
        }
    }
    // Refresh viewer with above settings
    this.reportViewer1.RefreshReport();
}

Now we need 1 function called GetMyDataTable(int i)) and here is what it looks like.

private Microsoft.Reporting.WinForms.ReportDataSource GetMyDataTable(int i)
{
    // Form the datasource name - you need a naming convention for this to work
    string sDataSourceName = "Reportdata_Table" + i.ToString().Trim();
    // The line above will generate datasource names of "Reportdata_Table0" and
    // "Reportdata_Table1" for our 2 tabled dataset - we just need to ensure our .rdlc
    // report has been designed to receive 2 datasources with these same names, and that
    // the columns match up precisely one-to-one for each table.
    // Return the relevant dataset table
    return new Microsoft.Reporting.WinForms.ReportDataSource(sDataSourceName, this.dsMyDataSet.Tables[i]);
}

Create a function that sets up the 2-tabled dataset using our module variable dsMyDataSet. Call this function from the Form_Load event so that dsMyDataSet is pre-populated with some data ready for display.

This is what I did: Note the column names, you will see them later when we do the Stored Procedures.

private void SetupDataSet()
{
    // Create 1st DataTable to hold some report data
    System.Data.DataTable myTable0 = new DataTable("myTable0");
    System.Data.DataColumn column;
    System.Data.DataRow row;
    // Create 3 columns for myTable0
    column = new DataColumn();
    column.DataType = System.Type.GetType("System.String");
    column.ColumnName = "ID";
    column.ReadOnly = true;
    column.Unique = true;
    myTable0.Columns.Add(column);
    column = new DataColumn();
    column.DataType = System.Type.GetType("System.String");
    column.ColumnName = "DESCRIPTION";
    column.ReadOnly = true;
    column.Unique = true;
    myTable0.Columns.Add(column);
    column = new DataColumn();
    column.DataType = System.Type.GetType("System.String");
    column.ColumnName = "QUANTITY";
    column.ReadOnly = true;
    column.Unique = true;
    myTable0.Columns.Add(column);
    // Add a row of data to myTable0
    row = myTable0.NewRow();
    row["ID"] = "1234567890";
    row["DESCRIPTION"] = "Rickenbacker Electric Guitar";
    row["QUANTITY"] = "5";
    myTable0.Rows.Add(row);
    // Add a second row of data to myTable0
    row = myTable0.NewRow();
    row["ID"] = "777745632";
    row["DESCRIPTION"] = "Gibson Electric Guitar";
    row["QUANTITY"] = "7";
    myTable0.Rows.Add(row);
    // Add myTable0 to global dataset
    this.dsMyDataSet.Tables.Add(myTable0); // dsMyDataSet.Tables[0] object;
    // Create 2nd DataTable to hold some report data
    System.Data.DataTable myTable1 = new DataTable("myTable1");
    // Create 4 columns for myTable1
    column = new DataColumn();
    column.DataType = System.Type.GetType("System.String");
    column.ColumnName = "CUSTOMER_ID";
    column.ReadOnly = true;
    column.Unique = true;
    myTable1.Columns.Add(column);
    column = new DataColumn();
    column.DataType = System.Type.GetType("System.String");
    column.ColumnName = "NAME";
    column.ReadOnly = true;
    column.Unique = true;
    myTable1.Columns.Add(column);
    column = new DataColumn();
    column.DataType = System.Type.GetType("System.String");
    column.ColumnName = "ADDRESS";
    column.ReadOnly = true;
    column.Unique = true;
    myTable1.Columns.Add(column);
    column = new DataColumn();
    column.DataType = System.Type.GetType("System.String");
    column.ColumnName = "POSTAL_CODE";
    column.ReadOnly = true;
    column.Unique = true;
    myTable1.Columns.Add(column);
    // Add a row of data to myTable1
    row = myTable1.NewRow();
    row["CUSTOMER_ID"] = "56790";
    row["NAME"] = "John Lennon";
    row["ADDRESS"] = "Strawberry Fields, Liverpool, England";
    row["POSTAL_CODE"] = "NWE232";
    myTable1.Rows.Add(row);
    // Add a second row of data to myTable1
    row = myTable1.NewRow();
    row["CUSTOMER_ID"] = "44982";
    row["NAME"] = "George Harrison";
    row["ADDRESS"] = "22 Penny Lane, Liverpool, England";
    row["POSTAL_CODE"] = "NWE231";
    myTable1.Rows.Add(row);
    // Add myTable1 to global dataset
    this.dsMyDataSet.Tables.Add(myTable1); // dsMyDataSet.Tables[1] object;
}

Do not forget to call the above function from your Form_Load event, similar to.

private void Form1_Load(object sender, EventArgs e)
{
    this.cmbReport.Items.Add("TESTREPORT");
    this.SetupDataSet();
}

At this point, the project should be built.

We have now got our Dataset that contains 2 tables, i.e. we have created dsMyDataSet.Tables[0] and dsMyDataSet.Tables[1], a 3 and 4-column table respectively with the column names as shown in the above code snippet and a couple of rows of data for each one.

If you look at the return statement in the above GetMyDataTable(int i) function you can see that our report will have to be designed to expect 2 data sources to be added to it called ReportdataTable0 and ReportdataTable1, and additionally, these data sources have to have 3 and 4 columns defined within them respectively with identical names for the columns as we are using in the dataset dsMyDataSet.

The next step is to design the actual report, and the starting point for this is to get the report fields to show up in the Data Sources pane in Visual Studio so that you can drag and drop them onto the report designer surface.

Report Design

Create the report input fields via Stored Procedures

This is where we make use of the NorthwindCS database on your locally installed instance of SQL Server 2005. Here we just want to use a couple of stored procedures to define the report input fields. There is no intention to use the stored procedures at runtime to provide data. At runtime, the data will come from somewhere else (in this case a hard-coded Dataset in the app.), but in your application, the dataset can be from anywhere.

Make sure your local instance of SQL server 2005 is started. Check the icon in the system tray.

Step 2. Create  Stored Procedures in Notepad

Create  Stored Procedures in Notepad ready to copy across to SQL Server 2005 that look like this (or type them directly into the SQL Server 2005 query window after selecting NorthwindCS).

Sproc #1

USE NorthwindCS
GO
/*------------------------------------------------------------------*
This stored procedure provides a starting point for the
design of a Test SSRS Report.
*-------------------------------------------------------------------*/
CREATE PROC up_SSRS_TestReport_InputFields0
AS
SELECT DISTINCT
    '1' AS ID,
    '2' AS DESCRIPTION,
    '3' AS QUANTITY

Sproc #2

USE NorthwindCS
GO
/*------------------------------------------------------------------*
This stored procedure provides a starting point for the
design of a Test SSRS Report.
*-------------------------------------------------------------------*/
CREATE PROC up_SSRS_TestReport_InputFields1
AS
SELECT DISTINCT
    '1' AS CUSTOMER_ID,
    '2' AS NAME,
    '3' AS ADDRESS,
    '4' AS POSTAL_CODE

Note. It is the column names that are important here, not the data. Note also that the column names match exactly the column names that we added to our data tables earlier (dsMyDataSet).

Step 1. Go into SQL Server 2005, connect to the local server instance browse to NorthwindCS, and select New Query. Run each of the above SPROC codes as a query, then refresh to see your new Stored Procedures in the NorthwindCS database.

Step 2. In Visual Studio 2005 with your Test Harness app. open make sure you can see the Data Sources pane. Single right-click in this pane and select Add New Data Source. This starts the Wizard.

Step 3. For the question "Where will the application get data from?" leave the default setting of Database and click Next.

Database

Step 4. For the question "Which data connection should your application use to connect to the database?" click on New Connection.

If you installed SQL Server 2005 locally then your pc is the server. Type in your computer name into the Server name: field (this saves time there could be many servers on your network. So avoid browsing for them). I selected to use Windows authentication.

The section at the bottom of the Add Connection dialog should now be enabled, where you can select or enter a database name. Use the drop-down control to see the databases contained in your local instance of SQL Server 2005. Select NorthwindCS from the drop-down list.

Click on Test Connection to make sure it's working. Click OK to continue.

This is what my connection string looks like

Data Source=FNOA1281; Initial Catalog = NorthwindCS; Integrated Security=True

For the question "Save the Connection String to the Application Configuration File" just leave the default and click Next.

Step 5. Click Next. You will see a message briefly that says "Retrieving database information" followed by a dialog that allows you to Choose Your Database Objects. This list allows you to choose from Tables, Views, Stored Procedures, and Functions. In our case, we will be selecting the Stored Procedures we created earlier.

So expand the Stored Procedures section and put a tick against up_SSRS_TestReport_InputFields1 and up_SSRS_TestReport_InputFields2. This is a great feature of SSRS because you can lump related SPROCS together under a single category.

Step 6. In the DataSet name field at the bottom of the dialog change the default name that SSRS provides to something more meaningful like ReportdataTESTREPORT.

It should look like this

Choose your database objects

Click Finish to close the Wizard.

You should now have a data source category called ReportdataTESTREPORT and this is what it should look like in the Data Sources pane

Reportdata

From this information, SSRS will automatically form 2 Project data sources called.

Reportdata_TESTREPORT_up_SSRS_TestReport_InputFields1

and

Reportdata_TESTREPORT_up_SSRS_TestReport_InputFields2

i.e. the names get mangled together so they are unique. You will see them later in the Report Data Sources dialog.

Now we have our report fields courtesy of the NorthwindCS database and a couple of simple SPROCS. I knew that database would come in handy one day.

Add a new .rdlc file to your Visual Studio Project

Now we need to add a .rdlc report to the project so we can design our report.

In the Solution Explorer window single right-click the project name and select Add /New Item.From the templates displayed select Report and change the name at the bottom to TestReport.rdlc.

Report

Click Add. It now appears in the Solution Explorer pane and a blank design page is presented to you.

Open the SSRS toolbox pane and drag and drop 2 table controls onto the report surface. These will be called table1 and table2 by default. Add an extra column to the 2nd table. We want a 3 and a 4-column table to match our 3 and 4-column data tables created earlier.

Put any text you like in the Header fields of each table control (something descriptive). Add any bordering to the tables according to individual preference.

Open the Data Sources pane again and expand the ReportdataTESTREPORT project data source node you created earlier. Expand the SPROC nodes as well.

Do the following drag and drops

From up_SSRS_TestReport_InputFields1 Sproc

Drag & drop: ID > table1/Detail (1st column)

Drag & drop: DESCRIPTION table1/Detail (2nd column)

Drag & drop: QUANTITY table1/Detail (3rd column)

From up_SSRS_TestReport_InputFields2 Sproc

Drag & drop: CUSTOMER_ID table2/Detail (1st column)

Drag & drop: NAME > table2/Detail (2nd column)

Drag & drop: ADDRESS table2/Detail (3rd column)

Drag & drop: POSTAL_CODE table2/Detail (4th column)

We are almost there now

Remember that we need 2 report data sources called Reportdata_Table0 and Reportdata_Table1. The next section explains how to do this

Setup the Report Data Sources with the names you want

As soon as you start dragging and dropping the fields as described above, SSRS will allocate the following DataSet names for each table in your report

Report Table Name DataSetName property (as allocated by SSRS)
table1 Reportdata_TESTREPORT_up_SSRS_TestReport_InputFields1
table2 Reportdata_TESTREPORT_up_SSRS_TestReport_InputFields2


Check these values in the DataSetName property for each table on your report.

If we wanted to, at this point, we could use these DataSetName values (as they are shown in the table above). We just have to change our GetMyDataTable(int i) function to have the following line.

// create the Datasource name
sDataSourceName = "Reportdata_TESTREPORT_up_SSRS_TestReport_InputFields" + (i + 1).ToString().Trim();

But that is a bit of a mouthful. So let's make it something nicer.

Steps to renaming a report data source

  • Make sure the report designer is open with your new report showing in the designer.

  • From the menus select Report-->Data Sources. This opens the Report Data Sources dialog and it should look something like this

Reportdata testreport

Note. The upper control is for Project data sources and the lower control is for Report data sources.

Because we used fields from 2 data sources when we did the dragging and dropping earlier, SSRS has automatically added these 2 data sources to the above dialog. To change the name of the Report data sources just click on the first one in the list and click rename. Type in ReportdataTable0. Do the same for the second one but call it ReportdataTable1.

Now it should look like

Report date table

Click OK to dismiss the dialog, then Save your project so the new names stick. It's worth going back into this dialog to check that your new names have stuck. On some occasions when I have gone back in the names have still been set to their original values.

There is one final thing to do

Even though we have changed the name of the Report data sources to something that we want, this has only happened on the Visual Studio side and the report itself still thinks it is working with the original names that SSRS allocated. You can see this if you select one of your tables in the designer (Table say) and look at its DataSetName property.

Table1 property

Data set name

So the final thing to do is to select the pulldown control in this property and select ReportdataTable0 for table1 and ReportdataTable1 for table2 in your report.

Table1 property

Table one property

Table2 property

Table two property

Now Visual Studio and your SSRS report agree on the name of the DataSetName.

The lesson here is that if you change the name of a Report data source you then have to visit every control in your report that makes use of that data source and check the DataSetName property.

Implement the standalone .rdlc file

Browse to where Visual Studio has your TestReport.rdlc file and copy it to the C:\Reports folder. Then rename it to MyTestReport.rdlc (you can leave the name the same really if you want, just make sure that whatever name you give it here, you use the same name when you access the file in code).

Run the application and select TESTREPORT from the combo pull-down, then click View Report.

You should see the final result similar to this

Final result similar

If you wanted to you could now go back into your project and delete all of the data sources from the Data Sources pane by deleting the corresponding .xsd file (Reportdata_TESTREPORT.xsd) from the Solution Explorer pane, and your MyTestReport.rdlc file will still work.

You could also delete the TestReport.rdlc design version of the file from the Solution Explorer pane if you wanted to and MyTestReport.rdlc will still work correctly i.e. we have created a 100% standalone report that can be wired up to any data source provided that data source supplies the components that the report expects (which in this case is one data source called Reportdata_Table0 consisting of a 3 columned table (ID, DESCRIPTION, QUANTITY) and a second data source called Reportdata_Table1 consisting of a 4 columned table (CUSTOMER_ID, NAME, ADDRESS, POSTAL_CODE)).

Appendix I

Here is the full listing of my Form 1.cs file just in case you get lost

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Xml;
using System.Xml.Serialization;
using System.Data.OleDb;
using System.IO;
namespace TestHarness
{
    public partial class Form1 : Form
    {
        // Module scope dataset variable
        DataSet dsMyDataSet = new DataSet();
        public Form1()
        {
            InitializeComponent();
        }
        private void Form1_Load(object sender, EventArgs e)
        {
            this.cmbReport.Items.Add("TESTREPORT");
            this.SetupDataSet();
            this.reportViewer1.RefreshReport();
        }
        private void btnExit_Click(object sender, EventArgs e)
        {
            this.Close();
        }
        private void btnViewreport_Click(object sender, EventArgs e)
        {
            // Reset the report viewer
            this.reportViewer1.Reset();
            // Set processing to local mode
            this.reportViewer1.ProcessingMode = Microsoft.Reporting.WinForms.ProcessingMode.Local;
            // Check which report to test
            if (this.cmbReport.Text.Trim() == "TESTREPORT")
            {
                // Load .rdlc file and add a datasource
                this.reportViewer1.LocalReport.ReportPath = @"\Reports\MyTestReport.rdlc";
                // Loop through each table in our dataset
                for (int i = 0; i < this.dsMyDataSet.Tables.Count; i++)
                {
                    this.reportViewer1.LocalReport.DataSources.Add(this.GetMyDataTable(i));
                }
            }
            // Refresh viewer with above settings
            this.reportViewer1.RefreshReport();
        }
        private Microsoft.Reporting.WinForms.ReportDataSource GetMyDataTable(int i)
        {
            // Form the datasource name - you need a naming convention for this to work
            string sDataSourceName = "Reportdata_Table" + i.ToString().Trim();
            // The line above will generate datasource names of "Reportdata_Table0" and
            // "Reportdata_Table1" for our 2 tabled dataset - we just need to ensure our .rdlc
            // report has been designed to receive 2 datasources with these same names and that
            // the columns match up precisely one-to-one for each table.
            // Return the relevant dataset table
            return new Microsoft.Reporting.WinForms.ReportDataSource(sDataSourceName, this.dsMyDataSet.Tables[i]);
        }
        private void SetupDataSet()
        {
            // Create 1st DataTable to hold some report data
            System.Data.DataTable myTable0 = new DataTable("myTable0");
            System.Data.DataColumn column;
            System.Data.DataRow row;
            // Create 3 columns
            column = new DataColumn();
            column.DataType = System.Type.GetType("System.String");
            column.ColumnName = "ID";
            column.ReadOnly = true;
            column.Unique = true;
            // Add the Column to the DataColumnCollection.
            myTable0.Columns.Add(column);
            column = new DataColumn();
            column.DataType = System.Type.GetType("System.String");
            column.ColumnName = "DESCRIPTION";
            column.ReadOnly = true;
            column.Unique = true;
            // Add the Column to the DataColumnCollection.
            myTable0.Columns.Add(column);
            column = new DataColumn();
            column.DataType = System.Type.GetType("System.String");
            column.ColumnName = "QUANTITY";
            column.ReadOnly = true;
            column.Unique = true;
            // Add the Column to the DataColumnCollection.
            myTable0.Columns.Add(column);
            // Add a row of data
            row = myTable0.NewRow();
            row["ID"] = "1234567890";
            row["DESCRIPTION"] = "Rickenbacker Electric Guitar";
            row["QUANTITY"] = "5";
            // Add the row of data to the table
            myTable0.Rows.Add(row);
            // And a second row
            row = myTable0.NewRow();
            row["ID"] = "777745632";
            row["DESCRIPTION"] = "Gibson Electric Guitar";
            row["QUANTITY"] = "7";
            // Add the row of data to the table
            myTable0.Rows.Add(row);
            // Add myTable0 to global dataset
            this.dsMyDataSet.Tables.Add(myTable0); // dsMyDataSet.Tables[0] object;
            // Create 2nd DataTable to hold some report data
            System.Data.DataTable myTable1 = new DataTable("myTable1");
            // Create 4 columns
            column = new DataColumn();
            column.DataType = System.Type.GetType("System.String");
            column.ColumnName = "CUSTOMER_ID";
            column.ReadOnly = true;
            column.Unique = true;
            // Add the Column to the DataColumnCollection.
            myTable1.Columns.Add(column);
            column = new DataColumn();
            column.DataType = System.Type.GetType("System.String");
            column.ColumnName = "NAME";
            column.ReadOnly = true;
            column.Unique = true;
            // Add the Column to the DataColumnCollection.
            myTable1.Columns.Add(column);
            column = new DataColumn();
            column.DataType = System.Type.GetType("System.String");
            column.ColumnName = "ADDRESS";
            column.ReadOnly = true;
            column.Unique = true;
            // Add the Column to the DataColumnCollection.
            myTable1.Columns.Add(column);
            column = new DataColumn();
            column.DataType = System.Type.GetType("System.String");
            column.ColumnName = "POSTAL_CODE";
            column.ReadOnly = true;
            column.Unique = true;
            // Add the Column to the DataColumnCollection.
            myTable1.Columns.Add(column);
            // Add a row of data
            row = myTable1.NewRow();
            row["CUSTOMER_ID"] = "56790";
            row["NAME"] = "John Lennon";
            row["ADDRESS"] = "Strawberry Fields , Liverpool , England";
            row["POSTAL_CODE"] = "NWE232";
            // Add the row of data to the table
            myTable1.Rows.Add(row);
            // Add a row of data
            row = myTable1.NewRow();
            row["CUSTOMER_ID"] = "44982";
            row["NAME"] = "George Harrison";
            row["ADDRESS"] = "22 Penny Lane , Liverpool , England";
            row["POSTAL_CODE"] = "NWE231";
            // Add the row of data to the table
            myTable1.Rows.Add(row);
            // Add myTable1 to global dataset
            this.dsMyDataSet.Tables.Add(myTable1); // dsMyDataSet.Tables[1] object;
        }
    }
}

Appendix II

Here are two more examples of GetMyData() type functions that I have used recently: the 1st one runs a SQL query against a remote server and the 2nd one runs a mainframe DB2 query.

private Microsoft.Reporting.WinForms.ReportDataSource GetMyDataEAR005()
{
    // TEST Reporting server
    string strConn = "server=EDSQL022\\SQL07;database=MgtReporting;Integrated Security=SSPI;";
    string sQuery = "SELECT DISTINCT AL1.ReportName AS Report_Name, " +
                    "reportMnemonic AS Report_Mnemonic, AL1.ReportType AS Report_Type, " +
                    "AL1.Active, AL2.ReportGroup AS Report_Group, AL4.ADRole AS AD_Role, " +
                    "L3.BizArea AS Business_Area " +
                    "FROM dbo.vwFullReportList AS AL1 " +
                    "LEFT OUTER JOIN dbo.vwReportGroupMap AS AL2 ON AL1.ReportID = AL2.ReportID AND AL1.ReportType = AL2.ReportType " +
                    "LEFT OUTER JOIN dbo.vwReportBizMap AS AL3 ON AL3.ReportID = AL1.ReportID AND AL3.ReportType = AL1.ReportType " +
                    "LEFT OUTER JOIN dbo.vwReportRoleMap AS AL4 ON AL4.ReportID = AL1.ReportID AND AL4.ReportType = AL1.ReportType " +
                    "ORDER BY Report_Mnemonic, AD_Role";
    DataSet ds = new DataSet("ReportDataSet");
    SqlConnection cn = new SqlConnection(strConn);
    cn.Open();
    SqlDataAdapter da = new SqlDataAdapter(sQuery, cn);
    da.Fill(ds);
    cn.Close();
    return new Microsoft.Reporting.WinForms.ReportDataSource("ReportList_EAR005", ds.Tables[0]);
}
private Microsoft.Reporting.WinForms.ReportDataSource GetMyDataCWCR004()
{
    string strConn = "Provider=DB2OLEDB;Data Source=DBT1;Persist Security Info=True;Password=xxxxxxx;User ID=my_id;Initial Catalog=DBT1;Network Transport Library=TCPIP;Network Address=fbrukdev.network;Network Port=5025;Package Collection=HIS2004;Default Schema=MIS";
    string sQuery = "SELECT DISTINCT AL1.ESA_CODE, AL1.ESA_DESCRIPTION, AL2.FUTURE_MTH_NUM, " +
                    "CASE WHEN AL1.FUTURE_MTH_NUM = 0 THEN 'Past' ELSE 'Future' END AS TIMESPAN, " +
                    "AL1.YR_MONTH, AL1.TOTAL_CNT, AL1.EXTRACT_DATE " +
                    "FROM MIS.CWC_JSKR_FLOW AL1, MIS.CWC_JSKR_FLOW AL2 " +
                    "WHERE AL1.ESA_CODE = AL2.ESA_CODE AND (1 = 1)";
    DataSet ds = new DataSet("ReportDataSet");
    OleDbConnection myOleDbConnection = new OleDbConnection(strConn);
    OleDbDataAdapter da = new OleDbDataAdapter();
    OleDbCommand myCommand = new OleDbCommand(sQuery, myOleDbConnection);
    myCommand.CommandTimeout = 600;
    da.SelectCommand = myCommand;
    myOleDbConnection.Open();
    da.Fill(ds);
    myOleDbConnection.Close();
    return new Microsoft.Reporting.WinForms.ReportDataSource("ReportData_CWCR004", ds.Tables[0]);
}


Similar Articles