Example T830472

Visible to All Users

Reporting for ASP.NET Core - Configuration-Dependent Connection Strings for Report Designer

Implementation

In .NET Core applications, the default connection string provider implementation searches the appsettings.json file in the current directory and reads connection strings from the file’s ConnectionStrings section.

This example demonstrates how to get connection strings from a set of different configuration sources in an ASP.NET Core application.

This example implements a custom configuration and loads connection strings from various data sources. This example uses the ConfigurationBuilder class and its AddJsonFile and AddInMemoryCollection extension methods.

Register Connection Strings Globally

To register connection strings from a custom configuration globally, call the static DefaultConnectionStringProvider.AssignConnectionStrings method at the application startup.

These connection strings are available for the Report Designer's Preview to fill a report's data source and generate the resulting document.

In this example, global connection strings are read from the following sources:

appsettings.json file;
appsettings.Development.json file;
in-memory collection.

Register Connection Strings For the Data Source Wizard

The Report Designer obtains connection strings from a custom configuration using the RegisterDataSourceWizardConfigurationConnectionStringsProvider method at the application startup.

The obtained connection strings are shown in the SQL Data Source Wizard in the section that prompts the user to create new data sources. Note that the SQL Data Source Wizard uses only the specified set of connection strings, not including the strings registered globally.

In this example, the Report Designer's connection strings are read from the following files:

appsettings.json
appsettings.Development.json

Documentation

More Examples

ASP.NET Core Reporting - Best Practices

Does this example address your development requirements/objectives?

(you will be redirected to DevExpress.com to submit your response)

Example Code

DXReportingCustomConnectionString/Startup.cs

C#
using System;
using System.IO;
using DevExpress.AspNetCore;
using DevExpress.AspNetCore.Reporting;
using DevExpress.Security.Resources;
using DevExpress.XtraReports.Web.Extensions;
using Microsoft.AspNetCore.Builder;
using Microsoft.AspNetCore.Hosting;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Hosting;
using Microsoft.Extensions.Logging;
using DXReportingCustomConnectionString.Services;
using DXReportingCustomConnectionString.Data;

namespace DXReportingCustomConnectionString {
    public class Startup {
        readonly CustomConfigurationProvider configurationProvider;
        public Startup(IConfiguration configuration, IWebHostEnvironment hostingEnvironment) {
            Configuration = configuration;
            this.configurationProvider = new CustomConfigurationProvider(hostingEnvironment);
        }

        public IConfiguration Configuration { get; }

        // This method gets called by the runtime. Use this method to add services to the container.
        public void ConfigureServices(IServiceCollection services) {
            services.AddDevExpressControls();
            services.AddScoped<ReportStorageWebExtension, CustomReportStorageWebExtension>();
            services
                .AddMvc()
                .AddNewtonsoftJson();
            services.ConfigureReportingServices(configurator => {
                configurator.ConfigureReportDesigner(designerConfigurator => {
                    designerConfigurator.RegisterDataSourceWizardConnectionStringsProvider<CustomSqlDataSourceWizardConnectionStringsProvider>();
                    designerConfigurator.RegisterDataSourceWizardConfigurationConnectionStringsProvider(configurationProvider.GetReportDesignerWizardConfigurationSection());
                });
                configurator.ConfigureWebDocumentViewer(viewerConfigurator => {
                    viewerConfigurator.UseCachedReportSourceBuilder();
                });
                configurator.UseAsyncEngine();
            });
            services.AddDbContext<ReportDbContext>(options => options.UseSqlite(Configuration.GetConnectionString("ReportsDataConnectionString")));
        }

