ADO.NET - Working with Disconnected Data

Why should you use disconnected data? Disconnected data can be used to communicate between distributed applications or web services. Like connected data, disconnected data supports multiple tables and data binding. But disconnected data also makes it possible to navigate forward and backwards in a resultset and manipulate xml.

DataSet classes

The DataSet class is a disconnected data access class. In fact it implements a database that only exists in the memory and is loose from any other data source. The tables with columns, rows and constratins can be created manually. Using the DataAdapter it is possible to pull data out of one multiple databases and to restore it with optimistic locking. The data and the structure from the database implemented by the DataSet class can be written to or read from xml documents and schemes.

The tables in a dataset are implemented by the DataTable class. This class has Rows, Columns and Constraints properties which consist out of a collection with respectively DataRow, DataColumn and DataConstraint objects. The DataColumn can be seen and implemented as it was a normal database column. It supports all column specifics like AllowDBNull, AutoIncrement, Unique, ...
The DataRow has two special properties, the first one is called RowState. This property keeps the state since the last call of the AcceptChanges method. Possible states are Detached (created, but not in table), Added, Unchanged, Modified and Deleted. The second property is DataRowVersion which tracks different versions of each DataRow. The possible versions are Current, Default, Original and Proposed.

Like with databases you're able to define views on certain tables. This is done with the DataView class. Normal manipulations like filtering data with the where clause or sorting can be done on these views.

DataAdapter

A DataSet never has a live connection with a data source. The DataAdapter connects with the data source, executes the query and places the result in a DataTable or updates a changed DataTable to the data source. Executing an sql command is done with the Fill function, updating changes to the data source is done with the Update function. You don't need an open connection to use the DataAdapter, the Fill and Update function open the connection, manipulate the data and closes the connection again.

Typed DataSet

A Typed DataSet can be seen as a databaseobject that fits in the object model of a .NET project. It generates an objectmodel in which tables and columns are strongly typed class objects extended on the ADO.NET disconnected classes (DataSet, DataTable, DataRow, ...).

To create a new Typed DataSet, right click in your Solution Explorer > Add new Item > DataSet. A TableAdapter Configuration Wizard helps you to set up everything. It's possible to create the sql select statement with a query builder and to generate all the other queries. Custom queries can be add to the designer by right clicking. The generated methods can also be changed if needed. Note that the wizard has troubles to generated the other queries if you use a join in your select query. It might be easier to write a normal select query, generate the other queries and then change the select query to add your join.

A Typed DataSet can contain several tables. This is done by adding a TableAdapter for every table you want to add to the DataSet.

It's possible to rename the Fill and Return methods. It might be easier to use your own names than the premade Fill and GetData.

The best way to connect your DataSet and its TableAdapters to your interface is with using a Facade class. To be able to use the TableAdapters you'll have to add them on top with 'using nameoftablesetTableAdapters'. It might be that your IntelliSense can't find the namespace, in this case build your project. It's also a good idea to cache your DataTable to have access to the data over several page calls. It wouldn't be really useful to use DataSets if you don't use it's strengths to be able to keep the data stored without having a connection to the database.

The Typed DataSet is converted to C# code at compile time. To have a look at this generated code open Class View or Object Browser and drill down to the adapters. The generated code can be overridden by using a Partial class.

This tutorial is written by Assesino.

Tutorial toolbar:  Tell A Friend  |  Add to favorites  |  Feedback  |