NVarchar vs NText

  • Thread starter Thread starter alx
  • Start date Start date
A

alx

MS documentation says that DbType.String is implicity converted to
NVarChar. But this implicit conversion will fail if the string is
greater than the maximum size of an NVarChar, which is 4000 characters.
For strings greater than 4000 characters, explicitly set the SqlDbType.

But following example works fine. Can anybody explain me why? Is it new
feature of the provider or bug?

using System;
using System.Text;
using System.Data;
using System.Data.SqlClient;

public class MyClass
{
public static void Main()
{
try
{
Test();
}
catch (Exception ex)
{
Console.WriteLine(ex);
}
}

private static void Test()
{
string connectionString = "Data Source=boss;Initial
Catalog=test15;Trusted_Connection=Yes;";
SqlConnection connection = new SqlConnection(connectionString);

string sql = "INSERT INTO Test (NText) VALUES(@NText)";

StringBuilder buffer = new StringBuilder();
for (int i = 0; i < 10000; i++)
{
buffer.Append("0");
}

IDbCommand command= connection.CreateCommand();
command.CommandType = CommandType.Text;
command.CommandText = sql;

IDataParameter param = command.CreateParameter();
param.ParameterName = "@NText";
param.DbType = DbType.String;
param.Value = buffer.ToString();

command.Parameters.Add(param);

Console.WriteLine(((SqlParameter)param).SqlDbType);
//Displays NVarChar

connection.Open();
command.ExecuteNonQuery();
connection.Close();

Console.WriteLine(((SqlParameter)param).SqlDbType);
//Displays NText
}
}
 
Hi,

From your example it is not clear what your database datatype is.
If you are inserting into nvarchar then the text is probably trimmed to fit
the size.
 
I use SQL Server 7.0 (System.Data.SqlClient, SqlConnection) and data is
successfully inserted into database (without trimming).
 
I would say a feature : it seems to make sense to say that strings that are
above the maximum length for varchar/nvarchar are actually text/ntext...

It's likely better though to explicitely set the type (who knows in case the
limit would be higher in SQL Server 2008 ;-)

Patrice
 
Back
Top