Setting the Data Source of Reporting Services at Runtime

• Download source code - 53.1 KB

Introduction

Generally, we use a single database server as the data source for reports. But think of a situation where the same needs to be used for production, development, and testing. What if for security reasons, the user will be given the privilege to access the same report but from different database servers? This article will focus on one of the ways of achieving the same.

Background

As already specified, there are many ways of achieving this. One good way of meeting the target has been proposed by Bilal Khawaja. I have come up with a different concept which I would like to share.

Software specification

Visual C# .NET 2008, SQL Server 2008.

Concept

As depicted in the above figure, there will be a central database that will have a table and a Stored Procedure.

Aim

To create a single report, and based on the people names, access the records from different databases. E.g., all names that start with A will be picked from DB-A; for B, it will be from DB-B, and the rest from DefaultDB.

Implementation

Step 1: Create three databases namely DefaultDB, DB-A, DB-B.

Step 2: Create a table (say tblInformation) in each of the databases with the columns [Person Name], [Age], [Sex], [Address].

Step 3: Insert some values in all the four tables.

Step 4: Create a Stored Procedure in DefaultDB (say usp_GetResult) like so:

CREATE PROCEDURE usp_GetResult 
    -- Add the parameters for the stored procedure here
    (
        @FirstLetter varchar(50)    
    )
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
     DECLARE @MAINQRY AS VARCHAR(50)
     
     SELECT @MAINQRY=
            CASE @FirstLetter
                WHEN 'A' THEN 'SELECT * FROM [DB-A].[dbo].[tblInformation]'
                WHEN 'B' THEN 'SELECT * FROM [DB-B].[dbo].[tblInformation]'

                                ELSE  'SELECT * FROM [DefaultDB].[dbo].[tblInformation]'
    END
     
     
     EXEC(@MAINQRY)
END

Step 5: Open VS 2008. Create a Windows Application.

Step 6: Click on Add->NewItem -> Dataset.

Step 7:

Step 8: Click on Server Explorer.

Step 9: Data Connection [right click] -> Add Connection.

Step 10: Choose the SQL Server connection, database name, and press the Test Connection button. Once successful, click the OK button.

Step 11: Now, expand the Stored Procedures tab and choose the Stored Procedure.

Step 12: Drag and drop the Stored Procedure (e.g., usp_GetResult) into the Dataset.

Step 13: Add New Item -> Report.

Step 14: Drag & Drop a TABLE item from the Reports Item Toolbox and place on the Report form [e.g., DisplayRecords.rdlc]. The field names from the Stored Procedures should be placed in the field that is to be displayed.

Step 15: Now, create a Form, say, Form2.cs. From the Toolbox, go to Reporting tab and choose MicrosoftReportViewer. Drag and drop the same on the form.

Step 16: Click on the |> sign on the top right corner of the ReportViewer and choose the report file (.rdlc).

Step 17: If everything is proper, we will get the following screen:

Step 18: And the code-behind, and add the following code:

public partial class Form2 : Form
{
    string FirstChar = string.Empty;
    public Form2()
    {
        InitializeComponent();
    }

    public Form2(string _FirstChar)
    {
        InitializeComponent();
        FirstChar = _FirstChar;
    }

    private void Form2_Load(object sender, EventArgs e)
    {
        // TODO: This line of code loads data into
        // the 'NameDS.usp_GetResult' table. You can move, or remove it, as needed.
        this.usp_GetResultTableAdapter.Fill(this.NameDS.usp_GetResult, FirstChar);

        this.reportViewer1.RefreshReport();
    }
}

NB:~ Since the Stored Procedure usp_GetResult accepts one argument, the FirstChar variable has been passed.

Step 19: Create another Form, say Form1, whose design view will be like:

Step 20: In the Report Button’s click event, add the following code:

private void btnReport_Click(object sender, EventArgs e)
{
    Form2 objForm = new Form2(txtName.Text.Substring(0, 1));
    objForm.ShowDialog();
}

Step 21: Run the application. Enter some text in the Name textbox and click on OK. If the name starts with A, it will hit DB-A, if the name starts with B, DB-B will be called, else the default database will be executed.

Where can we implement this

There are a number of situations where this concept can be applied either directly or with a little bit of twisting. Some of them are listed here:

1. If we want to display records to users based on some categories.
2. In testing, development, and production environments.
3. For security reasons, if we want the data to be split across tables.
4. etc.

Conclusion

This is an easy approach for accessing many database servers via a single report service. Further suggestions and feedback are highly appreciated for the improvement of the article.