What size do you use for varchar(MAX) in your parameter declaration?
C#Sql Serverado.netC# Problem Overview
I normally set my column size when creating a parameter in ADO.NET.
But what size do I use if the column is of type VARCHAR(MAX)?
cmd.Parameters.Add("@blah", SqlDbType.VarChar, ?????).Value = blah;
C# Solutions
Solution 1 - C#
In this case you use -1.
Solution 2 - C#
For those of us who did not see -1 by Michal Chaniewski, the complete line of code:
cmd.Parameters.Add("@blah",SqlDbType.VarChar,-1).Value = "some large text";
Solution 3 - C#
The maximum SqlDbType.VarChar size is 2147483647.
If you would use a generic oledb connection instead of sql, I found here there is also a LongVarChar datatype. Its max size is 2147483647.
cmd.Parameters.Add("@blah", OleDbType.LongVarChar, -1).Value = "very big string";
Solution 4 - C#
If you do something like this:
cmd.Parameters.Add("@blah",SqlDbType.VarChar).Value = "some large text";
size will be taken from "some large text".Length
This can be problematic when it's an output parameter, you get back no more characters then you put as input.
Solution 5 - C#
You do not need to pass the size parameter, just declare Varchar already understands that it is MAX like:
cmd.Parameters.Add("@blah",SqlDbType.VarChar).Value = "some large text";