Use a Custom Database Schema

Jun 07, 2024
5 minutes to read

This topic describes the next step after you have registered data connection strings for Web Report Designer and the corresponding data sources are available to users.

You must set up a custom database schema provider to control which tables, views, and stored procedures are available in the Data Source Wizard based on certain conditions. The Data Source Wizard in the Web Report Designer uses it to determine which data objects are available to a user. The Query Builder automatically uses a custom database schema provider registered in the application.

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

Custom Database Schema Provider Implementation

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

C#
VB.NET

csharp

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

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

Database Schema Provider Factory

Data Source Wizard and Query Builder

Data Source Wizard and Query Builder components use a factory that implement the IDataSourceWizardDBSchemaProviderExFactory interface. The following code example is a factory that returns a new custom data store schema provider MyDBSchemaProvider class in the IDataSourceWizardDBSchemaProviderExFactory.Create method:

C#
VB.NET

csharp

using DevExpress.DataAccess.Sql;
using DevExpress.DataAccess.Web;
// ...

public class MyDataSourceWizardDBSchemaProviderFactory : IDataSourceWizardDBSchemaProviderExFactory {
    public IDBSchemaProviderEx Create() {
        return new MyDBSchemaProvider();
    }
}

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

To register a custom database schema provider factory in the application, call the static DefaultReportDesignerContainer.RegisterDataSourceWizardDBSchemaProviderExFactory<T> method at application startup:

C#
VB.NET

csharp

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

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.
        DevExpress.XtraReports.Web.ReportDesigner.DefaultReportDesignerContainer _
            .RegisterDataSourceWizardDBSchemaProviderExFactory(Of MyDataSourceWizardDBSchemaProviderFactory)()
    End Sub
    ' ...
End Class

Report Designer Preview

A custom database schema provider registered for Report Designer Preview allows the preview component to retrieve data only for tables, views, stored procedures, and data fields specified in the custom database schema provider.

Use Same Factory As Report Designer

Report Designer Preview component can use a factory implemented in the previous step without modifications. For this, call the DefaultReportDesignerContainer.RegisterDataSourceWizardDBSchemaProviderExFactory<T>(Boolean) method at application startup with the overrideReportPreviewProviderparameter set to true:

C#
VB.NET

csharp

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.
        evExpress.XtraReports.Web.ReportDesigner.DefaultReportDesignerContainer
            .RegisterDataSourceWizardDBSchemaProviderExFactory<MyDataSourceWizardDBSchemaProviderFactory>(true);
    }
    // ...
}

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)(True)
    End Sub
    ' ...
End Class

Use Different Factory

The DefaultReportDesignerContainer.RegisterDataSourceWizardDBSchemaProviderExFactory<T>(Boolean) method with the overrideReportPreviewProviderparameter set to true instructs Report Designer to use the same database schema factory for Preview and Query Bulder.

If you want to use a different database schema factory for Report Preview , implement a class with the IDBSchemaProviderExFactory interface and create a new custom schema provider in the IDBSchemaProviderExFactory.Create method.

To register a custom database schema provider factory for the Report Preview , call the static DefaultWebDocumentViewerContainer.RegisterDBSchemaProviderExFactory<T> method at application startup:

C#
VB.NET

csharp

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

    }
    // ...
}

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.
        DevExpress.XtraReports.Web.WebDocumentViewer.DefaultWebDocumentViewerContainer.RegisterDBSchemaProviderExFactory() _
           (Of MyDataSourceWizardDBSchemaProviderFactory)()
    End Sub
    ' ...
End Class