[]
Adding a dynamic connection string to a data source is a convenient tool that allows you to change the connection string during the report execution. This is achieved by using parameterized parameters in the connection string, or by using completely different connection strings for completely different databases.
This topic describes two basic ways of working with dynamic connection strings:
Using the dynamic connection string as a parameter makes it much easier to manage the connection string at runtime. You can set the connection string as a parameter in several ways:
Creating a dynamic connection string in a Windows or Web application is a common scenario when interacting with the parameters of your report. Follow these steps to create a dynamic connection string.
type=note
Note: The sample below uses the Windows Forms Designer.
type=note
Note: The value =Parameters!dbName.Value is the value from the Name field, specified in step 1.
By following these steps, you will use the created data source while designing a report in the Windows or Web application and also manage it, using the code as demonstrated below.
private PageReport LoadReport()
{
// Your path to the report
string path = "";
// 0 - "Database1" from step 1
// 1 - "Database2" from step 1
int databaseIndex = 0;
// If "true", you must choose which database to use, otherwise the one you specified will be automatically selected
bool showDatabaseSelection = false;
PageReport report = new PageReport(new FileInfo(path));
ConfigureConnectionString(databaseIndex, report, showDatabaseSelection);
return report;
}
private void ConfigureConnectionString(int databaseIndex, PageReport report, bool showDatabaseSelection=true)
{
if (!showDatabaseSelection)
{
// Select the connection string from the previously created parameter
string connectionString = report.Report.ReportParameters[0].ValidValues.ParameterValues[databaseIndex].Value.ToString();
report.Report.ReportParameters.Clear();
report.Report.DataSources[0].ConnectionProperties.ConnectString = connectionString;
}
}
The ConfigureConnectionString method takes values as parameters, indicating what database to use from a previously created parameter.
This code example shows how to create a string.
private PageReport LoadReport()
{
// Your path to the report
string path = "";
PageReport report = new PageReport(new FileInfo(path));
// Create dynamic connection strings
report.Report.ReportParameters.Add(CreateDynamicConnectionString());
DataSource dataSource = report.Report.DataSources[0]; // Your data source
if (dataSource.ConnectionProperties.DataProvider == "SQL") // Use any other database
{
// Set a dynamic string to connect to the data source
dataSource.ConnectionProperties.ConnectString = @"=Parameters!dbName.Value";
}
return report;
}
private ReportParameter CreateDynamicConnectionString()
{
string database1 = ""; // Your first connection string
string database2 = ""; // Your second connection string
ValidValues validValues = CreateValidValues(database1, database2);
ReportParameter dynamicString = new ReportParameter
{
AllowBlank = true,
DataType = ReportParameterDataType.String,
Name = "", // The name of your parameter. It is used in the connection string
ValidValues = validValues,
Nullable = false,
Prompt = ""
};
return dynamicString;
}
private ValidValues CreateValidValues(string firstConnString, string secondConnString)
{
// The label of your parameter is "Database1"
ParameterValue firstParameter = CreateParameterValue("Database1", firstConnString);
// The label of your parameter is "Database2"
ParameterValue secondParameter = CreateParameterValue("Database2", secondConnString);
ValidValues validValues = new ValidValues();
validValues.ParameterValues.Add(firstParameter);
validValues.ParameterValues.Add(secondParameter);
return validValues;
}
private ParameterValue CreateParameterValue(string label, string value)
{
return new ParameterValue()
{
Label = label, // The Label of your parameter value
Value = value,
};
}
Looking at the code above, you can see that creating a dynamic connection string consists of 2 steps.
Creating the dynamic string parameter.
report.Report.ReportParameters.Add(CreateDynamicConnectionString());
It is important to create a report parameter because it stores information and connection strings. If this is not done or done incorrectly, nothing will work. This procedure is performed with the CreateDynamicConnectionString, CreateValidValues, and CreateParameterValue methods.
Applying this parameter to the data source in the connection string.
DataSource dataSource = report.Report.DataSources[0]; // Your data source
if (dataSource.ConnectionProperties.DataProvider == "SQL")
{
// Set a dynamic string to connect to the data source
dataSource.ConnectionProperties.ConnectString = @"=Parameters!dbName.Value";
}
In the example above you will notice that it uses a connection to the SQL provider. However, you can use a different database at this point. See Bind Page/RDLX Report at Run Time for the list of available database providers.
Note that you need to specify a previously created parameter as a connection string, not the value of the connection string itself.
// Set a dynamic string to connect to the data source
dataSource.ConnectionProperties.ConnectString = @"=Parameters!dbName.Value";
For example, a connection string that you can place in a parameter looks like this.
Data Source=[data source];Initial Catalog=[initial catalog];Integrated Security=True;Connect Timeout=30;Encrypt=False
If you want to change only a part of the parameter, just change the connection string as follows.
// Set a dynamic string to connect to the data source
dataSource.ConnectionProperties.ConnectString = "=\"Data Source=[data source];Initial Catalog=\" & Parameters!dbName.Value & \";Integrated Security=True;Connect Timeout=30;Encrypt=False\"";
In the CreateDynamicConnectionString method, you need to pass the names of the databases, not the full connection strings:
string database1 = "example1"; // Your first database name
string database2 = "example2"; // Your second database name
Having done this, you will be asked, which database name is substituted in your connection string at the stage of thre report execution.