[Solved] SQLDataSource Command with Dynamic Schema Name and Parameters

[Nitin]

I have imported a crystal report, which had 4 XML data sources which the Import Wizard of Standalone Report Designer has correctly converted to 4 SQL Data Sources.

Now, I must assign 4 SQL Server Stored Procedures (returning a dataset), 1 each for the 4 SQL Data Sources. Each stored procedure has some parameters. I can do this in the Configure Data Source Command window by selecting Stored Procedure.

My problem is that the SQL Database is determined at runtime. i.e. there are two databases having the same 4 stored procedures. So, it becomes a Dynamic command. So, I have to use "Select Statement" and not the "Stored Procedure" in the Command window.

It becomes something like EXEC {DBName}.[dbo].[StoredProc1] @prm1, @prm2 for 1st Data Source, EXEC {DBName}.[dbo].[StoredProc2] @prm for the second and so on for others.

DBName is determined at runtime. If there was only 1 SQL Data Source, I would have created a report parameter and created a binding like DataSource.SelectCommand= Format("{0}.[dbo].[StoredProc1]", Parameters.DBName.Value)

However, since there are 4 SQL Data Sources with each having a different stored procedure as its command, I cannot do this.

Any idea as to how to proceed in this situation?

[Dimitar]

Hello Nitin,

Thank you for the provided information about the scenario!

Using a binding to set the select command should be unnecessary in this situation. I will discuss two more approaches for handling this, with one of them being very similar to the binding approach. But first, a few words on the binding approach and using data sources in the reports.

Data Items and Data Sources

Let me start by mentioning that each data item can have only a single data source component assigned to it. What this means is that in your current setup, with 4 SqlDataSource components, you would need at least 4 data items to display all of the data.

Each data item has its own Bindings collection, so the approach with the binding on the SelectCommand property would work. You would simply need to do it on all four of the data items.

You can take a look at the Telerik Reporting Change Connection String dynamically through a report parameter - Telerik Reporting article, which demonstrates how to bind a different data source property, but the idea is the same.

However, I would not recommend this approach, given what I know so far, because it is a bit complex and requires some maintenance work in the future, most likely.

Set Database in the Connection String

You mentioned that the database is chosen at tuntim, but I am not sure exactly when that would happen. If the database is known at runtime, then you could include it in the connection string used by the SqlDataSource component. Let's say it is a connection named "Alias1".

The connection "Alias1" can be different depending on where you run the project. For example, if the database depends on the environment, there are various approaches for dynamically setting the connection string based on that, such as Telerik Reporting How to Retrieve the Oracle Connection Strings from Environment Variables - Telerik Reporting.

 As long as the database is specific in the connection string, for example:

Server=MyServer;Database=Sales;Integrated Security=True;

You should be able to call the stored procedure without specifying the database, for example:

dbo.GetOrders

Now, if the connection changes under different conditions, later in the app lifecycle, then this approach would not be the most suitable.

Report Parameter Approach

If the above approach does not work for your needs, then you could use a very similar solution to the one with the bindings. You could create a report parameter that passes either only your database as a string or the whole stored procedure string.

For example, this could be the SQL command in your SqlDataSource:

EXEC @storedProcedureParam

And you could set the value of this SQL parameter with a report parameter, which would allow you to pass the name or the whole sp through the report viewer:

Wrapping Up

I hope that the provided information and suggestions will help achieve the desired result!

Let me know if you have any additional questions.

Regards,
Dimitar
Progress Telerik