Named Variables?

[rburnham52]

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]

@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.

[rburnham52]

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]

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]

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]

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]

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]

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]

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.