Dapper.NET: The varchar(4000) default

Question

I am using Dapper.NET and when i execute the next code:

using (SqlConnection con = new SqlConnection(connectionString))
        {
            con.Open();
            con.Execute(@" insert Clients(name) values(@Name)", new {Name = "John"});
            con.Close();
        }

The query that it executes is the next one:

(@Name nvarchar(4000)) insert Clients(name) values(@Name)

And my question is: why is Dapper translating a string to a nvarchar(4000)? I mean... on the database, the name field is a nvarchar(50)...

Does anybody face this bug? How do you fix it? Have you found another bug like this?

I'd say (but it's pure conjecture) it's because Dapper doesn't know anything about your table structure, and in .NET `string` is UTF-16 (so non ANSI-string) that's potentially without size limit other than memory. That translates to `nvarchar` type with maximum available length. I'd say it's not a bug, but you have to do validation yourself or catch data integrity violation exceptions on `INSERT`. — Patryk Ćwiek, May 06 '15 at 23:38

score 12 · Answer 1 · answered May 06 '15 at 23:41

This is not a bug. Dapper has to pick a SQL data-type for a string parameter, without looking at the database structure (not to mention parsing your query and determining that you're inserting the parameter into a particular column).

Imagine if you were doing this:

insert Clients(name) values(@Name + 'abc')

Should Dapper have to figure out that @Name can be up to 47 characters?

You can be more specific about the size of your parameter if you like:

con.Execute(@" insert Clients(name) values(@Name)", 
    new { Name = new DbString { Value = "John", Length = 50 }});

Thanks, and do you know how can i do for having this more readable? Because if there is an insert with a lot of fields (string fields) it would be very ugly... And is there something like this with another type? Thanks — ascherman, May 07 '15 at 01:50

Dapper.NET: The varchar(4000) default

1 Answers1

Linked