Coming from a Java/JDBC background, I was reluctant to use strongly typed datasets. For one, as far as I know, there wasn't anything comparably generic with the same level of IDE support. This was around 2000-2001 so things have probably changed in the Java world.
Anyway, beyond being an unfamiliar piece of IDE functionality, I was concerned about memory and computational overhead. Retrieving an entire table just to manipulate a few of its rows struck me as gratuitous. In a web environment with large tables on the back end it wouldn't be too hard to use up all available memory in a few requests if each request filled a dataset from one of the larger tables.
I've been converted. Strongly typed datasets + IDE design support are an awesome combination. They're cool because:
- They reduce typing and typos. The database adapter wizard generates the select/insert/update and delete statements for you.
- They have built in support for parameters.
- Each of the DataAdapter commands is optional. If you only want to insert or update, delete the other commands.
- Strongly typed datasets take advantage of command completion (Intellisense); very handy for assigning values to rows in tables with lots of fields.
- They simplify iterative database design. Changing a column name, adding or dropping columns are a piece of cake; just refresh server explorer, update the XSD (via the XML schema designer), regenerate the dataset and possibly regenerate the DataAdapter and you're set. Beyond that, the compiler will let you know of any values that no longer apply. Without the visual database and dataset tools changes to the database design are a lot more work and error prone (e.g., forgetting to change an insert statement or parameter name).
My work flow so far is:
- Sketch out a rough design of the table structure on paper. This could be done inside Database Diagrams but I'm accustomed to working on paper for this part of the design.
- Use server explorer to create the tables.
- Generate a database diagram with the relevant tables.
- Create a single dataset for each related group of tables by dropping all of the tables on the Schema Designer. Save to generate the dataset (make sure generate dataset is checked).
- I usually manually create the DataRelations but only if I'm going to find it useful to traverse the tables via GetParent() or GetChildRows()...
- Create a separate data adapter for each table within the dataset. Only leave "refresh dataset" option for tables that have an autonumber/identity column.
- I also uncheck "use optimistic concurrency" because I think concurrency should be handled at the app level but this is optional.
- Use the strongly typed New
Row() and AddNew Row() methods to create the rows.
- Fill() and Update() as needed. Be sure to use the right data adapter to update the dataset.