Clash Royale CLAN TAG#URR8PPP

How to map to a Dictionary object from database results using Dapper Dot Net?

If I have a simple query such as:

string sql = "SELECT UniqueString, ID FROM Table";


and I want to map it to a dictionary object such as:

Dictionary<string, int> myDictionary = new Dictionary<string, int>();


How would I do this with Dapper?

I assume it is something like:

myDictionary = conn.Query<string, int>(sql, new ).ToDictionary();


But can't figure out the proper syntax.

5 Answers
5

There's various ways already shown; personally I'd just use the non-generic api:

var dict = conn.Query(sql, args).ToDictionary(
row => (string)row.UniqueString,
row => (int)row.Id);


Works also without an additional class:

var myDictionary = conn.Query<string, int, KeyValuePair<string,int>>(sql, (s,i) => new KeyValuePair<string, int>(s,i))
.ToDictionary(kv => kv.Key, kv => kv.Value);


NOTE: When using Dapper.NET 3.5 version, the Query method that takes the first, second and return types requires you specify more parameters, as the .NET 4.0 and .NET 4.5 versions take advantage of optional arguments.

In this case, the following code should work:

string splitOn = "TheNameOfTheValueColumn";
var myDictionary = conn.Query<string, int, KeyValuePair<string,int>>(sql, (s,i) => new KeyValuePair<string, int>(s,i), null, null, false, splitOn, null, null)
.ToDictionary(kv => kv.Key, kv => kv.Value);


Most of the arguments will revert to a default, but splitOn is required, as it will otherwise default to a value of 'id'.

splitOn

For a query that returns two columns, 'ID' and 'Description', splitOn should be set to 'Description'.

splitOn

I'm not sure if what you're trying to do is possible. If you define a class to map the query to this becomes far more trivial:

public class MyRow

public int Id get; set;
public string UniqueString get; set;



Then, you would just do this:

var sql = "SELECT UniqueString, ID FROM Table";
var myDictionary = conn.Query<MyRow>(sql).ToDictionary(row => row.UniqueString, row => row.Id);


Dapper also has an extension method for ExecuteReader. So, you could also do this:

ExecuteReader

var sql = "SELECT UniqueString, ID FROM Table";
var rows = new List<Dictionary<string, int>>();
using (var reader = cn.ExecuteReader(sql))
while (reader.Read())
var dict = new Dictionary<string, int>();
for (var i = 0; i < reader.FieldCount; i++)
dict[reader.GetName(i)] = reader.GetInt32(i);

rows.Add(dict);




This approach works without knowing the column names. Moreover, if you don't know the data types, you could change Dictionary<string,int> to Dictionary<string,object> and GetInt32(i) to GetValue(i).

Dictionary<string,int>

Dictionary<string,object>

GetInt32(i)

GetValue(i)

string strSql = "SELECT DISTINCT TableID AS [Key],TableName AS [Value] FROM dbo.TS_TStuctMaster";
Dictionary<string,string> dicts = sqlConnection.Query<KeyValuePair<string,string>>(strSql).ToDictionary(pair => pair.Key, pair => pair.Value);


You can use aliases and strong types.

Aliases are the key points, which match the attributes of KeyValuePair type Key and Value.

It works under strong typing and runs well.

I don't like dynamic type. It brings disaster in certain situations. Moreover, the boxing and unboxing brings performance loss.

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.