What is the proper way to use a .NET DataSet? [closed]

Question

I'm trying to figure out the correct way to use .NET DataSets. Are you supposed to create a DataTable for every table in your database and create relationships in the DataSet? It seems crazy to me to reconstruct your entire database structure in the DataSet.

I'll use a simplified example of my database. Lets say I have a CompanyEquipment table. Each piece of equipment belongs to a cost center, and the cost center data is stored in the CostCenter table and is related to the CompanyEquipment table. Lets say I have a stored procedure that returns CompanyEquipment data already joined with the CostCenter table to return all the info in one query. I could use the stored procedure to populate a DataTable, but how would this de-normalized result table be saved back to the database? I don't know if this is a good way to do it. Querying many-to-many relationships that make use of association (bridge) tables would require multiple queries anyway I think. For example, I have an EquipmentRole table, that stores different equipment usages, and an EquipmentRoleAssignment table where multiple roles can be assigned to pieces of equipment.

Alternatively, if I have DataTables that mimic the database, I would have to query each table separately using DataAdapter.Fill() on each table to put the related data in each DataTable. So I would have something like select * from CompanyEquipment where equipID = 1234 and I would use that with an adapter to fill a CompanyEquipment DataTable, and then I would have to use another query select * from CostCenter where costCenterID = <costcenterID from the companyequipment record> and fill the CostCenter DataTable. With this approach I have to hit the database twice, and I lose the power of the DBMS to perform joins.

I'm new to data driven applications, so I'm not familiar with the workflow; especially how many times I should hit the database to get one representation of data. Any help is appreciated. I'm working in C#.

Answer 1

Not at all? I have not been using DataSets since .NET 1.0. Use an ORM, game over. DataSets have zero real use outside of reporting applications that let the user put together their queries fully dynamically, and even in that situation there are better solutions most of the time.

Answer 2

I have used Datasets in the past (way long ago though) and this is my 0.02$ based on my experience

Both your option 1 as well as Option 2 are valid scenarios as to how datasets could be used.

For Retrieval Scenarios :- It depends on how you are using the data.

For example, in a scenario where you know that all the main table + related table data is being for eg displayed to the user immediately, option 1 is probably preferable.

However, in a scenario, where you might show a table with main table data and only on click of a row, show the related table information, it might be preferable to go with option 2 instead as it allows you to get the data as requested.

For Insert / Update Scenarios

Option 2 works best in such scenarios as your in-memory structure mimics your database. So, using an adapter or even the commandbuilder objects and the datarow state (Inserted / Updated / Deleted), you could easily insert / update / delete the appropriate data without too much hassle. If you were to use Option 1, you will need to write the logic to split it up and save into the separate tables yourself.

Hope this is useful. However, like most real life scenarios, it really depends

Note: I totally avoid datasets / datatables and i have for a while due to them being quite heavy objects and hence tend to be a bit of a drain on resources. If you do use them, make sure you test for performance - based on your performance requirements to ensure that they dont end up being unsuitable in the long term