SQL Database - Update Connections

• Dec 27, 2020
• 3 minutes to read

Runtime Update

You can create a report that obtains data from a sample database, for development purposes, and switches the database connection to the production database, for end-users. To retrieve production data, specify a database connection at runtime and implement a method to update database connections when users open a report.

1. Implement the IConnectionProviderFactory service:

   C#

   using DevExpress.DataAccess.Web;
   using DevExpress.DataAccess.Wizard.Services;
   // ... 
       public class MyConnectionProviderFactory : IConnectionProviderFactory
       {
           public IConnectionProviderService Create()
           {
               return new MyConnectionProviderService();
           }
       }
   

   VB.NET

   Imports DevExpress.DataAccess.Web
   Imports DevExpress.DataAccess.Wizard.Services
   ' ...
       Public Class MyConnectionProviderFactory
           Implements IConnectionProviderFactory
           Public Function Create() As IConnectionProviderService Implements IConnectionProviderFactory.Create
               Return New MyConnectionProviderService()
           End Function
       End Class
   

   The service returns an instance of the IConnectionProviderService that supplies connections.

2. Implement the IConnectionProviderService class:

   C#

   using DevExpress.DataAccess.ConnectionParameters;
   using DevExpress.DataAccess.Sql;
   using DevExpress.DataAccess.Wizard.Services;
   using System.Collections.Generic;
   // ...
       public class MyConnectionProviderService : IConnectionProviderService
       {        public SqlDataConnection LoadConnection(string connectionName)
           {
               switch (connectionName)
               {
                   case ("NWindConnectionString"):
                       return new SqlDataConnection("NWindConnectionString",
                               new MsSqlConnectionParameters()
                               {
                                   AuthorizationType = MsSqlAuthorizationType.Windows,
                                   DatabaseName = "Northwind",
                                   ServerName = "localhost"
                               });
                   default:
                       throw new KeyNotFoundException($"Connection string '{connectionName}' not found.");
               }
           }
       }
   

   VB.NET

   Imports DevExpress.DataAccess.ConnectionParameters
   Imports DevExpress.DataAccess.Sql
   Imports DevExpress.DataAccess.Wizard.Services
   '...
       Public Class MyConnectionProviderService
           Implements IConnectionProviderService
   
           Public Function LoadConnection(ByVal connectionName As String) As SqlDataConnection Implements IConnectionProviderService.LoadConnection
               Select Case connectionName
                   Case ("NWindConnectionString")
                       Return New SqlDataConnection("NWindConnectionString", New MsSqlConnectionParameters() With {.AuthorizationType = MsSqlAuthorizationType.Windows, .DatabaseName = "Northwind", .ServerName = "localhost"})
                   Case Else
                       Throw New KeyNotFoundException($"Connection string '{connectionName}' not found.")
               End Select
           End Function
       End Class
   

   When the Document Viewer or Report Designer Preview loads a report, it calls the IConnectionProviderService.LoadConnection method and allows you to return a connection with the specified name stored in a report.

   You can use various DataConnectionParametersBase descendants to establish a connection to different DBMS and data files in different formats, such as:

   • MS SQL Server - MsSqlConnectionParameters
   • Oracle - OracleConnectionParameters
   • XML file - XmlFileConnectionParameters.

   To specify a custom connection string, use the CustomStringConnectionParameters instance.

3. Register the MyConnectionProviderFactory service at application startup:

   C#

   using DevExpress.AspNetCore.Reporting;
   using Microsoft.Extensions.DependencyInjection;
   // ...
       public class Startup {
       // ...
           public void ConfigureServices(IServiceCollection services) {
           // ...
               services.ConfigureReportingServices(configurator => {
               // ...
                   configurator.ConfigureWebDocumentViewer(viewerConfigurator => {
                   // ...              
                       viewerConfigurator.RegisterConnectionProviderFactory<MyConnectionProviderFactory>(); 
                   });
               });
           }
           // ...
       }
   

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:

C#

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);
        }
    }

Register the CustomConnectionProviderFactory and CustomConnectionProvider services at application start:

C#

using DevExpress.AspNetCore.Reporting;
using Microsoft.Extensions.DependencyInjection;
// ...
    public class Startup {
    // ...
        public void ConfigureServices(IServiceCollection services) {
        // ...
        services.AddScoped<IConnectionProviderService, CustomConnectionProvider>();
        services.AddScoped<IConnectionProviderFactory, CustomConnectionProviderFactory>();
        }
        // ...
    }