2

I have a Web API solution I'm using to work with various databases. Everything was working until I accessed a table with a GUID number in it. I finally narrowed down the problem to the Serialize/Deserialize methods. When the DataTable is serialized to JSON and then subsequently deserialized back to a DataTable, the data type of the column containing the GUID becomes System.String instead of System.Guid like it was originally.

Take this simple example:

System.Data.DataTable myData = new DataTable();
myData.Columns.Add("ID", typeof(System.Guid));
myData.Columns.Add("Name", typeof(System.String));
myData.Rows.Add(System.Guid.NewGuid(), "Name1");

// Reports: System.Guid
System.Diagnostics.Debug.Write(myData.Columns["ID"].DataType.FullName);

string mySerializedString = Newtonsoft.Json.JsonConvert.SerializeObject(myData);
myData = Newtonsoft.Json.JsonConvert.DeserializeObject<System.Data.DataTable>(mySerializedString);

// Reports: System.String
System.Diagnostics.Debug.Write(myData.Columns["ID"].DataType.FullName);

This is a generic API so I have no idea if the table(s) being passed around have columns containing GUIDs or any other data types that may cause issues with JSON.

Hopefully it's just because I'm missing something stupid and it's just a simple fix?

**** Update ****

Thanks for the tips all. In the meantime I have run into yet another problem that may just be the end of it all.

When you initially load a table from the database, every rowstate = unchanged. However going through the Serialize process, the result is every rowstate becomes "Added".

So take for example I query 1 record, modify that record and send it back. The DataTable has now flagged that record as "Added" which then breaks the primary key constraint and it blows up.

This sucks!

I'm basically looking at some sort of huge kludge job on both ends where I would have to pass a Dictionary of all the properties that I discover get hosed via the JSON process and then Rebuild it all on the other end.

All while trying to be generic and not knowing what kind of data I'm supposed to be dealing with.

