Default Parameter Type for DynamicParameters

[roend83]

I would like to globally override the default parameter type used for DynamicParameters queries when the parameter value is NULL. It seems to be defaulting to NVarChar (DbType.String) in Sql Server right now, and I would like it to default to VarChar (DbType.AnsiString). I'm happy to send a PR to support this, but I want to make sure this is something you'd actually want first.

[mgravell]

There are already options to control the string format at both the per-parameter level (DbString) and globally (there's a static property somewhere). Do these not work for your scenario?

…

On 1 Mar 2018 3:27 pm, "Nathan Roe" ***@***.***> wrote: I would like to globally override the default parameter type used for DynamicParameters queries when the parameter value is NULL. It seems to be defaulting to NVarChar (DbType.String) in Sql Server right now, and I would like it to default to VarChar (DbType.AnsiString). I'm happy to send a PR to support this, but I want to make sure this is something you'd actually want first. — You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub <#958>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AABDsFGEPM0Ac-XXr3ftV_QJNY9-p4LOks5taBNmgaJpZM4SYfVU> .

[roend83]

No, I'm already using the static property to control this for all strings. The issue that I'm running into is unique to using the DyanamicParameters class to pass parameters. Something like this:

string myValue = null;
DynamicParameters param = new DynamicParameters();
param.Add("myValue", myValue);

Because myValue is NULL and we can't do NULL.GetType(), the DbType on the command parameter will not be set and thus default to NVARCHAR in Sql Server. I know that I can go in and change all my code that uses DynamicParameters to explictly pass a DbType when I pass strings, but I'd rather not have to do that now or remember that going forward.
I can think of 2 ways to gracefully solve this problem:

1. Add a static property somewhere to allow me to set the default parameter type when it can't be inferred from a null value
2. Change DynamicParameters.Add to be a generic method (i.e. void Add<T>(string name, T value, ...) and store the type away to do the DbType lookup later.
   Does this make sense?

[mgravell]

Option 2 has some appeal and I could "get with". Option 1 sounds risky. If you have ideas on implementing option 2, it sounds viable.

…

On 1 Mar 2018 7:13 pm, "Nathan Roe" ***@***.***> wrote: No, I'm already using the static property to control this for all strings. The issue that I'm running into is unique to using the DyanamicParameters class to pass parameters. Something like this: string myValue = null; DynamicParameters param = new DynamicParameters(); param.Add("myValue", myValue); Because myValue is NULL and we can't do NULL.GetType(), the DbType on the command parameter will not be set and thus default to NVARCHAR in Sql Server. I know that I can go in and change all my code that uses DynamicParameters to explictly pass a DbType when I pass strings, but I'd rather not have to do that now or remember that going forward. I can think of 2 ways to gracefully solve this problem: 1. Add a static property somewhere to allow me to set the default parameter type when it can't be inferred from a null value 2. Change DynamicParameters.Add to be a generic method (i.e. void Add<T>(string name, T value, ...) and store the type away to do the DbType lookup later. Does this make sense? — You are receiving this because you commented. Reply to this email directly, view it on GitHub <#958 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AABDsLbPKWlYseqJYir6oStsKTT_uNvxks5taEhNgaJpZM4SYfVU> .

[roend83]

I took a stab at this. It seems to work in my application. I can't run the tests on my machine though. Are there instructions for the test setup somewhere?

[NickCraver]

See #963 for progress here.

[rodri-oliveira-dev]

I ran into a related version of this problem, particularly when the CLR value is null and therefore doesn’t carry enough information to express the intended SQL Server contract.

Instead of changing Dapper’s global default, I experimented with making the SQL Server type explicit at the call site:

new
{
Code = SqlParam.VarChar(null, 30)
}

Even though the CLR value is null, the resulting SqlParameter is still explicitly configured as SqlDbType.VarChar with Size = 30.

I extracted this approach into an external library:

https://github.com/rodri-oliveira-dev/Dapper.TypedParameters

It doesn’t provide a global override for Dapper’s defaults, so it isn’t a direct solution to the feature request in this issue. I’m mentioning it because it provides another way of avoiding parameter-type inference in cases where the database contract is already known.