2

I am trying to serialize DBParameters which I define in my class.

when it trying to convert into serialize it gives following error.

"Use the XmlInclude or SoapInclude attribute to specify types that are not known statically."

here is my code

[Serializable]
public class DemoClass{
public List<DbParameter> Parameters { get; set; }
}

can anybody help me to sortout this issue ? how can I serialize Parameters property ?

Thanks

Sandip
  • 481
  • 1
  • 9
  • 26
  • See this question http://stackoverflow.com/questions/11886290/use-the-xmlinclude-or-soapinclude-attribute-to-specify-types-that-are-not-known and this one: http://stackoverflow.com/questions/4616505/is-there-a-reason-why-a-base-class-decorated-with-xmlinclude-would-still-throw-a – Edgar Oct 09 '13 at 08:34

2 Answers2

2

DbParameter is not a good choice for serialization.

Firstly, there is the issue that it is an abstract base-class, so for most serializers (including XmlSerializer) you would need to formally advertise the expected concrete sub-classes in advance - and those that don't need this will usually want [Serializable] instead - which it isn't.

The second (perhaps more significant) issue is that it is implementation (it is fundamentally tied into ADO.NET), where-as serialization should be about data.

I would strongly advise writing your own DTO model for serialization purposes, that focuses on what you need to know, for example Name, and Value being the most obvious things, but DataType and Direction being important too - and serialize your own model. This would address both issues.

There is a third issue, which is that I would question the purpose of serializing a parameter in the first place. If this is for RPC purposes, I would strongly advise against it, as it sounds like you are in the process of opening up an arbitrary sql hole. For RPC, just define and pass parameters / arguments as part of the service boundary.

Marc Gravell
  • 1,026,079
  • 266
  • 2,566
  • 2,900
  • I'm not the OP but I came across this answer because I was trying to use JsonSerializer to generically serialize a DbCommand and its Parameters for logging errors. The DbConnection and DbCommand itself serialize just fine, but the DbParameterCollection only spits out the ParameterName of each item unless, as you suggest, you handle it with explicit property extraction. Even casting each item in the ParameterCollection to DbParameter (or even SqlParameter) yields only the name in the serialization output. Arg. – mdisibio Apr 11 '18 at 18:42
0

It's best to manually serialize them (a collection is no dictionary):

public static string ParameterCollectionToXml(System.Data.IDataParameterCollection collection)
{
    System.Text.StringBuilder sb = new System.Text.StringBuilder();

    using (System.IO.TextWriter sw = new System.IO.StringWriter(sb))
    {
        using (System.Xml.XmlWriter xw = System.Xml.XmlWriter.Create(sw, new System.Xml.XmlWriterSettings()
        {
            Indent = true,
            IndentChars = " ",
            CheckCharacters = false
        }))
        {
            xw.WriteStartDocument();
            xw.WriteStartElement("Parameters");

            foreach (System.Data.IDataParameter thisParameter in collection)
            {
                xw.WriteStartElement("Parameter");


                xw.WriteStartElement("ParameterName");
                xw.WriteValue(thisParameter.ParameterName);
                xw.WriteEndElement();


                xw.WriteStartElement("DbType");
                xw.WriteValue(thisParameter.DbType);
                xw.WriteEndElement();


                xw.WriteStartElement("Direction");
                xw.WriteValue(thisParameter.Direction);
                xw.WriteEndElement();

                xw.WriteStartElement("IsNullable");
                xw.WriteValue(thisParameter.IsNullable);
                xw.WriteEndElement();


                xw.WriteStartElement("SourceColumn");
                xw.WriteValue(thisParameter.SourceColumn);
                xw.WriteEndElement();

                // SourceVersion

                xw.WriteStartElement("Value");
                xw.WriteValue(thisParameter.Value);
                xw.WriteEndElement();

                xw.WriteEndElement();
            } // Next thisParameter 


            xw.WriteEndElement(); // root 
            xw.WriteEndDocument();
            xw.Flush();
        } // End Using xw

        sw.Flush();
    } // End Using sw 

    return sb.ToString();
} // End Function ParameterCollectionToXml 


