SQL Data Source

• Feb 27, 2023
• 2 minutes to read

This tutorial shows how to add the DashboardSqlDataSource to an in-memory data source storage, and make it available to users. The sample MDF database supplies the dashboard with data.

1. In your application, add the NWind.mdf database to the App_Data folder from the C:\Users\Public\Documents\DevExpress Demos 24.1\Components\Data directory.

2. In Web.config, specify a connection string to the database.

   <configuration>
       <connectionStrings>
           <add name="NWindConnectionString" connectionString="data source=(localdb)\mssqllocaldb;attachdbfilename=|DataDirectory|\NWind.mdf;integrated security=True;connect timeout=120" providerName="System.Data.SqlClient" />    
       </connectionStrings>
   </configuration>
   

3. In the dashboard configuration file (for example, DashboardConfig.cs / DashboardConfig.vb), create a public method that returns the configured dashboard’s data source storage (DataSourceInMemoryStorage) and define the SQL data source.

   C#

   using System;
   using DevExpress.DashboardCommon;
   using DevExpress.DashboardWeb;
   
   public static DataSourceInMemoryStorage CreateDataSourceStorage() {
       DataSourceInMemoryStorage dataSourceStorage = new DataSourceInMemoryStorage();
   
           DashboardSqlDataSource sqlDataSource = new DashboardSqlDataSource("SQL Data Source", "NWindConnectionString");
           SelectQuery query = SelectQueryFluentBuilder
               .AddTable("SalesPerson")
               .SelectAllColumnsFromTable()
               .Build("Sales Person");
           sqlDataSource.Queries.Add(query);
           dataSourceStorage.RegisterDataSource("sqlDataSource", sqlDataSource.SaveToXml());  
   
       return dataSourceStorage;
   }
   

   VB.NET

   Imports System
   Imports DevExpress.DashboardCommon
   Imports DevExpress.DashboardWeb
   
   Public Shared Function CreateDataSourceStorage() As DataSourceInMemoryStorage
       Dim dataSourceStorage As New DataSourceInMemoryStorage()
   
       Dim sqlDataSource As New DashboardSqlDataSource("SQL Data Source", "NWindConnectionString")
       Dim query As SelectQuery = SelectQueryFluentBuilder.AddTable("SalesPerson").SelectAllColumnsFromTable().Build("Sales Person")
       sqlDataSource.Queries.Add(query)
       dataSourceStorage.RegisterDataSource("sqlDataSource", sqlDataSource.SaveToXml())
   
       Return dataSourceStorage
   End Function
   

   Note

   Use one of the following approaches to specify connection settings at runtime:

   • Pass the data provider’s connection parameters (the SqlServerConnectionParametersBase descendant) to the DashboardSqlDataSource constructor.
   • Handle the ASPxDashboard.ConfigureDataConnection event.

4. Call the DashboardConfigurator.SetDataSourceStorage method to configure the data source storage. Use the created CreateDataSourceStorage method as the SetDataSourceStorage parameter.

   C#

   using System;
   using DevExpress.DashboardWeb;
   
   public static void RegisterService(RouteCollection routes) {
       routes.MapDashboardRoute("dashboardControl", "DefaultDashboard");
   
       // ...
   
       DashboardConfigurator.Default.SetDataSourceStorage(CreateDataSourceStorage());
   }
   

   VB.NET

   Imports System
   Imports DevExpress.DashboardWeb
   
   Public Shared Sub RegisterService(ByVal routes As RouteCollection)
       routes.MapDashboardRoute("dashboardControl", "DefaultDashboard")
   
       ' ...
   
       DashboardConfigurator.Default.SetDataSourceStorage(CreateDataSourceStorage())
   End Sub
   

The SQL Data Source is now available in the Web Dashboard:

Users can now bind dashboard items to data in the Web Dashboard’s UI.

Example

The example shows how to make a set of data sources available for users in the Web Dashboard application.

View Example: How to Register Data Sources for the ASP.NET MVC Dashboard Extension