Sipo Sipo - 6 months ago 24
SQL Question

How to insert a null-value to the database in C# without using parameters?

Using SQL parameters, I could insert a

NULL
value to the database using this sort of C# code:

string query = "INSERT INTO table_name (column_name) VALUES (@value)";
using (SqlConnection connection = new SqlConnection(/* connection info */))
{
using (SqlCommand command = new SqlCommand(query, connection))
{
command.Parameters.Add("@value", DBNull.Value);
command.ExecuteNonQuery();
}
}


Not using parameters is usually considered to be bad practice, because of SQL-Injection and some other reasons. But, I am curious to know if there is a way to do so without using them. Something like this:

var nullValue = DBNull.value;
string query = "INSERT INTO table_name (column_name) VALUES (" + nullValue + ")";


or even:

string query = "INSERT INTO table_name (column_name) VALUES (\\NULL\\)";


or whatever. Thanks.

Answer

Uhmm yes you can do, if you know its going to always be NULL then write the SQL statement in a full string, like this.

string query = "INSERT INTO table_name (column_name) VALUES (NULL)";

if you take input from a user, then never do this

var nullValue = DBNull.value;
string query = "INSERT INTO table_name (column_name) VALUES (" + nullValue + ")";

This should be the first rule of web development, NEVER trust user data. Use parmeters.

using(SqlConnection connection = new SqlConnection("connection_String"))
{
    string query = "INSERT INTO table_name (column_name) VALUES (@val1)";

    using(SqlCommand inputQuery = new SqlCommand(query))
    {
        inputQuery.Connection = openCon;
        inputQuery.Parameters.AddWithValue("@val1", DBNull.Value);
        try
        {
            connection.Open();
            int recordsAffected = inputQuery.ExecuteNonQuery();
        }
        catch(SqlException)
        {
            // error here
        }
        finally
        {
            connection.Close();
        }
    }
}
Comments