Serializing System.Data.OleDb.OleDbParameter[] not working with Newtonsoft.Json

Question

I'm implementing an abstraction layer between client software and a database. The software was originally written using OleDb and it's working well. We already deal with data access through a Data component but the client software still calls its procedures using OleDb based procedures. To be precise, we would still pass System.Data.OleDb.OleDbParameter[] arrays into the Data component and it would return System.Data.DataSet objects to the client.

We are now writing an API as an intermediate between database and Data Component, so in order to cause as little impact as possible to the client software we will, for starters anyway, still call its methods in exactly the same way. Internally, the Data Component will serialize the OleDbParameter[] arrays we pass in to JSON and call the API, which will in turn return the Data Sets serialized to JSON so that our Data Component can then Deserialize them back to a System.Data.DataSet and the Client software won't be any the wiser.

This is where we hit an odd snag.

Having tried System.Text.Json and System.Web.Script.Serialization I find that both don't work very happily with DataSets. Both of these appear to have problem with circular references when trying to serialize System.Data.DataSet object. Here, NewtonSoft.Json comes to the rescue:

Serializing the DataSet is easy with NewtonSoft.Json:

string sData = string.Empty;
Newtonsoft.Json.JsonSerializerSettings oSet = new Newtonsoft.Json.JsonSerializerSettings();
sData = Newtonsoft.Json.JsonConvert.SerializeObject(value: oDS, type: oDS.GetType(), settings: oSet);

And so is deserializing it back into a DataSet

System.Data.DataSet oDS = null;
oDS = Newtonsoft.Json.JsonConvert.DeserializeObject<System.Data.DataSet>(value: sData);

So you'd think the choice is an easy one. NewtonSoft.Json it is, right. Except.... It gives me problems with the Parameters

string sParameters = string.Empty;
System.Data.OleDb.OleDbParameter oXML;
oXML = new System.Data.OleDb.OleDbParameter(name: "XML", dataType: System.Data.OleDb.OleDbType.VarWChar, size: -1);
oXML.Value = "NotRelevantToThisQuestion";

System.Data.OleDb.OleDbParameter oTotalRecords;
oTotalRecords = new System.Data.OleDb.OleDbParameter(name: "TotalRecords", dataType: System.Data.OleDb.OleDbType.Integer);
oTotalRecords.Value = 0;
oTotalRecords.Direction = System.Data.ParameterDirection.InputOutput;

System.Data.OleDb.OleDbParameter[] oParms;
oParms = new System.Data.OleDb.OleDbParameter[] {oXML, oTotalRecords };

Newtonsoft.Json.JsonSerializerSettings oSet = new Newtonsoft.Json.JsonSerializerSettings();
sParameters = Newtonsoft.Json.JsonConvert.SerializeObject(value: oParms, type: oParms.GetType(), settings: oSet);

It seems to produce a JSON string that lists the two parameters, but none of their properties are present. The resulting JSON is:

["XML","TotalRecords"]

I can get THAT one to work using System.Web.Serialization.JavaScriptSerializer

System.Web.Script.Serialization.JavaScriptSerializer oSer;
oSer = new System.Web.Script.Serialization.JavaScriptSerializer();
sParameters = oSer.Serialize(oParms);

This produces:

[{"DbType":16,"OleDbType":202,"ParameterName":"XML","Precision":0,"Scale":0,"Value":"NotRelevantToThisQuestion","Direction":1,"IsNullable":false,"Size":-1,"SourceColumn":"","SourceColumnNullMapping":false,"SourceVersion":512},{"DbType":11,"OleDbType":3,"ParameterName":"TotalRecords","Precision":0,"Scale":0,"Value":0,"Direction":3,"IsNullable":false,"Size":0,"SourceColumn":"","SourceColumnNullMapping":false,"SourceVersion":512}]

And after the call the Input/Output parameter gets a value which I can then also serialize, send back and deserialize at the receiving end to allow me to read its value, as well as the DataSet which I already deserialized using NewtonSoft.Json

System.Data.OleDb.OleDbParameter[] oParms;
System.Web.Script.Serialization.JavaScriptSerializer oSer;
oSer = new System.Web.Script.Serialization.JavaScriptSerializer();
oParms = oSer.Deserialize<System.Data.OleDb.OleDbParameter[]>(sParameters);

So if you were patient enough to read all the way through to here I can just about imagine what you're thinking now: "Just use NewtonSoft.Json for your DataSet, and use System.Web.Script.Serialization.JavaScriptSerializer for your parameters. What's your problem?"

