Implement a Custom Database Schema in ASP.NET Web Forms

Feb 27, 2023
6 minutes to read

When you use an SQL data source, you can specify which database entities should be included in a database schema. For this, use a custom database schema provider. This topic describes how to implement a custom database schema provider for SQL Data Sources in ASP.NET Web Forms.

A custom database schema provider allows you to restrict access to tables, views, stored procedures, and columns in the Query Builder depending on certain conditions (such as user role or connection settings).

Implement the IDBSchemaProviderEx interface to use a custom database schema provider for SQL data sources. To assign the provider to the Web Dashboard, pass an instance of the created class to the DashboardConfigurator.SetDBSchemaProvider / ASPxDashboard.SetDBSchemaProvider method. The selected method depends on the server-side API used in your app.

The code snippet below shows how to use the ASPxDashboard.SetDBSchemaProvider method:

// The ASPxDashboard1 variable is an instance of the ASPxDashboard class.
ASPxDashboard1.SetDBSchemaProvider(new CustomDBSchemaProvider());

The following sections contain code samples that implement the IDBSchemaProviderEx interface for different user tasks.

Limit the List of Available Tables and Views

The following example implements the IDBSchemaProviderEx interface to limit the list of views, tables, and stored procedures displayed in the Query Builder. This custom database schema provider uses the default provider (the DBSchemaProviderEx class) to load all database entities and then filters them according to the specified conditions.

View Example

The code below filters the SQL data source to display the following database entities:

Tables whose names start with the letter C
Views whose names start with Sales
Stored procedures that have zero arguments.
C#
VB

using DevExpress.DataAccess.Sql;
using DevExpress.Xpo.DB;
using System.Linq;

public class CustomDBSchemaProvider : IDBSchemaProviderEx {
    DBSchemaProviderEx provider;
    public MyDBSchemaProvider() {
        this.provider = new DBSchemaProviderEx();
    }

    public DBTable[] GetTables(SqlDataConnection connection, params string[] tableList) {
        // Returns only the tables whose names start with the letter C.
        return provider.GetTables(connection, tableList)
            .Where(table => table.Name.StartsWith("C"))
            .ToArray();
    }

    public DBTable[] GetViews(SqlDataConnection connection, params string[] viewList) {
        // Returns only the views whose names start with Sales.
        return provider.GetViews(connection, viewList)
            .Where(view => view.Name.StartsWith("Sales"))
            .ToArray();
    }

    public DBStoredProcedure[] GetProcedures(SqlDataConnection connection, params string[] procedureList) {
        // Returns only the stored procedures with zero arguments.
        return provider.GetProcedures(connection, procedureList)
            .Where(storedProcedure => storedProcedure.Arguments.Count == 0)
            .ToArray();
    }

    public void LoadColumns(SqlDataConnection connection, params DBTable[] tables) {
        // Loads all columns in tables.
        provider.LoadColumns(connection, tables);

    }
}

Imports DevExpress.DataAccess.Sql
Imports DevExpress.Xpo.DB
Imports System.Linq

Public Class CustomDBSchemaProvider
    Implements IDBSchemaProviderEx

    Private provider As DBSchemaProviderEx

    Public Sub New()
        provider = New DBSchemaProviderEx()
    End Sub

    Public Function GetTables(ByVal connection As SqlDataConnection, ParamArray tableList As String()) As DBTable() Implements IDBSchemaProviderEx.GetTables
        ' Returns only the tables whose names start with the letter C.
        Return provider.GetTables(connection, tableList).Where(Function(table) table.Name.StartsWith("C")).ToArray()
    End Function

    Public Function GetViews(ByVal connection As SqlDataConnection, ParamArray viewList As String()) As DBTable() Implements IDBSchemaProviderEx.GetViews
        ' Returns only the views whose names start with Sales.
        Return provider.GetViews(connection, viewList).Where(Function(view) view.Name.StartsWith("Sales")).ToArray()
    End Function

    Public Function GetProcedures(ByVal connection As SqlDataConnection, ParamArray procedureList As String()) As DBStoredProcedure() Implements IDBSchemaProviderEx.GetProcedures
        ' Returns only the stored procedures with zero arguments.
        Return provider.GetProcedures(connection, procedureList).Where(Function(storedProcedure) storedProcedure.Arguments.Count = 0).ToArray()
    End Function

    Public Sub LoadColumns(ByVal connection As SqlDataConnection, ParamArray tables As DBTable()) Implements IDBSchemaProviderEx.LoadColumns
        ' Loads all columns in tables.
        provider.LoadColumns(connection, tables)
    End Sub
