SQL Database - Register Connections

• Mar 11, 2024
• 6 minutes to read

This document describes how to create a set of SQL data connections for the End-User Report Designer in ASP.NET Core Applications. The Data Source Wizard displays these connections when users create new SQL data sources.

Tip

Online Example: How to read connection strings from different configuration sources in an ASP.NET Core application

Requirements

The SQL Data Source Wizard can use only connection strings that contain the XpoProvider key. Otherwise, the “Schema loading failed.” error occurs.

You can use one of the following approaches to avoid this error:

• Add the XpoProvider key to the existing connection strings.
• Duplicate the connection strings and add the XpoProvider key to the copied strings. To display the strings with this key only, build a custom configuration or implement a custom connection string provider.

Use the Default Connection String Provider

Use this approach to provide the Report Designer with connection strings registered in an application globally.

Register the Default Implementation

In ASP.NET Core applications, the default connection string provider implementation uses the IConfiguration service to read connection strings. Applications created based on ASP.NET Core templates call the CreateDefaultBuilder method in the Program.cs file to register this service. If the IConfiguration service is not registered, the default provider searches the appsettings.json file in the current directory and reads connection strings from the file’s ConnectionStrings section.

To enable the Report Designer to use this provider, call the static ReportDesignerConfigurationBuilder.RegisterDataSourceWizardConfigFileConnectionStringsProvider method at the application’s startup.

C#

using Microsoft.Extensions.DependencyInjection;
using DevExpress.AspNetCore;
using DevExpress.AspNetCore.Reporting;

var builder = WebApplication.CreateBuilder(args);

builder.Services.AddDevExpressControls();
builder.Services.ConfigureReportingServices(configurator => {
    configurator.ConfigureReportDesigner(designerConfigurator => {
        designerConfigurator.RegisterDataSourceWizardConfigFileConnectionStringsProvider();
    });
});

var app = builder.Build();

Override the Default Implementation

You can override the default connection string provider to get connection strings from appsettings.Development.json, connectionStrings.json, in-memory collection, and other configuration sources.

1. Create a custom configuration and load connection strings from all the required sources. For instance, create the ConfigurationBuilder class instance and use its extension methods (AddJson, AddInMemoryCollection, etc.) See Configuration in ASP.NET Core for more information.

   C#

   using System.Collections.Generic;
   using Microsoft.Extensions.Configuration;
   // ...
   
   IDictionary<string, string> GetGlobalConnectionStrings() {
       var connectionStrings = new Dictionary<string, string> {
           [$"ConnectionStrings:VehiclesInMemory"] = "XpoProvider=SQLite;Data Source=Data/vehicles.db",
           [$"ConnectionStrings:CarsInMemory"] = "XpoProvider=SQLite;Data Source=Data/cars.db;"
       };
       return new ConfigurationBuilder()
       .SetBasePath(builder.Environment.ContentRootPath)
       .AddJsonFile("appsettings.json", optional: false, reloadOnChange: true)
       .AddJsonFile($"appsettings.{builder.Environment.EnvironmentName}.json", optional: true)
       .AddInMemoryCollection(connectionStrings)
       .AddEnvironmentVariables()
       .Build()
       .GetSection("ConnectionStrings")
       .AsEnumerable(true)
       .ToDictionary(x => x.Key, x => x.Value);
   }
   

2. Call the static DefaultConnectionStringProvider.AssignConnectionStrings method at the application’s startup after the UseDevExpressControls method to register connection strings globally.

   C#

   using DevExpress.DataAccess;
   
   var builder = WebApplication.CreateBuilder(args);
   
   DefaultConnectionStringProvider.AssignConnectionStrings(GetGlobalConnectionStrings());
   
   var app = builder.Build();
   

3. Call the static ReportDesignerConfigurationBuilder.RegisterDataSourceWizardConfigFileConnectionStringsProvider method at the application’s startup as shown above.

Important

When the SQL Data Source Wizard obtains connection strings from configuration sources, only the connection names are serialized with the report definition.

Build a Custom Configuration for the Designer Only

This approach allows you to provide the Report Designer with an individual set of connection strings. You can also make the Designer read these strings from multiple configuration sources.

1. Create the ConfigurationBuilder class instance and use its extension methods (AddJson, AddInMemoryCollection, etc.) to add configuration sources. See Configuration in ASP.NET Core for more information.

   C#

   using System.Collections.Generic;
   using Microsoft.Extensions.Configuration;
   
   IConfigurationSection GetConnectionStrings() {
     var connectionStrings = new Dictionary<string, string> {
       [$"ConnectionStrings:VehiclesInMemory"] = "XpoProvider=SQLite;Data Source=Data/vehicles.db",
       [$"ConnectionStrings:CarsInMemory"] = "XpoProvider=SQLite;Data Source=Data/cars.db;"
     };
     return new ConfigurationBuilder()
       .SetBasePath(builder.Environment.ContentRootPath)
       .AddJsonFile("appsettings.json", optional: false, reloadOnChange: true)
       .AddJsonFile($"appsettings.{builder.Environment.EnvironmentName}.json", optional: true)
       .AddInMemoryCollection(connectionStrings)
       .Build()
       .GetSection("ConnectionStrings");
   }
   

