Is there a way retrieve each row as IDictionary using Dapper?

Question

I have a table structure like this:

Table tbUser

--ID (int)

--Name (varchar)

--Birthday (datetime)

--Rating (double)

When

"SELECT * FROM tbUser"

I want each row in query result looks like (turns every column into string format):

row["ID"] = "1";
row["Name"] "John"
row["Birthday"] = "1980-01-01"
row["rating"] = "3.4"

The reason I need strings as result is they are easy to manipulate/format/display and some times safer (If another engineer changed column type in DB --e.g. "rating" column changed from double to int, the program is less likely throw exception as it only cares about strings)

I know there's a way to convert a DapperRow to IDictionary<string, object>, which is very close to IDictionary<string, string>;

var result = conn.Query("SELECT * FROM tbUser");
foreach (IDictionary<string, object> row in result) {
  // I have to write my own object->string conversion in every loop
}

Any suggestion is highly appreciated!

Answer 1

No, Dapper doesn't do it for you (nor should it!). First of all - would the conversion be done in the database, or in code? And how are things formatted? (How do we format 2,000.20? Not all cultures use . as a decimal place, nor , as thousands separators. Gets even worse for dates).

Nevertheless, you can write something like this:

var data = Connection.Query("SELECT TOP 100 * FROM People") 
                      as IEnumerable<IDictionary<string, object>>;

var outData = data.Select(r => r.ToDictionary(d => d.Key, d => d.Value?.ToString()));

Or you can write your own extension:

public static class DapperExtensions
{
    public static IEnumerable<IDictionary<string, string>> QueryDictionary(this IDbConnection connection, string query)
    {
        var data = Dapper.SqlMapper.Query(connection, query) as IEnumerable<IDictionary<string, object>>;
        return data.Select(r => r.ToDictionary(d => d.Key, d => d.Value?.ToString()));
    }
}

And use it as so:

var data = Connection.QueryDictionary("SELECT TOP 100 * FROM People");

Answer 2

This seems to have already been answered.

You would need to create a model / class, then follow this code snippet to achieve what you are wanting;

Dictionary<string, SP> dictionary = connection.Query<SP>(@"
SELECT 
     routine_name AS RoutineName,
     created AS Created,
     modified AS Modified
FROM PROCEDURES
").ToDictionary(m => m.RoutineName, m => m);

See this thread for the full answer - How to put objects into a dictionary using Dapper in C#?