implicit type cast operators to avoid needing to explicitly use DynamicParameters

[AlanSamet]

tl;dr;
cn.Execute("Pass @Param as VARCHAR instead of NVARCHAR", new {Param = (AnsiString)"MyString"});

I frequently use anonymous types to call Dapper with parameterized SQL statements. As SQL Server uses NVARCHAR in precedence to VARCHAR, I have to explicitly set the DbType to DbType.AnsiString in order to get it to pass a VARCHAR instead of an NVARCHAR; when I fail to do this, SQL Server will ignore indexes on the VARCHAR column. I've started using this technique to be able to continue to pass anonymous types, while getting the correct parameter type that utilizes the index. I searched the repo and the web for this, and don't see it anywhere and I felt this could be a useful pattern for "fire and forget" passing of anonymous types while passing strings as VARCHAR instead of NVARCHAR.

`
SqlMapper.AddTypeHandler(new AnsiStringTypeHandler());
//...
public class AnsiString
{
string _value;
private AnsiString(string value)
{
_value = value;
}

public override bool Equals(object? obj)
{
    return _value == null ? false : _value.Equals(obj);
}

public override int GetHashCode() => (_value != null ? _value.GetHashCode() : 0);

public static implicit operator string(AnsiString value) => value._value;
public static implicit operator AnsiString(string value) => new AnsiString(value);

}

public class AnsiStringTypeHandler : SqlMapper.TypeHandler
{
public override AnsiString? Parse(object value)
{
return (AnsiString?)value;
}

public override void SetValue(IDbDataParameter parameter, AnsiString? value)
{
    parameter.Value = value != null ? (string)value : DBNull.Value;
    parameter.DbType = DbType.AnsiString;
    parameter.Size = DbString.DefaultLength;
}

}
//Usage:
using (var cn = new SqlConnection(connectionString))
{
await cn.OpenAsync();
await cn.ExecuteAsync(@"CREATE TABLE #Test (Col1 NVARCHAR(20), Col2 VARCHAR(20))

        INSERT #Test
        VALUES (@A, @B)
        ,      (@C, @D)", new { A = "TestA", B = (AnsiString)"TestB", C = "TestC", D = "TestD" });
    }

`

[mgravell]

The DbString type does something very similar to this; vexingly we created it as a class when it should be a struct, but that isn't much different than your proposed approach anyway; similarly, Dapper.AOT supports attributes for similar scenarios (although attributes don't work with anonymous types)

Try using DbString