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!
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");
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#?
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With