Always Encrypted (SQL Server only)

• Jul 03, 2024
• 3 minutes to read

XPO supports the Always Encrypted feature that allows you to protect sensitive data, such as credit card numbers. This article describes how to enable this feature and how it affects XPO functionality.

How to enable Always Encrypted

1. Use SQL Server 2016 or newer.
2. Configure Always Encrypted in your database.
3. Enable Always Encrypted for application queries.
4. Ensure that the ConnectionProviderSql.GlobalQueryParameterMode and ConnectionProviderSql.QueryParameterMode fields are set to Default or SetTypeAndSize.

Always Encrypted Limitations

Since client applications do not reveal encryption keys to the database engine, many server-side operations do not work with encrypted columns. Refer to the Feature Details section for additional information.

Certain XPO functions do not work with encrypted columns due to Always Encrypted limitations, for example:

• Server Mode collections (XPServerCollectionSource, XPInstantFeedbackSource, XPServerModeView, XPInstantFeedbackView) cannot sort, filter, and group records against encrypted database columns. This restriction also applies to summary calculations. Disable these functions for corresponding columns in a data-bound control.
• LINQ to XPO sort, group, and filter data on the server side. Do not include encrypted columns in filter expressions and do not sort or group data against encrypted columns. If you use encrypted columns in projection operations, ensure that a projection expression does not require decryption on the server side.
• View collections (XPView, XPServerModeView, XPInstantFeedbackView) calculate expressions on the server side. If you use encrypted columns in view properties, ensure that a property expression does not require decryption on the server side.
• The Criteria or FixedFilterCriteria properties do not work if a filter expression contains encrypted columns.

Tip

You can sort and group against encrypted columns on the server side if you use the deterministic encryption.

How to execute direct SQL queries

Use the QueryParameterCollection class and overloaded ExecuteXXX methods to specify parameter types: ExecuteNonQuery(String, QueryParameterCollection), ExecuteQuery(String, QueryParameterCollection), ExecuteScalar(String, QueryParameterCollection).

• C#
• VB.NET

Session session = new Session();
string sql = "delete Categories where CategoryID=@p0";
DevExpress.Xpo.DB.ParameterValue categoryIdParameter = new ParameterValue();
categoryIdParameter.Value = 9;
categoryIdParameter.DBTypeName = "int";
int deeletedRowsCount = session.ExecuteNonQuery(sql, new QueryParameterCollection(
        categoryIdParameter
    ));

Dim session As New Session()
Dim sql As String = "delete Categories where CategoryID=@p0"
Dim categoryIdParameter As DevExpress.Xpo.DB.ParameterValue = New ParameterValue()
categoryIdParameter.Value = 9
categoryIdParameter.DBTypeName = "int"
Dim deeletedRowsCount As Integer = session.ExecuteNonQuery(sql, New QueryParameterCollection(categoryIdParameter))

By convention, parameter names consist of the “p” letter and the serial number: p0, p1, p2… To set custom parameter names, use these overloaded methods: ExecuteNonQuery(String, String[], QueryParameterCollection), ExecuteQuery(String, String[], QueryParameterCollection), ExecuteScalar(String, String[], QueryParameterCollection).

• C#
• VB.NET

string sql = "delete Categories where CategoryID=@CategoryID";
DevExpress.Xpo.DB.ParameterValue categoryIdParameter = new ParameterValue();
categoryIdParameter.Value = 9;
categoryIdParameter.DBTypeName = "int";
int deeletedRowsCount = session.ExecuteNonQuery(sql, new string[] { "CategoryID" }, 
    new QueryParameterCollection(
        categoryIdParameter
    ));

Dim sql As String = "delete Categories where CategoryID=@CategoryID"
Dim categoryIdParameter As DevExpress.Xpo.DB.ParameterValue = New ParameterValue()
categoryIdParameter.Value = 9
categoryIdParameter.DBTypeName = "int"
Dim deeletedRowsCount As Integer = session.ExecuteNonQuery(sql, New String() { "CategoryID" }, New QueryParameterCollection(categoryIdParameter))