Implement a Custom Database Schema in ASP.NET Web Forms
- 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.
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 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.
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;
}
}