        // This method gets called by the runtime. Use this method to configure the HTTP request pipeline.
        public void Configure(IApplicationBuilder app, IWebHostEnvironment env, ILoggerFactory loggerFactory, ReportDbContext db) {
            db.InitializeDatabase();
            var contentDirectoryAllowRule = DirectoryAccessRule.Allow(new DirectoryInfo(Path.Combine(env.ContentRootPath, "..", "Content")).FullName);
            AccessSettings.ReportingSpecificResources.TrySetRules(contentDirectoryAllowRule, UrlAccessRule.Allow());
            DevExpress.XtraReports.Configuration.Settings.Default.UserDesignerOptions.DataBindingMode = DevExpress.XtraReports.UI.DataBindingMode.Expressions;
            app.UseDevExpressControls();

            DevExpress.DataAccess.DefaultConnectionStringProvider.AssignConnectionStrings(() => configurationProvider.GetGlobalConnectionStrings());


            System.Net.ServicePointManager.SecurityProtocol |= System.Net.SecurityProtocolType.Tls12;
            if(env.IsDevelopment()) {
                app.UseDeveloperExceptionPage();
            } else {
                app.UseExceptionHandler("/Home/Error");
                // The default HSTS value is 30 days. You may want to change this for production scenarios, see https://aka.ms/aspnetcore-hsts.
                app.UseHsts();
            }
            app.UseHttpsRedirection();
            app.UseStaticFiles();
            
            app.UseRouting();

            app.UseAuthorization();
            app.UseEndpoints(endpoints => {
                endpoints.MapControllerRoute(
                    name: "default",
                    pattern: "{controller=Home}/{action=Index}/{id?}");
            });
        }
    }
}

DXReportingCustomConnectionString/Services/CustomConfigurationProvider.cs

C#
using System.Collections.Generic;
using System.Linq;
using Microsoft.AspNetCore.Hosting;
using Microsoft.Extensions.Configuration;

public class CustomConfigurationProvider {
    readonly IWebHostEnvironment hostingEnvironment;
    public CustomConfigurationProvider(IWebHostEnvironment hostingEnvironment) {
        this.hostingEnvironment = hostingEnvironment;
    }
    public IDictionary<string, string> GetGlobalConnectionStrings() {
        var connectionStrings = new Dictionary<string, string> {
            [$"ConnectionStrings:Vehicles_InMemory"] = "XpoProvider=SQLite;Data Source=Data/vehicles.db",
            [$"ConnectionStrings:Cars_InMemory"] = "XpoProvider=SQLite;Data Source=Data/cars.db;"
        };
        return new ConfigurationBuilder()
            .SetBasePath(hostingEnvironment.ContentRootPath)
            .AddJsonFile("appsettings.json", optional: false, reloadOnChange: true)
            .AddJsonFile($"appsettings.{hostingEnvironment.EnvironmentName}.json", optional: true)
            .AddInMemoryCollection(connectionStrings)
            .AddEnvironmentVariables()
            .Build()
            .GetSection("ConnectionStrings")
            .AsEnumerable(true)
            .ToDictionary(x => x.Key, x => x.Value);
    }

    public IConfigurationSection GetReportDesignerWizardConfigurationSection() {
        return new ConfigurationBuilder()
            .SetBasePath(hostingEnvironment.ContentRootPath)
            .AddJsonFile("appsettings.json", optional: false, reloadOnChange: true)
            .AddJsonFile($"appsettings.{hostingEnvironment.EnvironmentName}.json", optional: true)
            .Build()
            .GetSection("ConnectionStrings");
    }
}

DXReportingCustomConnectionString/appsettings.json

JSON
{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft": "Warning",
      "Microsoft.Hosting.Lifetime": "Information"
    }
  },
  "ConnectionStrings": {
    "Northwind_Json": "XpoProvider=SQLite;Data Source=|DataDirectory|/Data/nwind.db",
    "ReportsDataConnectionString": "Filename=Data/reportsData.db"
  }
}

DXReportingCustomConnectionString/appsettings.Development.json

JSON
{
  "Logging": {
    "LogLevel": {
      "Default": "Debug",
      "System": "Information",
      "Microsoft": "Information"
    }
  },
  "ConnectionStrings": {
    "Countries_DevelopmentJson": "XpoProvider=SQLite;Data Source=Data/countries.db"
  }
}

DXReportingCustomConnectionString/Controllers/HomeController.cs

C#
using System.Collections.Generic;
using System.Threading.Tasks;
using DevExpress.DataAccess.Sql;
using Microsoft.AspNetCore.Mvc;
using DevExpress.XtraReports.UI;
using DevExpress.XtraReports.Web.ReportDesigner;
using DevExpress.AspNetCore.Reporting.QueryBuilder;
using DevExpress.AspNetCore.Reporting.ReportDesigner;
using DevExpress.AspNetCore.Reporting.WebDocumentViewer;


namespace DXReportingCustomConnectionString.Controllers {
    public class HomeController : Controller {
        public IActionResult Index() {
            return View();
        }
        public IActionResult Error() {
            Models.ErrorModel model = new Models.ErrorModel();
            return View(model);
        }

