Any simple ways to hide individual JSON values based on column value?

Question

I'm developing some test methods in C# that validate database records are updated correctly after UI changes are made to various records.

In doing so, I've developed a method that returns a SQL query string that I then serialize into a JSON string. With this string, I am writing it to a temporary file and then comparing it to a baseline expected file to then validate that the update is correct.

The issue is that the tables that I pull from are very extensive and have many columns, some of which will diff every time due to values like date created, last date updated, or anything similar.

Question:

Given a simple JSON string like so (my actual strings are far larger):

[
    {
        "id": 1,
        "name": "A green door",
        "price": 12.50,
        "home": "Penn"
        "date": "April 4, 2015"
    }
]

Is there any simple way to mask all "date" values with any specified value that would then cause the return to look like:

[
    {
        "id": 1,
        "name": "A green door",
        "price": 12.50,
        "home": "Penn"
        "date": "MASKEDVALUE"
    }
]

If any more information is needed for clarification please let me know.

Serialization code:

public string ReturnQueryAsJson(string statement)
{
    var dataTable = new DataTable();
    var configManager = new ConfigurationManager();
    var connectionString = configManager.AppSettings["DatabaseConnectionString"];
    string query = statement;

    SqlConnection connection = new SqlConnection(connectionString);
    SqlCommand command = new SqlCommand(query, connection);
    connection.Open();

    SqlDataAdapter dataAdapter = new SqlDataAdapter(command);

    dataAdapter.Fill(dataTable);
    connection.Close();
    dataAdapter.Dispose();

    JsonConvert.SerializeObject(dataTable);
    string JsonResult;            
    JsonResult = JsonConvert.SerializeObject(dataTable, Formatting.Indented);

    return JsonResult;
}

Why don't you avoid fetching those columns in the query itself? — Dipen Shah, Mar 15 '18 at 14:27
Sounds like this should be done while serializing to JSON, rather than after the fact in a string... If you can show that code, it would help. — Heretic Monkey, Mar 15 '18 at 14:27
@DipenShah I am wanting to avoid fetching them in the query itself because of the amount of columns I'd have to avoid in each query. I'm hoping to create a list of "masking" columns that I can then loop through every JSON object I get and it'll apply the masking. — JOberloh, Mar 15 '18 at 14:33
Perhaps you can capture only the wanted column in a class an serialize everything else in a [JsonExtensionData] — Drag and Drop, Mar 15 '18 at 14:38
@DragandDrop I couild do that, however getting as much returned from the query would be more desirable from a QA viewpoint. — JOberloh, Mar 15 '18 at 14:41
you could write your own Json date converter, which converts to the same constant value see this example: https://stackoverflow.com/a/8639415/1037841 — FrankM, Mar 15 '18 at 15:28
@JOberloh Why don't you mask the data in query itself, i.e. use masked value as a value for column. — Dipen Shah, Mar 15 '18 at 17:00

score 0 · Answer 1 · answered Mar 15 '18 at 15:46

You could use some kind of transformer which will do the transformation for you, for example:

   public DataTable DataTableDataTransformer(DataTable original, string[] columns, string maskedValue)
   {
        // get column indices
        int[] columnIndices = columns.Select(col => original.Columns.IndexOf(col))
                                      .Where(i => i >= 0)
                                      .ToArray();

        // clone original table structure
        var retVal = original.Clone();

        // change datatype of the columns in cloned table
        foreach (var index in columnIndices)
            retVal.Columns[index].DataType = typeof(string);

        //add data to new table
        foreach(DataRow row in original.Rows)
        {
            var values = row.ItemArray;
            foreach (var index in columnIndices)
                values[index] = maskedValue;

            retVal.Rows.Add(values);
        }

        return retVal;
    }

And then you can use new datatable to serialize data instead of using original one.

Any simple ways to hide individual JSON values based on column value?

1 Answers1