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

• Sep 20, 2022
• 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.

C#

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

VB.NET

Protected Overrides Sub CreateDefaultObjectSpaceProvider(ByVal args As CreateCustomObjectSpaceProviderEventArgs)
    ' ...
    (CType(args.ObjectSpaceProviders(0), SecuredObjectSpaceProvider)).AllowICommandChannelDoWithSecurityContext = True
End Sub

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.

   C#

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

   VB.NET

   Protected Overrides Sub CreateDefaultObjectSpaceProvider(ByVal args As CreateCustomObjectSpaceProviderEventArgs)
       ' ...
       (CType(args.ObjectSpaceProviders(0), SecuredObjectSpaceProvider)).AllowICommandChannelDoWithSecurityContext = True
   End Sub
   

2. 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, true);
   

   VB.NET

   Dim mainDataStore As IDataStore = XpoDefault.GetConnectionProvider(connectionString, AutoCreateOption.SchemaAlreadyExists)
   Dim dataLayerProvider As Func(Of IDataLayer) = Function() New ThreadSafeDataLayer(XpoTypesInfoHelper.GetXpoTypeInfoSource().XPDictionary, mainDataStore)
   Dim serviceHost As 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:

C#

// ...
.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.