NVarchar vs NText

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

 

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

 

[alx]

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

 

[Patrice]

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

 

[alx]

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

 

[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

 

[Robbe Morris [C# MVP]]

Or, you could just have the ado.net source code generator below
do all this work for you.

I haven't typed SqlCommand, SqlConnection, or SqlDataAdapter in
almost 3 months now...

--
2005 Microsoft MVP C#
Robbe Morris
http://www.robbemorris.com
http://www.mastervb.net/home/ng/forumvbcode/post10017013.aspx
http://www.eggheadcafe.com/articles/adonet_source_code_generator.asp