Capturing Count from an SQL Query

Capturing count from an SQL query

Use SqlCommand.ExecuteScalar() and cast it to an int:

cmd.CommandText = "SELECT COUNT(*) FROM table_name";
Int32 count = (Int32) cmd.ExecuteScalar();

C# Count number of rows in sql result

1. Make sure your SQL is protected from SQL injection attack by parameterizing it
2. Rewrite SQL to return COUNT
3. Use ExecuteScalar to retrieve the answer

The query should look like this:

var sql = "SELECT COUNT(*) FROM users WHERE name = @Name";

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

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.

Need a row count after SELECT statement: what's the optimal SQL approach?

There are only two ways to be 100% certain that the COUNT(*) and the actual query will give consistent results:

• Combined the COUNT(*) with the query, as in your Approach 2. I recommend the form you show in your example, not the correlated subquery form shown in the comment from kogus.
• Use two queries, as in your Approach 1, after starting a transaction in SNAPSHOT or SERIALIZABLE isolation level.

Using one of those isolation levels is important because any other isolation level allows new rows created by other clients to become visible in your current transaction. Read the MSDN documentation on SET TRANSACTION ISOLATION for more details.

Find total count of success and failed records

You can use two levels of aggregation -- the inner one to get the status per parent and device. For this, you can actually use min(taskStatus) to get the status:

select ParentTaskId,
       sum(case when taskStatus = 'Success' then 1 else 0 end) as success,
       sum(case when taskStatus = 'Failed' then 1 else 0 end) as failed,
       count(*) as total
from (select ParentTaskId, deviceId, min(taskStatus) as taskStatus
      from t
      group by ParentTaskId, deviceId
     ) pd
group by ParentTaskId;

---

Related Topics