How to: Customize a Data Store Schema for SQL Data Sources

• Mar 31, 2018
• 6 minutes to read

This example demonstrates how to customize a data store schema for a dashboard data source that uses a connection to the Northwind database.

In this example, the IDBSchemaProviderEx interface is implemented by a class that defines a custom data store schema containing two related tables.

To see the result, add a new query or edit the existing query.

Note

A complete sample project is available at https://github.com/DevExpress-Examples/aspnet-mvc-dashboard-how-to-customize-a-data-store-schema-for-sql-data-sources-t584271.

Index.cshtml (C#)

<script type="text/javascript">
    function onBeforeRender(sender) {
        var control = sender.getDashboardControl();
        control.registerExtension(new DevExpress.Dashboard.DashboardPanelExtension(control));
    }
</script>
@Html.DevExpress().Dashboard(settings => {
    settings.Name = "Dashboard";
    settings.Width = Unit.Percentage(100);
    settings.Height = Unit.Percentage(100);
    settings.ClientSideEvents.BeforeRender = "onBeforeRender";
}).GetHtml()

Global.asax.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Http;
using System.Web.Mvc;
using System.Web.Routing;

namespace MvcDashboard_CustomSchemaProvider {
    // Note: For instructions on enabling IIS6 or IIS7 classic mode, 
    // visit http://go.microsoft.com/?LinkId=9394801

    public class MvcApplication : System.Web.HttpApplication {
        protected void Application_Start() {
            DashboardConfig.RegisterService(RouteTable.Routes);
            AreaRegistration.RegisterAllAreas();

            GlobalConfiguration.Configure(WebApiConfig.Register);
            FilterConfig.RegisterGlobalFilters(GlobalFilters.Filters);
            RouteConfig.RegisterRoutes(RouteTable.Routes);

            ModelBinders.Binders.DefaultBinder = new DevExpress.Web.Mvc.DevExpressEditorsBinder();

            DevExpress.Web.ASPxWebControl.CallbackError += Application_Error;
        }

        protected void Application_Error(object sender, EventArgs e) {
            Exception exception = System.Web.HttpContext.Current.Server.GetLastError();
            //TODO: Handle Exception
        }
    }
}

RouteConfig.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.Web.Routing;

namespace MvcDashboard_CustomSchemaProvider {
    public class RouteConfig {
        public static void RegisterRoutes(RouteCollection routes) {
            routes.IgnoreRoute("{resource}.axd/{*pathInfo}");
            routes.IgnoreRoute("{resource}.ashx/{*pathInfo}");

            routes.MapRoute(
                name: "Default", // Route name
                url: "{controller}/{action}/{id}", // URL with parameters
                defaults: new { controller = "Home", action = "Index", id = UrlParameter.Optional } // Parameter defaults
            );
        }
    }
}

FilterConfig.cs

using System.Web;
using System.Web.Mvc;

namespace MvcDashboard_CustomSchemaProvider {
    public class FilterConfig {
        public static void RegisterGlobalFilters(GlobalFilterCollection filters) {
            filters.Add(new HandleErrorAttribute());
        }
    }
}

HomeController.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;

namespace MvcDashboard_CustomSchemaProvider.Controllers {
    public class HomeController : Controller {
        public ActionResult Index() {
            return View();
        }
    }
}

DashboardConfig.cs

using DevExpress.DashboardWeb;
using DevExpress.DashboardWeb.Mvc;
using DevExpress.DataAccess.Sql;
using DevExpress.Xpo.DB;
using System.Collections.Specialized;
using System.Web.Routing;

namespace MvcDashboard_CustomSchemaProvider {
    public static class DashboardConfig {
        public static void RegisterService(RouteCollection routes) {
            routes.MapDashboardRoute("dashboardControl");

            DashboardFileStorage dashboardFileStorage = new DashboardFileStorage("~/App_Data/Dashboards");
            DashboardConfigurator.Default.SetDashboardStorage(dashboardFileStorage);  

            DashboardConfigurator.Default.SetDBSchemaProvider(new CustomDBSchemaProvider());
        }
    }

    // Creates a new class that defines a custom data store schema by implementing the 
    // IDBSchemaProvider interface.
    class CustomDBSchemaProvider : IDBSchemaProviderEx {
        DBTable[] tables;
        public void LoadColumns(SqlDataConnection connection, params DBTable[] tables) {
            foreach (DBTable table in tables) {
                if (table.Name == "Categories" && table.Columns.Count == 0) {
                    DBColumn categoryIdColumn = new DBColumn { Name = "CategoryID" };
                    table.AddColumn(categoryIdColumn);
                    DBColumn categoryNameColumn = new DBColumn { Name = "CategoryName" };
                    table.AddColumn(categoryNameColumn);
                }
                if (table.Name == "Products" && table.Columns.Count == 0) {
                    DBColumn categoryIdColumn = new DBColumn { Name = "CategoryID" };
                    table.AddColumn(categoryIdColumn);
                    DBColumn productNameColumn = new DBColumn { Name = "ProductName" };
                    table.AddColumn(productNameColumn);

                    DBForeignKey foreignKey = new DBForeignKey(
                        new[] { categoryIdColumn },
                        "Categories",
                        CustomDBSchemaProvider.CreatePrimaryKeys("CategoryID"));
                    table.ForeignKeys.Add(foreignKey);
                }
            }
        }

        public static StringCollection CreatePrimaryKeys(params string[] names) {
            StringCollection collection = new StringCollection();
            collection.AddRange(names);
            return collection;
        }

        public DBTable[] GetTables(SqlDataConnection connection, params string[] tableList) {
            if (connection.Name == "nwindConnection") {
                if (tables != null) {
                    return tables;
                }
                tables = new DBTable[2];

                DBTable categoriesTable = new DBTable("Categories");
                tables[0] = categoriesTable;

                DBTable productsTable = new DBTable("Products");
                tables[1] = productsTable;
            }
            else
                tables = new DBTable[0];
            return tables;
        }

        public DBTable[] GetViews(SqlDataConnection connection, params string[] viewList) {
            DBTable[] views = new DBTable[0];
            return views;
        }

        public DBStoredProcedure[] GetProcedures(SqlDataConnection connection, params string[] procedureList) {
            DBStoredProcedure[] storedProcedures = new DBStoredProcedure[0];
            return storedProcedures;
        }
    }
}

DashboardConfig.vb

Imports System.Collections.Specialized
Imports System.Web.Routing
Imports DevExpress.DashboardWeb
Imports DevExpress.DashboardWeb.Mvc
Imports DevExpress.DataAccess.Sql
Imports DevExpress.Xpo.DB

Namespace MvcDashboard_CustomSchemaProvider
    Public NotInheritable Class DashboardConfig

        Private Sub New()
        End Sub

        Public Shared Sub RegisterService(ByVal routes As RouteCollection)
            routes.MapDashboardRoute("dashboardControl")

            Dim dashboardFileStorage As New DashboardFileStorage("~/App_Data/Dashboards")
            DashboardConfigurator.Default.SetDashboardStorage(dashboardFileStorage)

            DashboardConfigurator.Default.SetDBSchemaProvider(New CustomDBSchemaProvider())
        End Sub
    End Class

    ' Creates a new class that defines a custom data store schema by implementing the 
    ' IDBSchemaProvider interface.
    Friend Class CustomDBSchemaProvider
        Implements IDBSchemaProviderEx

        Private tables() As DBTable
        Public Sub LoadColumns(ByVal connection As SqlDataConnection, ParamArray ByVal tables() As DBTable) _
            Implements IDBSchemaProviderEx.LoadColumns
            For Each table As DBTable In tables
                If table.Name = "Categories" AndAlso table.Columns.Count = 0 Then
                    Dim categoryIdColumn As DBColumn = New DBColumn With {.Name = "CategoryID"}
                    table.AddColumn(categoryIdColumn)
                    Dim categoryNameColumn As DBColumn = New DBColumn With {.Name = "CategoryName"}
                    table.AddColumn(categoryNameColumn)
                End If
                If table.Name = "Products" AndAlso table.Columns.Count = 0 Then
                    Dim categoryIdColumn As DBColumn = New DBColumn With {.Name = "CategoryID"}
                    table.AddColumn(categoryIdColumn)
                    Dim productNameColumn As DBColumn = New DBColumn With {.Name = "ProductName"}
                    table.AddColumn(productNameColumn)

                    Dim foreignKey As New DBForeignKey({categoryIdColumn}, "Categories", CustomDBSchemaProvider.CreatePrimaryKeys("CategoryID"))
                    table.ForeignKeys.Add(foreignKey)
                End If
            Next table
        End Sub

        Public Shared Function CreatePrimaryKeys(ParamArray ByVal names() As String) As StringCollection
            Dim collection As New StringCollection()
            collection.AddRange(names)
            Return collection
        End Function

        Public Function GetTables(ByVal connection As SqlDataConnection, ParamArray ByVal tableList() As String) As DBTable() _
            Implements IDBSchemaProviderEx.GetTables
            If connection.Name = "nwindConnection" Then
                If tables IsNot Nothing Then
                    Return tables
                End If
                tables = New DBTable(1) {}

                Dim categoriesTable As New DBTable("Categories")
                tables(0) = categoriesTable

                Dim productsTable As New DBTable("Products")
                tables(1) = productsTable
            Else
                tables = New DBTable() {}
            End If
            Return tables
        End Function

        Public Function GetViews(ByVal connection As SqlDataConnection, ParamArray ByVal viewList() As String) As DBTable() _
            Implements IDBSchemaProviderEx.GetViews
            Dim views(-1) As DBTable
            Return views
        End Function

        Public Function GetProcedures(ByVal connection As SqlDataConnection, ParamArray ByVal procedureList() As String) As DBStoredProcedure() _
            Implements IDBSchemaProviderEx.GetProcedures
            Dim storedProcedures(-1) As DBStoredProcedure
            Return storedProcedures
        End Function
    End Class
End Namespace

RouteConfig.vb

Imports System.Web.Mvc
Imports System.Web.Routing

Namespace MvcDashboard_CustomSchemaProvider
    Public Class RouteConfig
        Public Shared Sub RegisterRoutes(ByVal routes As RouteCollection)
            routes.IgnoreRoute("{resource}.axd/{*pathInfo}")
            routes.IgnoreRoute("{resource}.ashx/{*pathInfo}")

            routes.MapRoute(name:="Default", url:="{controller}/{action}/{id}", defaults:=New With {
                Key .controller = "Home",
                Key .action = "Index",
                Key .id = UrlParameter.Optional
            })
        End Sub
    End Class
End Namespace

Global.asax.vb

Imports System.Web.Http
Imports System.Web.Mvc
Imports System.Web.Routing

Namespace MvcDashboard_CustomSchemaProvider
    ' Note: For instructions on enabling IIS6 or IIS7 classic mode, 
    ' visit http://go.microsoft.com/?LinkId=9394801

    Public Class MvcApplication
        Inherits System.Web.HttpApplication

        Protected Sub Application_Start()
            DashboardConfig.RegisterService(RouteTable.Routes)
            AreaRegistration.RegisterAllAreas()

            GlobalConfiguration.Configure(AddressOf WebApiConfig.Register)
            FilterConfig.RegisterGlobalFilters(GlobalFilters.Filters)
            RouteConfig.RegisterRoutes(RouteTable.Routes)

            ModelBinders.Binders.DefaultBinder = New DevExpress.Web.Mvc.DevExpressEditorsBinder()

            AddHandler DevExpress.Web.ASPxWebControl.CallbackError, AddressOf Application_Error
        End Sub

        Protected Sub Application_Error(ByVal sender As Object, ByVal e As EventArgs)
            Dim exception As Exception = System.Web.HttpContext.Current.Server.GetLastError()
            'TODO: Handle Exception
        End Sub
    End Class
End Namespace

FilterConfig.vb

Imports System.Web.Mvc

Namespace MvcDashboard_CustomSchemaProvider
    Public Class FilterConfig
        Public Shared Sub RegisterGlobalFilters(ByVal filters As GlobalFilterCollection)
            filters.Add(New HandleErrorAttribute())
        End Sub
    End Class
End Namespace

HomeController.vb

Imports System
Imports System.Collections.Generic
Imports System.Linq
Imports System.Web
Imports System.Web.Mvc

Namespace MvcDashboard_CustomSchemaProvider.Controllers
    Public Class HomeController
        Inherits Controller

        Public Function Index() As ActionResult
            Return View()
        End Function
    End Class
End Namespace

Index.cshtml (VB.NET)

<script type="text/javascript">
    function onBeforeRender(sender) {
        var control = sender.getDashboardControl();
        control.registerExtension(new DevExpress.Dashboard.DashboardPanelExtension(control));
    }
</script>
@Html.DevExpress().Dashboard(settings => {
    settings.Name = "Dashboard";
    settings.Width = Unit.Percentage(100);
    settings.Height = Unit.Percentage(100);
    settings.ClientSideEvents.BeforeRender = "onBeforeRender";
}).GetHtml()