How to create arguments for a Dapper query dynamic

问题:

I have a dictionary of values Eg "Name": "Alex"

Is there a way to pass this to Dapper as arguments for a query?

Here is an example showing what I want to do.

IDictionary<string, string> args = GetArgsFromSomewhere();
string query = "select * from people where Name = @Name";
var stuff = connection.Query<ExtractionRecord>(query, args);

回答1:

Yes:

var dbArgs = new DynamicParameters();
foreach(var pair in args) dbArgs.Add(pair.Key, pair.Value);

Then pass dbArgs in place of args:

var stuff = connection.Query<ExtractionRecord>(query, dbArgs);

Alternatively, you can write your own class that implements IDynamicParameters.

Note that if you are starting from an object (the usual approach with dapper), you can also use this template with DynamicParameters as a starting point:

var dbArgs = new DynamicParameters(templateObject);

回答2:

I know this is an old question (like, 5 years old) but I was struggling with the same thing. The complete answer is in the comments to the other answer, but I thought I would offer a full example here.

string query = "SELECT * FROM MyTableName WHERE Foo = @Foo AND Bar = @Bar";

Dictionary<string, object> dictionary = new Dictionary<string, object>();
dictionary.Add("@Foo", "foo");
dictionary.Add("@Bar", "bar");

var results = connection.Query<MyTableName>(query, new DynamicParameters(dictionary));

Or, to be fully dynamic, you can create a method like this, which will take any model, any query, and any set of query parameters:

    public static IEnumerable<T> Get<T>(string query, Dictionary<string, object> dictionary)
    {
        IEnumerable<T> entities = connection.Query<T>(query, new DynamicParameters(dictionary));
        return entities;
    }

And then to call this method:

var results = Get<MyTable>(query, dictionary)

回答3:

One can also use an ExpandoObject as the parameters of a query, instead of the Dapper-specific class DynamicParameters:

ExpandoObject param = new ExpandoObject();

IDictionary<string, object> paramAsDict = param as IDictionary<string, object>;
paramAsDict.Add("foo", 42);
paramAsDict.Add("bar", "test");

MyRecord stuff = connection.Query<MyRecord>(query, param);