Problem with SqlParameterCollection.Add method to make SqlDbType all NVarChar

Question

I am trying to access a SQL Server table with a program created in C# to update and register data.

I am thinking of parameterizing the query and using the SqlParameterCollection.Add method to create a SQL statement.

In this case, if I set SqlDbtype to NVarChar for both Name and Age, as in the example below, is there any problem?

query ="INSERT INTO [myTable] ([Name],[Age]) VALUES (@Name,@Age)";
using(SqlCommand cmd = new SqlCommand(query, conn)) 
{
  cmd.Parameters.Add(new SqlParameter("@Name", SqlDbType.NVarChar));
  cmd.Parameters.Add(new SqlParameter("@Age", SqlDbType.NVarChar));
  cmd.Parameters["@Name"].Value = "Tom";
　cmd.Parameters["@Age"].Value = "28";　
  cmd.ExecuteNonQuery(); 
}

In the program I am trying to create, not all of the corresponding fields are of type NVARCHAR.

Is there any problem, such as an error that would occur if I set the SqlDbtype to NVarChar, even when inserting into an Int or DateTime2 type?

Answer 1

Hi, @那由多. Welcome to Microsoft Q&A. 

SqlDbType should be specified as the type of the corresponding field in the database.

For example: If the Age field in the database is of type Int, then SqlDbType should be SqlDbType.Int.

string query = "INSERT INTO myTable (Name,Age) VALUES (@Name,@Age)";
using (SqlCommand cmd = new SqlCommand(query, conn))
{
    cmd.Parameters.Add(new SqlParameter("@Name", SqlDbType.NVarChar));
    cmd.Parameters.Add(new SqlParameter("@Age", SqlDbType.Int));
    cmd.Parameters["@Name"].Value = "Tom";
    cmd.Parameters["@Age"].Value = 28;
    cmd.ExecuteNonQuery();
}
conn.Close();

If the database field is not of NVarChar type, using SqlDbType.NVarChar cannot guarantee that the conversion will be successful. Sometimes a conversion failure error will occur, making the program less robust.

You could compare the document to specify the correct SqlDbType type.

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Is there any problem, such as an error that would occur if I set the SqlDbtype to NVarChar, even when inserting into an Int or DateTime2 type?

It does not make sense to me to consider such irregular settings unless you have very special and concrete reasons to do so. Please explain your reason.

I suggest that you read the following Microsoft document. Match the "SQL Server Database Engine type" with ".NET Framework type" in your ADO.NET code and select proper "SqlDbType enumeration" when you create SqlParameter object

SQL Server Data Type Mappings

Answer 3

I would recommend using NuGet package Dapper.

For example, note there is no need to open/close the connection; parameters are done for you.

using System;
using System.Data;
using System.Data.SqlClient;
using Dapper;

class Program
{
    private static string _connectionString = "TODO";

    static void Main()
    {
        using var conn = new SqlConnection(_connectionString);

        string query = "INSERT INTO [myTable] ([Name],[Age]) VALUES (@Name, @Age)";

        var parameters = new { Name = "Tom", Age = 28 };

        conn.Execute(query, parameters);
        
        Console.WriteLine("Record inserted successfully.");
    }
}

When not using Dapper, each parameter created needs to match the underlying column type.

Answer 4

with command parameters you are defining the datatype of the parameter when sent to sqlserver. if the parameter type does not match its use a conversion is required.

there are implicit data type conversion, used when a one value is set to another and explicit conversions done with cast or convert function.

if the datatype of the parameter can be implicitly converted to data type required then it will work. but a string value can not be converted to a number for instance. see implicit list:

https://learn.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-ver16

varchar and and nvarchar can implicitly convert. but an encoding choice must be made. as all c# string are unicode (nvarchar), you should always use SqlDbType.NVarChar for a string parameter, as the default encoding in your application may not match the encoding in the SQL Server.