Change SQL Data Source Connection Settings at Runtime
- 3 minutes to read
If you want to change connections settings of your SqlDataSource component at runtime, do the following:
- Retrieve a required component from your report.
- 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:
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.
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";
}
}
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.
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;
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.