Building stored proc commands with `static` references

[celluj34]

Hello;

I am trying to convert our usage of Dapper-QueryBuilder to InterpolatedSql.Dapper. One problem I am running into is, we store all of our stored procedure names in a common file, like so:

    public static class StoredProcedures
    {
        public static FormattableString GetThings => $"upProc_GetThings";
    }

And they are using in the old CommandBuilder like so:

   var items = await connection.CommandBuilder(StoredProcedures.GetThings)
                               .AddParameter("parm1", parm1)
                               .QueryStoredProcedureAsync<Thing>();

This worked great with the old package, but I can't get it to compile using the new package. I get various errors, one being Argument 2 must be passed with the 'ref' keyword, the next being Argument 2: cannot convert from 'ref string' to 'ref InterpolatedSql.InterpolatedSqlHandler', and finally Parameters or locals of type 'InterpolatedSqlHandler' cannot be declared in async methods or async lambda expressions..

We are using .Net 7, and (according to the docs) the SqlBuilder extension. How can I accomplish what I used to using the new library?

[Drizin]

If you're using net6+ then all methods/constructors that take an interpolated string will intercept the interpolated string using an InterpolatedStringHandler (named InterpolatedSqlHandler) - basically you just write the interpolated string and it's "intercepted" by the compiler (instead of getting a FormattableString which requires some degree of parsing, as we were doing before and still using for net5 and older). That's why you can't pass a FormattableString.

I think the easiest hack here is to use the empty constructor and write the command (in your case the SP name) using AppendFormattableString overload (which still takes FormattableString, so a static reference works):

var items = await connection.SqlBuilder()
    .AppendFormattableString(StoredProcedures.GetThings)
    .AddParameter("parm1", parm1)
    .QueryAsync<Thing>(commandType: CommandType.StoredProcedure);

Let me know how it goes.

[celluj34]

Awesome, thanks for the help! I'll try it out tomorrow and report back.

[celluj34]

Yep, that works for me! I introduced an extension to make it easier (for myself) in case someone else wants to do something similar:

public static class IDbConnectionExtensions
{
    public static SqlBuilder CommandBuilder(this IDbConnection dbConnection, FormattableString value)
    {
        return dbConnection.SqlBuilder().AppendFormattableString(value);
    }
}

[Drizin]

I'm glad it worked.

When I added the net6+ InterpolatedStringHandler thing I tried to have different overloads (allow FormattableString) but I was getting "ambiguous method calls". I tried to solve with a new type and implicit conversions, but I don't think I ever reached any clean design. Now with your example I've just realized that I could use extensions to provide that overload (Instance methods have priority over extension methods). So thanks for sharing!