How to check if mysql table is empty?

Question

How to check if my table is empty from C#?

I have something like:

public MySqlConnection con;
public MySqlCommand cmd;

 con = new MySqlConnection(GetConnectionString());
 con.Open();
 cmd = new MySqlCommand("SELECT * FROM data;", con);

Or I don't need to call SELECT statement?

Select COUNT(*) from data would return the number of rows. If it's 0, then it's empty. It would be faster than what you have (if it does have data) because it would not have to return all the data. — Brook
– Brook, Commented May 27, 2012 at 17:15
"select count(*) from mytable" is the answer. "int nRows = System.Convert.ToInt32(cmd.ExecuteScalar())" is how you can execute it. — paulsm4
– paulsm4, Commented May 27, 2012 at 17:27

John Conde · Accepted Answer · 2015-04-23 15:43:44Z

5

You can use COUNT(*) with no WHERE close and see if exactly how many rows exist with the result.

Or you can do a SELECT (id) FROM tablename with no WHERE clause and if no rows are returned then the table is empty.

edited Apr 23, 2015 at 15:43

answered May 27, 2012 at 17:14

John Conde

220k100 gold badges464 silver badges504 bronze badges

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

2 Comments

user123_456 Over a year ago

can you give me an example please.

John Conde Over a year ago

I don't know C# so I can't post a code example. But if you can do basic MySQL queries this should be easy to do.

Héctor M. · Accepted Answer · 2018-08-22 05:21:37Z

5

I'll give you an example in C# good luck

public bool checkEmptyTable(){
        try
        {
            MySql.Data.MySqlClient.MySqlCommand com = new MySql.Data.MySqlClient.MySqlCommand();
            conn = new MySql.Data.MySqlClient.MySqlConnection("YOUR CONNECTION");
            com.Connection = conn;
            com.CommandText = "SELECT COUNT(*) from data";

            int result = int.Parse(com.ExecuteScalar().ToString());

            return result == 0; // if result equals zero, then the table is empty
        }
        finally
        {
            conn.Close();
        }
   }

edited Aug 22, 2018 at 5:21

Héctor M.

2,4324 gold badges20 silver badges36 bronze badges

answered Jul 6, 2015 at 13:39

Brodie

631 silver badge7 bronze badges

Comments

Don · Accepted Answer · 2020-05-14 10:44:21Z

0

If 'data' might be a big table you would be better with this (where pkdata is your primary key field)

SELECT COUNT(*) FROM data WHERE pkdata = (SELECT pkdata FROM data LIMIT 1);

This will run very quickly whether you have 0 rows in 'data' or millions of rows. Using SELECT with no WHERE or ORDER BY means it just pulls the first row available, LIMIT 1 stops it getting more than 1. Maybe something to look for if you have a program that ran very quickly six months ago but now runs like a dog in treacle!

answered May 14, 2020 at 10:44

Don

2,4172 gold badges17 silver badges17 bronze badges

Comments

Nimantha · Accepted Answer · 2020-05-15 08:30:39Z

-1

SELECT COUNT(*) 
FROM table 
WHERE `col_name` IS NOT NULL

edited May 15, 2020 at 8:30

Nimantha

6,5376 gold badges32 silver badges78 bronze badges

answered May 27, 2012 at 17:18

Sanket

3931 gold badge3 silver badges12 bronze badges

1 Comment

palaѕн Over a year ago

While this code may provide a solution to problem, it is highly recommended that you provide additional context regarding why and/or how this code answers the question. Code only answers typically become useless in the long-run because future viewers experiencing similar problems cannot understand the reasoning behind the solution.

Collectives™ on Stack Overflow

How to check if mysql table is empty?

4 Answers 4

2 Comments

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related