Skip to main content
.NET 8.0+

DevExpress v24.2 Update — Your Feedback Matters

Our What's New in v24.2 webpage includes product-specific surveys. Your response to our survey questions will help us measure product satisfaction for features released in this major update and help us refine our plans for our next major release.

Take the survey Not interested

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

  • 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)

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

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

#In the Middle-Tier Server (.NET)

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.

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:

IDataStore dataStore = XpoDefault.GetConnectionProvider(connectionString, AutoCreateOption.SchemaAlreadyExists);
Func<IDataLayer> dataLayerProvider = () => new ThreadSafeDataLayer(XpoTypesInfoHelper.GetXpoTypeInfoSource().XPDictionary, dataStore);
ServiceHost 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.