Show number of rows from a grid view

Question

I am trying to show number of rows from a grid view using a label. I used SQL Count statement but it doesn't work. The problem is I only get only number 1 shown in the label which is not matched with the number of rows I have in my table! I have posted similar question but unfortunately no body gave a clear answer!

My codes as:

Basket.ac

  public int td()
   {
       int customers;
        //I tried this select query but still gets number 1
       //String sql = String.Format("SELECT COUNT(*) FROM dbo.Baskets");
       string sql = string.Format("SELECT COUNT(*) FROM Baskets");
       customers = Db.RunQuery(sql).Rows.Count;

       //customers = Convert.ToInt32(Db.RunQuery(sql).Rows.Count);
       return customers;


    }

DataBaseConn.ac

 public class DataBaseConn
  {
   SqlConnection conn;
   SqlCommand cmd;
   DataTable tbl;
   private void Intialise(CommandType commandtype, string DataBase)
   {

    conn = new SqlConnection();
    cmd = new SqlCommand();

    //Requirements
    conn.ConnectionString = ConfigurationManager.ConnectionStrings[1].ToString();
    cmd.Connection = conn;
    cmd.CommandType = commandtype;
    cmd.CommandText = DataBase;
    conn.Open();
  }

 public int RunProcedure(string Procedure, SortedList ParameterV)
  {

    Intialise(CommandType.StoredProcedure, Procedure);


    for (int i = 0; i < ParameterV.Count; i++)


        try
        {
            if (ParameterV.GetByIndex(i) != null)
                cmd.Parameters.AddWithValue(ParameterV.GetKey(i).ToString(), 
                                                 PrameterV.GetByIndex(i).ToString());
        }
        catch { ;}

    return RunUpdate();

   }

  public int RunUpdate(string InsDelUpd)
   {

    Intialise(CommandType.Text, InsDelUpd);
    return RunUpdate();

  }

   private int RunUpdate()
   {
    try
   {      
        int x = cmd.ExecuteNonQuery();
        conn.Close();
        return x;
     }
     catch (SqlException ex)
      {
        conn.Close();
        return ex.Number;
      }

   }

  public DataTable RunQuery(string Select)
   {
    Intialise(CommandType.Text, Select);
    tbl = new DataTable();
    tbl.Load(cmd.ExecuteReader());
    conn.Close();
    return tbl;

   }
 public bool EData(string selection)
  {

    if (RunQuery(selection).Rows.Count > 0)
        return true;
    else
         return false;

    }


 }

Basket.aspx

    lblQueue.Text = _b.td().ToString();

Answer 1

You don't want to return the data table's .Rows.Count - this will always be 1 (as in 1 row(s) affected) for your count(*) query.

You should instead look at using ExecuteScalar to return the first column of the first row from your query

I'm not sure how you want to build this into your DataBaseConn data helper class, but the gist of it is you need the following sequence:

using (var conn = new SqlConnection(connectionStringHere))
using (var cmd = new SqlCommand(conn))
{
   cmd.CommandType = CommandType.Text;
   cmd.CommandText = "SELECT COUNT(*) FROM Baskets";
   return (int)cmd.ExecuteScalar();
}

Edit

If you aren't able to extend your DataBaseConn helper with an ExecuteScalar, then I guess you'll be able to use the existing RunQuery method which returns a DataTable. Just scrape the first column of the first row like so:

return Db.RunQuery(sql).Rows[0].Field<int>(0);

As a side note, you might look at replacing your DataHelper entirely with the Microsoft Patterns and Practices Data Application Access Block (DAAB), or, if you prefer, look at using an ORM like Entity Framework. By upgrading to a mainstream Data Access encapsulation, you won't need to spend as much time debugging data access issues like this one :)