When used together, providers and data sets give an end-to-end interface to individual data values, from the fields in your database tables to the in-memory items of a
DataRow record. Figure 10-1 shows this object interaction.
When you interact with data from an external database, you always use the provider classes, but it's up to you whether you want to also use data sets. There are pros and cons of both methods, some of which appear in Table 10-1.
Table 10-1. The pros and cons of using data sets
Without data sets
With data sets
You must supply all SQL statements, in the format the provider expects. This is true for all
Data retrieved through the
Data read from the database can be modified in-memory, and updated as a batch with a single method call.
Data transfers are very efficient, since no overhead is needed to move data into a complex data set structure.
There may be a performance hit as the data set builds the necessary objects required for each transferred record.
Memory allocation is limited to a single record's worth of data fields, plus some minimal overhead.
Memory allocation is required for the entire result set, plus overhead for every table, field, column, and row in the result set.
Only a single