ineztia ineztia - 1 year ago 324
JSON Question

Dapper result to json (using fastjson)

===== UPDATED 8/20/2016 =====

latest version of fastjson can now handle
Dictionary<string, ?>
type correctly, my problem is solved now.


I'm using fastjson to serialize the query result from Dapper, the table in DB has data like this:

id | name | price
1 | x | 100
2 | y | 200

And when I

using Dapper;
using fastJSON;
// ....
JSON.Parameters.KVStyleStringDictionary = false;
// ....
result = JSON.toJSON(conn.Query("SELECT * FROM tableX"));

I want the result to be:


However the actual result outputs:


Lots of key-value pairs are generated which looks redundant.

Is there a way to get the correct result ?

Or should I switch to another JSON serializer ?

========== UPDATED ==========

makubex88's answer indicates that I can create a customized class mapping the table and use
to get the correct json, though it works for this scenario, it looks like I have to create hundreds of classes for every table in DB to get ideal json result, which is indeed tiring work for me. (Thanks any way:P)

Any alternative solutions would be highly appreciated!

Answer Source

I found a solution to handle it (however it may lose some efficiency), to achieve this, I write my own QueryEx method, each row in query result is an IDictionary object:

public IEnumerable<IDictionary> QueryEx(IDbConnection conn, string sql, object argSet = null) {
    var result = conn.Query(sql, argSet) as IEnumerable<IDictionary<string, object>>;
    return result.Select(r => r.Distinct().ToDictionary(d => d.Key, d => d.Value));


result = JSON.toJSON(conn.QueryEx("SELECT * FROM tableX"));
// output: [{"id":1,"name":"x","price":100},{"id":2,"name":"y","price":200},...]

reason: fastJSON can only parse IDictionary interface correctly, any generic versions of IDictionary will be parsed as Key-Value pair lists

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download