In the previous post, we discovered the connected layer of ADO.NET, namely the Command, Connection, DataReader classes. In this post, I’d like to review the disconnected layer of ADO.NET. The main classes are the DataSet and the various DataAdapter classes in this layer. But when should we use this one?
As noted previously, database connections are probably the most expensive resources in a given application. Therefore, it’s a good idea to use a limited number of queries for retrieving large pieces of data, than use numerous queries for little amount of data. The disconnected layer is built upon this theory. Large pieces of data (in fact, even a whole database) can be pulled to the client, then processed without the requirement of an open connection to the database. When the processing of data is completed on the client, it can send back the changes committed, and the database can be updated with these changes. Now let’s see the class that provides all of these features:
DataSet
The DataSet contains two main collections: tables and relations between them. Tables (unless you work with a Typed DataSet mentioned later) are DataTable objects, which can contain DataColumns and DataRows, Constraints and children DataRelations. I wouldn’t like to waste time on these self-explaining classes, let’s focus on the main methods of the DataSet, and on how to fill it with the required data.
- GetXml and GetXmlSchema: returns the data contained in the DataSet in a string in XML format, or the schema of the DataSet (relations, tables, types, etc.) in XML too.
- WriteXml and WriteXmlSchema: writes the data or the schema to an external XML file.
- ReadXml and ReadXmlSchema: create the data or the schema of the DataSet from an internal string or an XML file.
- Clear: clears all data from the tables, but persists the schema.
- Copy: copies the whole DataSet.
- Clone: creates an empty DataSet with the exact same schema.
- Merge: takes another DataSet, DataTable or collection of DataRow object and merges them into the current DataSet.
Now we have seen what are the most important methods in a DataSet, let’s see it how to get the data, and how to write it back to the database.
DataAdapter
DataAdapters are provider specific classes, which serves as a bridge between a single DataTable object in the DataSet and the data source. To enable selection, inserting, editing and deleting, you should set the appropriate commands of the DataAdapter. The most important methods of the DataAdapter are as follows:
- Fill: adds a DataTable to the DataSet by executing the SelectCommand of the DataAdapter.
- FillSchema: adds a DataTable to a DataSet by executing the SelectCommand of the DataAdapter, but builds only the schema of the table, no data will be added.
- Update: updates the data source with the modified data in the current DataTable.
To fill a DataSet, use the following code:
SqlDataAdapter dataAdapter = new SqlDataAdapter(sqlCommandString, sqlConnectionObject);
DataSet ds = new DataSet();
dataAdapter.Fill(ds, “TableName”);
That’s all. If you need to specify further commands, use the appropriate property of the DataAdapter, such as InsertCommand, UpdateCommand, etc.
To search for specific rows in a given DataTable, you should use the DataTable’s Select method, with something similar to the Where clause of a SQL string, like this:
DataRow[] rows = ds.Tables[“Products”].Select(“ProductID=3);
A considerable choice when working with DataSets is the DataView class. As its name suggests, a DataView provides a way to view into a collection of data. You can even filter and sort this view.
By default, each DataTable exposes a single DataView object, which you can reach through the DataTable.DefaultView property. To create a DataView, simply pass in the DataTable object to its constructor with which you’d like to associate the DataView:
DataView dw = new DataView(ds.Tables[“MyTable”]);
To sort the data in the DataView, call the Sort method of it. You can specify a column name, or a comma-delimited array of column names, as long as they can be sorted (for example, you can’t sort binary data). It’s a common practice to bind DataViews to rich data controls, such as the GridView.
To filter the data seen in a DataView, you use the RowFilter property just as the same way as if you’d write a WHERE clause (without writing the word WHERE itself).
Typed DataSet
Using Visual Studio, you have the ability to build strongly typed DataSets. This has two main benefits:
- Speed: as working with a hard-coded schema, you can notice a slight performance increase. This is due to the fact that the DataSet won’t have to set up itself with the schema it requested from the data source at runtime.
- Coding: as you access the table names and field values using strongly typed property names, you will be notified from your possible typos at compile time, not at runtime. You gain IntelliSense support, too.
Creating a typed DataSet is relatively easy. Maintaining it is not. You should be aware of that the easiest way of updating a typed DataSet to reflect even minor changes in the data source is to completely rewrite it from scratch. This said, see how to write it:
The easiest way to create a strongly typed DataSet is to use Visual Studio’s Server Explorer tab. Simply create a DataSet object with the Add New Item wizard, then drag and drop the tables from Server Explorer to the design surface, and you’re done.
You can also create queries, TableAdapters, etc. in the design surface. When you are creating a table within a typed DataSet, you will notice that a strongly typed DataTable, and a strongly typed TableAdapter is generated. You can use the TableAdapter just as you would use any DataAdapter to fill or update data.
Further Readings
Gergely Koncz's blog 