.NET Dataset design

Question

I have some concern about designing Datasets.

I was told that it would be good to have a dataset for each table for maintenance purpose. Because frequent change in database tables is expected, having one dataset including every tables would be pain to make the corresponding change in the application.

Q. is it good approach to have a dataset for each table ( I would end up having 30~40 datasets for each db table using Stored procedure) ?

I have a separate project for commonly used datasets. each project include "dataset project" as a reference, and use it by including needed dataset into Forms, classes, etc.

Q. Does this approach makes the whole system to be slower? If I have a set of datasets as a separate project, it would be beneficial, since It will be easier to make a change (I only need to make modification in one place)

Answer 1

Performance wise having multiple datasets (and multiple queries) is typically slower. However, on the maintenance side, having a single query and dataset per tables is definitely easier to maintain.

It all depends on how related the tables are. If you can use a hierarchical query to get all of the related data and put it in one dataset I would recommend that. If the tables are not related at all the separating the datasets and queries might be the best route.

Answer 2

I find that the best approach is to have one dataset per scenario. The big downside is that your table definition is spread across multiple datasets (if one table is referenced in more than one scenario) which is obviously bad for maintenance if schema changes; however the upsides are that however convoluted the changes you need to make to your data on the client, the dataset can do all the work for you tracking the change in relationships/deletion of children/insertion of children, however many levels of hierarchy you have, and this makes the maintenance of your app as the UI changes.

These are the two main factors for me - finding a trade-off between maintenance with respect changes to the schema, and maintenance with respect changes to the UI. Both one-table-per-dataset and all-tables-in-one-dataset are going to reduce your work in DB maintenance but mean that you have to recode a large amount whenever the requirements of a particular screen change. Finding the right balance for your app is the best plan.