C# return mysql number of rows

I have Created a full Console application using mysql. In your select query you are querying the whole table which is a bad idea. use limit to get only one result - this should be enough to determine if there are any rows in the table.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using MySql;
using MySql.Data.MySqlClient;


namespace ConsoleApplication29
{
    class Program
    {
        static void Main(string[] args)
        {
            if (AnyRows())
            {
                Console.WriteLine("There are rows in the database");
            }
            else
                Console.WriteLine("no data found in database");

            //This will pause the output so you can view the results. Otherwise you will see the dos screen open then close.
            Console.Read();
        }

        //This is the Methos to call
        public static bool AnyRows()
        {
            string connectionString = "Server=localhost;Database=test;Uid=root;Pwd=yourpassword;";

            //Wrap this is using clause so you don't have to call connection close
            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                connection.Open();
                string query  = "select * from mytable limit 1";

                using (MySqlCommand command = new MySqlCommand(query, connection))
                {
                    MySqlDataReader reader = command.ExecuteReader();
                    return reader.HasRows;
                }
            }
        }
    }
}

assuming "results" is of type int

if(results != 0)
{
   //do operation
}
else
{
   Console.WriteLine("..."); //or output elsewhere
}

c# if else

add a reference to linq, and it gets really easy

var result = SomeDatabaseCall();
if (result.Any()){
    // do something
}

if you want to filter the results even further, you can do that inside the Any

var result = SomeDatabaseCall();
if (result.Any(r => r.ID == SomeID)){ // ID can be replaced with any properties in your return model
    // do something
}

I got the solution. It is actually very simple. BTW thanks for those who helping. This is the solution:

class DBConnect
{
    private MySqlConnection connection;
    private string server;
    private string database;
    private string uid;
    private string password;

    public DBConnect()
    {
        Initialize();
    }

    private void Initialize()
    {
        server = "your_server";
        database = "your_db";
        uid = "usr";
        password = "pwd";
        string connectionString;
        connectionString = "SERVER=" + server + ";" + "DATABASE=" + database + ";" + "UID=" + uid + ";" + "PASSWORD=" + password + ";";

        connection = new MySqlConnection(connectionString);
    }

    //open connection to database
    private bool OpenConnection()
    {
        try
        {
            connection.Open();
            return true;
        }
        catch (MySqlException ex)
        {
            //When handling errors, you can your application's response based on the error number.
            //The two most common error numbers when connecting are as follows:
            //0: Cannot connect to server.
            //1045: Invalid user name and/or password.
            switch (ex.Number)
            {
                case 0:
                    MessageBox.Show("Cannot connect to server.  Contact administrator");
                    break;

                case 1045:
                    MessageBox.Show("Invalid username/password, please try again");
                    break;
            }
            return false;
        }
    }

    //Close connection
    private bool CloseConnection()
    {
        try
        {
            connection.Close();
            return true;
        }
        catch (MySqlException ex)
        {
            MessageBox.Show(ex.Message);
            return false;
        }
    }  


public void Select()
    {
            string query = "SELECT * FROM table";

            //Open connection
            if (this.OpenConnection() == true)
            {
                //Create Command
                MySqlCommand cmd = new MySqlCommand(query, connection);

                //Create a data reader and Execute the command
                MySqlDataReader dataReader = cmd.ExecuteReader();

                //Read the data in the database

                if (dataReader.HasRows == true)
                {
                    while (dataReader.Read())
                    {
                        //do retrieve data
                    }
                }else
                {
                    MessageBox.Show("There's no recods in the database");
                }
           }
     }
}

It's best to use SELECT 1 instead of grabbing unnecessary data from the database to simply check for the existence of rows.

public static bool IsTableEmpty(string tableName)
{
    string cs = "Server=localhost;Database=test;Uid=root;Pwd=sesame;";
    using (var conn = new MySqlConnection(cs))
    {
       conn.Open();
       string sql = string.Format("SELECT 1 FROM {0}", tableName);
       using (var cmd = new MySqlCommand(sql, conn))
       {
          using (var reader = cmd.ExecuteReader())
          {
             return !reader.HasRows;
          }
       }
    }
}

If you want to get the row count, then you'll need something like:

public static int GetTableRowCount(string tableName)
{
    string cs = "Server=localhost;Database=test;Uid=root;Pwd=sesame;";
    using (var conn = new MySqlConnection(cs))
    {
       conn.Open();
       string sql = string.Format("SELECT COUNT(*) FROM {0}", tableName);
       using (var cmd = new MySqlCommand(sql, conn))
       {
          return Convert.ToInt32(cmd.ExecuteScalar());
       }
    }
}