14

I'm using Dapper against a database where strings are stored primarily in VarChar columns. By default Dapper uses NVarChar parameters when generating queries and while I can wrap each and every string parameter I use with DbString it'd be great to use AnsiStrings by default and use DbString for the NVarChar case.

I tried changing the type map in the Dapper source from DbType.String to DbType.AnsiString however that seems to cause an error in the IL generation for the parameters delegate (throws an InvalidProgramException).

Is there an easier way to do this?

Update

Just changing the typeMap was not sufficient I needed to alter some if (dbType == DbType.String) checks too. Now it works!

John Foster
  • 8,697
  • 2
  • 35
  • 37
  • There is nothing great about ansi strings. Unicode is the everywhere, starting at the machine in front of you and the language you use to program it. Do expect trouble when you try to use an option that everybody avoids. – Hans Passant Jun 17 '11 at 13:21
  • 2
    I agree, but tell that to the guys who designed the DB schema 10 years ago when it was on SQL Server 7. By all means unicode should be the default I was just wondering if there's an easier way to change it for this particular use case. – John Foster Jun 17 '11 at 13:27
  • 2
    storing things in unicode in the database pretty much doubles the amount of bytes consumed, so if you nvarchars instead your massively increasing the data needed. Space isn't this issue but this will definitely impact performance, e.g. if you use a nvarchar(max) instead of a varchar(max) you have just halved the amount of data in in 1 row in SQL Server. So you need the ansistrings in .Net to avoid implicit conversions when passing variables to the db – Chris Wood May 14 '13 at 14:36

2 Answers2

34

You can accomplish this without modifying the source code.

Dapper.SqlMapper.AddTypeMap(typeof(string), System.Data.DbType.AnsiString);

Setting this once will adjust all of your strings to varchar.

BillRob
  • 4,659
  • 4
  • 26
  • 38
  • 1
    Yep thanks to this commit https://github.com/StackExchange/dapper-dot-net/commit/4f239a8e8fb4bc8e65e6b06bad26adc3e308ccc8 and opening up the type map you no longer have to tweak the code – John Foster Oct 09 '14 at 20:46
3

To use ansistrings by default I had to (referring to Dapper 1.3 source from NuGet):

  • Alter the type map to use DbType.AnsiString on L164 instead of DbType.String
  • In the method CreateParamInfoGenerator change the checks on L960, L968, L973 to include DbType.AnsiString as well as DbType.String.

The problem with the invalid IL seemed to be that the later branch of the code on L1000 checks for typeof(string) whereas the preceeding branches use DbType.

Doing that everything is peachy again - no more index scans!

John Foster
  • 8,697
  • 2
  • 35
  • 37