Implement a Custom Database Schema
- 5 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 MVC and ASP.NET Core.
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 method:
// The configurator variable is an instance of the DashboardConfiguartor class.
configurator.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 examples implement 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.
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.
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);
}
}
Limit the List of Tables and Columns to Be Loaded
The following examples implement 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.
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;
}
}
Limit the List of Available Tables Depending on the User Role
The code below shows how to create a custom database schema provider to restrict user access to the tables based on their role. To do this, create a class that inherits the DBSchemaProviderEx class. The DBSchemaProviderEx class contains basic implementation of the IDBSchemaProviderEx interface. Methods implemented in this class return all available views, tables, columns, and stored procedures. Override the methods you need.
The following code filters tables to show only the Cars table if the username is User:
using DevExpress.DataAccess.Sql;
using DevExpress.Xpo.DB;
using System.Linq;
using System.Web;
public class CustomDBSchemaProvider : DBSchemaProviderEx {
//...
public override DBTable[] GetTables(SqlDataConnection connection, params string[] tableList) {
var result = base.GetTables(connection, tableList);
var userName = contextAccessor.HttpContext.Session.GetString("CurrentUser");
if (userName == "Admin") {
return result;
}
else if (userName == "User") {
return result.Where(t => t.Name == "Cars").ToArray();
}
else {
return new DBTable[0];
}
}
}
For more information about multi-tenancy, refer to the following article: Manage Multi-Tenancy.