Formattable SQL

FormattableSql.cs

void Main()
{
	var name = "test";
	var state = "WA";
	var cmd = new MySqlConnection().CreateCommand($"SELECT * FROM Customers WHERE Name = {name} AND State = {state};");
	// cmd.CommandText == "SELECT * FROM Customers WHERE Name = @p0 AND State = @p1;"
}

public static class DbExtensions
{
	public static DbCommand CreateCommand(this DbConnection connection, FormattableString sql)
	{
		var command = connection.CreateCommand();
		command.CommandText = sql.ToString(new SqlFormatProvider(command));
		return command;
	}
}

public class SqlFormatProvider : IFormatProvider
{
	public SqlFormatProvider(DbCommand command) => Formatter = new SqlFormatter(command);

	public object GetFormat(Type formatType) => formatType == typeof(ICustomFormatter) ? Formatter : null;

	private SqlFormatter Formatter { get;}

	private class SqlFormatter : ICustomFormatter
	{
		public SqlFormatter(DbCommand command) => Command = command;

		public string Format(string format, object arg, IFormatProvider formatProvider)
		{
			if (format != null)
				throw new NotSupportedException($"Custom format '{format}' is not supported.");
				
			var parameter = Command.CreateParameter();
			parameter.ParameterName = "@p" + command.Parameters.Count.ToString();
			parameter.Value = arg;
			Command.Parameters.Add(parameter);
			return parameter.ParameterName;
		}
		
		DbCommand Command { get; }
	}
}