DbTable Objects in ADO.NET v2.0

In the previous post I talked about the factory classes in ADO.NET that support writing provider independent code. This was one addition to ADO.NET that was identified quite early by developers and led to the creation of custom classes to perform this functionality. Another area that developers quickly identified after the release of ADO.NET v1.0 was the bulkiness of the DataSet/DataAdapter interaction. Developers wondered why you needed to create an entire DataSet when often you only needed to return data in a single table and why individual DataTable objects couldn’t be serialized to XML. In addition the DataAdapter seemed a bit bulky requiring the commands to be explicitly configured.

Microsoft seems to have listened to this kind of feedback and added a DbTable base class and derived classes for each .NET Data Provider – SqlDataTable, OleDbDataTable, OdbcDataTable, and OracleDataTable. Each of these classes combines the best aspects of the DataSet and DataAdapter into a single object. For example, it contains Fill and Update methods like a DataAdapter (and the new UpdateBatchSize property that if supported by the provider allows changes to be batched instead of sent as individual requests to the database) but derives from the DataTable class while exposing the ReadXml and WriteXml methods like a DataSet.

For example, the constructor of a SqlDataTable accepts a command object and can fill itself based on the command you send it.

SqlDataTable sqldt = new SqlDataTable(cmd);
sqldt.Fill (FillOptions.None);

One of the most interesting aspects is that if you provide a table name to the object it can dynamically generate the update, delete, and insert SQL statements when the Update method is called (assuming the SQL statement or stored procedure is simple enough).

sqldt.TableName = "Products";
sqldt.Rows[0][1] = "Change the data";
sqldt.Update (UpdateOptions.None);
sqldt.WriteXml(Console.Out); //serialize to XML

You can also hook a DbTable to a child table and have it fill the child table as well using the FillOptions.FillChildren option.

The inclusion of this object is a productivity enhancement for developers building applications that work with single result sets.