SQL Database - Customize the Schema

• Mar 27, 2024
• 2 minutes to read

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

A custom database schema provider allows you to restrict access to tables, views, stored procedures, and data fields 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 to fetch the modified schema to the Data Source Wizard and Query Builder.

   C#

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

3. Do one of the following to use a custom database schema provider for the report’s preview:

   • 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. At application startup, register a custom database schema provider factory for the Data Source Wizard and Query Builder. After that, register a class that implements the IDBSchemaProviderExFactory interface (a custom database schema provider factory for the report’s preview).

   Program.cs

   using Microsoft.Extensions.DependencyInjection;
   using DevExpress.AspNetCore;
   using DevExpress.AspNetCore.Reporting;
   
   var builder = WebApplication.CreateBuilder(args);
   
   builder.Services.AddTransient<IDataSourceWizardDBSchemaProviderExFactory, MyDataSourceWizardDBSchemaProviderFactory>();
   
   builder.Services.AddTransient<IDBSchemaProviderExFactory, MyDataSourceWizardDBSchemaProviderFactory>();
   
   var app = builder.Build();