CODEWITHSUNDEEP
c#sql-servert-sqlado.netdapper
Yanick Rochon
Yanick Rochon

Reputation: 53521

Procedurally create a dynamic object for Dapper

I've seen many posts about creating ExpandoObject objects and such, but it does not work in my case. I need to create an object like

var someObj = new {
    term1 = "someValue",
    term2 = "other",
    ...
};

Basically, we are using Dapper and we need to create a query dynamically, where the WHERE clause is fabricated from a given array of arguments. We are not generalizing queries! It's a single method receiving a variable number of arguments and we need to check OR each value on a single column.

Right now, the only viable solution is to revert and directly use System.Data.SqlClient.SqlConnection, or is there any way to make this work?

Update:

This is what most likely should work, but doesn't :

string inWhere = null;
dynamic inTerms = new ExpandoObject();
IDictionary<string, object> inTermsDict = inTerms;

if (!(string.IsNullOrEmpty(filter.Term) || string.IsNullOrWhiteSpace(filter.Term))) {
    inWhere = "(" + string.Join(" OR ", filter.Terms.Select((t, i) => "{0} LIKE @p" + i)) + ")";
    int termIndex = 0;
    foreach (string term in filter.Terms) {
        inTermsDict.Add("p" + (termIndex++), term);
    }
}

// ...

var rows = db.Query("SELECT * FROM {table} WHERE {baseCondition}" +
     (string.IsNullOrEmpty(inWhere) ? "" : string.Format(" AND " + inWhere, "columnName")),
     inTerms as object);

Upvotes: 4

Views: 3499

Answers (1)

Yanick Rochon
Yanick Rochon

Reputation: 53521

Just to answer my own question, as we found the proper solution earlier today.

Simply put, we found the IDynamicParameters And this class simply solves everything, acting as a Dictionary.

var inTerms = new Dapper.DynamicParameters();

inTerms.Add("@p" + (termIndex++), somveValue);

Everyone's happy!

Upvotes: 6

Related Questions