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?

Solution 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.

Solution 2

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

Solution 3

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;

Solution 4

int row_count = dt.Rows.Count;

Solution 5

int numberOfRecords = 0;

numberOfRecords = dtFoo.Select().Length;

MessageBox.Show(numberOfRecords.ToString());

View more solutions

Comments

[Sesame]

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

[PedroC88]

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

[Cosmin]

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

[Aditya Bokade]

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

[Alexander]

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

[Sunny_Sid]

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

[saluce]

@Sunny_Sid That is not correct. You have to explicitly convert to Enumerable to use the Enumerable.Where.