End Class

Limit the List of Tables and Columns to Be Loaded

The following example implements the IDBSchemaProviderEx interface to limit the list of views, tables, columns, and stored procedures to be loaded. The default database schema provider loads all available database entities which can cause the performance degradation with certain databases. The following technique improves the Data Source Wizard performance when loading the database schema since the Web Dashboard loads only the specified database entities.

View Example

The following code loads only the Categories and Products tables for the NWindConnectionString connection. These tables are linked by the CategoryID field and contain two columns::

using DevExpress.DataAccess.Sql;
using DevExpress.Xpo.DB;
using System.Collections.Specialized;

public class CustomDBSchemaProvider : IDBSchemaProviderEx {
    DBTable[] tables;
    public void LoadColumns(SqlDataConnection connection, params DBTable[] tables) {
        // Loads the specified columns in the Categories and Products tables.
        foreach (DBTable table in tables) {
            if (table.Name == "Categories" && table.Columns.Count == 0) {
                DBColumn categoryIdColumn = new DBColumn { Name = "CategoryID", ColumnType = DBColumnType.Int32 };
                table.AddColumn(categoryIdColumn);
                DBColumn categoryNameColumn = new DBColumn { Name = "CategoryName", ColumnType = DBColumnType.String };
                table.AddColumn(categoryNameColumn);
            }
            if (table.Name == "Products" && table.Columns.Count == 0) {
                DBColumn categoryIdColumn = new DBColumn { Name = "CategoryID", ColumnType = DBColumnType.Int32 };
                table.AddColumn(categoryIdColumn);
                DBColumn productNameColumn = new DBColumn { Name = "ProductName", ColumnType = DBColumnType.String };
                table.AddColumn(productNameColumn);

                // Links the tables by the CategoryID field.
                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) {
        // Loads only the Categories and Products tables for the NWindConnectionString connection.
        if (connection.Name == "NWindConnectionString") {
            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];

        LoadColumns(connection, tables);
        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;
    }
}

Imports DevExpress.DataAccess.Sql
Imports DevExpress.Xpo.DB
Imports System.Collections.Specialized

Public Class CustomDBSchemaProvider
    Implements IDBSchemaProviderEx

    Private tables As DBTable()

    Public Sub LoadColumns(ByVal connection As SqlDataConnection, ParamArray tables As DBTable()) Implements IDBSchemaProviderEx.LoadColumns
        ' Loads the specified columns in the Categories and Products tables.
        For Each table As DBTable In tables
            If Equals(table.Name, "Categories") AndAlso table.Columns.Count = 0 Then
                Dim categoryIdColumn As DBColumn = New DBColumn With {.Name = "CategoryID", .ColumnType = DBColumnType.Int32}
                table.AddColumn(categoryIdColumn)
                Dim categoryNameColumn As DBColumn = New DBColumn With {.Name = "CategoryName", .ColumnType = DBColumnType.String}
                table.AddColumn(categoryNameColumn)
            End If

            If Equals(table.Name, "Products") AndAlso table.Columns.Count = 0 Then
                Dim categoryIdColumn As DBColumn = New DBColumn With {.Name = "CategoryID", .ColumnType = DBColumnType.Int32}
                table.AddColumn(categoryIdColumn)
                Dim productNameColumn As DBColumn = New DBColumn With {.Name = "ProductName", .ColumnType = DBColumnType.String}
                table.AddColumn(productNameColumn)
                ' Links the tables by the CategoryID field.
                Dim foreignKey As DBForeignKey = New DBForeignKey({categoryIdColumn}, "Categories", CreatePrimaryKeys("CategoryID"))
                table.ForeignKeys.Add(foreignKey)
            End If
        Next
    End Sub

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

    Public Function GetTables(ByVal connection As SqlDataConnection, ParamArray tableList As String()) As DBTable() Implements IDBSchemaProviderEx.GetTables
        ' Loads only the Categories and Products tables for the NWindConnectionString connection.
        If Equals(connection.Name, "NWindConnectionString") Then
            If tables IsNot Nothing Then
                Return tables
            End If

            tables = New DBTable(1) {}
            Dim categoriesTable As DBTable = New DBTable("Categories")
            tables(0) = categoriesTable
            Dim productsTable As DBTable = New DBTable("Products")
            tables(1) = productsTable
        Else
            tables = New DBTable(-1) {}
        End If

        LoadColumns(connection, tables)
        Return tables
    End Function

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

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