public static string ParameterCollectionToXml(System.Data.Common.DbParameterCollection collection)
{
    System.Text.StringBuilder sb = new System.Text.StringBuilder();

    using (System.IO.TextWriter sw = new System.IO.StringWriter(sb))
    {
        using (System.Xml.XmlWriter xw = System.Xml.XmlWriter.Create(sw, new System.Xml.XmlWriterSettings()
        {
            Indent = true,
            IndentChars = " ",
            CheckCharacters = false
        }))
        {
            xw.WriteStartDocument();
            xw.WriteStartElement("Parameters");

            foreach (System.Data.Common.DbParameter thisParameter in collection)
            {
                xw.WriteStartElement("Parameter");


                xw.WriteStartElement("ParameterName");
                xw.WriteValue(thisParameter.ParameterName);
                xw.WriteEndElement();


                xw.WriteStartElement("DbType");
                xw.WriteValue(thisParameter.DbType);
                xw.WriteEndElement();


                xw.WriteStartElement("Direction");
                xw.WriteValue(thisParameter.Direction);
                xw.WriteEndElement();

                xw.WriteStartElement("IsNullable");
                xw.WriteValue(thisParameter.IsNullable);
                xw.WriteEndElement();

                xw.WriteStartElement("Size");
                xw.WriteValue(thisParameter.Size);
                xw.WriteEndElement();


                xw.WriteStartElement("SourceColumn");
                xw.WriteValue(thisParameter.SourceColumn);
                xw.WriteEndElement();

                xw.WriteStartElement("SourceColumnNullMapping");
                xw.WriteValue(thisParameter.SourceColumnNullMapping);
                xw.WriteEndElement();

                // SourceVersion

                xw.WriteStartElement("Value");
                xw.WriteValue(thisParameter.Value);
                xw.WriteEndElement();

                xw.WriteEndElement();
            } // Next thisParameter 


            xw.WriteEndElement(); // root 
            xw.WriteEndDocument();
            xw.Flush();
        } // End Using xw

        sw.Flush();
    } // End Using sw 

    return sb.ToString();
} // End Function ParameterCollectionToXml 


public static string ParameterCollectionToJson(System.Data.Common.DbParameterCollection collection)
{
    System.Text.StringBuilder sb = new System.Text.StringBuilder();

    using (System.IO.TextWriter sw = new System.IO.StringWriter(sb))
    {


        using (Newtonsoft.Json.JsonWriter jw = new Newtonsoft.Json.JsonTextWriter(sw))
        {
            jw.Formatting = Newtonsoft.Json.Formatting.Indented;

            jw.WriteStartArray();

            foreach (System.Data.Common.DbParameter thisParameter in collection)
            {
                jw.WriteStartObject();

                jw.WritePropertyName("ParameterName");
                jw.WriteValue(thisParameter.ParameterName);

                jw.WritePropertyName("DbType");
                jw.WriteValue(thisParameter.DbType);


                jw.WritePropertyName("Direction");
                jw.WriteValue(thisParameter.Direction);

                jw.WritePropertyName("IsNullable");
                jw.WriteValue(thisParameter.IsNullable);

                jw.WritePropertyName("Size");
                jw.WriteValue(thisParameter.Size);

                jw.WritePropertyName("SourceColumn");
                jw.WriteValue(thisParameter.SourceColumn);

                jw.WritePropertyName("SourceColumnNullMapping");
                jw.WriteValue(thisParameter.SourceColumnNullMapping);

                // SourceVersion

                jw.WritePropertyName("Value");
                jw.WriteValue(thisParameter.Value);

                jw.WriteEndObject();
            } // Next thisParameter 


            jw.WriteEndArray();
            jw.Flush();
        } // End Using xw

        sw.Flush();
    } // End Using sw 

    return sb.ToString();
}


public static string ParameterCollectionToJson(System.Data.IDataParameterCollection collection)
{
    System.Text.StringBuilder sb = new System.Text.StringBuilder();

    using (System.IO.TextWriter sw = new System.IO.StringWriter(sb))
    {


        using (Newtonsoft.Json.JsonWriter jw = new Newtonsoft.Json.JsonTextWriter(sw))
        {
            jw.Formatting = Newtonsoft.Json.Formatting.Indented;

            jw.WriteStartArray();

            foreach (System.Data.IDataParameter thisParameter in collection)
            {
                jw.WriteStartObject();

                jw.WritePropertyName("ParameterName");
                jw.WriteValue(thisParameter.ParameterName);

                jw.WritePropertyName("DbType");
                jw.WriteValue(thisParameter.DbType);


                jw.WritePropertyName("Direction");
                jw.WriteValue(thisParameter.Direction);

                jw.WritePropertyName("IsNullable");
                jw.WriteValue(thisParameter.IsNullable);

                jw.WritePropertyName("SourceColumn");
                jw.WriteValue(thisParameter.SourceColumn);

                // SourceVersion

                jw.WritePropertyName("Value");
                jw.WriteValue(thisParameter.Value);

                jw.WriteEndObject();
            } // Next thisParameter 


            jw.WriteEndArray();
            jw.Flush();
        } // End Using xw

        sw.Flush();
    } // End Using sw 

    return sb.ToString();
}