        public async Task<IActionResult> Designer(
            [FromServices] IReportDesignerClientSideModelGenerator clientSideModelGenerator,
            [FromQuery] string reportName) {
            Models.ReportDesignerCustomModel model = new Models.ReportDesignerCustomModel();
            model.ReportDesignerModel = await CreateDefaultReportDesignerModel(clientSideModelGenerator, reportName, null);
            return View(model);
        }

         public static Dictionary<string, object> GetAvailableDataSources() {
            var dataSources = new Dictionary<string, object> {
                { "Northwind", CreateDataSource("Northwind_Json", SelectQueryFluentBuilder.AddTable("Products").SelectAllColumnsFromTable().Build("Products")) },
                { "Countries", CreateDataSource("Countries_DevelopmentJson", SelectQueryFluentBuilder.AddTable("Regions").SelectAllColumnsFromTable().Build("Regions")) },
                { "Vehicles", CreateDataSource("Vehicles_InMemory", SelectQueryFluentBuilder.AddTable("Model").SelectAllColumnsFromTable().Build("Model")) },
                { "Cars", CreateDataSource("Cars_InMemory", SelectQueryFluentBuilder.AddTable("Cars").SelectAllColumnsFromTable().Build("Cars")) }
            };
            return dataSources;
        }

        public static SqlDataSource CreateDataSource(string connectionStringName, SelectQuery query) {
            SqlDataSource ds = new SqlDataSource(connectionStringName);
            ds.Queries.Add(query);
            ds.RebuildResultSchema();
            return ds;
        }

        public static async Task<ReportDesignerModel> CreateDefaultReportDesignerModel(IReportDesignerClientSideModelGenerator clientSideModelGenerator, string reportName, XtraReport report) {
            reportName = string.IsNullOrEmpty(reportName) ? "TestReport" : reportName;
            var dataSources = GetAvailableDataSources();
            if(report != null) {
                return await clientSideModelGenerator.GetModelAsync(report, dataSources, ReportDesignerController.DefaultUri, WebDocumentViewerController.DefaultUri, QueryBuilderController.DefaultUri);
            }
            return await clientSideModelGenerator.GetModelAsync(reportName, dataSources, ReportDesignerController.DefaultUri, WebDocumentViewerController.DefaultUri, QueryBuilderController.DefaultUri);
        }
    }
}

DXReportingCustomConnectionString/Views/Home/Designer.cshtml

Razor
@using DXReportingCustomConnectionString.Models
@model ReportDesignerCustomModel

@{
    var designerRender = Html.DevExpress().ReportDesigner("reportDesigner")
        .Height("100%")
        .Bind(Model.ReportDesignerModel);
    @designerRender.RenderHtml()
}

@section Scripts {
    <link href="~/css/dx-reporting-skeleton-screen.css" rel="stylesheet" />
    <link rel="stylesheet" href="~/css/viewer.part.bundle.css" />
    <link rel="stylesheet" href="~/css/designer.part.bundle.css" />
    <link rel="stylesheet" href="~/css/ace/ace.bundle.css" />
    <link rel="stylesheet" href="~/css/dx.material.blue.light.bundle.css" />

    <script src="~/js/reporting.thirdparty.bundle.js"></script>
    <script src="~/js/viewer.part.bundle.js"></script>
    <script src="~/js/designer.part.bundle.js"></script>

    @designerRender.RenderScripts()
}

DevExpress Support Team

last modified 2 years ago

Modified: June 28, 2023 7:23 AM

Platform: ASP.NET Core Reporting

Product: Reporting for ASP.NET Core

Disclaimer: The information provided on DevExpress.com and affiliated web properties (including the DevExpress Support Center) is provided "as is" without warranty of any kind. Developer Express Inc disclaims all warranties, either express or implied, including the warranties of merchantability and fitness for a particular purpose. Please refer to the DevExpress.com Website Terms of Use for more information in this regard.

Confidential Information: Developer Express Inc does not wish to receive, will not act to procure, nor will it solicit, confidential or proprietary materials and information from you through the DevExpress Support Center or its web properties. Any and all materials or information divulged during chats, email communications, online discussions, Support Center tickets, or made available to Developer Express Inc in any manner will be deemed NOT to be confidential by Developer Express Inc. Please refer to the DevExpress.com Website Terms of Use for more information in this regard.