Using very long (10,000+ characters) strings as an SqlParameter value

Question

I am trying to execute the insert command in the database, and one of the columns is of type nvarchar (MAX). The Insert command is created using the .NET SqlCommand class, and each parameter is represented by a single SqlParameter object.

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

The following are examples of how I tried to create the 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 solutions yielded results. If anyone knows what the problem is, tell me.

I am using MS SQL Server 2008.

Thanks in advance.

Answer 1

According to MSDN

NVARCHAR:

Line. Unicode character stream of variable length between 1 and 4000 characters. Implicit conversion fails if the string exceeds 4000 characters. Explicitly set the object when working with strings longer than 4000 characters.

Here ntext:

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

also check this

Answer 2

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

Answer 3

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

Answer 4

One Suggestesstion .. You tried using SqlDbType.XML ... There is no need to parse the xml at the other end ... you can just save the value in varchar.

Answer 5

You still have a problem if you explicitly create a SqlParameter, for example:

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

and then add it to the parameters?

Also, if you call sproc, just make sure the sproc parameter is also declared as nvarchar (max), or if you are not using sproc, try using it. I click varbinary (max) blobs without any problems, so maybe the binary cure is improving.