Serializing a DataSet/DataTable with properties in json.net

Question

I'm looking a solution to serialize dataset to json, but I need to get the rowstate in json.

Does json.net serialize / deserialize properties of the dataset / datatable, like rowstate? I can only find examples with row value.

Thanks.

Answer 1

Huge thanks to @Carson and @Alex. I needed to tweak @Carson's code slightly to support both generic and typed datasets. I couldn't get ReadXmlSchema to work for typed datasets with foreign keys so I removed the code that pertained to the schema and its serialization. Fortunately in my case I didn't actually need to serialize the schema.

In case it helps anyone, here's my code.

public class CustomJsonConverter_DataSet<T> : JsonConverter<T>
{
   private class DataSetSerializer
   {
      //public string SchemaString { get; set; }
      public string DataString { get; set; }

      private static string GetSchema(DataSet ds)
      {
         using (var sw = new StringWriter())
         {
            ds.WriteXmlSchema(sw);
            return sw.ToString();
         }
      }
      private static string GetData(DataSet ds)
      {
         using (var sw = new StringWriter())
         {
            ds.WriteXml(sw, XmlWriteMode.DiffGram);
            return sw.ToString();
         }
      }
      private DataSet GetDataSet(Type dataSetType)
      {
         DataSet ds;
         if (dataSetType == typeof(DataSet))
            ds = new DataSet();
         else
            ds = (DataSet)Activator.CreateInstance(dataSetType);

         //using (var sr1 = new StringReader(SchemaString))
         //{
         //   ds.ReadXmlSchema(sr1);
         //}
         using (var sr2 = new StringReader(DataString))
         {
            ds.ReadXml(sr2, XmlReadMode.DiffGram);
         }
         return ds;
      }
      public static string Serialize(DataSet ds)
      {
         var serializer = new DataSetSerializer() { DataString = GetData(ds) }; //{ SchemaString = GetSchema(ds), DataString = GetData(ds) };
         return JsonConvert.SerializeObject(serializer);
      }
      public static DataSet DeSerialize(string s, Type dataSetType)
      {
         var serializer = JsonConvert.DeserializeObject<DataSetSerializer>(s);
         return serializer.GetDataSet(dataSetType);
      }
   }

   public override void WriteJson(JsonWriter writer, T? value, JsonSerializer serializer)
   {
      if (value == null)
         throw new Exception("Passed in DataSet is null");
      else if (!typeof(DataSet).IsAssignableFrom(value.GetType()))
         throw new Exception("Value is not a DataSet");

      writer.WriteValue(DataSetSerializer.Serialize(ds: (DataSet)(Object)(value)));
   }

   public override T ReadJson(JsonReader reader, Type objectType, T? existingValue, bool hasExistingValue, JsonSerializer serializer)
   {
      return (T)(Object)DataSetSerializer.DeSerialize((string)reader.Value, objectType);
   }
}

Answer 2

I needed a solution that maintained RowState for DataSets and DataTables for a legacy app migrating from SOAP calls to WebApi calls but using Newtonsoft rather than System.Text.Json. Thank you Alex for the last answer which was just what I needed.

Here is a Newtonsoft version of the same thing:

using System;
using System.Data;
using System.IO;
using Newtonsoft.Json;
using Xunit;

namespace DataSetsAndTablesTester
{
    public class CustomJsonConverter_DataSet : JsonConverter<DataSet>
    {
        private class DataSetSerializer
        {
            public string SchemaString { get; set; }
            public string DataString { get; set; }

            private static string GetSchema(DataSet ds)
            {
                using (var sw = new StringWriter())
                {
                    ds.WriteXmlSchema(sw);
                    return sw.ToString();
                }
            }
            private static string GetData(DataSet ds)
            {
                using (var sw = new StringWriter())
                {
                    ds.WriteXml(sw, XmlWriteMode.DiffGram);
                    return sw.ToString();
                }
            }
            private DataSet GetDataSet()
            {
                var ds = new DataSet();
                using (var sr1 = new StringReader(SchemaString))
                {
                    ds.ReadXmlSchema(sr1);
                }
                using (var sr2 = new StringReader(DataString))
                {
                    ds.ReadXml(sr2, XmlReadMode.DiffGram);
                }
                return ds;
            }
            public static string Serialize(DataSet ds)
            {
                var serializer = new DataSetSerializer() { SchemaString = GetSchema(ds), DataString = GetData(ds) };
                return JsonConvert.SerializeObject(serializer);
            }
            public static DataSet DeSerialize(string s)
            {
                var serializer = JsonConvert.DeserializeObject<DataSetSerializer>(s);
                return serializer.GetDataSet();
            }
        }

