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?

CodePudding user response：

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.

CodePudding user response：

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