Sure. But it sticks in my craw that I would have to use two different packages to do a job that one package should be able to handle. So with that said I can finally get to my question:

Is there any way to handle both Serialization/Deserialization requirements with a single package? As long as it's documented clearly I am happy to use any of the 3 packages I already mentioned or even another one you might present here.

Your question is not clear. OleDb is just a db provider. It has nothing to do how to serialize or deserialize data. You just have to separe a db level from a data model level — Serge, May 22 '23 at 21:27
Yor problem is that `OleDbParameter` is being serialized as a string rather than as an object. In the debugger, if I do `TypeDescriptor.GetConverter(typeof(System.Data.OleDb.OleDbParameter))`, it comes back with an `OleDbParameterConverter`. This may be the same problem as [Newtonsoft.JSON cannot convert model with TypeConverter attribute](https://stackoverflow.com/q/31325866/3744182). Try adding `NoTypeConverterJsonConverter` from [this answer](https://stackoverflow.com/a/31328131/3744182) to `JsonSerializerSettings.Converters` and see if the problem resolves. — dbc, May 22 '23 at 21:31
Indeed using `NoTypeConverterJsonConverter()` from [this answer](https://stackoverflow.com/a/31328131/3744182) to [Newtonsoft.JSON cannot convert model with TypeConverter attribute](https://stackoverflow.com/q/31325866/3744182) causes `OleDbParameter` to be serialized fully, see https://dotnetfiddle.net/ghTqBV. Is it enough to mark your question as a duplicate, or do you need more specific help? — dbc, May 22 '23 at 22:30
Thank you @dbc - let me have a play with that and I will let you know — DinahMoeHumm, May 23 '23 at 07:51
@dbc - I implemented the code sample as described but I am getting an error on the statement var contract = this.CreateObjectContract(objectType); - the error is "System.ArgumentException: 'Invalid type owner for DynamicMethod.'" - this happens when I try to serialise the System.Data.OleDb.OleDbParameter[] object after adding a new NoTypeConverterJsonConverter to the JsonSerializerSettings — DinahMoeHumm, May 23 '23 at 08:59
Hello @dbc - I got it to work but I couldn't get it to work for System.Data.OleDb.OleDbParameter[], but I did get it to work for a System.Data.OleDb.OleDbParameter, and I managed to get it to work for a System.Collections.Generic.List of those, so then all I need to do is convert the array to a list and serialize it, and convert it back to an array after deserializing it. That, I can live with. I would argue that this question is specific enough to stand on its own so I would appreciate it if it weren't marked as a duplicate? — DinahMoeHumm, May 23 '23 at 09:36
*this happens when I try to serialise the System.Data.OleDb.OleDbParameter[] object after adding a new `NoTypeConverterJsonConverter`* -- no, use **`new NoTypeConverterJsonConverter()` ** as shown in https://dotnetfiddle.net/ghTqBV. — dbc, May 23 '23 at 13:20

DinahMoeHumm · Answer 1 · 2023-05-24T10:11:34.590

There were two problems I had to address. The first problem was how to serialize a single OleDbParameter. For that problem there is already a solution as pointed out by @dbc:

Newtonsoft.JSON cannot convert model with TypeConverter attribute

For the OleDbParameter I added this:

[TypeConverter(typeof(OleDBParameterConverter))]
[JsonConverter(typeof(NoTypeConverterJsonConverter<System.Data.OleDb.OleDbParameter>))]
public class OleDBParameterConverter : TypeConverter
{
    public override bool CanConvertFrom(ITypeDescriptorContext context, System.Type sourceType)
    {
        if (sourceType == typeof(string))
        {
            return true;
        }
        return base.CanConvertFrom(context, sourceType);
    }
    public override object ConvertFrom(ITypeDescriptorContext context, System.Globalization.CultureInfo culture, object value)
    {
        if (value is string)
        {
            string s = value.ToString();
            //s = s.Replace("\\", "");
            System.Data.OleDb.OleDbParameter[] f = JsonConvert.DeserializeObject<System.Data.OleDb.OleDbParameter[]>(s);
            return f;
        }
        return base.ConvertFrom(context, culture, value);
    }
}

And I added the NoTypeConverterJsonConverter class

public class NoTypeConverterJsonConverter<T> : JsonConverter
{
    static readonly IContractResolver resolver = new NoTypeConverterContractResolver();

    class NoTypeConverterContractResolver : DefaultContractResolver
    {
        protected override JsonContract CreateContract(Type objectType)
        {
            if (typeof(T).IsAssignableFrom(objectType))
            {
                var contract = this.CreateObjectContract(objectType);
                contract.Converter = null; // Also null out the converter to prevent infinite recursion.
                return contract;
            }
            return base.CreateContract(objectType);
        }
    }

    public override bool CanConvert(Type objectType)
    {
        return typeof(T).IsAssignableFrom(objectType);
    }

    public override object ReadJson(JsonReader reader, Type objectType, object existingValue, JsonSerializer serializer)
    {
        return JsonSerializer.CreateDefault(new JsonSerializerSettings { ContractResolver = resolver }).Deserialize(reader, objectType);
    }

    public override void WriteJson(JsonWriter writer, object value, JsonSerializer serializer)
    {
        JsonSerializer.CreateDefault(new JsonSerializerSettings { ContractResolver = resolver }).Serialize(writer, value);
    }
}

This allowed me to serialize a single System.Data.OleDb.OleDbParameter, but I need to serialize an array of them, which I tried by defining a NoTypeConverterJsonConverter<System.Data.OleDb.OleDbParameter[]>

Newtonsoft.Json.JsonSerializerSettings oJSet = new Newtonsoft.Json.JsonSerializerSettings();
NoTypeConverterJsonConverter<System.Data.OleDb.OleDbParameter[]> oConv = new NoTypeConverterJsonConverter<System.Data.OleDb.OleDbParameter[]>();
oJSet.Converters.Add(oConv);

But when I tried to use it, it led to an ArgumentException.

Edit: this is not necessary I eventually figured out I could avoid this by moving the Array to a System.Collections.Generic.List<System.Data.OleDb.OleDbParameter>

//No need to do this. Commented out
//System.Collections.Generic.List<System.Data.OleDb.OleDbParameter> oLParms = new System.Collections.Generic.List<System.Data.OleDb.OleDbParameter>();
//foreach (System.Data.OleDb.OleDbParameter oParm in oParms)
//{
//    oLParms.Add(oParm);
//}

Edit: I don't need to move the Array to a List, so I can replace this in the statement below. This, I was able to serialize without a problem -- Edit: As per dbc's suggestion, I can now use a NoTypeConverterJsonConverter<System.Data.OleDb.OleDbParameter> instead, and then simply convert the entire array in one go:

Newtonsoft.Json.JsonSerializerSettings oJSet = new Newtonsoft.Json.JsonSerializerSettings();
NoTypeConverterJsonConverter<System.Data.OleDb.OleDbParameter> oConv = new NoTypeConverterJsonConverter<System.Data.OleDb.OleDbParameter>();
oJSet.Converters.Add(oConv);
//No need to do this, replaced with statement below //sParameters = Newtonsoft.Json.JsonConvert.SerializeObject(value: oLParms, type: oLParms.GetType(), settings: oJSet); //not necessary!
sParameters = Newtonsoft.Json.JsonConvert.SerializeObject(value: oParms, type: oParms.GetType(), settings: oJSet);

And deserializing it is easy, too:

//not necessary to use a list, replaced with direct assignment to oParms below //oLParms = Newtonsoft.Json.JsonConvert.DeserializeObject<System.Collections.Generic.List<System.Data.OleDb.OleDbParameter>>(value: sParameters, settings: oJSet);
//not necessary to use a list, replaced with direct assignment to oParms below //oParms = oLParms.ToArray();
oParms = Newtonsoft.Json.JsonConvert.DeserializeObject<System.Data.OleDb.OleDbParameter[]>(value: sParameters, settings: oJSet);

Many thanks @dbc for your help.

You don't apply the converters **to the converter** -- you never serialize the converter. Generally the converter to the type being serialized, here `OleDbParameter`, but you can't because it is built into the framework and read only. So instead, add `new NoTypeConverterJsonConverter()` to `settings.Converters` and it will get used. You don't need a converter for `OleDbParameter []`, only for `OleDbParameter`. Then serialization of `OleDbParameter []` will work, see https://dotnetfiddle.net/ghTqBV. — dbc, May 23 '23 at 13:17
Aha! I will be giving that a try later .... You've been very helpful, cheers. — DinahMoeHumm, May 23 '23 at 16:04
@dbc .... Unsurprisingly you are absolutely right. Copying the System.Data.OleDb.OleDbParameter[] to a System.Collections.Generic.List and vice versa is unnecessary; the serialization and deserialization works perfectly with the array. You have been exceedingly helpful, so thanks again! — DinahMoeHumm, May 24 '23 at 10:01

Serializing System.Data.OleDb.OleDbParameter[] not working with Newtonsoft.Json

1 Answers1