Parameter bindings to Sql about querybuilder HOT 6 CLOSED

Hi @generik0, thanks for your feedback, could you please show me an example of what you expect ?

from querybuilder.

Her is an example of the translation. _sqlTypeMapper is just a dictionary with e.g. System.Int type = int.

var r = compiler.Compile(query);

var parameters = r.Bindings.Select(parameter =>
{
var para = new StringBuilder();
para.Append($"DECLARE @{parameter.Key} {_sqlTypeMapper[parameter.Value.GetType()]}\n");
para.Append($"SET @{parameter.Key} = {parameter.Value}");
return para.ToString();
});

var sqlFull = {string.Join("\n", parameters)}\n{r.Sql};

from querybuilder.

Ah ok I got your idea now, but why not replacing the values of the parameters inline similar to SqlResult.ToString() method ?

https://github.com/sqlkata/querybuilder/blob/master/QueryBuilder/SqlResult.cs#L40-L59

from querybuilder.

If we use the parameter query, wont the query still be cached and using the parameters get a performance “boost”? I.e. instead of different versions of the same query.

We will also prevent SQL injections.
Maybe this is to much of a corner case?
We are using DevExtreme dashboards that we feed with custom sql “data sources”. This is why the sql with parameters will benefit us...

from querybuilder.

@generik0

Converting the bindings to the actual string of the SQL statement opens you back up to SQL injection and i'd advise against doing that at all cost. Also, I'm not a SQL expert but I believe you would most likely loose the cache hit for the query plan under some circumstances / queries if you convert the bindings into the statement as text. It's something to test but i'd still advise against.

I just made a quick glance at some documentation for the DevExpress dashboard. Are you setting the datasource in code?

If so, please refer to this link

You'll notice that the CustomSqlQuery object has a property called 'Parameters' and you would only really need to map the SqlResult.Bindings into that collection.

If the above isn't your use case; please provide us a little more information so we can try to help.

Thanks

from querybuilder.

Sounds like the best plan.....

from querybuilder.