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.
DbProviderFactory: Oracle-Type -> DbType Mapping?
Here is a mapping between the System.Data.DbType and Devart.Data.Oracle.OracleDbType enumerations:
Here is a general article "Working with BLOB and CLOB Data":
http://www.devart.com/dotconnect/oracle/docs/?Lob.html.
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();
}
}
http://www.devart.com/dotconnect/oracle/docs/?Lob.html.
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.
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.
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 :
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 !!
Thanks in advance, again
Thomas P.
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 :
My question now:
- ...
- Changed behaviour: OracleParameter.OracleDbType will be set by default to OracleDbType.Clob for the values which are longer than 4000 symbols
- ...
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 !!
Thanks in advance, again
Thomas P.
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();