Change SQL Data Source Connection Settings at Runtime

• Mar 20, 2023
• 3 minutes to read

If you want to change connections settings of your SqlDataSource component at runtime, do the following:

1. Retrieve a required component from your report.
2. Specify new connection settings for this component.

See the sections below for details on how to complete these steps.

Retrieve the SqlDataSource Component from a Report

If your report contains only one such component, you can access it through the report’s DataSource property and update connection settings as needed:

C#

(report.DataSource as SqlDataSource).ConnectionName = "nwind";

VB.NET

TryCast(report.DataSource, SqlDataSource).ConnectionName = "nwind"

If your report contains multiple data sources, you can use DataSourceManager class methods to get required data sources.

The following code sample shows how to get all report data sources (except for subreport data sources), iterate through these data sources, and update settings (the ConnectionName property) of each data source whose type is SqlDataSource.

C#

using DevExpress.DataAccess.Sql;
using DevExpress.XtraReports;
//...

var report = new XtraReport1();

var dataSources = DataSourceManager.GetDataSources(report, includeSubReports: false);

foreach (var dataSource in dataSources) {
    if (dataSource is SqlDataSource) {
        (dataSource as SqlDataSource).ConnectionName = "nwind";
    }
}

VB.NET

Imports DevExpress.DataAccess.Sql
Imports DevExpress.XtraReports
'...

Private report = New XtraReport1()

Private dataSources = DataSourceManager.GetDataSources(report, includeSubReports:= False)

For Each dataSource In dataSources
    If TypeOf dataSource Is SqlDataSource Then
        TryCast(dataSource, SqlDataSource).ConnectionName = "nwind"
    End If
Next dataSource

Refer to the following method descriptions for more examples:

GetDataSources<T>

Returns all report data sources.

GetDataSourceAssignables<T>

Returns a report and its elements (subreports, controls, bands, parameters) that match the specified type and to which a data source can be assigned.

Specify New Connection Settings

The SqlDataSource component can store connection settings in the following properties:

ConnectionParameters

Stores a DataConnectionParametersBase class descendant.

ConnectionName

Has effect only if the ConnectionParameters property is not specified. Stores the name of a connection string that the component loads from your project’s default configuration file.

Thus, after you access an SqlDataSource component, you can do one of the following to update the component’s connection settings:

• Create a new DataConnectionParametersBase class descendant instance and assign it to the component’s ConnectionParameters property.
• Assign the name of a new connection string to the components’s ConnectionName property (in this case, ensure that the ConnectionParameters property value is null).

The following code sample shows how to create and configure an MsSqlConnectionParameters instance (a DataConnectionParametersBase descendant) and assign it to the ConnectionParameters property of a report data source.

C#

using DevExpress.DataAccess.ConnectionParameters;
using DevExpress.DataAccess.Sql;
using DevExpress.XtraReports.UI;
//...

var report = new XtraReport1();

var msSqlConnectionParams = new MsSqlConnectionParameters() {
    ServerName = "localhost", DatabaseName = "Northwind",
    UserName = null, Password = null,
    AuthorizationType = MsSqlAuthorizationType.Windows
};

(report.DataSource as SqlDataSource).ConnectionParameters = msSqlConnectionParams;

VB.NET

Imports DevExpress.DataAccess.ConnectionParameters
Imports DevExpress.DataAccess.Sql
Imports DevExpress.XtraReports.UI
'...

Private report = New XtraReport1()

Private msSqlConnectionParams = New MsSqlConnectionParameters() With {
    .ServerName = "localhost",
    .DatabaseName = "Northwind",
    .UserName = Nothing,
    .Password = Nothing,
    .AuthorizationType = MsSqlAuthorizationType.Windows
}

TryCast(report.DataSource, SqlDataSource).ConnectionParameters = msSqlConnectionParams

If you want to use custom logic for transforming a report’s ConnectionName property value to connection settings, implement and register the IConnectionProviderService interface in your application. Refer to the description of this interface for detailed code samples. In ASP.NET Core applications, you can also use the DefaultConnectionStringProvider class for the same purpose.