Json.NET Serialization - Exclude DataTable column(s)

user_817659 0
Viewed 73

If I have a DataTable like this:

DataTable dt = new DataTable();

dt.Columns.Add("AccountNumber", typeof(string));
dt.Columns.Add("InstrumentType", typeof(string));
dt.Columns.Add("Symbol", typeof(string));
dt.Columns.Add("Quantity", typeof(int));
dt.Columns.Add("Exp", typeof(string));
dt.Columns.Add("Days", typeof(int));
dt.Columns.Add("Strike", typeof(double));
dt.Columns.Add("COP", typeof(string));
dt.Columns.Add("Action", typeof(string));

When serializing this DataTable using Json.NET, I would like to exclude certain columns, for example:

"AccountNumber", "COP", "Days", "Exp"

Is there a way to do this through a property? I believe for a class, you can use:

class Foo
{
    [JsonIgnore]
    public string COP{ get; set; }
}

Or do I have to write a custom converter?

public class MyDataTableConverter : JsonConverter
{
}

I can't find an example of how to do this?

custom-attributes json.net serialization c#
2 Answers

You can subclass Newtonsoft's DataTableConverter to add filtering based on column name.

First, define the following converter:

public class FilteringDataTableConverter : FilteringDataTableConverterBase
{
    HashSet<string> ColumnsToExclude { get; }
    public FilteringDataTableConverter(IEnumerable<string> columnsToExclude) => this.ColumnsToExclude = columnsToExclude.ToHashSet();
    protected override bool ShouldWriteColumn(DataColumn column, JsonSerializer serializer) => !ColumnsToExclude.Contains(column.ColumnName);
}

public abstract class FilteringDataTableConverterBase : Newtonsoft.Json.Converters.DataTableConverter
{
    // Adapted from https://github.com/JamesNK/Newtonsoft.Json/blob/master/Src/Newtonsoft.Json/Converters/DataTableConverter.cs
    // By https://github.com/JamesNK/
    protected abstract bool ShouldWriteColumn(DataColumn column, JsonSerializer serializer);

    public override void WriteJson(JsonWriter writer, object? value, JsonSerializer serializer)
    {
        if (value == null)
        {
            writer.WriteNull();
            return;
        }

        var table = (DataTable)value;
        var resolver = serializer.ContractResolver as DefaultContractResolver;

        writer.WriteStartArray();

        foreach (var row in table.AsEnumerable())
        {
            writer.WriteStartObject();
            foreach (var column in row.Table.Columns.Cast<DataColumn>().Where(c => ShouldWriteColumn(c, serializer)))
            {
                object columnValue = row[column];
                if (serializer.NullValueHandling == NullValueHandling.Ignore && (columnValue == null || columnValue == DBNull.Value))
                    continue;
                writer.WritePropertyName(resolver?.GetResolvedPropertyName(column.ColumnName) ?? column.ColumnName);
                serializer.Serialize(writer, columnValue);
            }
            writer.WriteEndObject();
        }

        writer.WriteEndArray();
    }   
}

And now you will be able to do the following:

var exclude = new [] { "AccountNumber", "COP", "Days", "Exp" };

var settings = new JsonSerializerSettings
{
    Converters = { new FilteringDataTableConverter(exclude) },
};

var json = JsonConvert.SerializeObject(dt, Formatting.Indented, settings);

Notes:

  • You will need to write a custom converter to prevent serialization of specific columns. As you can see from the source code for DataTableConverter.WriteJson() there is currently no filtering logic for data columns.

  • If you would prefer to filter based on e.g. some data column extended property you may subclass the base class FilteringDataTableConverterBase and modify ShouldWriteColumn() as required.

Demo fiddle here.

user_37441820

Solution 1: Convert to Class then Serialize

Since you have created your own class and applied the [JsonIgnore] attribute to the properties, in order to make the property being ignored in serialization, instead of serializing from DataTable object, you should serialize from Foo (your class).

class Foo
{
    public string AccountNumber { get; set; }
    public string InstrumentType { get; set; }
    public string Symbol { get; set; }
    [JsonIgnore]
    public string COP { get; set; }
    // Following properties
}

The following code is an extension method for converting the data from DataTable to List<T>.

public static class DataTableExtensions
{
    public static List<T> ToList<T>(this DataTable dt)
    {
        if (dt == null)
            return null;
        
        if (dt.Rows.Count == 0)
            return default;
                
        List<T> list = new List<T>();
        foreach (DataRow row in dt.Rows)
        {
            T item = GetItem<T>(row);
            list.Add(item);
        }
        
        return list;
    }
    
    private static T GetItem<T>(DataRow row)
    {
        Type temp = typeof(T);
        T obj = (T)Activator.CreateInstance(temp);
        PropertyInfo[] propertyInfos = temp.GetProperties();
        
        foreach (DataColumn column in row.Table.Columns)
        {           
            var prop = propertyInfos.FirstOrDefault(x => x.Name == column.ColumnName);          
            if (prop == null)
                continue;
            
            var colValue = row[column.ColumnName];
            Type valueType = Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType;
            object safeValue = colValue != DBNull.Value
                ? Convert.ChangeType(colValue, valueType)
                : default;
            
            prop.SetValue(obj, safeValue, null);
        }
        
        return obj;
    }
}

Caller function

List<Foo> list = dt
    .ToList<Foo>();

Solution 2: Remove column from DataTable

This approach is much easier by controlling the column(s) which should/shouldn't be included in serialization from DataTable instead of implementing the extension methods or JsonConverter methods.

dt.Columns.Remove("COP");

Demo @ .NET Fiddle

user_80176900
Related