50

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?

Sesame
  • 3,370
  • 18
  • 50
  • 75

8 Answers8

92

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.

saluce
  • 13,035
  • 3
  • 50
  • 67
  • 1
    Excellent Solution, can you please let me know some source where I can get to see many examples of LINQ? – Aditya Bokade Mar 29 '14 at 14:21
  • @Saluce, dtFoo.AsEnumerable().Where(expr) does no need to convert to enumerable as we can use dtFoo.Where(expr) is also already enumerable – Sunny_Sid Jul 14 '15 at 11:53
  • 2
    @Sunny_Sid That is not correct. You have to explicitly convert to `Enumerable` to use the `Enumerable.Where`. – saluce Oct 08 '15 at 13:58
10
int numberOfRecords = DTb.Rows.Count;
int numberOfColumns = DTb.Columns.Count;
Danh
  • 5,916
  • 7
  • 30
  • 45
user7159980
  • 127
  • 1
  • 2
3
int row_count = dt.Rows.Count;
slfan
  • 8,950
  • 115
  • 65
  • 78
Javed Alam
  • 39
  • 1
2
int numberOfRecords = 0;

numberOfRecords = dtFoo.Select().Length;

MessageBox.Show(numberOfRecords.ToString());
Chris Barlow
  • 3,274
  • 4
  • 31
  • 52
user_SS
  • 39
  • 1
  • 2
    @Sesame already described dtFoo.Select approach in question. Besides, the question was about counting by some criteria, not retrieving total number of records. – Alexander Apr 30 '14 at 12:48
2

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;
Cosmin
  • 2,365
  • 2
  • 23
  • 29
  • 1
    How is `int rows = new DataView(dtFoo, "IsActive = 'Y'", "IsActive", DataViewRowState.CurrentRows).Table.Rows.Count;` shorter than `int rows = dtFoo.Select("IsActive = 'Y'").Length;`? – PedroC88 Jul 04 '13 at 05:15
  • @PedroC88: It's not; it's shorter than the original poster's methods. – Cosmin Jul 04 '13 at 09:42
1
object count =dtFoo.Compute("count(IsActive)", "IsActive='Y'");
Zohaib Iqbal
  • 263
  • 1
  • 4
  • 6
1

Try this

int numberOfRecords = dtFoo.Select("IsActive = 'Y'").Count<DataRow>();    
Console.WriteLine("Count: " + numberOfRecords.ToString());
Karthick Prakash
  • 11
  • 3
0

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.

Marcus
  • 2,470
  • 1
  • 22
  • 29