Execute Direct SQL Queries in Integrated Mode and with Middle Tier Security

• Dec 28, 2023
• 3 minutes to read

By default, you cannot execute Direct SQL Queries and Stored Procedures in Integrated Mode of the Security System, or when the Middle Tier Application Server is used. The “Transferring requests via ICommandChannel is prohibited within the security engine“ exception occurs when you execute the corresponding methods of the Session.

Note

Do not use this approach to handle a database update when the application version changes. Instead, use the protected methods of the ModuleUpdater class.

Enable Direct SQL Queries

In Integrated Mode (.NET 6+)

To enable direct queries and stored procedure execution in these configurations, set the SecuredXPObjectSpaceProviderOptions.AllowICommandChannelDoWithSecurityContext property to true in application startup code that registers the XPO Object Space Provider:

File: MySolution.Blazor.Server/Startup.cs., MySolution.Win/Startup.cs, MySolution.WebAPI/Startup.cs

C# (Blazor)

public void ConfigureServices(IServiceCollection services) {-+
    services.AddXaf(Configuration, builder => {
        // ...
        builder.ObjectSpaceProviders
            .AddSecuredXpo((serviceProvider, options) => {
                string connectionString = null;
                if(Configuration.GetConnectionString("ConnectionString") != null) {
                    connectionString = Configuration.GetConnectionString("ConnectionString");
                }
                ArgumentNullException.ThrowIfNull(connectionString);
                options.ConnectionString = connectionString;
                options.ThreadSafe = true;
                options.UseSharedDataStoreProvider = true;
                options.AllowICommandChannelDoWithSecurityContext = true;
            })
        // ...
    });
    // ...
}

C# (WinForms)

public static WinApplication BuildApplication(string connectionString) {
    builder.ObjectSpaceProviders
        .AddSecuredXpo((application, options) => {
            options.ConnectionString = connectionString;
            options.AllowICommandChannelDoWithSecurityContext = true; ;
        })
    // ...
}

C# (Web API Service)

public void ConfigureServices(IServiceCollection services) {
    services.AddXafWebApi(builder => {
        // ...
        builder.ObjectSpaceProviders
            .AddSecuredXpo((serviceProvider, options) => {
                string connectionString = null;
                if(Configuration.GetConnectionString("ConnectionString") != null) {
                    connectionString = Configuration.GetConnectionString("ConnectionString");
                }
                ArgumentNullException.ThrowIfNull(connectionString);
                options.ConnectionString = connectionString;
                options.ThreadSafe = true;
                options.UseSharedDataStoreProvider = true;
                options.AllowICommandChannelDoWithSecurityContext = true;
            })
        // ...
    });
    // ...
}   

In the Middle-Tier Server (.NET 6+)

To enable direct queries and stored procedure execution, set the AllowICommandChannelDoWithSecurityContext property to true in the Startup.cs file in the Middle Tier Security project as follows:

File: MySolution.MiddleTier/Startup.cs.

C#

public void ConfigureServices(IServiceCollection services) {
    // ...
    services.AddXafMiddleTier(Configuration, builder => {
        builder.ConfigureDataServer(options => {
            options.UseConnectionString(Configuration.GetConnectionString("ConnectionString"));
            options.UseDataStorePool(true);
            options.AllowICommandChannelDoWithSecurityContext(true);
        });
        // ...
    });
}   

In the Middle-Tier Server (WCF, .NET Framework)

If you use the middle-tier application server, modify the Program.cs (Program.vb) file located in the application server project. Change the code that creates the ServiceHost object as follows:

C#

IDataStore dataStore = XpoDefault.GetConnectionProvider(connectionString, AutoCreateOption.SchemaAlreadyExists);
Func<IDataLayer> dataLayerProvider = () => new ThreadSafeDataLayer(XpoTypesInfoHelper.GetXpoTypeInfoSource().XPDictionary, dataStore);
ServiceHost serviceHost = new WcfXafServiceHost(dataLayerProvider, dataServerSecurityProvider, allowICommandChannelDoWithSecurityContext: true);

VB.NET

Dim dataStore As IDataStore = XpoDefault.GetConnectionProvider(connectionString, AutoCreateOption.SchemaAlreadyExists)
Dim dataLayerProvider As Func(Of IDataLayer) = Function() New ThreadSafeDataLayer(XpoTypesInfoHelper.GetXpoTypeInfoSource().XPDictionary, dataStore)
Dim serviceHost As ServiceHost = New WcfXafServiceHost(dataLayerProvider, dataServerSecurityProvider, allowICommandChannelDoWithSecurityContext:=True)

If you run the Application Server as a windows service, you can use the same code in the ApplicationServerService.cs (ApplicationServerService.vb) file.

Run Direct SQL Queries

After you enable the direct SQL queries, you can access the Object Space, cast it to the XPObjectSpace type, get the Session object using the XPObjectSpace.Session property, and call Session.ExecuteQuery, Session.ExecuteSproc or other suitable methods.