SQL Data Source in ASP.NET Core

• Feb 27, 2023
• 3 minutes to read

This topic shows how to add the DashboardSqlDataSource to an in-memory data source storage, and make it available to users.

To configure an SQL data source, specify a database connection first and then create the data source in code or in the UI.

Specify a Database Connection

You can specify a connection to the database in one of the following ways:

• Specify a connection in appsettings.json. Use this way when creating a data source both in the UI and in code.

  In this example, the connection name is NWindConnectionString. The connection supplies the dashboard with data from the database file (nwind.db).

  "ConnectionStrings": {
    "NWindConnectionString": "XpoProvider=SQLite;Data Source=Data/nwind.db"
  }
  

  You can find connection strings for supported SQL data providers in the following article: Custom Connection Strings for Data Sources.

• Implement a custom connection string provider. A custom data connection provider allows you to add custom logic. For example, you can read connection strings from a custom source or manage connection strings between users. To use a custom connection provider, implement the IDataSourceWizardConnectionStringsProvider interface and pass the new provider to the DashboardConfigurator.SetConnectionStringsProvider method call. See the following topic for details: Register Default Data Connections.

• Handle the DashboardConfigurator.ConfigureDataConnection event to specify the connection parameters at runtime and assign them to the e.ConnectionParameters property. Use this event to create a data source in code.

  In the following code snippet, the connection name is sqliteConnection.

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

Create an SQL Data Source in Code

Create a Data Source

To create a new SQL Data Source, follow the steps below:

1. Create a DashboardSqlDataSource instance.
2. Assign the data connection name you specified earlier to the SqlDataSource.ConnectionName property.
3. Create the Select query. Use one of the following objects:
   • The SelectQueryFluentBuilder object specifies a set of tables/columns that form a SELECT statement when you execute a query.
   • The CustomSqlQuery object specifies an SQL query manually as a query string.
   • The StoredProcQuery object performs a stored procedure call to supply the dashboard with data.
4. Add the query object to the SqlDataSource.Queries collection.

using DevExpress.DashboardCommon;
using DevExpress.DataAccess.Sql;

DashboardSqlDataSource sqlDataSource = new DashboardSqlDataSource("SQL Data Source", "NWindConnectionString");
sqlDataSource.DataProcessingMode = DataProcessingMode.Client;
SelectQuery query = SelectQueryFluentBuilder
    .AddTable("Categories")
    .Join("Products", "CategoryID")
    .SelectAllColumnsFromTable()
    .Build("Products_Categories");
sqlDataSource.Queries.Add(query);

Register the Data Source in the Storage

Call the DataSourceInMemoryStorage.RegisterDataSource method to register the data source in the data source storage. Call the DashboardConfigurator.SetDataSourceStorage method to specify the data source storage for the Web Dashboard.

// Create a data source storage.
DataSourceInMemoryStorage dataSourceStorage = new DataSourceInMemoryStorage();

// Register the SQL data source.
dataSourceStorage.RegisterDataSource("sqlDataSource", sqlDataSource.SaveToXml());

// Register the storage for the Web Dashboard.
configurator.SetDataSourceStorage(dataSourceStorage);

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.

Create an SQL Data Source in the UI

Users can use the Dashboard Data Source Wizard to create a new SQL data source based on an existing connection.

They can create/edit a query, select a stored procedure, or add query parameters.

See the following topic for details: Specify Data Source Settings (Database).

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 ASP.NET Core Dashboard Control