NVarchar vs NText


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
}
}
 
Ad

Advertisements

M

Miha Markic [MVP C#]

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.
 
A

alx

I use SQL Server 7.0 (System.Data.SqlClient, SqlConnection) and data is
successfully inserted into database (without trimming).
 
P

Patrice

I would use SQL Profiler to see what SQL statements are send to the
server...
 
A

alx

I did it. Provider send this parameter as NText. But I don't known it's
bug or feature.
 
P

Patrice

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
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top