Issues with Writing Unicode Using Dapper and ODAC Managed Driver

TLDR

• Scenario: When using Dapper with Oracle Managed Driver (ODAC) to write Unicode characters (such as Simplified Chinese), the data appears as garbled text in the database.
• Root Cause: DbType.String in ODP.NET defaults to OracleDbType.Varchar2 instead of OracleDbType.NVarchar2, which supports Unicode.
• Solution: Since OracleDbType cannot be set directly via Dapper's DynamicParameters, you must implement a custom IDynamicParameters class to manually specify the parameter type.

Analysis of Unicode Write Garbled Text Issue

When does this issue occur: When developers use Dapper for database write operations, and the target column is an Oracle Unicode format column such as NVARCHAR2 or NCHAR.

In Oracle's Managed Driver, DbType.String is incorrectly mapped to OracleDbType.Varchar2. This causes encoding conversion errors when processing Unicode characters, resulting in garbled text. Even manually setting DbType.String in Dapper does not resolve the issue because the underlying driver's mapping logic does not direct it to the correct NVarchar2 type.

By decompiling Oracle.ManagedDataAccess.Core (version 3.21.100), we can observe:

• DbType.String maps to 16.
• OracleDbType.NVarchar2 maps to 119.
• OracleDbType.Varchar2 maps to 126.

The driver's internal logic forces DbType.String to map to OracleDbType.Varchar2, causing Unicode data to lose its correct encoding information during the write process.

 

Custom DynamicParameters Solution

Since Dapper's DynamicParameters does not support specifying OracleDbType directly, developers must implement the SqlMapper.IDynamicParameters interface to create a custom parameter container, allowing OracleParameter objects to be correctly added to the IDbCommand.

The following is an implementation example:

public class MyDynamicParameters : SqlMapper.IDynamicParameters {
    private readonly Dapper.DynamicParameters dynamicParameters = new();
    private readonly List<IDbDataParameter> dbDataParameters = new();

    public void Add(string name, object value, DbType? dbType, ParameterDirection? direction, int? size) {
        dynamicParameters.Add(name, value, dbType, direction, size);
    }

    public void Add(IDbDataParameter paramerter) {
        dbDataParameters.Add(paramerter);
    }

    void SqlMapper.IDynamicParameters.AddParameters(IDbCommand command, SqlMapper.Identity identity) {
        ((SqlMapper.IDynamicParameters)dynamicParameters).AddParameters(command, identity);

        foreach (IDbDataParameter p in dbDataParameters) {
            command.Parameters.Add(p);
        }
    }
}

Usage is as follows:

using (IDbConnection conn = new OracleConnection(connStr)) {
    conn.Open();

    MyDynamicParameters parameters = new();
    parameters.Add(new OracleParameter {
        ParameterName = "Name",
        Value = value,
        OracleDbType = OracleDbType.NVarchar2
    });
    conn.Query(sql, parameters);
}

By using this custom class, you can bypass Dapper's default type mapping, ensuring that parameters are passed to the Oracle database in OracleDbType.NVarchar2 format, thereby correctly handling Unicode characters.

Change Log

• 2023-06-15 Initial version created.