XML Serialization of dapper Results

Question

I store SQL results in a dynamic list that has a base type of DapperRow. I am trying to serialize / unserialize this list which complains about XMLserializer:

There was an error generating the XML document. ---> System.InvalidOperationException: To be XML serializable, types which inherit from IEnumerable must have an implementation of Add(System.Object) at all levels of their inheritance hierarchy. Dapper.SqlMapper+DapperRow does not implement Add(System.Object).

Is there a way around this (in addition to obviously casting the results to my own specific object) or is it possible to somehow make the DapperRow objects comply with the limitations of System.Xml.XMLserializer?

He claims that my array of results. System.Collections.Generic.List<dynamic> {System.Collections.Generic.List<object>} Opening the array, he says that every object is of typeobject {Dapper.SqlMapper.DapperRow}

I think because DapperRows is now basically IDictionary<string, object>that XML has problems (I cannot use anything except System.Xml.XmlSerializer, therefore I do not offer an alternative).

I just want to use List<dynamic>which I get from Dapper and serialize and deserialize correctly usingSystem.Xml.XmlSerializer

Answer 1

I was able to get something at least serializable using a serializable dictionary: http://weblogs.asp.net/pwelter34/444961

            var results = conn.Query<dynamic>(sql, param);
            var resultSet = new List<SerializableDictionary<string, object>>();
            foreach (IDictionary<string, object> row in results)
            {
                var dict = new SerializableDictionary<string, object>();
                foreach (var pair in row)
                {
                    dict.Add(pair.Key, pair.Value);
                }
                resultSet.Add(dict);
            }

Its ugly, so I hope more elegant solutions come

Answer 2

First, decorate with an DapperResultSetattribute [Serializable]. Also create a constructor and in this assign Rowsempty List<object>. Use this modified code: (it also contains a modified implemented method)

[Serializable]
public class DapperResultSet : IEnumerable<object>

{public list lines {get; set; }

public void Add(dynamic o)
{
    Rows.Add(o);
}

public DapperResultSet()
{
    Rows = new List<object>();
}

public IEnumerator<object> GetEnumerator()
{
    return Rows.GetEnumerator();
}

System.Collections.IEnumerator System.Collections.IEnumerable.GetEnumerator()
{
    return GetEnumerator();
}

}

( ):

var results = conn.Query<dynamic>(sql, param);
var r = new DapperResultSet();
foreach (var row in results)
{
    r.Add(row);
}
//Here is the serialization part:
XmlSerializer xs = new XmlSerializer(typeof(DapperResultSet));
xs.Serialize(new FileStream("Serialized.xml", FileMode.Create), r); //Change path if necessary

,

XmlSerializer xs = new XmlSerializer(typeof(DapperResultSet));
DapperResultSet d_DRS = xs.Deserialize(new FileStream("Serialized.xml", FileMode.Open)); //Deserialized

Answer 3

- DapperRow System.Xml.XMLserializer?

, . DapperRow .

, .

. , .

( ):

public class Cell
{
    public string Name { get; set; }
    public object Value { get; set; }

    public Cell(){}

    public Cell(KeyValuePair<string, object> kvp)
    {
        Name = kvp.Key;
        Value = kvp.Value;
    }
}

public class Row : List<Cell>
{
    public Row(){}

    public Row(IEnumerable<Cell> cells)
        : base(cells){}        
}

public class Rows : List<Row>
{
    public Rows(){}

    public Rows(IEnumerable<Row> rows )
        :base(rows){}
}

:

public static class Extensions
{
    public static void Serialize(this IEnumerable<dynamic> enumerable, Stream stream)
    {
        var rows =
            new Rows(
                enumerable
                    .Cast<IEnumerable<KeyValuePair<string, object>>>()
                    .Select(row =>
                        new Row(row.Select(cell => new Cell(cell)))));

        XmlSerializer serializer = new XmlSerializer(typeof(Rows));

        serializer.Serialize(stream, rows);
    }
}

:

var result = connection.Query("SELECT * From Customers");

var memory_stream = new MemoryStream();

