Skip to main content
.NET Framework 4.5.2+

Call Direct SQL Queries in Integrated Mode, through the Middle Tier Application Server, and Middle Tier Web API

  • 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

To enable direct queries and stored procedure execution in these configurations, set the SecuredObjectSpaceProvider.AllowICommandChannelDoWithSecurityContext property to true after instantiating the SecuredObjectSpaceProvider object. By default, this object is created in the XafApplication.CreateDefaultObjectSpaceProvider method overridden in the WinApplication.cs (WinApplication.vb), WebApplication.cs (WebApplication.vb) and BlazorApplication.cs files.

protected override void CreateDefaultObjectSpaceProvider(CreateCustomObjectSpaceProviderEventArgs args) {
    // ...
    ((SecuredObjectSpaceProvider)args.ObjectSpaceProviders[0]).AllowICommandChannelDoWithSecurityContext = true;    
}

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

If you are using the middle-tier application server, do the following:

  1. Set the SecuredObjectSpaceProvider.AllowICommandChannelDoWithSecurityContext property to true after instantiating the SecuredObjectSpaceProvider object in the XafApplication.CreateDefaultObjectSpaceProvider method overridden in the WinApplication.cs (WinApplication.vb) or WebApplication.cs (WebApplication.vb) files.

    protected override void CreateDefaultObjectSpaceProvider(CreateCustomObjectSpaceProviderEventArgs args) {
        // ...
        ((SecuredObjectSpaceProvider)args.ObjectSpaceProviders[0]).AllowICommandChannelDoWithSecurityContext = true;    
    }
    
  2. Modify the Program.cs (Program.vb) file located in the application server project. Change the code that creates the ServiceHost object as follows:

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

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

In the Middle-Tier Server (ASP.NET Core Web API, .NET 6+)

To enable direct queries and stored procedures execution, set the AllowICommandChannelDoWithSecurityContext property to true in the Startup.cs file in the Web API project as follows:

// ...
.AddXafMiddleTier(options => {
    options.UseConnectionString(Configuration.GetConnectionString("ConnectionString"));
    options.UseDataStorePool(true);
    // Enable direct queries and stored procedures execution.
    options.AllowICommandChannelDoWithSecurityContext = true;
});
// ...

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.