&*(^% it!

da_jokker
  • 954
  • 2
  • 12
  • 16
  • You can use [this answer](https://stackoverflow.com/a/60463490/3189412) to pervent lost column type. – Sadegh Feb 29 '20 at 09:11

3 Answers3

4

There's no way in JSON to indicate that something is a GUID.
From json.org:

A value can be a string in double quotes, or a number, or true or false or null, or an object or an array. These structures can be nested.

Because you are not deserializing back into something with a defined type, there's no way for the deserializer to know that it should attempt to convert the "GUID as a string" back into an actual GUID.

If you were deserializing back to an object that had a property with a type of GUID, it would attempt to parse the string into a GUID for that property, but the structure of the datatable is not part of the transmitted JSON.

Craig H
  • 2,001
  • 1
  • 14
  • 18
1

As @Craig H pointed out, JSON has a very limited type system-- it can differentiate numbers, booleans, strings, objects and arrays, and the null value. If you need anything more specific than that, you have to resort to embedding type information as metadata in the JSON itself.

Json.Net has a TypeNameHandling setting which can be used to make it write type information into the JSON. Unfortunately, the DataTableConverter that ships with Json.Net does not seem to honor this setting. To work around this, you can use a custom JsonConverter for DataTables such as the one in How to include column metadata in JSON for an empty DataTable

Use it like this:

JsonSerializerSettings settings = new JsonSerializerSettings();
settings.Converters.Add(new CustomDataTableConverter());

string mySerializedString = JsonConvert.SerializeObject(myData, settings);
myData = JsonConvert.DeserializeObject<DataTable>(mySerializedString, settings);

Here is a demo with your example code: https://dotnetfiddle.net/wXNy9o

Community
  • 1
  • 1
Brian Rogers
  • 125,747
  • 31
  • 299
  • 300
1

Thanks everyone for the tips. In dealing with JSON and .NET Datatables, I have had to overcome numerous different challenges.

  1. Tables that return 0 rows, don't serialize so you end up with a Datatable with 0 columns.
  2. JSON loses the DataType of the Columns
  3. JSON loses the RowState of the records.
  4. JSON loses the Original\Proposed values of a Modified Row so it won't save
  5. JSON converts the DataType Byte[] to a Base64 string, but doesn't convert it back.

As a result, I have found a solution that seems to overcome all of these in one shot. To Summarize the solution..

  • Use the Built in XML feature of the DataTable to restore the Schema
  • Save off the Original RowState in order to restore it back
  • Save off the Original Values for Modified records to restore it back.

Since we all like code...

The Class that is going to be serialized (there are more properties but these are the focus)

    public System.Data.DataTable Data { get; set; }

    [Newtonsoft.Json.JsonProperty]
    private string _DataTableSchema { get; set; }

    [Newtonsoft.Json.JsonProperty]
    private List<DeconstructedDataRow> _DeconstructedDataRows { get; set; }

    private class DeconstructedDataRow
    {
        public System.Data.DataRowState RowState { get; set; }
        public List<object> RowOriginalValues { get; set; }

        public DeconstructedDataRow()
        {
            RowState = System.Data.DataRowState.Unchanged;
            RowOriginalValues = new List<object>();
        }
    }

From the one side, BEFORE Serializing the table you call .Data_Deconstruct....

    public void Data_Deconstruct()
    {  
        //Couple of Pre-Checks
        _DataTableSchema = String.Empty;
        _DeconstructedDataRows.Clear(); 
        if (this.Data == null || this.Data.Columns.Count == 0)
        {
            return;
        }

        //We need to mess around a bit so instead of tampering with their original table, we work with a copy
        System.Data.DataTable myWorkingData = this.Data.Copy();


        //In order to serialize to XML, the table MUST have a name
        if (String.IsNullOrWhiteSpace(myWorkingData.TableName) == true)
        {
            myWorkingData.TableName = System.Guid.NewGuid().ToString();
        }

        //JSON doesn't carry over the Schema of the Table so we loose the specific 
        //DataTypes of each colum. So we use the Built-in method of the DataTable object to create 
        //a XML\String version of its schema 
        System.IO.StringWriter myStringWriter = new System.IO.StringWriter();
        myWorkingData.WriteXmlSchema(myStringWriter, true);
        _DataTableSchema = myStringWriter.ToString();


        //JSON and the process of Serializing and Deserializing doesn't carry over 
        //the proper RowState for each Record. In addition, for those records that
        //have been Modified, we lose the Original values, and for those records that 
        //have been Deleted, we can't serialize them. So this is a KLUDGE king that
        //seems to sort all this out.
        for (Int32 intRowIndex = 0; intRowIndex < myWorkingData.Rows.Count; intRowIndex++)
        {
            DeconstructedDataRow myDeconstructedDataRow = new DeconstructedDataRow();

            //So start by saving off the current RowState
            myDeconstructedDataRow.RowState = myWorkingData.Rows[intRowIndex].RowState;

            //If the RowState is DELETED, then the ORGINAL Record will not serialize, 
            //so we need to reject the ORIGINAL state for now and we will restore it later
            if (myDeconstructedDataRow.RowState == System.Data.DataRowState.Deleted)
            {
                this.Data.Rows[intRowIndex].RejectChanges();
            }

            //If the RowState is MODIFIED, then we have to restore the ORIGINAL values
            //when we restore this record. Without the Original Values, the record won't 
            //update and even if we force it, it will error out because of 'concurrency' errors.
            if (myDeconstructedDataRow.RowState == System.Data.DataRowState.Modified)
            {
                myWorkingData.Rows[intRowIndex].RejectChanges();      
                myDeconstructedDataRow.RowOriginalValues.AddRange(myWorkingData.Rows[intRowIndex].ItemArray);
            }

            //And don't forget to add it to our list
            this._DeconstructedDataRows.Add(myDeconstructedDataRow);
        }

        //Clean up our Clone.
        myWorkingData.Dispose();
        myWorkingData = null;
    }

From the other side, AFTER Deserialized you call .Data_Reconstruct....

    public void Data_Reconstruct()
    {
        //Couple of Pre-Checks
        if (this.Data == null || String.IsNullOrWhiteSpace(_DataTableSchema) == true)
        {
            return;
        }


        //So first we build a new DataTable with the correct Schema
        System.Data.DataTable myWorkingData = new System.Data.DataTable();
        System.IO.StringReader myStringReader = new System.IO.StringReader(_DataTableSchema);
        myWorkingData.ReadXmlSchema(myStringReader);


        //Now we transfer over all the data that was serialize 'as-is' from the existing to the new Table
        foreach (System.Data.DataRow myRow in this.Data.Rows)
        {
            //myWorkingData.ImportRow(myRow);  //Should have been this easy BUT ...

            // JSON converts some data types to a different format, but then when it deserializes
            // it doesn't convert them back (not sure why). So we have to account for that
            // and at a performance cost
            System.Data.DataRow myNewRecord = myWorkingData.NewRow();  //Create a New row from the table with the Proper Schema
            foreach (System.Data.DataColumn myField in myWorkingData.Columns)
            {
                if (myField.DataType.Equals(typeof(System.Byte[])))
                {
                    myNewRecord[myField.ColumnName] = Convert.FromBase64String(Convert.ToString(myRow[myField.ColumnName]));
                }
                else
                {
                    myNewRecord[myField.ColumnName] = myRow[myField.ColumnName];
                }
            }
            myWorkingData.Rows.Add(myNewRecord);

        }

        //We have to accept the changes because all rows are currently marked as "Added" (via JSON as well)
        myWorkingData.AcceptChanges();


        //Now restore their Row States 
        for (Int32 intRowIndex = 0; intRowIndex < myWorkingData.Rows.Count; intRowIndex++)
        {
            switch (_DeconstructedDataRows[intRowIndex].RowState)
            {
                case System.Data.DataRowState.Added:
                    myWorkingData.Rows[intRowIndex].SetAdded();
                    break;

                case System.Data.DataRowState.Deleted:
                    myWorkingData.Rows[intRowIndex].Delete();
                    break;

                case System.Data.DataRowState.Modified:
                    //For Modified, we have to do some kludge stuff or else the UPDATE will not trigger
                    //We start by saving off the Values that are in the Record NOW (aka the New values)
                    object[] objNewValues = myWorkingData.Rows[intRowIndex].ItemArray;

                    //Now we replace those values with the ORIGINAL values we saved off before transporting
                    for (Int32 intFieldIndex = 0; intFieldIndex < this._DeconstructedDataRows[intRowIndex].RowOriginalValues.Count; intFieldIndex++)
                    {
                        if (this._DeconstructedDataRows[intRowIndex].RowOriginalValues[intFieldIndex] == null)
                        {
                            this._DeconstructedDataRows[intRowIndex].RowOriginalValues[intFieldIndex] = DBNull.Value;
                        }
                    }
                    myWorkingData.Rows[intRowIndex].ItemArray = this._DeconstructedDataRows[intRowIndex].RowOriginalValues.ToArray();
                    myWorkingData.Rows[intRowIndex].AcceptChanges();

                    //and Last we replace those Original values with the New Values, which not only
                    //correctly sets the Original\Proposed values, but also changes the RowState to MODIFIED
                    myWorkingData.Rows[intRowIndex].ItemArray = objNewValues; 
                    break;

                default:
                    //These would be the Unchanged
                    break;
            }
        }

        //And finally, we replace the existing Table with our fixed one.
        this.Data = myWorkingData;

    }

To test this I created 2 records in my database

Rec1_Delete Rec2_Modify

Then I loaded the table using the methods above, flagged the Delete Record as Delete, Modified a field(s) in the Modify records, and Added a brand new record.

I then sent the datatable back to the API and in the database, the Deleted record got deleted, the modified record got modified, and the new record was added.

As a side note, I expose the actual DataTable, because if you happen to call this from something other than .NET (like Javascript in an HTML file). you can still use the DataTable directly to read and display data. The whole DeConstruct and ReConstruct was for .NET clients while still allowing for limited access for those others.

da_jokker
  • 954
  • 2
  • 12
  • 16