C# – DataTable Row count always returning 1


I am trying to get the total number of rows of a table using a data adapter but my row count always returns 1. When i use the reader, I get the accurate number. I would like to use a dataAdapter.

Here is my code:

public double GetRowNumber()
        using (OleDbConnection con = new OleDbConnection(someConnectionString))
       String cmdString = "SELECT COUNT (*) FROM " + "[" + Table +"]" 

            OleDbDataAdapter db = new OleDbDataAdapter(cmdString, dbConn);
            DataTable dt = new DataTable();
            return dt.Rows.Count;


Any ideas?

Best Solution

Your function will always return 1 because


statement will fill your datatable with 1 record, the number of count.

If you want the row count use

   String cmdString = "SELECT COUNT (*) FROM " + "[" + Table +"]" 
   OleDbCommand cmd = new OleDbCommand();
   cmd.Text = cmdString ;
   cmd.Connection = con;
   int count = Convert.ToInt32(cmd.ExecuteScalar());

more quick and light for what you want. You do not have to use an object such datatable.