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.

I suggest that you first look into using parameters instead of concatenating values into your SQL. — juharr, Mar 03 '16 at 16:02

score 4 · Accepted Answer · edited May 23 '17 at 11:59

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.

That works like a charm, thank you so much. Can you please guide me towards writing more such queries? as I have to write update and delete queries as well with more complex entries like date and time. — Muhammad Nouman, Mar 03 '16 at 18:29
@MuhammadNouman: Updated answer. this is a link to a good mysql tutorial in C#: http://zetcode.com/db/mysqlcsharptutorial/ — Arturo Menchaca, Mar 03 '16 at 18:48

score 0 · Answer 2 · answered Mar 03 '16 at 16:14

You are encapsulating the value of Systemuser.Boss in single quotes, doesn't this indicate that you are trying to insert a string into an integer column?

string query = @"INSERT INTO {0}.system_user (
                         `boss_id`,
                         `name`,
                         `user_name`,
                         `password_2`,
                         `designation`,
                         `digital_signature`,
                         `functional_role`,
                         `group_2`)
                         VALUES
                         {1},
                         '{2}',
                         '{3}',
                         '{4}',
                         '{5}',
                         '{6}',
                         '{7}',
                         '{8}')
                         ";
string formattedQuery = string.Format(query, 
                                      databasename, // {0}
                                      Systemuser.Boss, // {1}
                                      Systemuser.Name, // {2}
                                      etc, etc);

EDIT: missed a part where you said 'when it was null'... you need to use:

(Systemuser.Boss ?? "NULL")

Have an upvote, because `Boss ?? "NULL"` solves the problem. But the _best_ solution would use parameters, as @juharr remarked. — CompuChip, Mar 03 '16 at 16:19
The real problem is the use of string concatenation, not how that concatenation is done. Using `string.Format` may work for `int` but what about decimals? Dates? What happens if the `Systemuser` is actually Bobby Tables? — Panagiotis Kanavos, Mar 03 '16 at 16:25

Insert nullable int in c# using mysql

2 Answers2