Getting content of DynamicParameters in Dapper
We are migrating databases from Oracle to MySQL. Oracle and MySQL use a different symbol for the bind variables @
and :
. We are using Dapper to query the database. We pass DynamicParameters to Dapper and the bind variables work.
What I want to do is go through DynamicParameters and see the name of the value and change the symbol in the front panel and then replace it in the SQL string as well. I know how I will do it.
The problem is that you cannot enumerate DynamicParameters to get key and value.
My ideas are to try and get hold of a private field <string, DynamicParameters.ParamInfo> parameters
at runtime using reflection. I cannot figure out how to make it work as it DynamicParameters.ParamInfo
is a private class.
Any ideas on what I can do?
source to share
The task consists of two parts:
What I want is to go through DynamicParameters and look at the value name and change the symbol on the front panel
and the second:
then replace it also in the SQL line
The first part interested me as a smart enough Dapper. This proves that it is reasonable enough to handle the arguments passed to it in DynamicParameters
. You have the following:
// Source of the DynamicParameters
var args = new Dictionary<string,string>();
args.Add("@emailPromotion", "1");
// additional args goes here
// An example of how you build a DynamicParameters
var dbArgs = new DynamicParameters();
foreach (var pair in args) dbArgs.Add(pair.Key, pair.Value);
// The query - using SQL Server (AdventureWorks)
var sql = "select * from Person.Contact WHERE EmailPromotion = @EmailPromotion";
// The Dapper call (this works as expected)
var items = the_connection.Query<dynamic>(sql, dbArgs);
Now, let's say that you migrated from Oracle using colon
, and you passed DynamicParameters from that source:
var oracle_args = new Dictionary<string,string>();
oracle_args.Add(":emailPromotion", "1");
If you use this one oracle_args
with the rest of the above code, it will still work. You expected it to be different, as SQL Server will not be able to understand the colon. The only thing that will throw an error is that the request ( var sql
) itself has an invalid character - colon
.
How does this relate to your question? This means you don't have to worry about the "first part" and let Dapper do its job. You just need to take care of the "second part" and customize your (sql) queries. And if you have complete control over the requests, it shouldn't give you any trouble. A simple string replacement would do the trick.
source to share