For System.Text.Json, this is

namespace JsonTools
{


    public class ParameterCollectionHelper 
    {

        private delegate bool WriteValueExtender_t(System.Type t, System.Text.Json.Utf8JsonWriter jsonWriter, object obj);


        // https://github.com/JamesNK/Newtonsoft.Json/blob/master/Src/Newtonsoft.Json/JsonWriter.cs#L525
        private static bool WriteValueInternalUnhandled(System.Type t, System.Text.Json.Utf8JsonWriter jsonWriter, object obj)
        {
            // The problem with the functional c-type switch is that it is not getting the precompiled speed that the actual switch-case syntax gets. 

            // C# 7+ Switch
            switch (obj)
            {
                case System.Guid tGuid:
                    jsonWriter.WriteStringValue(tGuid);
                    return false;
                case System.String tString:
                    jsonWriter.WriteStringValue(tString);
                    return false;
                case System.Char tChar:
                    jsonWriter.WriteStringValue(tChar.ToString(System.Globalization.CultureInfo.InvariantCulture));
                    return false;
                case System.Boolean tBoolean:
                    jsonWriter.WriteBooleanValue(tBoolean);
                    return false;
                case System.DateTime tDateTime:
                    jsonWriter.WriteStringValue(tDateTime);
                    return false;
                case System.DateTimeOffset tOffset:
                    jsonWriter.WriteStringValue(tOffset);
                    return false;
                case System.Single tSingle:
                    jsonWriter.WriteNumberValue(tSingle);
                    return false;
                case System.Double tDouble:
                    jsonWriter.WriteNumberValue(tDouble);
                    return false;
                case System.Decimal tDecimal:
                    jsonWriter.WriteNumberValue(tDecimal);
                    return false;
                case System.Byte tByte:
                    jsonWriter.WriteNumberValue(tByte);
                    return false;
                case System.SByte tSByte:
                    jsonWriter.WriteNumberValue(tSByte);
                    return false;
                case System.Int16 tInt16:
                    jsonWriter.WriteNumberValue(tInt16);
                    return false;
                case System.UInt16 tUInt16:
                    jsonWriter.WriteNumberValue(tUInt16);
                    return false;
                case System.Int32 tInt32:
                    jsonWriter.WriteNumberValue(tInt32);
                    return false;
                case System.UInt32 tUInt32:
                    jsonWriter.WriteNumberValue(tUInt32);
                    return false;
                case System.Int64 tInt64:
                    jsonWriter.WriteNumberValue(tInt64);
                    return false;
                case System.UInt64 tUInt64:
                    jsonWriter.WriteNumberValue(tUInt64);
                    return false;
                case System.IntPtr tIntPtr:
                    jsonWriter.WriteNumberValue(tIntPtr.ToInt64());
                    return false;
                case System.UIntPtr tUIntPtr:
                    jsonWriter.WriteNumberValue(tUIntPtr.ToUInt64());
                    return false;
                // https://github.com/microsoftarchive/bcl/blob/master/Libraries/BigRational/BigRationalLibrary/BigRational.cs
                // https://github.com/orgs/microsoftarchive/repositories
                case System.Numerics.BigInteger tBigInteger:
                    jsonWriter.WriteStringValue(tBigInteger.ToString(System.Globalization.CultureInfo.InvariantCulture)); // TODO: FixMe
                    return false;
                case byte[] tBuffer:
                    jsonWriter.WriteStringValue(System.Convert.ToBase64String(tBuffer));
                    return false;
            }

            return true;
        }


