Determine which SQL data types require value to be quoted?

Question

I need to send a command to a database. I know the names and data types of all the fields in the affected table, and I need to generate a command using them. My only problem is determining which column values are of data types that require quotes in TSQL - for example, if the field was a string Hello World of type varchar, I'd need to quote it, but if it were an int, 5, I wouldn't need to quote it.

I could use something like the following code, but it seems very inefficient. Can someone point me to a better way of doing this - possibly using a built in SQL server or C# feature?

public string QuoteStringIfDatatypeRequires(string columnName)
{
    if (DataTypes[columnName].Contains("date") || DataTypes[columnName].Contains("time") ||
        DataTypes[columnName].Contains("char") || DataTypes[columnName].Contains("text") ||
        DataTypes[columnName].Contains("binary") || DataTypes[columnName].Contains("image"))
    {
        return "'" + columnName + "'";
    }

    return columnName;
}

Answer 1

You can quote everything, and that will be converted to the appropriate data type by database itself.

But that will be incorrect. Instead you need to use parameterized queries and send values as is:

command.Parameters.AddWithValue("@name", value);

or

command.Parameters.Add("@name", type, size).Value = value;

See MSDN.