Count rows in dataset WHERE ...

  • Thread starter Thread starter Assimalyst
  • Start date Start date
A

Assimalyst

Hi,

I am attempting to do a number of count functions on a single table of
data for presentation in an ASP webpage. The functions are number
today, number this week, number this month etc. As the Database table
is large it does not make sense to query it directly a number of times.

My idea is to create a dataset containing the table "ApplicationForms".
I then want to query this and perform the various count functions.

If I were querying the database the query would be:

SELECT COUNT(DISTINCT fkiBatchNumber) AS 'YearCount'
FROM tblApplicationForms
WHERE (dtDateScanned < dtYear)

dtToday is a datetime predefined in C# code.

I have no idea how to query the dataset in an equivilent manner.

Is this the most suitable method of acheiving my requirement? If so,
any help in doing so is much appreciated.

Thanks.
 
You can use the Select method of a particular datatable to select the rows
matching your where clause. This will return an array of rows matching the
criteria, at which point you can check the Length of that array to see how
many rows matched.

However, datatables and datasets are not in-memory databases, and you should
not attempt to use them as such. You are not going to be able to do most
things you can do in a database query, and it is certainly going to be far
less efficient then a SQL engine that has query optimization, various types
of scans, and so on.
 
Thanks Marina,

I have tried this:

string sqlBatchCountYear = "SELECT COUNT(DISTINCT fkiBatchNumber) AS
'YearCount' " + "FROM ApplicationForms " +
"WHERE (dtDateScanned < " + dtYear + ")";

int countYear =
int.Parse(dsApplications.Tables["Applications"].Select(sqlBatchCountYear).ToString());

But i'm getting a runtime error "Missing operand after 'COUNT'
operator". Any idea, where i'm going wrong? I have tried it with query
analyser, and it runs fine there.

Thanks again.
 
As I mentioned, you can only pass the WHERE clause to Select. Please look up
the documentation for this method.

This is very important: You cannot run SQL queries on a dataset or a
datatable.

As I said in my first post, a datatable is not an in memory database. It has
a very limited set of operations you can do that can mimic some of the
database like behaviors, but it's not even close.
 
Assimalyst,

Be aware that a dataset is a wrapper around datatables and datarelation.

Do you need a count of selected records in a table than you have plenty of
possibilities.

I am mostly lazy and take the rowfilter.

Not tested dates is always a terrible job to do.
\\\\
dt.rowfilter = "MyDate > #01-01-2004# AND < #01-01-2006#";
int myCoung = dt.defaultview.count();
////

But there are more solutiong for that.

I hoe this helps

Cor
 
Back
Top