Dapper and varchars

dapper varchar

Question

I found the following comment on the Dapper .NET project home page.

Dapper supports varchar params, if you are executing a where clause on a varchar column using a param be sure to pass it in this way:

    Query<Thing>("select * from Thing where Name = @Name", new {Name = 
    new DbString { Value = "abcde", IsFixedLength = true, Length = 10, IsAnsi = true });

On Sql Server it is crucial to use the unicode when querying unicode and ansi when querying non unicode

I'm evaluating Dapper for use with a legacy database (SQL Server 2008), with lots of stored procedures with varchar parameters, and I'm a little confused by this restriction.

With hand-crafted ADO.NET code, I'd use the following for the above query:

new SqlParameter("@Name", "abcde")

without specifying whether it's unicode or not, nor the length.

  • Why do I need this verbose DbString syntax with Dapper, specifying the column length, IsFixedLength and IsAnsi?

  • Why IsFixedLength = true for a varchar column (I'd expect it to be true for a char or nchar column)?

  • Do I have to use DbString like this for stored procedure parameters?

I was expecting Dapper to make my DAL code more concise, but this seems to be making it more verbose for varchar parameters.

UPDATE

I've researched a bit further, to try to understand why Dapper would have this varchar restriction, which I don't appear to have in my hand-crafted code, where I would normally create an input parameter as follows:

var parameter = factory.CreateParameter(); // Factory is a DbProviderFactory
parameter.Name = ...;
parameter.Value = ...;

and usually leave the provider to infer the DbType using its own rules, unless I specifically want to coerce it.

Looking at Dapper's DynamicParameters class, it has a method AddParameters which creates parameters as follows:

var dbType = param.DbType; // Get dbType and value
var val = param.Value;     // from 

...
// Coerce dbType to a non-null value if val is not null !!!!!
if (dbType == null && val != null) dbType = SqlMapper.LookupDbType(val.GetType(),name);
...
var p = command.CreateParameter();
...
if (dbType != null)                     
{                         
    p.DbType = dbType.Value;                     
}

I.e. it explicitly coerces IDataParameter.DbType to a value it looks up with its own algorithm, rather than leaving the provider to use its own rules.

Is there a good reason for this? It seems wrong for me, particularly in the light of the comment about Dapper's support for varchar parameters.

Popular Answer

You need this syntax when working with ODBC.

You would need to define a CHAR(30) field as a DbString in c# for Dapper and also set the length (30) and ansi (true) values to prevent Dapper from assuming the string was a text/blob type. Otherwise you will likely receive the error: "Illegal attempt to convert Text/Byte blob type".

I was getting this error using ODBC to connect to Informix until I defined my param as a DbString() and set the length and ansi values.

More info here.



Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why