SQL Data Source in ASP.NET Web Forms

Sep 20, 2024
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.

Specify a Database Connection

Specify a connection to the database in Web.config.

In this example, the connection name is NWindConnectionString. The connection supplies the dashboard with data from the MS SQL Server database file (NWind.mdf).

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

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

Register an SQL Data Source

For example, your ASPX page contains the ASPxDashboard control which unique identifier is ASPxDashboardSql:

ASPX

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
        <div style="position: absolute; top: 0; bottom: 0; left: 0; right: 0">
            <dx:ASPxDashboard ID="ASPxDashboardSql" runat="server" Width="100%" Height="100%">
            </dx:ASPxDashboard>
        </div>
    </form>
</body>
</html>

You can define the SQL Data Source in the code-behind page that has the .aspx.cs or .aspx.vb extension depending on the language used:

Create a DashboardSqlDataSource instance.
Assign the data connection name you added in Web.config (in this example, NWindConnectionString) to the SqlDataSource.ConnectionName property.

An alternative is to handle the ASPxDashboard.ConfigureDataConnection or DashboardConfigurator.ConfigureDataConnection event: specify the connection parameters at runtime (for example, MySqlConnectionParameters) and assign them to the e.ConnectionParameters property. The selected event depends on the server-side API used in your app.
Create the Select query. Use one of the following objects:
- The SelectQuery 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.
Add the query object to the SqlDataSource.Queries collection.
Register the created data source instance in the data source storage.

Note

A code-behind page is one of the variants where you can register the data sources. For example, you can also register them in the Global.asax.cs (Global.asax.vb) file.

C#
VB.NET

using DevExpress.DashboardCommon;
using DevExpress.DashboardWeb;
using DevExpress.DataAccess.Sql;
using System;

namespace WebFormsDashboardDataSources.Pages {
    public partial class SqlDashboard : System.Web.UI.Page {
        protected void Page_Load(object sender, EventArgs e) {
        // ...
            // Create a data source storage.
            DataSourceInMemoryStorage dataSourceStorage = new DataSourceInMemoryStorage();

            // Register an SQL data source.
            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());

            // Set the configured data source storage.
            ASPxDashboardSql.SetDataSourceStorage(dataSourceStorage);

            ASPxDashboardSql.InitialDashboardId = "dashboardSql";
        }
    }
}

Imports DevExpress.DashboardCommon
Imports DevExpress.DashboardWeb
Imports DevExpress.DataAccess.Sql
Imports System

Namespace WebFormsDashboardDataSources.Pages

    Public Partial Class SqlDashboard
        Inherits Web.UI.Page

        Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)
        ' ...
            ' Create a data source storage.
            Dim dataSourceStorage As DataSourceInMemoryStorage = New DataSourceInMemoryStorage()
            ' Register an SQL data source.
            Dim sqlDataSource As DashboardSqlDataSource = 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())
            ' Set the configured data source storage.
            ASPxDashboardSql.SetDataSourceStorage(dataSourceStorage)
            ASPxDashboardSql.InitialDashboardId = "dashboardSql"
        End Sub
    End Class
End Namespace

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

web-dashboard-ex-core-SQL-data-source