ADO.Net, DataAdapters & DataSets: What are they?

ADO.Net is Microsoft's .Net interface to databases. Traditionally, to work with databases like Access, SQL Server and the like, you needed to know a fair bit about SQL. With ADO.Net you still need to know SQL commands but some pretty near classes are provided that allow you to hive off the SQL stuff and work with a much easier set of objects when adding, deleting or updating items in a database.

The two major classes described in this post are the DataAdapter and DataSet classes. It wasn't until I'd actually coded some example that the ease of use of these classes became clear to me. I'll expand on the classes in later posts and give some code examples but for now I'm just going to give an overview of their purpose. This picture gives an idea of how the classes interact to allow you (the programmer/ user) to work with a database.

The four items in the picture are:-

The Database

This is something like an Access or SQL Server database. ADO.Net provides classes to handle many different types of database. All the classes inherit from a set of base classes so to a degree you can hide the details of the specific database type from your code.

The DataAdapter

The DataAdapter class is the SQL workhorse. There are a number of different DataAdapter classes for different databases, e.g. OldDataAdapter for working with an Access database. It's the DataAdapter class that does all the work - reading, inserting, deleting and updating - in interacting with the database. All you have to do is build the SQL commands for the DataAdapter to do the work and then let it get on with it's job.

The DataSet

The DataSet is the class you interact with when manipulating data values in the database. The data within a table in the actual database can be thought of as a collection of rows, with each row containing a number of named field values. The DataSet mirrors this view of the database. The DataSet is a collection of DataRow objects, with each DataRow begin a Dictionary of the values in the row where the dictionary keys are the field names.

How do the classes interact?

To work with the data, you configure a DataAdapter instance with the SQL commands to read data, insert, update and delete data in the database. You then ask the DataAdapter to fill a DataSet. You can then change values in rows in the DataSet, add new rows or delete rows. When you've made the changes, you ask the DataSet to get the DataAdapter to reflect your changes into the actual database.

In my next post, I'll give some code examples using the DataSet and DataAdapter classes.