2. Call the ReportDesignerConfigurationBuilder.RegisterDataSourceWizardConfigurationConnectionStringsProvider method at the application’s startup after the AddDevExpressControls method call.

   C#

   using DevExpress.AspNetCore;
   using DevExpress.AspNetCore.Reporting;
   using Microsoft.Extensions.DependencyInjection;
   
   var builder = WebApplication.CreateBuilder(args);
   
   builder.Services.ConfigureReportingServices(configurator => {
       configurator.ConfigureReportDesigner(designer => {
           designer.RegisterDataSourceWizardConfigurationConnectionStringsProvider(GetConnectionStrings());
       });
   });
   
   var app = builder.Build();
   

Note that if you registered connection strings at the Designer level, the SQL Data Source Wizard does not use the strings registered globally. The latter strings are available for the Report Designer’s Preview to fill a report’s data source and generate the resulting document.

Implement a Custom Connection String Provider

Use this approach to implement complex logic for specifying connection strings.

To configure connection strings based on strings from the appsettings.json file, follow the steps below.

1. Implement the IDataSourceWizardConnectionStringsProvider interface as demonstrated below to create a custom connection string provider.

   C#

   using System.Collections.Generic;
   using System.Linq;
   using DevExpress.DataAccess.ConnectionParameters;
   using DevExpress.DataAccess.Native;
   using DevExpress.DataAccess.Web;
   // ...
   
   public class MyDataSourceWizardConnectionStringsProvider : IDataSourceWizardConnectionStringsProvider {
       public Dictionary<string, string> GetConnectionDescriptions() {
           Dictionary<string, string> connections = AppConfigHelper.GetConnections().Keys.ToDictionary(x => x, x => x);
   
           // Customize the loaded connections list. 
           connections.Remove("Cars");
           connections.Add("Custom Connection", "Custom SQL Connection");
           return connections;
       }
   
       public DataConnectionParametersBase GetDataConnectionParameters(string name) {
           // Return custom connection parameters for the custom connection. 
           if (name == "Custom Connection") {
               return new MsSqlConnectionParameters("localhost", "dbName", "userName", "password", MsSqlAuthorizationType.SqlServer);
           }
           return AppConfigHelper.LoadConnectionParameters(name);
       }
   }
   

   In this code sample, the IDataSourceWizardConnectionStringsProvider.GetConnectionDescriptions method obtains existing connection strings from the appsettings.json file, removes an undesired data connection, and adds a new custom connection with a description.

   The IDataSourceWizardConnectionStringsProvider.GetDataConnectionParameters method provides connection parameters for a custom data connection.

2. Call the static ReportDesignerConfigurationBuilder.RegisterDataSourceWizardConnectionStringsProvider<T> method at the application’s startup to register the custom connection string provider.

   C#

   using Microsoft.Extensions.DependencyInjection;
   using DevExpress.AspNetCore;
   using DevExpress.AspNetCore.Reporting;
   
   var builder = WebApplication.CreateBuilder(args);
   
   builder.Services.ConfigureReportingServices(configurator => {
       configurator.ConfigureReportDesigner(designerConfigurator => {
           designerConfigurator.RegisterDataSourceWizardConnectionStringsProvider<MyDataSourceWizardConnectionStringsProvider>();
       });
   });
   
   var app = builder.Build();
   

   This method has another overload with the overrideWebConfigConnections parameter. When you call the method without parameters or with the parameter set to false, data connection parameters are serialized with the report data source when the report is passed to the client.

   Note

   The ASP.NET Core data protection service encrypts these parameters. You can also implement the ISecureDataConverter interface to provide a custom data protection mechanism.

   C#

   using Microsoft.Extensions.DependencyInjection;
   using DevExpress.AspNetCore;
   using DevExpress.AspNetCore.Reporting;
   
   var builder = WebApplication.CreateBuilder(args);
   
   builder.Services.ConfigureReportingServices(configurator => {
       configurator.ConfigureReportDesigner(designerConfigurator => {
           designerConfigurator.RegisterDataSourceWizardConnectionStringsProvider<MyDataSourceWizardConnectionStringsProvider>(true);
       });
   });
   
   var app = builder.Build();
   

   If you pass true as the method parameter, only the connection name is serialized with the report definition (data connection parameters are not saved). When the Web Document Viewer and the Report Designer’s Preview generate a report document, they request connection parameters from the custom connection string provider instead from the predefined provider that gets connection information from the appsettings.json file.

Print or Export Reports Outside the End-User Report Designer’s Context

The IDataSourceWizardConnectionStringsProvider interface allows you to fill the connection string list for the SQL Data Source Wizard only.

If you print or export reports in code outside the End-User Report Designer’s context, implement the cross-platform IConnectionProviderService interface instead. This interface allows you to restore the data connections of a report and its child detail report bands.