result.Serialize(memory_stream);

, , .

, , , , (, Rows) :

XmlSerializer serializer = new XmlSerializer(typeof(Rows));

Rows rows = (Rows)serializer.Deserialize(stream);

, Dapper Rows Rows . :

public static Rows ToRows(this IEnumerable<dynamic> enumerable)
{
    return
        new Rows(
            enumerable
                .Cast<IEnumerable<KeyValuePair<string, object>>>()
                .Select(row =>
                    new Row(row.Select(cell => new Cell(cell)))));
}

:

var rows = connection.Query("SELECT * From Customers").ToRows();

XmlSerializer serializer = new XmlSerializer(typeof(Rows));

serializer.Serialize(stream, rows);

Answer 4

, Dapper . , .

- IXmlSerializable. , .

, . , SerializableDictionary<TKey, TValue>, . typeof(TKey) typeof(TValue) '. ( ), object. , XML. . , , , , . , , . , , , .

, :

using System;
using System.Collections.Generic;
using System.Globalization;
using System.Linq.Expressions;
using System.Reflection;
using System.Xml;
using System.Xml.Schema;
using System.Xml.Serialization;

namespace Samples
{
    public class DapperResultSet : IXmlSerializable
    {
        static readonly Type TableType;
        static readonly Type RowType;
        static readonly Func<object, string[]> GetFieldNames;
        static readonly Func<object, object[]> GetFieldValues;
        static readonly Func<string[], object> CreateTable;
        static readonly Func<object, object[], object> CreateRow;
        static DapperResultSet()
        {
            TableType = typeof(Dapper.SqlMapper).GetNestedType("DapperTable", BindingFlags.NonPublic);
            RowType = typeof(Dapper.SqlMapper).GetNestedType("DapperRow", BindingFlags.NonPublic);
            // string[] GetFieldNames(object row)
            {
                var row = Expression.Parameter(typeof(object), "row");
                var expr = Expression.Lambda<Func<object, string[]>>(
                    Expression.Field(Expression.Field(Expression.Convert(row, RowType), "table"), "fieldNames"),
                    row);
                GetFieldNames = expr.Compile();
            }
            // object[] GetFieldValues(object row)
            {
                var row = Expression.Parameter(typeof(object), "row");
                var expr = Expression.Lambda<Func<object, object[]>>(
                    Expression.Field(Expression.Convert(row, RowType), "values"),
                    row);
                GetFieldValues = expr.Compile();
            }
            // object CreateTable(string[] fieldNames)
            {
                var fieldNames = Expression.Parameter(typeof(string[]), "fieldNames");
                var expr = Expression.Lambda<Func<string[], object>>(
                    Expression.New(TableType.GetConstructor(new[] { typeof(string[]) }), fieldNames),
                    fieldNames);
                CreateTable = expr.Compile();
            }
            // object CreateRow(object table, object[] values)
            {
                var table = Expression.Parameter(typeof(object), "table");
                var values = Expression.Parameter(typeof(object[]), "values");
                var expr = Expression.Lambda<Func<object, object[], object>>(
                    Expression.New(RowType.GetConstructor(new[] { TableType, typeof(object[]) }),
                        Expression.Convert(table, TableType), values),
                    table, values);
                CreateRow = expr.Compile();
            }
        }
        static readonly dynamic[] emptyItems = new dynamic[0];
        public IReadOnlyList<dynamic> Items { get; private set; }
        public DapperResultSet()
        {
            Items = emptyItems;
        }
        public DapperResultSet(IEnumerable<dynamic> source)
        {
            if (source == null) throw new ArgumentNullException("source");
            Items = source as IReadOnlyList<dynamic> ?? new List<dynamic>(source);
        }
        XmlSchema IXmlSerializable.GetSchema() { return null; }
        void IXmlSerializable.WriteXml(XmlWriter writer)
        {
            if (Items.Count == 0) return;
            // Determine field names and types
            var fieldNames = GetFieldNames((object)Items[0]);
            var fieldTypes = new TypeCode[fieldNames.Length];
            for (int count = 0, i = 0; i < Items.Count; i++)
            {
                var values = GetFieldValues((object)Items[i]);
                for (int c = 0; c < fieldTypes.Length; c++)
                {
                    if (fieldTypes[i] == TypeCode.Empty && values[c] != null)
                    {
                        fieldTypes[i] = Type.GetTypeCode(values[c].GetType());
                        if (++count >= fieldTypes.Length) break;
                    }
                }
            }
            // Write fields
            writer.WriteStartElement("Fields");
            writer.WriteAttributeString("Count", XmlConvert.ToString(fieldNames.Length));
            for (int i = 0; i < fieldNames.Length; i++)
            {
                writer.WriteStartElement("Field");
                writer.WriteAttributeString("Name", fieldNames[i]);
                writer.WriteAttributeString("Type", XmlConvert.ToString((int)fieldTypes[i]));
                writer.WriteEndElement();
            }
            writer.WriteEndElement();
            // Write items
            writer.WriteStartElement("Items");
            writer.WriteAttributeString("Count", XmlConvert.ToString(Items.Count));
            foreach (IDictionary<string, object> item in Items)
            {
                writer.WriteStartElement("Item");
                foreach (var entry in item)
                {
                    writer.WriteStartAttribute(entry.Key);
                    writer.WriteValue(entry.Value);
                    writer.WriteEndAttribute();
                }
                writer.WriteEndElement();
            }
            writer.WriteEndElement();
        }
        void IXmlSerializable.ReadXml(XmlReader reader)
        {
            reader.MoveToContent();
            bool isEmptyElement = reader.IsEmptyElement;
            reader.ReadStartElement(); // Container
            if (isEmptyElement) return;
            // Read fields
            int fieldCount = XmlConvert.ToInt32(reader.GetAttribute("Count"));
            reader.ReadStartElement("Fields");
            var fieldNames = new string[fieldCount];
            var fieldTypes = new TypeCode[fieldCount];
            var fieldIndexByName = new Dictionary<string, int>(fieldCount);
            for (int c = 0; c < fieldCount; c++)
            {
                fieldNames[c] = reader.GetAttribute("Name");
                fieldTypes[c] = (TypeCode)XmlConvert.ToInt32(reader.GetAttribute("Type"));
                fieldIndexByName.Add(fieldNames[c], c);
                reader.ReadStartElement("Field");
            }
            reader.ReadEndElement();
            // Read items
            int itemCount = XmlConvert.ToInt32(reader.GetAttribute("Count"));
            reader.ReadStartElement("Items");
            var items = new List<dynamic>(itemCount);
            var table = CreateTable(fieldNames);
            for (int i = 0; i < itemCount; i++)
            {
                var values = new object[fieldCount];
                if (reader.MoveToFirstAttribute())
                {
                    do
                    {
                        var fieldName = reader.Name;
                        var fieldIndex = fieldIndexByName[fieldName];
                        values[fieldIndex] = Convert.ChangeType(reader.Value, fieldTypes[fieldIndex], CultureInfo.InvariantCulture);
                    }
                    while (reader.MoveToNextAttribute());
                }
                reader.ReadStartElement("Item");
                var item = CreateRow(table, values);
                items.Add(item);
            }
            reader.ReadEndElement(); // Items
            reader.ReadEndElement(); // Container
            Items = items;
        }
    }
}

Some things would be easier if we changed the source code of Dapper, but I assume that you do not want to do this.

And here is a usage example:

static void Test(IEnumerable<dynamic> source)
{
    var stream = new MemoryStream();

    var sourceSet = new DapperResultSet(source);
    var serializer = new XmlSerializer(typeof(DapperResultSet));
    serializer.Serialize(stream, sourceSet);

    stream.Position = 0;
    var reader = new StreamReader(stream);
    var xml = reader.ReadToEnd();

    stream.Position = 0;
    var deserializer = new XmlSerializer(typeof(DapperResultSet));
    var target = ((DapperResultSet)deserializer.Deserialize(stream)).Items;
}