Dapper row to json

Question

Question

We have a Dapper Row as a result from a Dapper Query, which I want to store as a json string in our database. Unfortunately, I can't seem to get that to work. So, let's start with some background information.

Background info

We're doing a project where we extract Table Names from a table, to know which tables we have to address. We also want this to be as flexible as possible, so we decided to not use a particular POCO for our data.

We're using SQL Server 2014, so unfortunately we don't have the option 'FOR JSON' yet.

Code

Our code looks something like this, where GetData is our actual query:

var data = _queryHandler.Handle(new GetData(tableName.ToString(), 0), database);

The Handle technically just connects to the Database, calling

conn.QueryAsync(query, parameters)

GetData looks like this (simplified):

EXEC ('SELECT * FROM ' + @table + ')'

Reasoning

Because the table name differs each time, we don't want to force a POCO on the output. Sometimes it's a user, other times a role, so to say, so there's no prediction what output it returns.

Results

This works fine. We can extract the data in our variable and this looks like it's an IEnumerable, which should be fine. I gather we can just read them in a loop and extract the rows. So far, no problem.

The issue at hand

Next thing we want to do is to convert the data from said DapperRow to a json string, but I cannot seem to get the data to behave like a json string as JsonConvert.SerializeObject fails miserably. The DapperRow looks like this (again, simplified).

{{DapperRow, Id = '07501399-b385-4d8e-bacc-gad9d04c35f7', UserName = 'test8', ApplicationId = '4721fafb-12e6-4e3c-9298-etd82d18a0cb', IsApproved = 'True', IsLockedOut = 'False', CreateDate = '26-3-2019 07:52:55' }}

I've already looked into things like the SqlMapper.ITypeHandler, but I'm still not getting there. For instance with the TypeHandler, I get stuck on the destinationType as I don't want a particular type - well, except for a json object. But that's not an accepted type.

public class JsonObjectTypeHandler : SqlMapper.ITypeHandler
{
    public void SetValue(IDbDataParameter parameter, object value)
    {
        parameter.Value = (value == null)
            ? (object)DBNull.Value
            : JsonConvert.SerializeObject(value);
        parameter.DbType = DbType.String;
    }

    public object Parse(Type destinationType, object value)
    {
        return JsonConvert.DeserializeObject(value.ToString(), destinationType);
    }
}

The only other thing that crosses my mind is to extract each and every column and building a type object out of it, but as I said, we don't want to use a model/type for the data as we want to keep it flexible.

Could anyone point me in the right direction? I have the feeling I seem to overlook something simple.

Answer 1

If you are using the non-typed Query API, each returned row is also an IDictionary<string,object> (in addition to the dynamic API), which usually works fine with JsonConvert; for example, the following works OK for me:

var tables = (from row in await conn.QueryAsync("select top 5 * from sys.tables")
              select (IDictionary<string, object>)row).AsList();
var json = JsonConvert.SerializeObject(tables, Formatting.Indented);
System.Console.WriteLine(json);

outputting:

[
  {
    "name": "spt_fallback_db",
    "object_id": 117575457,
    "principal_id": null,
    "schema_id": 1,
    "parent_object_id": 0,
    "type": "U ",
    "type_desc": "USER_TABLE",
    "create_date": "2003-04-08T09:18:01.557",
    "modify_date": "2017-08-22T19:40:40.763",
    "is_ms_shipped": true,
    "is_published": false,
    "is_schema_published": false,
    "lob_data_space_id": 0,
    "filestream_data_space_id": null,
    "max_column_id_used": 8,
  ... etc