SQL Database - Update Connections (ASP.NET Core)

Mar 27, 2024
4 minutes to read

Runtime Update

You may need to update the database connections at runtime in the following situations:

When connection strings depend on parameters determined at runtime (current user ID or location).
When connection strings specified in the Configuration Manager (appsettings.json) should be filtered or replaced at runtime.

Resolve Connection by Name

When you create a data source with the Data Source Wizard, the Wizard creates a connection string in the application configuration file and specifies the connection name in the SqlDataSource.ConnectionName property. The saved report layout (.REPX file) contains only the connection name. When the report layout is loaded, the connection name is resolved to a connection string, and you can implement a custom resolution service (IConnectionProviderService), as described later.

Connection Serialized with Parameters

You cannot use the custom IConnectionProviderService if a serialized connection contains connection parameters. The IConnectionProviderService.LoadConnection method is not called when a report loads a layout that contains data source connections with embedded parameters.

If you have a saved report with serialized connection parameters, open the report, clear connection parameters, and save the report. The following code snippet uses the DataSourceManager to clear connection parameters:

csharp

XtraReport report;
var dataSources = DataSourceManager.GetDataSources(report, true);
foreach (var dataSource in dataSources) { 
   if(dataSource is SqlDataSource sds && !String.IsNullOrEmpty(sds.ConnectionName)) {
        sds.ConnectionParameters = null;
    }
}

Another option is the SqlDataSource.ConfigureDataConnection event. Handle the ConfigureDataConnection event and use the e.ConnectionParameters property to access and modify connection parameters. You can register the WebDocumentViewerOperationLogger service and subscribe to that event in the WebDocumentViewerOperationLogger.ReportOpening method.

How to Implement a Custom Connection Resolution Service

Implement the IConnectionProviderFactory service:
Implement the IConnectionProviderService class:

How to Register a Service

csharp

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

var builder = WebApplication.CreateBuilder(args);

builder.Services.ConfigureReportingServices(configurator => {            
    configurator.ConfigureWebDocumentViewer(viewerConfigurator => {
        viewerConfigurator.RegisterConnectionProviderFactory<MyConnectionProviderFactory>(); 
    });
});

var app = builder.Build();

User Specific Connection

You can specify connections based on authenticated user IDs. To do this, implement the connection provider services and use the HttpContextAccessor class in the IConnectionProviderService constructor to get the userID, as the following code snippet illustrates:

csharp

using DevExpress.Data.Entity;
using DevExpress.DataAccess.ConnectionParameters;
using DevExpress.DataAccess.Native;
using DevExpress.DataAccess.Sql;
using DevExpress.DataAccess.Web;
using DevExpress.DataAccess.Wizard.Services;
using Microsoft.AspNetCore.Http;
using Microsoft.Extensions.Configuration;
using System.Collections.Generic;
// ...
    public class CustomConnectionProviderFactory : IConnectionProviderFactory
    {
        readonly IConnectionProviderService connectionProviderService;
        public CustomConnectionProviderFactory(IConnectionProviderService connectionProviderService)
        {
            this.connectionProviderService = connectionProviderService;
        }
        public IConnectionProviderService Create()
        {
            return connectionProviderService;
        }
    }
    public class CustomConnectionProvider : IConnectionProviderService
    {
        readonly IConfiguration configuration;
        readonly string userID;
        public CustomConnectionProvider(IConfiguration configuration, IHttpContextAccessor httpContextAccessor)
        {
            this.configuration = configuration;
            this.userID = httpContextAccessor.HttpContext?.User.Identity.Name;
        }
        public SqlDataConnection LoadConnection(string connectionName)
        {
            // Implement custom logic based on the userID value.
            // ...
            ConnectionStringInfo nwindSQLiteConnectionStringInfo = connectionName != "NWindConnection"
                ? null
                : new ConnectionStringInfo
                {
                    RunTimeConnectionString = "XpoProvider=SQLite;Data Source=Database/nwind.db",
                    Name = connectionName,
                    ProviderName = "SQLite"
                };
            DataConnectionParametersBase connectionParameters;
            if (nwindSQLiteConnectionStringInfo == null
                || !AppConfigHelper.TryCreateSqlConnectionParameters(nwindSQLiteConnectionStringInfo, out connectionParameters)
                || connectionParameters == null)
            {
                throw new KeyNotFoundException($"Connection string '{connectionName}' not found.");
            }
            return new SqlDataConnection(connectionName, connectionParameters);
        }
    }

csharp

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

var builder = WebApplication.CreateBuilder(args);

builder.Services.AddScoped<IConnectionProviderService, CustomConnectionProvider>();
builder.Services.AddScoped<IConnectionProviderFactory, CustomConnectionProviderFactory>();

var app = builder.Build();