Insert nullable int in c# using mysql

Question

I have a table in mysql for users. Sometime user has a boss and sometime it don't. So boss data type in nullable int(it is a foreign key, that's why nullable INT). I was using following code and it was causing problem when boss value is null, producing following error "Incorrect integer value: '' for column 'boss_id' at row 1"

 string query = " INSERT INTO " + databasename + ".system_user (" +
                "`boss_id`, " +
                "`name`, " +
                "`user_name`, " +
                "`password_2`, " +
                "`designation`," +
                "`digital_signature`," +
                "`functional_role`," +
                "`group_2`) " +
                "VALUES ('" +
                systemuser.Boss + "', '" +
                systemuser.Name + "','" +
                systemuser.UserName + "', '" +
                systemuser.Password + "', '" +
                systemuser.Designation + "', '" +
                systemuser.DigitalSignature + "', '" +
                systemuser.FunctionalRole + "', '" +
                systemuser.Group + "');";
        MySqlConnection conDataBase = new MySqlConnection(myconnection);
        MySqlCommand cmdDataBase = new MySqlCommand(query, conDataBase);
        MySqlDataReader myreader;

        try
        {
            conDataBase.Open();
            myreader = cmdDataBase.ExecuteReader();
            conDataBase.Close();
            return true;
        }
        catch (Exception ex)
        {
            conDataBase.Close();
            MessageBox.Show(ex.Message);
            return false;
        }

So, i changed the code for string query as follow:

 string query = "";
        if(systemuser.Boss!=null)
        {
            query = " INSERT INTO " + databasename + ".system_user (" +
                "`boss_id`, " +
                "`name`, " +
                "`user_name`, " +
                "`password_2`, " +
                "`designation`," +
                "`digital_signature`," +
                "`functional_role`," +
                "`group_2`) " +
                "VALUES ('" +
                systemuser.Boss + "', '" +
                systemuser.Name + "','" +
                systemuser.UserName + "', '" +
                systemuser.Password + "', '" +
                systemuser.Designation + "', '" +
                systemuser.DigitalSignature + "', '" +
                systemuser.FunctionalRole + "', '" +
                systemuser.Group + "');";
        }
        else
        {
            query = " INSERT INTO " + databasename + ".system_user (" +
              "`name`, " +
              "`user_name`, " +
              "`password_2`, " +
              "`designation`," +
              "`digital_signature`," +
              "`functional_role`," +
              "`group_2`) " +
              "VALUES ('" +
              systemuser.Name + "','" +
              systemuser.UserName + "', '" +
              systemuser.Password + "', '" +
              systemuser.Designation + "', '" +
              systemuser.DigitalSignature + "', '" +
              systemuser.FunctionalRole + "', '" +
              systemuser.Group + "');";
        }

It worked because, Mysql by default put null at the skipped values.

Now according to my scenario, I have to update boss_id from int to null and sometime from null to int. But my query always skip if value is null. Can you please help me in changing the insert statement in such a way that it would insert null value in boos(if its null) and don't just skip it.

Answer 1

Firstly, you should use parameters, it gives you a clean code and avoid injection.

You can use parameters like this:

string query = string.Format("INSERT INTO {0}.system_user (`boss_id`, `name`, `user_name`, `password_2`, `designation`, `digital_signature`, `functional_role`, `group_2`)" +
                                                    "VALUES (@boss_id, @name, @user_name, @password_2, @designation, @digital_signature, @functional_role, @group_2)", databasename);


MySqlConnection conDataBase = new MySqlConnection(myconnection);
MySqlCommand cmdDataBase = new MySqlCommand(query, conDataBase);

cmdDataBase.Parameters.AddWithValue("@boss_id", systemuser.Boss ?? (object)DBNull.Value);
cmdDataBase.Parameters.AddWithValue("@name", systemuser.Name);
cmdDataBase.Parameters.AddWithValue("@user_name", systemuser.UserName);
cmdDataBase.Parameters.AddWithValue("@password_2", systemuser.Password);
cmdDataBase.Parameters.AddWithValue("@designation", systemuser.Designation);
cmdDataBase.Parameters.AddWithValue("@digital_signature", systemuser.DigitalSignature);
cmdDataBase.Parameters.AddWithValue("@functional_role", systemuser.FunctionalRole);
cmdDataBase.Parameters.AddWithValue("@group_2", systemuser.Group);

Note "@boss_id", systemuser.Boss ?? (object)DBNull.Value, this is because you can not use null directly in the parameters.

UPDATE:

If you want to update or delete you can use parameters too:

You can write your queries like this:

string query = string.Format("UPDATE {0}.system_user SET `name` = @name WHERE `boss_id` = @boss_id", databasename);

or

string query = string.Format("DELETE FROM {0}.system_user WHERE `boss_id` = @boss_id", databasename);

For datetime columns you can see this question. It has very good answers.