Issues with Writing Unicode Using Dapper and ODAC Managed Driver

TLDR

• Scenario: When using Dapper with Oracle.ManagedDataAccess.Core to write Unicode characters (such as Simplified Chinese), the database displays garbled text.
• Root Cause: The ODP.NET driver defaults DbType.String to OracleDbType.Varchar2 instead of OracleDbType.NVarchar2, which supports Unicode.
• Solution: You cannot directly set OracleDbType via Dapper's DynamicParameters. You must implement a custom IDynamicParameters class to manually add OracleParameter to IDbCommand.

Problem Analysis and Cause

When developers use Dapper for database operations without explicitly specifying parameter types, Dapper maps them based on DbType. In the Oracle Managed Driver, DbType.String is incorrectly mapped to OracleDbType.Varchar2, causing Unicode characters to become garbled due to encoding mismatches during transmission. Even explicitly specifying DbType.String in the code does not resolve this, as the driver's internal mapping logic does not route it to OracleDbType.NVarchar2.

Custom DynamicParameters Solution

Since Dapper's native DynamicParameters does not support passing OracleParameter objects directly to specify OracleDbType, you need to extend functionality by implementing the SqlMapper.IDynamicParameters interface, allowing for the manual addition of IDbDataParameter.

Implementation Steps

Create a custom MyDynamicParameters class to forward parameters to IDbCommand:

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

When executing SQL commands, use MyDynamicParameters and explicitly specify OracleDbType.NVarchar2:

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 approach, you can ensure that parameters are written to the Oracle database in the correct Unicode format, avoiding garbled text issues.

Change Log

• 2023-06-15 Initial version created.