records count from database

Question

I have 8 tabs containing number of records in each one and a function that should count the number of records in each tab and put it in the tab’s header name like the following:

public void count_records(object sender, EventArgs e)
    {
        SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["Test"].ConnectionString);
        string[] commands = {
                                "SELECT * FROM myTable",
                                "SELECT * FROM myTable WHERE Status=2",
                                "SELECT * FROM myTable WHERE Status=3",
                                "SELECT * FROM myTable WHERE Status=8",
                                "SELECT * FROM myTable WHERE Status=4",
                                "SELECT * FROM myTable WHERE Status=7",
                                "SELECT * FROM myTable WHERE Status=1",
                                "SELECT * FROM myTable WHERE Status=5"
                            };
        int[] LLCount = new int[commands.Length];
        try
        {
            for (int i = 0; i < commands.Length; i++)
            {
                SqlCommand cmd = new SqlCommand(commands[i], con);
                SqlDataReader reader;
                int count = 0;
                con.Open();
                reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    count++;
                }
                LLCount[i] = count;
                myTab1.HeaderText += " (" + LLCount[0] + ")";
                myTab2.HeaderText += " (" + LLCount[1] + ")";
                myTab3.HeaderText += " (" + LLCount[2] + ")";
                myTab4.HeaderText += " (" + LLCount[3] + ")";
                myTab5.HeaderText += " (" + LLCount[4] + ")";
                myTab6.HeaderText += " (" + LLCount[5] + ")";
                myTab7.HeaderText += " (" + LLCount[6] + ")";
                myTab8.HeaderText += " (" + LLCount[7] + ")";
            }
        }
        catch (Exception ex) { string ee = ex.Message; }
        finally { con.Close(); }
    }

Now, the problem I’m facing is that the reader gets the number of records of the first command string correctly "LLCount[0]" but the rest of them are zeros.

EDIT:

I added reader.Close(); and moved the assignment outside the loop but it didn't even show the zeros or anything as before. So, I think it dosen't matter whether you put reader.Close(); or not.

Use reader.Close(); after the while loop. I think an exception is raised and caught in catch block when you tries to open the reader second time before closing previous reader. Suggestion - Assign HeaderText property of tab outside of for loop. You can use using block to open reader. — Romil Kumar Jain, Jun 25 '12 at 12:25
I added reader.Close(); and moved the assignment outside the loop but it didn't even show the zeros or anything as before. so i think it dosen't matter whether you put reader.Close(); or not. — Kemoid, Jun 25 '12 at 12:34
how to use this Count(*) ?? can you please give me a reference? — Kemoid, Jun 25 '12 at 12:44
SELECT count(*) as cnt FROM myTable. Now no need to use reader and looping to calculate count, just use executescalar function of sqlcommand. — Romil Kumar Jain, Jun 25 '12 at 12:46

score 1 · Accepted Answer · answered Jun 26 '12 at 10:05

Look for //* in the code to find the differences. The main chnage was that I put con.Open(); before the for loop. Opening the same connection again is throwing error when the connection is not closed. this error was getting caught in catch block,

public void count_records(object sender, EventArgs e)
{
    SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["Test"].ConnectionString);
    //*** See Count(*) in the sql
    string[] commands = {
                       "SELECT count(*) FROM myTable",
                        "SELECT count(*) FROM myTable WHERE Status=2",
                        "SELECT count(*) FROM myTable WHERE Status=3",
                        "SELECT count(*) FROM myTable WHERE Status=8",
                        "SELECT count(*) FROM myTable WHERE Status=4",
                        "SELECT count(*) FROM myTable WHERE Status=7",
                        "SELECT count(*) FROM myTable WHERE Status=1",
                        "SELECT count(*) FROM myTable WHERE Status=5"

                    };
    int[] LLCount = new int[commands.Length];
    try
    {
        //*****This is the change I made
        con.Open();

        for (int i = 0; i < commands.Length; i++)
        {
            SqlCommand cmd = new SqlCommand(commands[i], con);

            int count = 0;
            //*** Se the use of ExecuteScalar
            count =Convert.ToInt32(  cmd.ExecuteScalar());
            LLCount[i] = count;
        }

        //***Now Assign the Tab Headers

    }
    catch (Exception ex) { string ee = ex.Message; }
    finally { con.Close(); }
}

}

records count from database

1 Answers1