Provide a Custom Database Schema

• Jul 15, 2020
• 2 minutes to read

After you register connection strings, you can specify tables, views and stored procedures to include in a database schema and display in the Data Source Wizard. You can also specify a custom database schema for the report’s preview.

Tip

A custom database schema provider allows you to restrict access to tables, views, stored procedures and data columns in the Query Builder. Fewer objects improve operation speed when a report rebuilds the database schema before data is displayed.

1. Implement the IDBSchemaProviderEx interface to create a custom database schema provider (named MyDBSchemaProvider).

   C#

   using DevExpress.DataAccess.Sql;
   using DevExpress.Xpo.DB;
   using System.Linq;
   // ...
   
   public class MyDBSchemaProvider : IDBSchemaProviderEx {
       DBSchemaProviderEx provider;
       public MyDBSchemaProvider() {
           this.provider = new DBSchemaProviderEx();
       }
   
       public DBTable[] GetTables(SqlDataConnection connection, params string[] tableList) {
           return provider.GetTables(connection, tableList)
               .Where(table => table.Name.StartsWith("C"))
               .ToArray();
       }
   
       public DBTable[] GetViews(SqlDataConnection connection, params string[] viewList) {
           return provider.GetViews(connection, viewList)
               .Where(view => view.Name.StartsWith("Order"))
               .ToArray();
       }
   
       public DBStoredProcedure[] GetProcedures(SqlDataConnection connection, params string[] procedureList) {
           return provider.GetProcedures(connection, procedureList)
               .Where(storedProcedure => storedProcedure.Arguments.Count == 0)
               .ToArray();
       }
   
       public void LoadColumns(SqlDataConnection connection, params DBTable[] tables) {
           provider.LoadColumns(connection, tables);
       }
   }
   

2. Use a custom database schema provider for the Data Source Wizard. Create a custom database schema provider factory (MyDataSourceWizardDBSchemaProviderFactory). Implement the IDataSourceWizardDBSchemaProviderExFactory interface and create a new custom data store schema provider (MyDBSchemaProvider) in the IDataSourceWizardDBSchemaProviderExFactory.Create method.

   C#

   using DevExpress.DataAccess.Sql;
   using DevExpress.DataAccess.Web;
   // ...
   
   public class MyDataSourceWizardDBSchemaProviderFactory : IDataSourceWizardDBSchemaProviderExFactory {
       public IDBSchemaProviderEx Create() {
           return new MyDBSchemaProvider();
       }
   }
   

3. Use a custom database schema provider for the report’s preview. You can do one of the following:

   • Implement the IDBSchemaProviderExFactory interface and create a new custom schema provider in the IDBSchemaProviderExFactory.Create method.
   • Inherit the MyDataSourceWizardDBSchemaProviderFactory class (defined in the previous step) from the IDBSchemaProviderExFactory interface. Both interfaces have the same Create method which returns a custom schema provider.

4. Register a custom database schema provider factory for the Data Source Wizard at application startup.

   Startup.cs

   using Microsoft.Extensions.DependencyInjection;
   using DevExpress.AspNetCore;
   using DevExpress.AspNetCore.Reporting;
   // ...
   
   public class Startup {
       // ...
       public void ConfigureServices(IServiceCollection services) {
           // ...
           services.AddTransient<IDataSourceWizardDBSchemaProviderExFactory, MyDataSourceWizardDBSchemaProviderFactory>();
       }
   }
   

5. Register a class that implements the IDBSchemaProviderExFactory interface (a custom database schema provider factory for the report’s preview) at application startup.

   Startup.cs

   using Microsoft.Extensions.DependencyInjection;
   using DevExpress.AspNetCore;
   using DevExpress.AspNetCore.Reporting;
   // ...
   
   public class Startup {
       // ...
       public void ConfigureServices(IServiceCollection services) {
           // ...
           services.AddTransient<IDBSchemaProviderExFactory, MyDataSourceWizardDBSchemaProviderFactory>();
       }
   }