[Mono-list] (Problem solved) Large strings truncated with SqlClient

[Adrien Dessemond]

Hello !

Someone asked some days ago how to send a large text buffer to a SQL 
Server database (in the database, his strings were truncated at their 
first 4000 bytes when the code is run under Mono and are complete 
when run with the Microsoft implementation). I post the solution here 
if someone else has the same kind of symptoms. Please, free feel to 
comment and complete/correct.

The "trick" is simply to force the DbSqlDatatype to SqlDbDataType.NText. E.g. :

sqlcmd.CommandText = "INSERT INTO yourtable(...YOURCOLUMN,...).. 
VALUES (.... at YOURCOLUMN,...)"
....

SqlParameter prm = new SqlParameter();
...
prm.ParameterName = "@YOURCOLUMN";
prm.Value = <a very long System.String>
prm.SqlDbDataType = SqlDbDataType.NText;  // your column must be of 
type NTEXT (or VARCHAR(MAX)) in the database !
sqlcmd.Add(prm);
...

By default, SqlDbDataType is set to NVarChar when a System.String 
object is assigned to the  SqlParameter, perfectly correct (both in 
Microsoft and Mono implementations).

Here lies the trick :

The Mono MSSQL implementation does not care about the real length of 
a string assigned to an SqlParameter. It blindly "trusts" the value 
set in the SqlDbDataType property of the SqlParameter object 
(SqlDbType.NVarChar in our case). As 'NVarChars' table columns are 
limited to 4000 unicode characters, the string is truncated to its 
first 4000 characters when sent to the database.

The Microsoft MSSQL implementation (gotcha ? lazzyproof ?) *cares 
about* the string length : if the string assigned to a SqlParameter 
object exceeds 4000 bytes, the SqlDbDataType property of this 
SqlParameter object is considered as set to SqlDbDataType.NText (even 
it still equals to SqlDbDataType.NVarChar). This explain why the 
string is not truncated when sent to the databse in that case. This 
behaviour is *not* documented in the MSDN (!).

Of course if you try to store your more-than-4000 bytes-string in a 
table column defined as "NVarChar" in the database, an exception 
saying "Data truncation will occur if you do that" will be thrown.... 
even SqlDbType is set to SqlDbType.NText. No miracles there.
Altought used 'NVARCHAR'/'NTEXT' here but the same applies for 
'VARCHAR'/'TEXT'.

(Many thanks to my collegue who accepted to lend me some of its SQL 
Server resources to solve this issue while in a rush)

Kind regards,

Adrien