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

Question

Hi, all,

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

Another suggestion to try: use SqlDbType.NText instead of SqlDbType.NVarChar.

Answer 2

One Suggesstion.. Have you tried using SqlDbType.XML... There is no necessary to parse the xml in the other end... you can simply store the value in varchar.

Answer 3

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

Answer 4

Have you tried calling AddWithValue(String name, Object value)?

Answer 5

Thank you all for quick answers. I will try this tomorrow and see what will happen.

@Beth

I read MSDN article You are quoting but the line:

 Explicitly set the object when working with strings longer than 4,000 characters.

really confused me. Can someone shed some light on this one?

@ck

I have assigned value to the parameter but didn't wrote that here in the example. Anyway thanks for noticing.

Update:

I have found the problem. My code is ok but the problem is that Microsoft SQL Server Management Studio I have was not showing properly fields with large values (really strange) and I was a fool for checking results directly through the Studio instead of writing specific test. So, this will teach me to write more unit tests covering as much as possible test cases. Thank you all for your time.

Answer 6

Do you still have the problem if you explicitly create SqlParameter, like:

SqlParameter t = new SqlParameter("@" + name, type);
t.Value = value;

and then add it to parameters?

Also if you are invoking a sproc, just to make sure that sproc's param is also declared as nvarchar(max), or if you are not using a sproc try to use it. I've been pushing varbinary(max) blobs without any issues so maybe a binary gets better treatment.