Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Named Variables? #3

Open
rburnham52 opened this issue Aug 13, 2021 · 8 comments
Open

Named Variables? #3

rburnham52 opened this issue Aug 13, 2021 · 8 comments
Labels
enhancement New feature or request good first issue Good for newcomers

Comments

@rburnham52
Copy link

Is there any way to name your SQL variables the same as the C# variable name instead of @p0, @p01?

I have a script I'm building up and even though i'm reusing the same variables in multiple parts of the script they appear in the outpoint as different prams, which is hard to verify later. i'm using approval tests to versify the script output.

@Drizin
Copy link
Owner

Drizin commented Aug 13, 2021

@ryburn52 C# compiler doesn't provide the names of interpolated variables, so you'd have to provide them as format or wrap under an anonymous object or some other type. As reference check out how it was made in InterpolatedLogging - it extracts names from anonymous objects and also from this NamedProperty wrapper.

You'd have to make this change in InterpolatedStatementParser where you would capture the variable names and then you would have to modify ParameterInfos.Add to receive the variable name that you extracted in the parser. Last, you'd have to fix the MergeParameter methods to avoid name clashes.

I'm not sure if someone is already passing anonymous objects to the library (since Dapper accepts anonymous objects), in this case it could break something.

I don't think this is a priority for now, but I'll leave this ticket open for future TODO.

@Drizin Drizin added enhancement New feature or request good first issue Good for newcomers labels Aug 13, 2021
@rburnham52
Copy link
Author

Thanks,
it would be neat to be able to define them in something like {name:UserName}. For now i noticed that the QueryBuilder has a Parameters prop. It looks like i can hard code the SQL variable name and add the value to the Parameters prop. This means when i check the Sql property the variable names will all match.

Something like this

var query = connection.QueryBuilder();
query.Parameters.Add(new ParameterInfo("asOfDate", context.AsOfDate, DbType.DateTime));

query.AppendLine($"Select * from Users Where ModifiedDate >= @asOfDate");

Approvals.Verify(query.Sql)

It might seem pointless but i'm having to generate the SQL template to use in the QueryBuilder. So i'm probably not using the builder to it's full potential.

I guess what it would look like is something like this

query.AppendLine(FormattableStringFactory.Create($"Select * from {nameof(Users)} Where {nameof(Users.ModifiedDate)} >= @asOfDate"));

@Drizin
Copy link
Owner

Drizin commented Oct 23, 2021

Not sure if I understood what you want, but have you checked the :raw modifier?

You should be able to do something like:
query.AppendLine($"Select * from {nameof(Users):raw} Where {nameof(Users.ModifiedDate):raw}>= {asOfDate:@asOfDate}");
as long as you make some changes to InterpolatedStatementParser like I explained above to capture the "@parameterName" and explicitly set the name of the parameter in ParameterInfos.Add.

@Drizin
Copy link
Owner

Drizin commented Sep 16, 2023

DapperQueryBuilder is being deprecated and this repo will be archived soon. The replacement library is InterpolatedSql.Dapper. I'll move this issue to that new repo.

@Drizin Drizin transferred this issue from Drizin/DapperQueryBuilder Sep 16, 2023
@Drizin
Copy link
Owner

Drizin commented Sep 16, 2023

InterpolatedSql.Dapper provides better extensibility support.
Here is a quick example of how you can customize the parameter names:

using InterpolatedSql.Dapper;
// ...

public class MyCustomSqlParameterMapper : SqlParameterMapper
{
    public override string CalculateAutoParameterName(InterpolatedSqlParameter parameter, int pos, InterpolatedSql.SqlBuilders.InterpolatedSqlBuilderOptions options)
    {
        // Dapper requires unique parameter names for each element of the array.
        // This is the default implementation:
        //return options.AutoGeneratedParameterPrefix +
        //        (IsEnumerable(parameter.Argument) ? options.ParameterArrayNameSuffix : "") +
        //        pos.ToString();
        // Instead of using "Prefix + Pos" we can just use the format (specified after the interpolated variable)
        return parameter.Format! + (base.IsEnumerable(parameter.Argument) ? options.ParameterArrayNameSuffix : "");
    }
}
//...
InterpolatedSqlDapperOptions.InterpolatedSqlParameterParser = new MyCustomSqlParameterMapper();
var cn = new SqlConnection(connectionString);

DateTime asOfDate = DateTime.Now;
var query = cn.QueryBuilder($"Select * from Users Where ModifiedDate >= {asOfDate:asOfDate}");

Assert.AreEqual("Select * from Users Where ModifiedDate >= @asOfDate", query.Build().Sql);

@terryaney
Copy link
Contributor

Curious...does the example above basically disable parameter hints like :varchar(200)? Don't think I'd need, but curious if you could support both?

@Drizin
Copy link
Owner

Drizin commented Mar 11, 2024

Haven't tested, but on a very quick look I think InterpolatedSqlParser.TransformArgument sends the argumentFormat as ref string, then whatever types/hints can be extracted from that are extracted and removed from the ref variable, and then argumentFormat should be updated back with whatever is remaining there.
So in theory it should work. At least that was the idea (allowing formats to be used for multiple purposes).

Feel free to test and fix if anything is wrong.

@terryaney
Copy link
Contributor

Tested and added PR. Think it is there if you following pattern of $"SELECT ... WHERE ... {param:paramName|varchar(200)}" with | delimiter. PR supports : delimiter if you want it.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
enhancement New feature or request good first issue Good for newcomers
Projects
None yet
Development

No branches or pull requests

3 participants