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.
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.
Visual C# .NET 2008, SQL Server 2008.
As depicted in the above figure, there will be a central database that will have a table and a Stored Procedure.
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.
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
SET NOCOUNT ON;
DECLARE @MAINQRY AS VARCHAR(50)
WHEN 'A' THEN 'SELECT * FROM [DB-A].[dbo].[tblInformation]'
WHEN 'B' THEN 'SELECT * FROM [DB-B].[dbo].[tblInformation]'
ELSE 'SELECT * FROM [DefaultDB].[dbo].[tblInformation]'
Step 5: Open VS 2008. Create a Windows Application.
Step 6: Click on Add->NewItem -> Dataset.
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(string _FirstChar)
FirstChar = _FirstChar;
private void Form2_Load(object sender, EventArgs e)
NB:~ Since the Stored Procedure
usp_GetResult accepts one argument, the
FirstChar variable has been passed.
Step 19: Create another
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));
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:
- If we want to display records to users based on some categories.
- In testing, development, and production environments.
- For security reasons, if we want the data to be split across tables.
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.
Lead Engineer at HCL Technologies Ltd.
Code Project MVP 2012