c# - mapping of .net framework types to dbtype, the weird string, nvarchar and char

Question

i found informations about mapping of string(.net framework type) and corresponding nvarchar(dbtype) on msdn, which says:

'This implicit conversion will fail if the string is larger than the maximum size of an NVarChar, which is 4000 characters. For strings larger than 4000 characters, explicitly set the SqlDbType.'

i just can not figurt out what`s mean by the "explicitly set". so i write some code as following:

char[] c = new char[5000];
for (int i = 0; i < 5000; i++)
{
    c[i] = 'a';
}
string s = new string(c);
using (SqlConnection conn = new SqlConnection(connstr))
{ 
    conn.Open();
    // create command object
    var comm = conn.CreateCommand();
    comm.CommandText = "select @s";

    // create parameter for command
    var p = comm.CreateParameter();
    p.ParameterName = "@s";
    // p.DbType = DbType.String;
    // p.Size = 5000;
    p.Value = s;

    // add parameter to command
    comm.Parameters.Add(p);

    // execute command
    var r = comm.ExecuteScalar();
}

as you see, i didn not set parameter type and parameter size, so based on the instructions on msdn, i thought there would be an exception during runtime. but unfortunately, it runs perfectly right, the result r was a string contains 5000 'a'. in fact, no matter i comment or uncomment the code of setting parameter`s type, size, result r will always be right, always be the same.

plz help me, am i misunderstood something about the instruction on msdn?

thank you.

Answer 1

I'm not completely sure what your question is. Are you confused, because the documentation says one thing; yet you are able to do something else?

You explicitly set the parameter type by using the following...

var p = comm.CreateParameter();
p.SqlDbType = System.Data.SqlDbType.VarChar;
p.Size = 5000;
p.ParameterName = "@s";
p.Value = 'c';

Edit: based on the documentation you included, the way I interpret it, the size can be larger than 4000; you just have to set the SqlDbType explicitly. In the past, when I've encountered this, the behavior was either it would truncate the data, or it would give me an errror stating that string or binary data would be truncated.

Unfortunately, SqlServer is not my strongest skill set.