How to get line count in asp.net mvc from sql database?

Question

I am trying to print something if the number of returned rows is greater than 0 based on the request:

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 to find the number of rows returned?

Answer 1

Your query always returns a single row, even rows do not exist. It will return 0 if WHEREthere are no rows for your condition

Use SqlCommand.ExecuteScalar Method ()

using (var con = new SqlConnection("ConnectionString"))
{
  con.Open();
  string query = "SELECT COUNT(*) FROM Some_Table WHERE Val > 5";

  using (var cmd = new SqlCommand(query, con))
  {
      int rowsAmount = (int)cmd.ExecuteScalar(); // get the value of the count
      if (rowsAmount > 0) 
      {
          Console.WriteLine("Returned more than 0 rows");
      }
      else
      {
          Console.WriteLine("Did not return more than 0 rows");
      }

      Console.ReadLine();
}

ScalarValuewill return the first column of the first row of the query result. Therefore, for your request this is a more effective method of obtaining the necessary information.

Answer 2

, ExecuteNonQuery - .

int numberOfRecords = cmd.ExecuteNonQuery();
if (numberOfRecords  > 0) 
{
  Console.WriteLine("Returned more than 0 rows");
}
else
{
    Console.WriteLine("Did not return more than 0 rows");
}

Answer 3

The ExecuteScalar () method of the CommandClient SQLClient object is specifically designed to return individual values ​​from a query. It is more efficient in terms of code and performance.

using (SqlConnection conn = new SqlConnection("ConnectionString"))
{
conn.Open();
string query = "SELECT COUNT(*) FROM Some_Table WHERE Val > 5";
SqlCommand command = new SqlCommand(query, con);
Int32 count = (Int32) cmd.ExecuteScalar()
}

Answer 4

Use DataSetto get a counter.

SqlCommand cmd = new SqlCommand(query, con);

SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = cmd;
DataSet ds = new DataSet();

da.Fill(ds);

var count = ds.Tables[0].Rows.count;