how to query a .Net(2.0) dataTable?

[Guest]

Hello,

I pull sql server data into a .Net (2.0) dataTable (vb2005 app) using
criteria like

CompanyName = '3com%' which will return every variation of '3com' in the
source data like '3com inc', '3com Group'.... The rows all contain a
recordID column. The recordID column in this datatable will contain
non-unique recordIDs. I need to retrieve/extract all distinct/unique
recordIDs contained in this resultant dataTable.

Is there a method/mechanism to perform this operation on my .Net dataTable
from within my application? What is this method/mechanism? Maybe a control
or some object? I looked at the dataview object, but that only perfroms
sorting and filtering.

Or am I limited to have to write an additional query against the source data
in the sql server? THe issue here is that the source data tables are very
large. I would like to keep the number of calls to the server to a minimum.

Ideally, I would like to write a query against the .Net dataTable within the
application.

Select Distinct recordID From myDataTable

Thanks,
Rich

 

[Cowboy \(Gregory A. Beamer\)]

Think this through. Which is more expensive:

1. Multiple small calls on a large database table?
2. One huge call on a large table followed by a bunch of small filters?

If you guessed 2, you are probably right. The number of calls to SQL Server,
which is designed to do the filtering on large tables rather efficiently,
esp. with proper indexing, is not a real issue. If you are having
performance problems, I would look into figuring the real cause rather than
pulling all of the data into memory on another server as a DataTable and
using an inefficient query process.

Can you filter the queries? Sure, with DataViews. But, it is not as wide
open as T-SQL queries, so you cannot do everything you could in SQL Server.
It is also not efficient.

Hey, I understand large tables. They can be a pain. Making sure you have a
query plan helps a lot, as does proper indexing, relationships, etc. If it
is running slow now, it will certainly run slow as a huge chunk of memory
without an index.

Another thing to consider is the large tables in memory have the potential
of getting stale and delivering wrong information.

Look at the issue and figure what the real problem is. Are you bogging down
the database? Can you replicate this data somehow for these types of queries
and use this database read only (great for reporting)? I can't answer the
questions (there are many more) unless I know all of the variables.

--
Gregory A. Beamer

*************************************************
Think Outside the Box!
*************************************************