        public override void WriteJson(JsonWriter writer, DataSet value, JsonSerializer serializer)
        {
            if (value == null)
                throw new Exception("Passed in DataSet is null");

            writer.WriteValue(DataSetSerializer.Serialize(ds: value));
        }

        public override DataSet ReadJson(JsonReader reader, Type objectType, DataSet existingValue, bool hasExistingValue, JsonSerializer serializer)
        {
            return DataSetSerializer.DeSerialize((string)reader.Value);
        }
    }

    public class CustomJsonConverter_DataTable : JsonConverter<DataTable>
    {
        private class DataTableSerializer
        {
            public string SchemaString { get; set; }
            public string DataString { get; set; }

            private static string GetSchema(DataTable dt)
            {
                using (var sw = new StringWriter())
                {
                    dt.WriteXmlSchema(sw);
                    return sw.ToString();
                }
            }
            private static string GetData(DataTable dt)
            {
                using (var sw = new StringWriter())
                {
                    dt.WriteXml(sw, XmlWriteMode.DiffGram);
                    return sw.ToString();
                }
            }
            private DataTable GetDataTable()
            {
                var dt = new DataTable();
                using (var sr1 = new StringReader(SchemaString))
                {
                    dt.ReadXmlSchema(sr1);
                }
                using (var sr2 = new StringReader(DataString))
                {
                    dt.ReadXml(sr2);
                }
                return dt;
            }
            public static string Serialize(DataTable dt)
            {
                var serializer = new DataTableSerializer() { SchemaString = GetSchema(dt), DataString = GetData(dt) };
                return JsonConvert.SerializeObject(serializer);
            }
            public static DataTable DeSerialize(string s)
            {
                var serializer = JsonConvert.DeserializeObject<DataTableSerializer>(s);
                return serializer.GetDataTable();
            }
        }

        public override void WriteJson(JsonWriter writer, DataTable value, JsonSerializer serializer)
        {
            if (value == null)
                throw new Exception("Passed in DataTable is null");

            if (string.IsNullOrEmpty(value.TableName))
                throw new Exception("Passed in DataTable Name is null or empty");

            writer.WriteValue(DataTableSerializer.Serialize(dt: value));
        }

        public override DataTable ReadJson(JsonReader reader, Type objectType, DataTable existingValue, bool hasExistingValue, JsonSerializer serializer)
        {
            return DataTableSerializer.DeSerialize((string)reader.Value);
        }
    }

    public class JsonConverterTester
    {
        public void TestJsonDataSetConverter()
        {
            DataSet ds = CreateTestDataSet();

            String json = JsonConvert.SerializeObject(value: ds, converters: new CustomJsonConverter_DataSet());

            Console.WriteLine(json);

            DataSet ds2 = JsonConvert.DeserializeObject<DataSet>(value: json, converters: new CustomJsonConverter_DataSet());

            CheckTestDataSet(ds2);
        }

        public void TestJsonDataTableConverter()
        {
            DataTable dt = CreateTestDataTable();

            String json = JsonConvert.SerializeObject(value: dt, converters: new CustomJsonConverter_DataTable());

            Console.WriteLine(json);

            DataTable dt2 = JsonConvert.DeserializeObject<DataTable>(value: json, converters: new CustomJsonConverter_DataTable());

            CheckTestDataTable(dt2);
        }

        private DataSet CreateTestDataSet()
        {
            var ds = new DataSet();
            var dt = ds.Tables.Add();
            dt.Columns.Add("A");
            dt.Columns.Add("B");
            dt.Rows.Add("A1", "B1");
            var dr = dt.Rows.Add("A2", "B2");
            ds.AcceptChanges();
            dr["A"] = "AA2";
            return ds;
        }

        private void CheckTestDataSet(DataSet ds)
        {
            var dt = ds.Tables[0];
            Assert.True(dt.Rows[0].RowState == DataRowState.Unchanged && dt.Rows[1].RowState == DataRowState.Modified);
        }

