Insert null value in database

Question

I have a database in MySQL Workbench with fields like (name, lastname, age, address, etc..) and a windows desktop application (a form) in visual studio with c# where you can insert, search and update. When I insert data from the form and I leave some fields empty, they are saved in the database as blank and I want them to be saved as null. Is there a way to make that happen?

Here is and insert button code:

conn = openconnection.GetConn();

MySqlCommand cmd = new MySqlCommand("INSERT INTO table_name (name,lastname,address, etc...) VALUES (' " + name_textbox.Text + " ',' " + lastname_textbox.Text + " ' etc... );", conn);

cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();
conn.Close();

Thanks a lot, it works fine but I have a problem with words with spaces, like: (father name , mother name , etc). When I insert a value at name it is fine but when I insert a value at father name or mother name it is null. I think it is because of space between words.

MySqlCommand cmd = new MySqlCommand("INSERT INTO table_name (name,`father name`,`mother name`, etc... VALUES (@name,@`father name`,@`mother name`,etc...);", conn);

cmd.Parameters.AddWithValue("@name", name_textbox.Text.NullString());
cmd.Parameters.AddWithValue("@`father name`", father_name_textbox.Text.NullString());
cmd.Parameters.AddWithValue("@`mother name`", mother_name_textbox.Text.NullString());

cmd.ExecuteNonQuery();

Solution 1

One way would be to simply do the interpretation on your own:

string.IsNullOrEmpty(name_textbox.Text) ? null : string.Format("'{0}'", name_textbox.Text);

However, I want to give you another option:

using (MySqlConnection conn = openconnection.GetConn())
{
    using (MySqlCommand cmd = new MySqlCommand("INSERT INTO table_name (name,lastname,address) VALUES (@name,@lastname,@address);", conn);
    {
        cmd.Parameters.AddWithValue("@name", name_textbox.Text.NullString());
        cmd.Parameters.AddWithValue("@lastname", lastname_textbox.Text.NullString());
        cmd.Parameters.AddWithValue("@address", address_textbox.Text.NullString());

        cmd.ExecuteNonQuery();
    }
}

namespace System
{
    public static class StringExtensions
    {
        public static string NullString(this string s)
        {
            return string.IsNullOrEmpty(s) ? null : s;
        }
    }
}

With this solution you'll be properly disposing the connection and command objects, but you'll also be able to streamline the string to null process and leverage prepared queries to make the process simpler and safer because it's not open to SQL Injection.

NOTE: the static class that you see should be placed in its own .cs file.

Solution 2

I like to use this nifty Function to test/insert DBNull.Value.

    public static object ToDBNull(object value)
    {
        if (null != value)
            return value;
        return DBNull.Value;
    }

Useful in other places but for your case:

    MySqlCommand cmd = new MySqlCommand("INSERT INTO table_name (name,lastname,address, etc...)" +
    "VALUES (' " + ToDBNull(name_textbox.Text) + " ',' " + ToDBNull(lastname_textbox.Text) + " ' etc... );", conn)

Also, you should consider your vulnerability to SQL injection attacks and consider Parameterized SQL command.