drilling into filled data adapter

[Brad Allison]

Okay, probably another simple question for you experts.

I have filled a data adapter with a dataset using a parametized query. This
brings down records from a table that includes events spanning three years.
I now want to further "drill" into this data adapter table to perhaps show
only the records that pertain to this current year or show only those for
2005.

Being fairly new with ADO, what do I have to do to "query a query"? I
figure it is a matter of setting up another selectCommand, but what table do
I use? Is it from the dataSet or the dataAdapter? Or how would I change
the selectCommand at run-time?

Thanks for the information.

Brad

 

[Brad Allison]

By the way, this is using OLE DB.

 

[William Ryan eMVP]

Brad:

Excellent question. I'm going to assume that the drilling down entails
looking only at records that are already encompassed in the first query. Is
that correct? (I'm going to assume it is for my answer, but if it's not,
let me know what you want to do and the scenario and I'll do my best to
answer it).

I have a full discussion of this subject here (there are also a few others
articles on advanced filtering which you might find interesting, just seach
on the site for DataView)
http://www.knowdotnet.com/articles/dataviews1.html
http://www.knowdotnet.com/articles/expressions.html
http://www.knowdotnet.com/articles/adopartiii.html
http://www.knowdotnet.com/articles/advancedrowfilter.html

If so, there are a few ways to handle it. A datatable has certain methods,
..Select being one that comes to mind, for getting subsets of records that
already exist in the datatable, without having to requery the database.
There is also the ability to create a dataview on the datatable which is
very easy and provides advanced filtering and finding. There's also a
..Compute method which will allow you to run aggregate functions on your
existing data , so you can use SUM, COUNT, AVERAGE etc without having to
requery your database and without having to loop through your datatable.

To create a dataview, you only need one line of code:
C#
DataView dv = someTable.DefaultView;
VB.NET
Dim dv as DataView= someTable.DefaultView

If you have the table in a dataset, you do the same thing, just reference
the dataset's table's collection... myDataSet.Tables[0].DefaultView or
myDataSet.Tables["SomeName"].DefaultView

Now you can use the .RowFilter, .Find , .Sort etc.

So lets say that you want to show only the rows of your table (assume you
have a firstName column) where the customer's last name is Smith ...
myDataView.RowFilter = "LastName == 'Smith'"

I have full examples of doing this in the articles I referenced above
including how to iterate through a dataview. I've used many different
sources to write the discussion above so I think you'll find just about
every sorting and filtering scenario covered but if you don't, just let me
know.

Bill

 

[Brad Allison]

William,

Thank you! This is just what I needed and creating a dataview from the
current dataset and then filtering works just great.

Thanks again,

Brad

Brad:

Excellent question. I'm going to assume that the drilling down entails
looking only at records that are already encompassed in the first query. Is
that correct? (I'm going to assume it is for my answer, but if it's not,
let me know what you want to do and the scenario and I'll do my best to
answer it).

I have a full discussion of this subject here (there are also a few others
articles on advanced filtering which you might find interesting, just seach
on the site for DataView)
http://www.knowdotnet.com/articles/dataviews1.html
http://www.knowdotnet.com/articles/expressions.html
http://www.knowdotnet.com/articles/adopartiii.html
http://www.knowdotnet.com/articles/advancedrowfilter.html

If so, there are a few ways to handle it. A datatable has certain methods,
.Select being one that comes to mind, for getting subsets of records that
already exist in the datatable, without having to requery the database.
There is also the ability to create a dataview on the datatable which is
very easy and provides advanced filtering and finding. There's also a
.Compute method which will allow you to run aggregate functions on your
existing data , so you can use SUM, COUNT, AVERAGE etc without having to
requery your database and without having to loop through your datatable.

To create a dataview, you only need one line of code:
C#
DataView dv = someTable.DefaultView;
VB.NET
Dim dv as DataView= someTable.DefaultView

If you have the table in a dataset, you do the same thing, just reference
the dataset's table's collection... myDataSet.Tables[0].DefaultView or
myDataSet.Tables["SomeName"].DefaultView

Now you can use the .RowFilter, .Find , .Sort etc.

So lets say that you want to show only the rows of your table (assume you
have a firstName column) where the customer's last name is Smith ...
myDataView.RowFilter = "LastName == 'Smith'"

I have full examples of doing this in the articles I referenced above
including how to iterate through a dataview. I've used many different
sources to write the discussion above so I think you'll find just about
every sorting and filtering scenario covered but if you don't, just let me
know.

Bill

Okay, probably another simple question for you experts.

I have filled a data adapter with a dataset using a parametized query. This
brings down records from a table that includes events spanning three years.
I now want to further "drill" into this data adapter table to perhaps show
only the records that pertain to this current year or show only those for
2005.

Being fairly new with ADO, what do I have to do to "query a query"? I
figure it is a matter of setting up another selectCommand, but what

table

do
I use? Is it from the dataSet or the dataAdapter? Or how would I change
the selectCommand at run-time?

Thanks for the information.

Brad

 

[William Ryan eMVP]

Glad it worked!

William,

Thank you! This is just what I needed and creating a dataview from the
current dataset and then filtering works just great.

Thanks again,

Brad

Brad:

Excellent question. I'm going to assume that the drilling down entails
looking only at records that are already encompassed in the first query. Is
that correct? (I'm going to assume it is for my answer, but if it's not,
let me know what you want to do and the scenario and I'll do my best to
answer it).

I have a full discussion of this subject here (there are also a few others
articles on advanced filtering which you might find interesting, just seach
on the site for DataView)
http://www.knowdotnet.com/articles/dataviews1.html
http://www.knowdotnet.com/articles/expressions.html
http://www.knowdotnet.com/articles/adopartiii.html
http://www.knowdotnet.com/articles/advancedrowfilter.html

If so, there are a few ways to handle it. A datatable has certain methods,
.Select being one that comes to mind, for getting subsets of records that
already exist in the datatable, without having to requery the database.
There is also the ability to create a dataview on the datatable which is
very easy and provides advanced filtering and finding. There's also a
.Compute method which will allow you to run aggregate functions on your
existing data , so you can use SUM, COUNT, AVERAGE etc without having to
requery your database and without having to loop through your datatable.

To create a dataview, you only need one line of code:
C#
DataView dv = someTable.DefaultView;
VB.NET
Dim dv as DataView= someTable.DefaultView

If you have the table in a dataset, you do the same thing, just reference
the dataset's table's collection... myDataSet.Tables[0].DefaultView or
myDataSet.Tables["SomeName"].DefaultView

Now you can use the .RowFilter, .Find , .Sort etc.

So lets say that you want to show only the rows of your table (assume you
have a firstName column) where the customer's last name is Smith ...
myDataView.RowFilter = "LastName == 'Smith'"

I have full examples of doing this in the articles I referenced above
including how to iterate through a dataview. I've used many different
sources to write the discussion above so I think you'll find just about
every sorting and filtering scenario covered but if you don't, just let me
know.

Bill

Okay, probably another simple question for you experts.

I have filled a data adapter with a dataset using a parametized query. This
brings down records from a table that includes events spanning three years.
I now want to further "drill" into this data adapter table to perhaps show
only the records that pertain to this current year or show only those for
2005.

Being fairly new with ADO, what do I have to do to "query a query"? I
figure it is a matter of setting up another selectCommand, but what

table

do
I use? Is it from the dataSet or the dataAdapter? Or how would I change
the selectCommand at run-time?

Thanks for the information.

Brad