Counting a specific number of records using MySqlDataReader

Question

I have this code that counts the number of records with the same year and date. But when I run the application it doesn't work. Here is my code:

try
{
    string query = "SELECT * FROM tblOrder WHERE dateTime_coded=@dateTimeNow";

    MySqlCommand cmd = new MySqlCommand(query, con);
    cmd.Parameters.AddWithValue("@dateTimeNow", Convert.ToDateTime(DateTime.Now).ToString("yyyy-MM"));

    MySqlDataReader dr = cmd.ExecuteReader();

    MessageBox.Show("OK");
    con.Open();

    while (dr.Read())
    {
        count++;
    }

    dr.Close();
    con.Close();
}
catch (Exception)
{

}

Offtopic `DateTime.Now` is alreadty `DateTime` you don't need the `Convert` part — V4Vendetta, May 15 '13 at 10:44
Can you explain on how you infer the *when I run the application it doesn't work* part — V4Vendetta, May 15 '13 at 10:46
What doesn't work? Do not write an empty catch block that hide the exception without any warning or message to let you know what is wrong with your code — Steve, May 15 '13 at 10:47
@V4Vendetta I have tried to have a messagebox that displays the number of count after the line `con.Close` but it doesn't show so I infer that it doesn't work. — Harvey, May 15 '13 at 10:54
`catch(Exception ex) {MessageBox.Show(ex.Message);} ` but this only for debugging purpose, if you don't have anything to do with an exception let it bubble to a level where you have something useful to do with the exception — Steve, May 15 '13 at 10:56
@Harvey I can't see any Messagebox printing count, Also you have the connection open after `ExecuteReader()`, so sure there the code goes kabooom , you would have known abt it if you atleast had `MessageBox(ex.Message)` in the catch — V4Vendetta, May 15 '13 at 10:57

score 1 · Accepted Answer · answered May 15 '13 at 11:00

First you have an empty catch block which makes no sense

Atleast this would have been better

catch (Exception ex)
{
     MessageBox(ex.Message);// you would know if in case it failed
}

Now the problem seems to be

MySqlDataReader dr = cmd.ExecuteReader();

MessageBox.Show("OK");
con.Open(); <--- opening after executing the reader !

you should try putting the connection in a using block

using(MySqlConnection con = new MySqlConnection())
{
//your stuff in here
}

Another observation

cmd.Parameters.AddWithValue("@dateTimeNow", Convert.ToDateTime(DateTime.Now).ToString("yyyy-MM"))

DateTime.Now is DateTime no need to Convert it again

score 1 · Answer 2 · edited Jun 20 '20 at 09:12

A better approach to your problem is through ExecuteScalar (link for SqlServer but it is the same for MySql) and using the COUNT function

using(MySqlConnection con = new MySqlConnection("your_connection_string_here"))
{
        con.Open();
        string query = "SELECT COUNT(*) FROM tblOrder WHERE dateTime_coded=@dateTimeNow";
        using(MySqlCommand cmd = new MySqlCommand(query, con))
        {
            cmd.Parameters.AddWithValue("@dateTimeNow", DateTime.Now.ToString("yyyy-MM");
            int count = (int)cmd.ExecuteScalar();
            Console.WriteLine("There are " + count.ToString() + " records");
        }
}

As you can see, I have removed the try/catch block that is useless here because you don't do anything with the exception. This will stop the program if your query contains a syntax error or you can't establish a connection with the server. So, if a try/catch is really needed depends on your requirements

(Added also the observation on the DateTime.Now from V4Vendetta)

What is the benefit/effect of using the `using block` compared to nothing? — Harvey, May 16 '13 at 02:18
See [using statement](http://msdn.microsoft.com/en-us/library/yh598w02%28v=VS.80%29.aspx) Basically, the using statement is a try/finally block where the finally block close and dispose the object declared and initialized in the using line. This syntax is elegant and effective. It removes the possibility of resource leaks that, particularly in case of database connection, are very expensive. If, for whatever reason, you get an exception inside a using block you are guaranteed that your code will execute the close and dispose of the object at the closing brace. — Steve, May 16 '13 at 07:40

score 0 · Answer 3 · answered May 15 '13 at 10:42

0

You can SELECT COUNT(*) FROM ... and then use cmd.ExecuteScalar() to retrieve the count returned.

answered May 15 '13 at 10:42

Axarydax

16,353
21
92
151

Counting a specific number of records using MySqlDataReader

3 Answers3