Saving a C# object as a Blob in Oracle

Question

I have a public class that I need to save as a Blob in Oracle. I built the DataLayer to be able to work with Oracle or MySQL, so DBType is the standard method of adding parameters. Now the problem is that because the datalayer was written to be agnostic, I'm using DBType when creating parameters. However, when you create a blob parameter in oracle, usually people use OracleDBType. There is no DBType.blob. I tried using DBType.object, but that gave me an error.

            IDbCommand command = ProviderManager.factory.CreateCommand();
            command.CommandText = commandText;
            command.Connection = connection;
            command.CommandType = commandType;

            DbParameter dbParam = ProviderManager.factory.CreateParameter();
            dbParam.ParameterName = "Summary";
            dbParam.Value = batchSummary;
            dbParam.Direction = parameterDirection;
            dbParam.DbType = DbType.Object;

            command.Parameters.Add(dbParam);
            command.ExecuteNonQuery();

This was the error I received:

Oracle.ManagedDataAccess.Client.OracleException: 'Unsupported column datatype'

why force yourself in serialize to byte[]? there are many libraries able to serialize whatever (more or less) in XML, Json and surely other format: Have a look at [JSon.Net](https://www.newtonsoft.com/json/help/html/SerializingJSON.htm): for simple scenario (and not for simple ones only) it can get your goal — Gian Paolo, Nov 06 '19 at 20:08
If you really need it to be a byte array - use some deterministic format like protobuf — fredrik, Nov 06 '19 at 20:38

Athanasios Kataras · Answer 1 · 2019-11-06T19:58:55.777

The way I see it, there are two ways for you to go.

The first one is described here and it is a global way to do it. Create a converter and use it wherever you want.

The second one would be to override the ConvertTo() function in the BatchSummary as your object does not support (yet, until you implement it) the conversion, and that is why you get the NotSupportedException.

Most native data types (Int32, String, enumeration types, and others) have default type converters that provide string-to-value conversions and perform validation checks. The default type converters are in the System.ComponentModel namespace and are named TypeConverterNameConverter. You can extend a type converter when the default functionality is not adequate for your purposes or implement a custom type converter when you define a custom type that does not have an associated type converter.

You can check microsoft docs for the way to go creating a convertor for not known values.

Effectively, what you need to do is a variation of the following

   // Overrides the ConvertTo method of TypeConverter.
   public override object ConvertTo(object value, Type destinationType) {  
      if (destinationType == typeof(byte[])) {
         // Do whatever transformation you want here
         return transformedObject;
      }
      return base.ConvertTo(value, destinationType);
   }

Since the object type is known you can do whatever you like to it. You might also need to create the ConvertFrom in order to get the object back.

Thanks, I opted for the easier route and propositioned the team to have that object serializable. However, I'm not running into a different problem as indicated in my updated post. — DrunkOldHobo, Nov 06 '19 at 20:11

score 0 · Accepted Answer · answered Nov 06 '19 at 23:42

0

Replace

dbParam.DbType = DbType.Object;

with

dbParam.DbType = DbType.Binary;

answered Nov 06 '19 at 23:42

DrunkOldHobo

57
5

Saving a C# object as a Blob in Oracle

2 Answers2