DbProviderFactory: Oracle-Type -> DbType Mapping?

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
tperetzke
Posts: 4
Joined: Tue 29 Jun 2010 13:26

DbProviderFactory: Oracle-Type -> DbType Mapping?

Post by tperetzke » Tue 29 Jun 2010 13:49

Hello,

I have a question about the devart related datatype mapping.

We ever used our own data-framework with the pure usage of DbProviderFactory and its abstract DbXxxx-Classes to connect to various databases (also oracle through devart direct-mode - everything worked fine...)

But now, we have a project were we need to read an existing CLOB-Field from the database into our model.
Neither we didn't find the mapping of the specific generic DbTypes to their real oracle-types in the documentation nor here in the forums. And this problem stalls our development now.

Can anybody post the mappings to the DbType's enumeration?

Thanks in advance

Thomas P.

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Thu 01 Jul 2010 16:41

Here is a mapping between the System.Data.DbType and Devart.Data.Oracle.OracleDbType enumerations:

Code: Select all

    public static DbType OracleDbTypeToDbType(OracleDbType oraDbType) {

      switch (oraDbType) {
        case OracleDbType.VarChar:
        case OracleDbType.Char:
        case OracleDbType.RowId:
        case OracleDbType.NChar:
        case OracleDbType.NVarChar:
        case OracleDbType.NClob:
        case OracleDbType.Xml:
          return DbType.String;
        case OracleDbType.Integer:
          return DbType.Int32;
        case OracleDbType.Float:
          return DbType.Single;
        case OracleDbType.Double:
          return DbType.Double;
        case OracleDbType.Number:
          return DbType.Decimal;
        case OracleDbType.Date:
        case OracleDbType.TimeStamp:
        case OracleDbType.TimeStampTZ:
        case OracleDbType.TimeStampLTZ:
          return DbType.DateTime;
        case OracleDbType.IntervalDS:
          return DbType.Time;
        case OracleDbType.IntervalYM:
          return DbType.Int32;
        case OracleDbType.Long:
        case OracleDbType.Clob:
          return DbType.String;
        case OracleDbType.Raw:
        case OracleDbType.LongRaw:
        case OracleDbType.Blob:
        case OracleDbType.BFile:
          return DbType.Binary;
        case OracleDbType.Cursor:
        case OracleDbType.Array:
        case OracleDbType.Object:
        case OracleDbType.Ref:
        case OracleDbType.Table:
          return DbType.Object;
        case OracleDbType.Boolean:
          return DbType.Boolean;
      default:
        throw new NotSupportedException();
      }
    }

    internal static OracleDbType DbTypeToOracleDbType(DbType dbType) {

      switch (dbType) {
        case DbType.AnsiString:
        case DbType.String:
          return OracleDbType.VarChar;
        case DbType.AnsiStringFixedLength:
        case DbType.StringFixedLength:
          return OracleDbType.Char;
        case DbType.Byte:
        case DbType.Int16:
        case DbType.SByte:
        case DbType.UInt16:
        case DbType.Int32:
          return OracleDbType.Integer;
        case DbType.Single:
          return OracleDbType.Float;
        case DbType.Double:
          return OracleDbType.Double;
        case DbType.Date:
          return OracleDbType.Date;
        case DbType.DateTime:
          return OracleDbType.TimeStamp;
        case DbType.Time:
          return OracleDbType.IntervalDS;
        case DbType.Binary:
          return OracleDbType.Blob;
        case DbType.Boolean:
          return OracleDbType.Boolean;
        case DbType.Int64:
        case DbType.UInt64:
        case DbType.VarNumeric:
        case DbType.Decimal:
        case DbType.Currency:
          return OracleDbType.Number;
        case DbType.Object:
          return OracleDbType.Object;
        case DbType.Guid:
          return OracleDbType.Raw;
        default:
          throw new NotSupportedException();
      }
    }
Here is a general article "Working with BLOB and CLOB Data":
http://www.devart.com/dotconnect/oracle/docs/?Lob.html.

tperetzke
Posts: 4
Joined: Tue 29 Jun 2010 13:26

Post by tperetzke » Fri 02 Jul 2010 08:11

Thank you Shalex!... also for the hint for the xLOB-Documentation...

So, as I understand now, it is not possible to implicitly retrieve CLOB data by just using the abstract .NET data classes and their Factories (without having an explicit reference to the devart assemblies) - it's a pity...


But anyway thx again...


Thomas P.

tperetzke
Posts: 4
Joined: Tue 29 Jun 2010 13:26

Post by tperetzke » Fri 08 Oct 2010 07:56

Hello Shalex,

sorry for pushing this thread, but I just read this: New build of dotConnect for Oracle 5.70.170 is available!)

And, I found this new feature :o:
  • ...
  • Changed behaviour: OracleParameter.OracleDbType will be set by default to OracleDbType.Clob for the values which are longer than 4000 symbols
  • ...
My question now:
Does this new feature simply fix my problems I have with the abstract DbParameter-Class, where I cannot address a CLOB-field by setting the DbType maybe in connection with Size to somewhat unique?
Would it suffice now to just set the DbType to String and "dotConnect" does the rest for me now?
That would be very useful for me :D !!

Thanks in advance, again

Thomas P.

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Fri 08 Oct 2010 16:43

Thomas, I have tried executing the following code with dotConnect for Oracle v 5.70.170 and succeded - 5000 symbols were inserted to the database. Please note that DbParameter.DbType is not set explicitly because in the case of DbType.String the value that is passed to database will be truncated to 4000 symbols.

Code: Select all

//create table clob_table
//(
//  clob_column clob
//);

    DbProviderFactory factory = DbProviderFactories.GetFactory("Devart.Data.Oracle");
    DbConnection conn = factory.CreateConnection();
    conn.ConnectionString = "server=ora1020;uid=***;pwd=***;";
    conn.Open();
    DbCommand cmd = conn.CreateCommand();
    cmd.CommandText = "insert into clob_table(clob_column) values (:p1)";
    string str = new string('a', 5000);
    DbParameter param = cmd.CreateParameter();
    param.ParameterName = "p1";
    param.Value = str;
    cmd.Parameters.Add(param);
    cmd.ExecuteNonQuery();

tperetzke
Posts: 4
Joined: Tue 29 Jun 2010 13:26

Post by tperetzke » Mon 11 Oct 2010 12:19

Thank you Shalex!!!

I've just tested it in our project and,... It works! :D


Best regards
Thomas P.

Post Reply