Spider Man Spider Man - 14 days ago 5
MySQL Question

How to retrieve the count of the number of rows in asp.net mvc from sql database?

I am trying to print something if the number of rows returned is more than 0 based on a query:

using (SqlConnection con = new SqlConnection("ConnectionString")){
con.Open();
string query = "SELECT COUNT(*) FROM Some_Table WHERE Val > 5";
SqlCommand cmd = new SqlCommand(query, con);
cmd.ExecuteNonQuery(); // get the value of the count
if (count > 0)
{
Console.WriteLine("Returned more than 0 rows");
}
else
{
Console.WriteLine("Did not return more than 0 rows");
}
Console.ReadLine();
}


How can I find the number of rows returned?

Answer

Another way is to use SqlDataReader.

I recommend you to use ExecuteReader because ExecuteReader is used for any result set.

ExecuteNonQuery is typically used for SQL statements without results (UPDATE, INSERT, DELETE,etc).

using (SqlConnection conn = new SqlConnection("ConnectionString"))
{
   conn.Open();
   string query = "SELECT COUNT(*) FROM Some_Table WHERE Val > 5";
   SqlCommand command = new SqlCommand(query, con);
   using (SqlDataReader reader = command.ExecuteReader())
   {
      if (reader.HasRows)
        ...
   }
}