[2005] DataSets - Best Practice

[Bruce Fox]

What is the best practice in 2005.

I have a single database accessed by a (current) single DataSet tru a TableAdapter etc loading all the data into a DataGrid - all ok.

My question is what is the best practice to use say 3 (additional) queries against that DB?

Should I create a separate DataSet/TableAdapter for each query,

Or

Add the 3 queries to the existing DataSet, and create DataTables for each (query) in code?

[shakti5385]

Pass Query using semicolumn (

"SELECT * FROM TABLE1;SELECT * FROM Table2"

This will return two table in the dataset.

[jmcilhinney]

You should have a DataTable in your DataSet for each table schema that will be returned by a query. Each DataTable will have a corresponding TableAdapter with a default query. If you want to add more queries that return the same schema then you should add a new query to the existing TableAdapter. If you want to add more queries that return different schema then you should add a new TableAdapter.

For instance, if you want to execute these two queries:

SELECT * FROM MyTable

SELECT * FROM MyTable WHERE MyColumn = True

Then you should have one TableAdapter with two queries because both queries return exactly the same schema.

[Bruce Fox]

Hi shakti5385,

But my question relates to best practices. I guess memory/overhead having multiple DataSets if not really required.

I have confussed myself (it ain't hard) as to using a single DataSet and filling DataTables via multiple TableAdapters.

[Bruce Fox]

Then you should have one TableAdapter with two queries because both queries return exactly the same schema.

Hi JM, I have been playing with something along those lines but wasn't too sure if it was the right way.

So If I understand, I can have one DataSet and one TableAdapter, that has an additional 3 queries (in this case) and load independant DataTables via each query as the schema is common, - is that right?

[jmcilhinney]

If you create a DataSet class with one DataTable and one TableAdapter with three queries then you can absolutely use the one TableAdapter to populate three DataTables using the three different queries. Note that each DataTable will be the same type and each DataSet object only contains one instance of the DataTable class. That means that if you want to populate three DataTables you have two choices:

1. Create three DataSet instances and use the TableAdapter's Fill methods to populate the DataTables in those DataSets.
2. Don't create any DataSets at all and use the TableAdapter's GetData methods to return populated, stand-alone DataTables.

[Bruce Fox]

I kinda understand.

I currently have one DataSet that holds all db records and they are displayed in a bound DataGrid - no problem here.

I now want to implement some 'graphing' (using zedGraph) based on Years (as previously posted - thankyou) and Hours.

What I thought was to keep overhead down (an not create seperate DataSets etc), was to simply re-quey the (full) DataSet via a (new) TableAdapter/DataTable to return those (Years and Hours SQL) queries.

[jmcilhinney]

I'm not 100% sure what you're saying. If you have a DataSet with a populated DataTable you can certainly use a TableAdapter to clear and repopulate that table if you're done with the existing data. If you need multiple sets of data though, you need multiple DataTables. In that case you need multiple stand-alone DataTables or multiple DataSets.

[Bruce Fox]

I think were on the same sheet.

I'll try to implement what we have discussed. Thank you once again.