Using very long (10000+ characters) strings as SqlParameter value

Question

I am trying to execute an insert command into the database and one of the columns is of type nvarchar(MAX). Insert command is created by using .NET SqlCommand class and each of the parameters is represented by one SqlParameter object.

My command gets executed always, but when I pass string which length is large (10000+ characters) and which is used as a value for the SqlParameter mapped to the column of type nvarchar(MAX) after the insertion, that particular column is left empty. I repeat, no exception is thrown, INSERT command is executed, but column is empty.

Following examples are how I've tried to create parameter:

// message is the large string variable
// first solution
insertCommand.Parameters.Add("@message", SqlDbType.NVarChar, -1);

// second solution
insertCommand.Parameters.Add("@message", SqlDbType.NVarChar, message.Length);

// third solution
insertCommand.Parameters.Add("@message", SqlDbType.NVarChar, message.Length * 2);

None of these solution gave result. If anyone knows what is the problem please tell me.

I am using MS SQL Server 2008.

Thanks in advance.

Answer 1

According to MSDN

NVarChar:

String. A variable-length stream of Unicode characters ranging between 1 and 4,000 characters. Implicit conversion fails if the string is greater than 4,000 characters. Explicitly set the object when working with strings longer than 4,000 characters.

Here's ntext:

String. A variable-length stream of Unicode data with a maximum length of 2 30 - 1 (or 1,073,741,823) characters.

also check out this