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

Question

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?

Possible duplicate of How to count the number of rows from sql table in c#? — Divyang Desai
– Divyang Desai, Commented Nov 26, 2016 at 18:34

marc_s · Accepted Answer · 2018-02-25 12:09:03Z

6

Your query always return one row even no rows exists. It will return 0 if no rows exists for your WHERE 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();
}

ScalarValue will return first column of first row of query result. So for your query this is more effective method to retrieve information you need.

edited Feb 25, 2018 at 12:09

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Nov 26, 2016 at 17:44

Fabio

32.5k4 gold badges35 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

James Harcourt Over a year ago

The database helper in WebMatrix is a far easier to read and work with than the overly verbose SqlCommand approach - much better for a newcomer as this guy seems to be.

Fabio Over a year ago

@JamesHarcourt, I think it is opinion based. Instead for newcomer, I think, much better to get a picture how ADO.NET works.

Sajeetharan · Accepted Answer · 2016-11-26 17:36:11Z

2

You can do this, Because ExecuteNonQuery - returns the number of rows affected.

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

answered Nov 26, 2016 at 17:36

Sajeetharan

223k65 gold badges370 silver badges409 bronze badges

4 Comments

Mike Miller Over a year ago

Won't this return 1?

Fabio Over a year ago

this not work for OP's query - SELECT COUNT(*) will always return 1 row

Spider Man Over a year ago

Thanks a lot @Sajeetharan.

Spider Man Over a year ago

However, I was having trouble using this.

Ibrahim Malluf · Accepted Answer · 2016-11-26 18:28:32Z

1

The ExecuteScalar() method of the SQLClient Command object is specifically provided for returning single 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()
}

answered Nov 26, 2016 at 18:28

Ibrahim Malluf

6674 silver badges6 bronze badges

Comments

trashr0x · Accepted Answer · 2016-11-26 21:14:07Z

0

Use DataSet to get the count

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;

edited Nov 26, 2016 at 21:14

trashr0x

6,5953 gold badges33 silver badges41 bronze badges

answered Nov 26, 2016 at 17:57

Syed Rizwan ul Haq

385 bronze badges

Collectives™ on Stack Overflow

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

4 Answers 4

2 Comments

4 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

4 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related