        private DataTable CreateTestDataTable()
        {
            var dt = new DataTable();
            dt.TableName = "TestTable";
            dt.Columns.Add("A");
            dt.Columns.Add("B");
            dt.Rows.Add("A1", "B1");
            var dr = dt.Rows.Add("A2", "B2");
            dt.AcceptChanges();
            dr["A"] = "AA2";
            return dt;
        }

        private void CheckTestDataTable(DataTable dt)
        {
            Assert.True(dt.Rows[0].RowState == DataRowState.Unchanged && dt.Rows[1].RowState == DataRowState.Modified);
        }
    }
}

Answer 3

I wrote a custom converter for DataSet which keeps the row state. The DataSet class can write the schema as xml with WriteXmlSchema, and it can write the data including the rowstate with WriteXml(sw, XmlWriteMode.DiffGram). This produces two strings that are properties in the DataSetSerializer class, which then can be serialized and deserialized with JsonSerializer. Here is the code in a test class:

using Microsoft.VisualStudio.TestTools.UnitTesting;
using System;
using System.Data;
using System.IO;
using System.Text.Json;
using System.Text.Json.Serialization;

namespace TestProject3
{
    public class DataSetConverter : JsonConverter<DataSet>
    {
        private class DataSetSerializer
        {
            public string SchemaString { get; set; }
            public string DataString { get; set; }
            private static string GetSchema(DataSet ds)
            {
                using (var sw = new StringWriter())
                {
                    ds.WriteXmlSchema(sw);
                    return sw.ToString();
                }
            }
            private static string GetData(DataSet ds)
            {
                using (var sw = new StringWriter())
                {
                    ds.WriteXml(sw, XmlWriteMode.DiffGram);
                    return sw.ToString();
                }
            }
            private DataSet GetDataSet()
            {
                var ds = new DataSet();
                using (var sr1 = new StringReader(SchemaString))
                {
                    ds.ReadXmlSchema(sr1);
                }
                using (var sr2 = new StringReader(DataString))
                {
                    ds.ReadXml(sr2, XmlReadMode.DiffGram);
                }
                return ds;
            }
            public static string Serialize(DataSet ds)
            {
                var serializer = new DataSetSerializer() { SchemaString = GetSchema(ds), DataString = GetData(ds) };
                return JsonSerializer.Serialize<DataSetSerializer>(serializer);
            }
            public static DataSet DeSerialize(string s)
            {
                var serializer = JsonSerializer.Deserialize<DataSetSerializer>(s);
                return serializer.GetDataSet();
            }
        }
        public override DataSet Read(ref Utf8JsonReader reader, Type typeToConvert, JsonSerializerOptions options)
        {
            return DataSetSerializer.DeSerialize(reader.GetString());
        }

        public override void Write(Utf8JsonWriter writer, DataSet value, JsonSerializerOptions options)
        {
            writer.WriteStringValue(DataSetSerializer.Serialize(value));
        }
    }

    [TestClass]
    public class TestDataSet
    {
        private DataSet CreateTestDataSet()
        {
            var ds = new DataSet();
            var dt = ds.Tables.Add();
            dt.Columns.Add("A");
            dt.Columns.Add("B");
            dt.Rows.Add("A1", "B1");
            var dr = dt.Rows.Add("A2", "B2");
            ds.AcceptChanges();
            dr["A"] = "AA2";
            return ds;
        }

        private void CheckTestDataSet(DataSet ds)
        {
            var dt = ds.Tables[0];
            Assert.IsTrue(dt.Rows[0].RowState == DataRowState.Unchanged && dt.Rows[1].RowState == DataRowState.Modified);
        }

        [TestMethod]
        public void TestDataSetConverter()
        {
            var ds = CreateTestDataSet();
            var serializeOptions = new JsonSerializerOptions { Converters = { new DataSetConverter() } };
            var jsonstring = JsonSerializer.Serialize(ds, serializeOptions);
            ds = JsonSerializer.Deserialize<DataSet>(jsonstring, serializeOptions);
            CheckTestDataSet(ds);
        }
    }
}

Answer 4

No, the DataTableConverter that ships with Json.Net does not serialize the RowState. If you really need this value in the JSON, it should be possible to create a custom JsonConverter to output it. However, it will not be possible to deserialize the RowState back to its original value due to the fact that it is a read-only property. (In fact, this value is calculated from a variety of internal state variables.)