OracleDataAdapter Safe Type Mapping

Using Safe Type Mapping

To use the Safe Type Mapping feature, the OracleDataAdapter.SafeMapping property must be set with a hash table of key-value pairs. The key-value pairs must map database table column names (of type string) to a .NET type (of type Type). ODP.NET supports Safe Type Mapping to byte[] and String types. Any other type mapping causes an exception.

In situations where the column names are not known at design time, an asterisk ("*") can be used to map all occurrences of database types to a safe .NET type. If both the valid column name and the asterisk are present, the column name is used.

Safe Type Mapping as a string is more readable without further conversion. Converting certain Oracle datatypes to a string requires extra conversion, which can be slower than converting it to a byte[]. Conversion of .NET strings back to ODP.NET types relies on the formatting information of the session.

SafeTyping Example

// C#
 
using System;
using System.Data;
using Oracle.DataAccess.Client; 
 
class SafeMappingSample
{
  static void Main()
  {
    string constr = "User Id=scott;Password=tiger;Data Source=oracle";
 
    // In this SELECT statement, EMPNO, HIREDATE and SALARY must be
    // preserved using safe type mapping.
    string cmdstr = "SELECT EMPNO, ENAME, HIREDATE, SAL FROM EMP";
 
    // Create the adapter with the selectCommand txt and the connection string
    OracleDataAdapter adapter = new OracleDataAdapter(cmdstr, constr);
 
    // Get the connection from the adapter
    OracleConnection connection = adapter.SelectCommand.Connection;
 
    // Create the safe type mapping for the adapter
    // which can safely map column data to byte arrays, where
    // applicable. By executing the following statement, EMPNO, HIREDATE AND
    // SALARY columns will be mapped to byte[]
    adapter.SafeMapping.Add("*", typeof(byte[]));
 
    // Map HIREDATE to a string
    // If the column name in the EMP table is case-sensitive,
    // the safe type mapping column name must be case-sensitive.
    adapter.SafeMapping.Add("HIREDATE", typeof(string));
 
    // Map EMPNO to a string
    // If the column name in the EMP table is case-sensitive,
    // the safe type mapping column name must also be case-sensitive.
    adapter.SafeMapping.Add("EMPNO", typeof(string));
    adapter.SafeMapping.Add("SAL", typeof(string));
 
    // Create and fill the DataSet using the EMP
    DataSet dataset = new DataSet();
    adapter.Fill(dataset, "EMP");
 
    // Get the EMP table from the dataset
    DataTable table = dataset.Tables["EMP"];
 
    // Get the first row from the EMP table
    DataRow row = table.Rows[0];
 
    // Print out the row info
    Console.WriteLine("EMPNO Column: type = " + row["EMPNO"].GetType() +
      "; value = " + row["EMPNO"]);
    Console.WriteLine("ENAME Column: type = " + row["ENAME"].GetType() +
      "; value = " + row["ENAME"]);
    Console.WriteLine("HIREDATE Column: type = " + row["HIREDATE"].GetType()+
      "; value = " + row["HIREDATE"]);
    Console.WriteLine("SAL Column: type = " + row["SAL"].GetType() +
      "; value = " + row["SAL"]);
  }
}