Friday 4 January 2008

SQL Server 2005 varchar(max) in C#

An interesting question at work today: How to properly define a parameter that's varchar(max) in Sql Server 2005 from within C#?

Sure, it should be possible to use the signature without adding the integer value. But what if a custom DataAdapter is being used that does require a value if you supply a string? We could use 4000 as a value, but that kind of defeats the purpose of using the new datatype. 8000 then? Same difference; you still possibly end up with truncated strings. Should we make it 2bn? That's 2GB of memory. Not really an otpion in my book.

After some googling, we found the answer: the size of the parameter should be set to -1. There's an example here, where the -1 value is not explained in detail, but just shown in the code. We tested, and it works like a charm.

No comments: