Inserting data after checking if table is empty

Question

I have a server program that will store certain data sent by the client. One data is the client's hostname. The server will check if the hostname exist, if not then it will insert that new data. It should look like this.

hostname_id | hostname
------------------------
      1     | Admin
      2     | Guest_PC
      3     | Bob_PC2

My problem is it won't store the newly inserted data. It keeps on returning zero but not storing anything. Here is my code.(Edited to correct version)

string constring = "Database=chtbuster;Data Source=localhost;User Id=root;Password=''";
    string count1 = "SELECT COUNT(hostName) FROM chtbuster.hostnametable WHERE hostName=@machineName ";
    using (MySqlConnection conDataBase = new MySqlConnection(constring))
    {
            MySqlCommand cmd1Database = new MySqlCommand(count1, conDataBase);
            conDataBase.Open();

             long count = (long)cmd1Database.ExecuteScalar();
             if (count == 0)
             {
                  string insert_ht = "INSERT INTO chtbuster.hostnametable(hostName) VALUES(@machineName);";
                  MySqlCommand cmd5Database = new MySqlCommand(insert_ht, conDataBase);

                  cmd5Database.Parameters.AddWithValue("@machineName", machineName);
                  cmd5Database.ExecuteNonQuery();
                  //*test* output.Text += "\n Empty " + count;
             }
             else
             {
                  //not empty, insert other data
             }

}

I have coded PHP database before and is new to C# database, I'm quite confused. Please help. Thank you.

You need to _execute_ the SQL insert command you created....DOH! — Steve Wellens, Oct 05 '15 at 16:48
@SteveWellens sir I am new to C# database. Is it the ExecuteNonQuery();? — charlie9495, Oct 05 '15 at 16:54

score 2 · Accepted Answer · answered Oct 05 '15 at 16:57

2

You can do this in one step with EXISTS:

IF NOT EXISTS (SELECT hostName FROM chtbuster.hostnametable WHERE hostName=@machineName)
  INSERT INTO chtbuster.hostnametable(hostName_id) VALUES(@machineName);

As mentioned in the comments, you need to execute the query to get a result.

answered Oct 05 '15 at 16:57

snow_FFFFFF

3,235
17
29

sir? is it okay to ask if how should i output a selected query? string gethostname_id = "SELECT INTO chtbuster.hostnametable(hostName_id) WHERE hostName=@machineName"; – charlie9495 Oct 05 '15 at 17:26
1

The query, in my answer, doesn't return a value. When executing, if you want to return a value, you would ExecuteScalar (like you did above), or ExecuteReader to return a data reader that includes any query results. – snow_FFFFFF Oct 05 '15 at 17:56
1

If you want to encapsulate your logic and return something meaningful, maybe write a stored procedure in the db. Then, you logic isn't hard-coded in some sql in your application. – snow_FFFFFF Oct 05 '15 at 17:58
1

"encapsulate your logic" i do not know much about encapsulation but i will try ExecuteReader. Thanks. – charlie9495 Oct 05 '15 at 18:03

Inserting data after checking if table is empty

1 Answers1