Connect the ASP.NET Core Dashboard to an SQL Database

• Dec 16, 2019
• 2 minutes to read

The example below shows how to create an SQL data source in code to make it available for end users. The dashboard is supplied with data from the SQLite database.

1. In your application, create the Data folder and add the nwind.db database to it from the following folder:

   C:\Users\Public\Documents\DevExpress Demos 19.1\Components\Data

2. In the Startup.cs file, create a public method that returns a configured in-memory dashboard data source storage (DataSourceInMemoryStorage).

   C#

   using DevExpress.DataAccess.Sql;
   
   public DataSourceInMemoryStorage CreateDataSourceStorage() {
       DataSourceInMemoryStorage dataSourceStorage = new DataSourceInMemoryStorage();
   
              DashboardSqlDataSource sqlDataSource = new DashboardSqlDataSource("SQL Data Source", "sqliteConnection");
               sqlDataSource.DataProcessingMode = DataProcessingMode.Client;
               DevExpress.DataAccess.Sql.SelectQuery query = DevExpress.DataAccess.Sql.SelectQueryFluentBuilder
                   .AddTable("SalesPerson")
                   .SelectAllColumns()
                   .Build("Sales Person");
               sqlDataSource.Queries.Add(query);
               dataSourceStorage.RegisterDataSource("sqlDataSource", sqlDataSource.SaveToXml());         
   
       return dataSourceStorage;
   }
   

3. Use the DashboardConfigurator.SetDataSourceStorage method to set the data source storage and pass the created CreateDataSourceStorage method to use the returned value as a parameter. Then subscribe to the DashboardConfigurator.ConfigureDataConnection event to pass the connection parameters the SQL data source requires.

   Global.asax.cs

   using DevExpress.AspNetCore;
   using DevExpress.DashboardAspNetCore;
   using DevExpress.DashboardWeb;
   
   public void ConfigureServices(IServiceCollection services) {            
       services
           .AddMvc()
           .AddDefaultDashboardController(configurator => {
               // ...
               configurator.SetDataSourceStorage(CreateDataSourceStorage());
               configurator.ConfigureDataConnection += Configurator_ConfigureDataConnection;
           });
   }
   

4. Provide the required connection parameters at runtime in the DashboardConfigurator.ConfigureDataConnection event handler.

   C#

   using DevExpress.DataAccess.ConnectionParameters;
   
   private void Configurator_ConfigureDataConnection(object sender, ConfigureDataConnectionWebEventArgs e) {
       if (e.ConnectionName == "sqliteConnection") {
           SQLiteConnectionParameters sqliteParams = new SQLiteConnectionParameters();
           sqliteParams.FileName = "file:Data/nwind.db";
           e.ConnectionParameters = sqliteParams;
       }
   }
   

5. As a result, the SQL Data Source is displayed as a Web Dashboard’s predefined data source.

   

   End users can now bind the dashboard items to data in the Web Dashboard’s UI. To learn more, see Binding Dashboard Items to Data in the Web Dashboard’s UI.