        private static void WriteValue(System.Text.Json.Utf8JsonWriter jsonWriter, object obj, WriteValueExtender_t extensions)
        {
            if (obj == System.DBNull.Value || obj == null)
            {
                jsonWriter.WriteNullValue();
                return;
            }

            System.Type t = obj.GetType();

            if (WriteValueInternalUnhandled(t, jsonWriter, obj))
                if (extensions != null || extensions(t, jsonWriter, obj))
                    throw new System.NotImplementedException("WriteValue for " + t.FullName);
        }

        private static void WriteValue(System.Text.Json.Utf8JsonWriter jsonWriter, object obj)
        {
            if (obj == System.DBNull.Value || obj == null)
            {
                jsonWriter.WriteNullValue();
                return;
            }

            System.Type t = obj.GetType();

            if (WriteValueInternalUnhandled(t, jsonWriter, obj))
                throw new System.NotImplementedException("WriteValue for " + t.FullName);
        }


        public static string ParameterCollectionToJson(System.Data.Common.DbParameterCollection collection)
        {
            string retValue = null;

            using (System.IO.MemoryStream stream = new System.IO.MemoryStream())
            {
                using (System.Text.Json.Utf8JsonWriter jw = new System.Text.Json.Utf8JsonWriter(stream,
                   new System.Text.Json.JsonWriterOptions
                   {
                       Indented = true
                   })
                )
                {
                    jw.WriteStartArray();

                    foreach (System.Data.Common.DbParameter thisParameter in collection)
                    {
                        jw.WriteStartObject();

                        jw.WritePropertyName("ParameterName");
                        WriteValue(jw, thisParameter.ParameterName);


                        jw.WritePropertyName("DbType");
                        WriteValue(jw, thisParameter.DbType);


                        jw.WritePropertyName("Direction");
                        WriteValue(jw, thisParameter.Direction);

                        jw.WritePropertyName("IsNullable");
                        WriteValue(jw, thisParameter.IsNullable);

                        jw.WritePropertyName("Size");
                        WriteValue(jw, thisParameter.Size);

                        jw.WritePropertyName("SourceColumn");
                        WriteValue(jw, thisParameter.SourceColumn);

                        jw.WritePropertyName("SourceColumnNullMapping");
                        WriteValue(jw, thisParameter.SourceColumnNullMapping);

                        // SourceVersion

                        jw.WritePropertyName("Value");
                        WriteValue(jw, thisParameter.Value);

                        jw.WriteEndObject();
                    } // Next thisParameter 

                    jw.WriteEndArray();
                    jw.Flush();
                } // End Using jw 

                stream.Flush();


                stream.Position = 0;
                byte[] bytes = stream.ToArray();
                retValue = System.Text.Encoding.UTF8.GetString(bytes);
            } // End Using stream

            return retValue;
        }



        public static string ParameterCollectionToJson(System.Data.IDataParameterCollection collection)
        {
            string retValue = null;

            using (System.IO.MemoryStream stream = new System.IO.MemoryStream())
            {
                using (System.Text.Json.Utf8JsonWriter jw = new System.Text.Json.Utf8JsonWriter(stream,
                   new System.Text.Json.JsonWriterOptions
                   {
                       Indented = true
                   })
                )
                {
                    jw.WriteStartArray();

                    foreach (System.Data.IDataParameter thisParameter in collection)
                    {
                        jw.WriteStartObject();

                        jw.WritePropertyName("ParameterName");
                        WriteValue(jw, thisParameter.ParameterName);


                        jw.WritePropertyName("DbType");
                        WriteValue(jw, thisParameter.DbType);


                        jw.WritePropertyName("Direction");
                        WriteValue(jw, thisParameter.Direction);

                        jw.WritePropertyName("IsNullable");
                        WriteValue(jw, thisParameter.IsNullable);

                        jw.WritePropertyName("SourceColumn");
                        WriteValue(jw, thisParameter.SourceColumn);

                        // SourceVersion

                        jw.WritePropertyName("Value");
                        WriteValue(jw, thisParameter.Value);

                        jw.WriteEndObject();
                    } // Next thisParameter 

                    jw.WriteEndArray();
                    jw.Flush();
                } // End Using jw 

                stream.Flush();


                stream.Position = 0;
                byte[] bytes = stream.ToArray();
                retValue = System.Text.Encoding.UTF8.GetString(bytes);
            } // End Using stream

            return retValue;
        }


    }


}
Stefan Steiger
  • 78,642
  • 66
  • 377
  • 442