Specify a Database Connection at Runtime

• Jun 05, 2022
• 3 minutes to read

Use Predefined Connections

When you use the Data Source Wizard to connect to a database, a connection string is added to the application’s configuration file:

XML

<configuration>  
...  
    <connectionStrings>  
        <add name="dxSample.NWINDConnectionString"  
            connectionString="Data Source=.;Initial Catalog=Northwind;Integrated Security=True" providerName="System.Data.SqlClient" />  
    </connectionStrings>  
...  
</configuration> 

You can use the name attribute to refer to this connection in code:

C#

sqlDataSource1.ConnectionName = "dxSample.NWINDConnectionString";

VB.NET

sqlDataSource1.ConnectionName = "dxSample.NWINDConnectionString"

Create a Connection

Code samples in this section creates a new SqlDataSource instance. However, you can use the DataSourceManager or DataSource property to get access to the report’s data source.

Use one of the following options to specify a connection:

1. Specify connection credentials. Create a DataConnectionParametersBase descendant instance (for instance, MsSqlConnectionParameters) and use its new properties to specify credentials, such as the server name, database name, user name, and password.

   C#

   MsSqlConnectionParameters connectionParameters = new MsSqlConnectionParameters("localhost", "nwind.mdf", "username", "password", MsSqlAuthorizationType.SqlServer);  
   SqlDataSource dataSource = new SqlDataSource();  
   dataSource.ConnectionName = "nwindConnection";  
   dataSource.ConnectionParameters = connectionParameters; 
   

   VB.NET

   Dim connectionParameters As New MsSqlConnectionParameters("localhost", "nwind.mdf", "username", "password", MsSqlAuthorizationType.SqlServer)
   Dim dataSource As New SqlDataSource()
   dataSource.ConnectionName = "nwindConnection"
   dataSource.ConnectionParameters = connectionParameters
   

2. Specify a connection string. Create a CustomStringConnectionParameters class instance and set its ConnectionString property to the connection string.

   C#

   string connectionString = @"XpoProvider=MSSqlServer;Data Source=(local);User ID=username;Password=password;Initial Catalog=database;Persist Security Info=true;";  
   CustomStringConnectionParameters connectionParameters = new CustomStringConnectionParameters(connectionString);  
   SqlDataSource ds = new SqlDataSource(connectionParameters);
   

   VB.NET

   Dim connectionString As String = "XpoProvider=MSSqlServer;Data Source=(local);User ID=username;Password=password;Initial Catalog=database;Persist Security Info=true;"  
   Dim connectionParameters As New CustomStringConnectionParameters(connectionString)  
   Dim ds As New SqlDataSource(connectionParameters)  
   

3. Handle the SqlDataSource.ConfigureDataConnection event and specify the event’s ConnectionParameters. Check the event’s ConnectionName property to identify the connection.

   C#

   private void sqlDataSource1_ConfigureDataConnection(object sender, DevExpress.DataAccess.Sql.ConfigureDataConnectionEventArgs e) {  
   if (e.ConnectionName == "connection1") {  
           e.ConnectionParameters = new Access97ConnectionParameters("../../nwind.mdb", "", "");  
      }  
   }  
   

   VB.NET

   Private Sub sqlDataSource1_ConfigureDataConnection(ByVal sender As Object, ByVal e As DevExpress.DataAccess.Sql.ConfigureDataConnectionEventArgs)  
       If e.ConnectionName = "connection1" Then  
           e.ConnectionParameters = New Access97ConnectionParameters("../../nwind.mdb", "", "")  
       End If  
   End Sub  
   

Restore Connections Serialized in a Report

You can implement a service with the IConnectionProviderService interface and register the service in the application. The interface has the LoadConnection method that is invoked when the report is loaded for each connection serialized in the report definition file. Implement the LoadConnection method to get a connection by name and return the valid data connection to the report.

Important

If you create a new SqlDataSource instance, it does not contain any queries and cannot retrieve any data. You should populate the SqlDataSource.Queries collection. For more information, review the following help topic: Bind a Report to a Microsoft SQL Server Database at Runtime.