How to: Call Direct SQL Queries in Integrated Mode or through the Middle Tier Application Server

• Jun 11, 2019
• 2 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.

To allow execution of direct queries and stored procedures 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 MobileApplication.cs (MobileApplication.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

If you are using the middle-tier application server, open the Program.cs (Program.vb) file located in the application server project and modify 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.

You can now 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.