Question about @value substitution

[Korporal]

Can someone point me to the code that does the substitution of @parameter values? It seems straightforward when we have something in a select SQL string like this:

            WHERE lc.course_id=@CourseId

The @CourseId is replaced with the respective value passed in via our anonymous object - all good.

But what about more complex SQL where it itself is using @parameter syntax ? for example this is the start of a quite involved query that is ultimately a SELECT:

            DECLARE @LearnerID VARCHAR(20) 

            SET @LearnerID = '{0}' 

            DECLARE @EndDate DATETIME 

            SET @EndDate = Cast((SELECT TOP 1 loa_finish_date AS LOAEndDate 
                                 FROM   learner_loa 
                                 WHERE  learner_id = '{1}' 
                                        AND approval_status = 1 
                                 ORDER  BY loa_approval_date DESC) AS DATETIME) 

How does dapper analyze this to separate user defined replacement values from other SQL declarations and assignments that also use @name syntax?

[mgravell]

This is a simple one. Dapper doesn't substitute @parameter values. It leaves them as parameters, to prevent SQL injection, etc. Parameters are good.

Dapper does allow literal injection of integers only in the example of {=foo}. It also does some tricks in some cases like in @foos (although in that case it just adds more parameters)

[Korporal]

@mgravell - Hello Marc.

I don't quite understand and I admit rather fragmented knowledge of these technologies up front. We have a lot of code (technically legacy code, poor design practices, huge bugfix rate, week standards etc).

I'm actively doing what I can to improve this code and refactor sensibly each time we do something to an older app.

There are countless examples of stuff like this (a very simplified example):

SELECT TeacherID from Courses
WHERE Course = @CourseID

These are passed into Dapper (Query<T>) along with an anonymous object with a property CourseID.

So something at some point must be replacing that @CourseID with the numeric value pulled from the property inside the anonymous object, so that SQL Server "sees" this:

SELECT TeacherID from Courses
WHERE Course = 10224

It seems from what you wrote that Dapper isn't doing that, so may I ask where is this being done?

Thx

[mgravell]

Dapper just tells the ADO.NET layer about the parameters and their intended values.

What happens next depends on what backend database you're talking to, i.e. what the actual connection is. In most cases, the RDBMS database engine supports parameters as a concept, and the ADO.NET implementation for your RDBMS just passed them down the server. This is efficient, safe, and good. It also means the server can use the same query plan with different final parameter values - good for reducing unnecessary repetitive work.

In some cases with minimal database engines, query parameters aren't a concept. In that case, the ADO.NET implementation for that specific database needs to take care of how to safely embed the values.

The good thing about this is that from the caller's perspective, you can just use parameters and the right things happen.

[enricosada]

@Korporal more info about ado.net parameters in https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/configuring-parameters-and-parameter-data-types

[Korporal]

Thanks gents, much appreciated.

[Korporal]

@mgravell @enricosada - Is there any support in Dapper for me to supply the args object as a dictionary of name/values? Our application code simply defines anonymous types when passing this but I need to do this programmatically, I guess there may be ways to dynamically define anonymous types from a dictionary, not ever looked at this though.

Our application code is calling Query<T>(...) incidentally.

[enricosada]

https://riptutorial.com/dapper/example/333/query-with-dynamic-parameters

like

var query = "Select * from Cats where Color = @Color and Age > @Age";
var dynamicParameters = new DynamicParameters();
dynamicParameters.Add("Color", color);
dynamicParameters.Add("Age", age);

var results = connection.Query(query, dynamicParameters);

[Korporal]

DynamicParameters

@enricosada - This looks good, by the way this refers to Query(...) but what about Query<T>(...) ? I cant see this anywhere (the app is using Dapper 1.50.2).

Thanks!

[enricosada]

The code without <...> is just because in c# that is optional if the type is inferred. But is the same generic method Query<>

[Korporal]

Its OK I answered my own question, yes you're right. Also it takes the param object and examines it, that's how it sees we used DynamicParameters() - all good.

Much appreciated @enricosada

[enricosada]

Np, I am not in dapper team but seems this issue is answered afaik @Korporal so maybe can be closed

[Korporal]

@mgravell @enricosada

Hmm - having examined this I still have a problem. We cannot generate the DynamicParametersobject because we do not know the names of the @value parameters without "looking at" the query text!

As you know if were passing an anonymous object in, we'd define property names that match the names used within the query string, this is easy for a human developer.

But this cannot be done programmatically (given just the query text and a list of arg objects).

It seems we MUST analyze the query string - to do what we need, this is not a criticism of Dapper by the way.

[enricosada]

You write the query, you choose the name for the parameters.

what's the difference between

var dynamicParameters = new DynamicParameters();
dynamicParameters.Add("Color", color);
dynamicParameters.Add("Age", age);

var results = connection.Query("Select * from Cats where Color = @Color and Age > @Age", dynamicParameters);

and

var results = connection.Query("Select * from Cats where Color = @Color and Age > @Age", new { Color = color, Age = age} );

is the same, you write the sql string, and you write the parameters

Also are just names, so you can choose whatever you want, doesnt need to match

var dynamicParameters = new DynamicParameters();
dynamicParameters.Add("Colore", color);
dynamicParameters.Add("Eta", age);

var results = connection.Query("Select * from Cats where Color = @Colore and Age > @Eta", dynamicParameters);

[Korporal]

@enricosada - Yes that's true but the name used for either the property (if using anonymous object) or the dynamic parameter key string must match the @<name> used in the query text.

So in your last example we can't write code to populate the dynamic parameters with the keys "Colore" or "Eta" without looking at the names used within the query text itself.

[mgravell]

@enricosada actually, Query(...) and Query<T>(...) in this case are completely different APIs with different semantics and target use-cases. What you're talking about is generic type parameter inference, but that isn't what is in play here.

[enricosada]

I modified the example I pasted, that one had a typed result so T was inferred, I was trying to simplify

Thx for pointing that @mgravell