[Mono-list] (Problem solved) Large strings truncated with SqlClient
adrien.dessemond at softhome.net
Tue Jun 12 01:25:10 EDT 2007
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 !
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
(Many thanks to my collegue who accepted to lend me some of its SQL
Server resources to solve this issue while in a rush)
More information about the Mono-list