Getting a count of rows in a datatable that meet certain criteria

Question

I have a datatable, dtFoo, and would like to get a count of the rows that meet a certain criteria.

EDIT: This data is not stored in a database, so using SQL is not an option.

In the past, I've used the following two methods to accomplish this:

Method 1

int numberOfRecords = 0;
DataRow[] rows;

rows = dtFoo.Select("IsActive = 'Y'");
numberOfRecords = rows.Length;

Console.WriteLine("Count: " + numberOfRecords.ToString());

Method 2

int numberOfRecords = 0;

foreach (DataRow row in dtFoo.Rows)
{
    if (row["IsActive"].ToString() == "Y")
    {
        numberOfRecords++;
    }
}

Console.WriteLine("Count: " + numberOfRecords.ToString());

My shop is trying to standardize on a few things and this is one issue that has come up. I'm wondering which of these methods is best in terms of performance (and why!), as well as which is most commonly used.

Also, are there better ways to achieve the desired results?

Photometer

Answer 1

One easy way to accomplish this is combining what was posted in the original post into a single statement:

int numberOfRecords = dtFoo.Select("IsActive = 'Y'").Length;

Another way to accomplish this is using Linq methods:

int numberOfRecords = dtFoo.AsEnumerable().Where(x => x["IsActive"].ToString() == "Y").ToList().Count;

Note this requires including System.Linq.

Moran

Excellent Solution, can you please let me know some source where I can get to see many examples of LINQ? – Dorsoventral

@Saluce, dtFoo.AsEnumerable().Where(expr) does no need to convert to enumerable as we can use dtFoo.Where(expr) is also already enumerable – Insult

@Insult That is not correct. You have to explicitly convert to Enumerable to use the Enumerable.Where. – Moran

Answer 2

int numberOfRecords = DTb.Rows.Count;
int numberOfColumns = DTb.Columns.Count;

Erlond

Answer 3

int numberOfRecords = 0;

numberOfRecords = dtFoo.Select().Length;

MessageBox.Show(numberOfRecords.ToString());

Kafiristan

@Photometer already described dtFoo.Select approach in question. Besides, the question was about counting by some criteria, not retrieving total number of records. – Disregardful

Answer 4

int row_count = dt.Rows.Count;

Burglar

Answer 5

Not sure if this is faster, but at least it's shorter :)

int rows = new DataView(dtFoo, "IsActive = 'Y'", "IsActive",
    DataViewRowState.CurrentRows).Table.Rows.Count;

Arabele

How is int rows = new DataView(dtFoo, "IsActive = 'Y'", "IsActive", DataViewRowState.CurrentRows).Table.Rows.Count; shorter than int rows = dtFoo.Select("IsActive = 'Y'").Length;? – Exocarp

@PedroC88: It's not; it's shorter than the original poster's methods. – Arabele

Answer 6

object count =dtFoo.Compute("count(IsActive)", "IsActive='Y'");

Liebermann

Answer 7

Try this

int numberOfRecords = dtFoo.Select("IsActive = 'Y'").Count<DataRow>();    
Console.WriteLine("Count: " + numberOfRecords.ToString());

Lief

Answer 8

If the data is stored in a database it will be faster to send the query to the database instead of getting all data and query it in memory.

A third way to do it will be linq to datasets, but i doubt any of these 3 methods differ much in performance.

Nazarius

This data is not stored in a database. I edited my original entry to reflect this. – Photometer