named parameters

[SGStino]

Is it possible to specify parameter names instead of indices?

I'd like to generate a query in the following style:

SELECT TOP (@TopCount) Id FROM [Schema].[Table] WHERE [ParentId] = @IdParameter and [Time] BETWEEN @StartTime AND @EndTime

instead of the current

SELECT TOP (@p0) Id FROM [Schema].[Table] WHERE [ParentId] = @p1 and [Time] BETWEEN @p2 AND @p3

[ahmad-moussawi]

Theoretically this is not possible, why do you need this ?

[ahmad-moussawi]

I will close this for now, feel free to reopen

[rafaelbenavent]

Theoretically this is not possible, why do you need this ?

Do you mean theoretically it is possible? Because i don't see why it would not be.

[ahmad-moussawi]

@rafaelbenavent the only way to do this is to introduce a new argument to pass the parameter name, something like

query.Where("Date", ">", startDate, "@startDate").Where("Date", "<", endDate, "@endDate")

I don't figure a way to automatically extract the name without affecting the current api.

it would be great if you can provide any example.

[mnns]

This would be great for me as well.
You can use the same name as in column name,
Date = "@Date"

I wish using sqlkata for the compilation of query and not actual querying the db.
I'd like the end result to have parameters instead of slices @p0 and so on.

If there was a flag to activate such function it would be great.

Thanks

[rajeevs1992]

One use for this feature would be to specify the exact data type of a column during insert. I have a table with JSON and XML columns. I believe the API does not provide direct support for these types. These are inserted using string datatype, with parameter type json or xml. When I use the SqlKata for the same insert, I get "expected type json, got text" errors. If I had access to the column in parameter names, I could have specified the type for each parameter manually.

[manuth]

I need to use SqlKata for creating queries for an SqlDataAdapter where I need to name variables in INSERT, DELETE and UPDATE-commands properly as well.

It'd be great to have some way to specify the name of the variables (especially in VALUES and WHERE-segments).
I'd love to see this issue reopened.

[puremass]

I agree I would love to see this opened as well. I would like to us SqlKata in my code specifically for dynamic query building and nothing else. My code solution already has a DAL for executing queries. So I would like to build the query using SqlKata and then build my parameters separately and send them into my DAL. It makes It makes it hard to build the parameter list properly when the index of a specific parameter could change depending on how the query is built.

A simple example would be something like this:

var accountIdsQuery = new Query("dbo.Account AS account")
.Select("account.Id")
.When(patientIds.Any(), q => q.WhereIn("account.PatientId", patientIds))
.When(accountNumbers.Any(), q => q.WhereIn("account.AccountNumber", accountNumbers))

If both patientIds and accoutnNumbers have values then patientIds would be @p0 and accountNumbers would be @p1.

SELECT [account].[Id] FROM [dbo].[Account] AS [account] WHERE [account].[PatientId] IN (@p0) AND [account].[AccountNumber] IN (@p1)

When I go to build may parameter dictionary that my custom DAL uses it would look like this:

var parameters = new ParameterDictionary(2)
{
{ "@p0", patientIds},
{ "@p1", accountNumbers}
};

If only accountNumbers contains values then the accountNumbers parameter ends up being @p0 instead of @p1 meaning I would have to know that and build a different parameter dictionary.

SELECT [account].[Id] FROM [dbo].[Account] AS [account] WHERE [account].[Accountnumber] IN (@p0)

If the parameters could be named instead of indexed I could always build my parameter dictionary the same way and send it into my DAL with the query even if some the parameters were unused. So my parameter dictionary would always look like this in this example:

var parameters = new ParameterDictionary(2)
{
{ "@patientIds", patientIds},
{ "@accountNumbers", accountNumbers}
};

SELECT [account].[Id] FROM [dbo].[Account] AS [account] WHERE [account].[Accountnumber] IN (@accountNumbers)

[vgb1993]

I would also like to have this issue opened again, having namd parameters and parameter dictionaries would be very helpful for us too.

Also, C# 10 shiped caller-argument-expression. I saw a NDC Conference video (or maybe somwhere else) where they used this feature to create the parameters with string interpolating and extracting them using the caller expression.
I with I could find the video :/

Do you see any way we could use this feature to accomplish this?
https://docs.microsoft.com/en-us/dotnet/csharp/language-reference/proposals/csharp-10.0/caller-argument-expression

[ahmad-moussawi]

@vgb1993 in the provided example the param name will always resolve to "col" as the param name for the Where function is string col, maybe we can guess the name from the column name provided for the Where for example but still there is a lot of cases that are not clear how to implement, I will list a few here.

what would be the params names in these cases:

• When the same param is used more than once

db.Query("table").Where("Name", name).orWhere("LastName", name);

• When the param is manipulated or a primitive value

db.Query("table").Where("Name", name.ToLowerCase()).orWhere("LastName", "ABC");

and I am pretty sure that there are many, I am open for suggestions, but in the current situation it's very hard to implement

[njlr]

This...

q.Select("books").Where("published", "<", UnsafeLiteral("@published_a"))

Gives...

SELECT "books" FROM "books" WHERE "published" < @published_a

A bit of a hack perhaps?
I don't know if there are negative consequences to this approach.