Check if a certain Table in MYSQL Database is empty C# (skeleton table)-CodePudding

My application starts with the login menu, a user provides his login credentials and the process of comparing passwords (hashed) goes on, logged in if everything is fine and error handlers kick in if there is an error.

My question is, is there a way to check if the targeted table is empty like not having any sort of data in it (just a skeleton table in a database). Because I am not willing 150 employees to the table and they may leave their job, get promoted and get fired... so I wanna leave it for the admins running the HR of the company...

I used the Form_Activated event but nothing changed, tried the Form_Initialize event no luck. What am I doing wrong here?

Should I change my query? I am totally lost here cause I read through dozens of forms and NON got even close!

Using the code provided with the form initialize event did not work. for it will dispose the form and you just can not get around the problem or at least I couldn't!



try
{
    using (MySqlConnection connection = Connect())
    {
        DataTable table = new DataTable("employee");
        string checkuserexistance = "select count(uname) from employee";
        MySqlCommand command = new MySqlCommand(checkuserexistance, connection);
        using (MySqlDataReader reader = command.ExecuteReader())
        {
            if (reader.Read() && reader.FieldCount > 0 && reader.HasRows)
            {
                Form1_Load(sender, e);
                reader.Close();
            }
            else
            {
                DialogResult dialog = MessageBox.Show("Can not sign in as the given user, would you like to add a user now?", "Empty Database", MessageBoxButtons.YesNo, MessageBoxIcon.Warning);
                if (dialog == DialogResult.Yes)
                {
                    new Thread(() => new User_Managment().ShowDialog()).Start();
                    this.Close();
                }
                else
                {
                    Application.Exit();
                }
            }
        }
    }
}
catch (MySqlException ex)
{
    MessageBox.Show(ex.Message, "Error Connecting to Database!", MessageBoxButtons.OK, MessageBoxIcon.Error);
}

CodePudding user response：

Your logic is currently checking whether there are any rows returned:

MySqlCommand command = new MySqlCommand("select count(uname) from employee", connection);
using (MySqlDataReader reader = command.ExecuteReader())
{
    if (reader.Read() && reader.FieldCount > 0 && reader.HasRows)
    {
        // OK
    }
}

However, a SELECT COUNT(...) always returns (at least) one row so you'll need to also check that the count read from that single line is more than zero by reading the zeroth result column's value.

MySqlCommand command = new MySqlCommand("select count(uname) from employee", connection);
using (MySqlDataReader reader = command.ExecuteReader())
{
    if (reader.Read() && reader.FieldCount > 0 && reader.HasRows && reader.GetInt32(0) > 0)
    {
        // OK
    }
}