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

Question

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);
}

If I'm understanding you correctly, maybe consider refactoring your application so this whole login/are-there-any-users procedure is in its own form, and you only show the authenticated main form after all that is done? — AKX
– AKX, Commented Oct 26, 2021 at 11:28
@AKX, I found a better solution, turned out I just need to edit my connection string as such string checkuserexistance = "select LEAST(1,uname) from employee"; thanks for the idea tho. — UnkownReality
– UnkownReality, Commented Oct 26, 2021 at 21:04

AKX · Accepted Answer · 2021-10-26 11:40:42Z

2

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
    }
}

answered Oct 26, 2021 at 11:40

AKX

171k16 gold badges147 silver badges229 bronze badges

Sign up to request clarification or add additional context in comments.

13 Comments

UnkownReality Over a year ago

Your answer works, yet as mentioned in the previous comment on the main question,

string checkuserexistance = "select LEAST(1,uname) from employee";                     MySqlCommand command = new MySqlCommand(checkuserexistance, connection);                     using (MySqlDataReader reader = command.ExecuteReader())                     {                         if (reader.Read() && reader.FieldCount > 0 && reader.HasRows)                         {

this approach worked too, using the LEAST(1,uname) did the trick thanks, buddy both approaches work ✌

UnkownReality Over a year ago

I was Wondering what's the difference between count and Least

AKX Over a year ago

They do entirely different things. select LEAST(1,uname) from employee will end up MySQL retrieving the usernames from all rows (and figuring out whether they're greater or smaller than 1), whereas COUNT(uname) (or even better, COUNT(*)) will only count the rows and return the count.

UnkownReality Over a year ago

thank you for the explanation mate, and with that said, using the reader.Read() && reader.FieldCount > 0 should be enough in knowing if a table is data-voided or not!

AKX Over a year ago

No, it's not, because you're making a query that always returns a row. You could do SELECT id FROM employee LIMIT 1 if you really only want to look at HasRows, since that's bound to return no rows if there are no employees and 1 row when there's at least 1 employee.

|

Collectives™ on Stack Overflow

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

1 Answer 1

13 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

13 Comments

Your Answer

Sign up or log in

Post as a guest

Related