Provide a Custom Database Schema

• Aug 18, 2023
• 4 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 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 the operation speed when a report rebuilds the database schema before the 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);
       }
   }
   

   VB.NET

   Imports DevExpress.DataAccess.Sql
   Imports DevExpress.Xpo.DB
   Imports System.Linq
   ' ...
   
   Public Class MyDBSchemaProvider
       Implements IDBSchemaProviderEx
       Private provider As DBSchemaProviderEx
       Public Sub New()
           Me.provider = New DBSchemaProviderEx()
       End Sub
   
       Public Function GetTables(connection As SqlDataConnection, ParamArray tableList As String()) As DBTable() Implements IDBSchemaProviderEx.GetTables
           Return provider.GetTables(connection, tableList).Where(Function(table) table.Name.StartsWith("C")).ToArray()
       End Function
   
       Public Function GetViews(connection As SqlDataConnection, ParamArray viewList As String()) As DBTable() Implements IDBSchemaProviderEx.GetViews
           Return provider.GetViews(connection, viewList).Where(Function(view) view.Name.StartsWith("Order")).ToArray()
       End Function
   
       Public Function GetProcedures(connection As SqlDataConnection, ParamArray procedureList As String()) As DBStoredProcedure() Implements IDBSchemaProviderEx.GetProcedures
           Return provider.GetProcedures(connection, procedureList).Where(Function(storedProcedure) storedProcedure.Arguments.Count = 0).ToArray()
       End Function
   
       Public Sub LoadColumns(connection As SqlDataConnection, ParamArray tables As DBTable()) Implements IDBSchemaProviderEx.LoadColumns
           provider.LoadColumns(connection, tables)
       End Sub
   End Class
   

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();
       }
   }
   

   VB.NET

   Imports DevExpress.DataAccess.Sql
   Imports DevExpress.DataAccess.Web
   ' ...
   
   Public Class MyDataSourceWizardDBSchemaProviderFactory
       Implements IDataSourceWizardDBSchemaProviderExFactory
       Public Function Create() As IDBSchemaProviderEx Implements IDataSourceWizardDBSchemaProviderExFactory.Create
           Return New MyDBSchemaProvider()
       End Function
   End Class
   

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

   • Implement theIDBSchemaProviderExFactory 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. To register a custom database schema provider factory for the Data Source Wizard, call the static DefaultReportDesignerContainer.RegisterDataSourceWizardDBSchemaProviderExFactory<T> method at application startup.

   C#

   using DevExpress.DataAccess.Web;
   using System;
   using DevExpress.XtraReports.Web.ReportDesigner
   // ...
   
   public class Global_asax : System.Web.HttpApplication {
       void Application_Start(object sender, EventArgs e) {
           // ...                          
           // Register a connection string provider.
           // ...
           // Register the custom provider factory.
           DefaultReportDesignerContainer.RegisterDataSourceWizardDBSchemaProviderExFactory<MyDataSourceWizardDBSchemaProviderFactory>();
       }
       // ...
   }
   

   VB.NET

   Imports DevExpress.DataAccess.Web
   Imports System
   Imports DevExpress.XtraReports.Web.ReportDesigner
   ' ...
   
   Public Class Global_asax
       Inherits System.Web.HttpApplication
       Private Sub Application_Start(sender As Object, e As EventArgs)
           ' ...                          
           ' Register a connection string provider.
           ' ...
           ' Register the custom provider factory.
           DefaultReportDesignerContainer.RegisterDataSourceWizardDBSchemaProviderExFactory(Of MyDataSourceWizardDBSchemaProviderFactory)()
       End Sub
       ' ...
   End Class
   

5. To register a custom database schema provider factory for the report’s preview, call the static DefaultWebDocumentViewerContainer.RegisterDBSchemaProviderExFactory<T> method at application startup. Note that the MyDataSourceWizardDBSchemaProviderFactory class should implement the IDBSchemaProviderExFactory interface.

   C#

   using DevExpress.DataAccess.Web;
   using System;
   using DevExpress.XtraReports.Web.ReportDesigner
   // ...
   
   public class Global_asax : System.Web.HttpApplication {
       void Application_Start(object sender, EventArgs e) {
           // ...                          
           // Register a connection string provider.
           // ...
           // Register the custom provider factory.
           DefaultWebDocumentViewerContainer.RegisterDBSchemaProviderExFactory<MyDataSourceWizardDBSchemaProviderFactory>();
       }
       // ...
   }
   

   VB.NET

   Imports DevExpress.DataAccess.Web
   Imports System
   Imports DevExpress.XtraReports.Web.ReportDesigner
   ' ...
   
   Public Class Global_asax
       Inherits System.Web.HttpApplication
       Private Sub Application_Start(sender As Object, e As EventArgs)
           ' ...                          
           ' Register a connection string provider.
           ' ...
           ' Register the custom provider factory.
           DefaultWebDocumentViewerContainer.RegisterDBSchemaProviderExFactory(Of MyDataSourceWizardDBSchemaProviderFactory)()
       End Sub
       ' ...
   End Class
   

Tip

You can use the MyDataSourceWizardDBSchemaProviderFactory class (defined in step 2) without modifications as a custom database schema provider for the report’s preview. Call the DefaultReportDesignerContainer.RegisterDataSourceWizardDBSchemaProviderExFactory<T>(Boolean) method at application startup with the overrideReportPreviewProvider parameter set to true.