Most .NET developers are familiar with the often overused DataSet objects. The DataTable
object receives much less attention, which is unfortunate since DataTable objects provide plenty of features that are more
appropriate in many development scenarios—especially when performance is a
concern. In today’s column, I take a closer look at utilizing a DataTable in your .NET projects.

Know your options

A DataTable is a .NET Framework class that represents a single database table; a DataSet contains the entire results of a SQL query so it may contain numerous tables. If you’re working with a
DataSet object, you may access one of its tables via
a DataTable object. On the other hand, you may avoid
the DataSet object and rely solely on a DataTable to work with a single table and its columns. You
can query, sort, and perform calculations on it.

While you can use DataSet and DataTable objects in the same situation, one of the biggest
selling points of using DataTable over DataSet is performance. A DataSet
is a rather large object placed side by side with its DataTable
counterpart. (It is worth noting that the DataReader
object is the ultimate resource-friendly data object, but it is not always
applicable.)

Creating a DataTable

Developers often work with tables via DataSet
objects, and you can access the DataTable objects
contained with the DataSet. In these cases, there is
no need to create new DataTable instances. In fact,
when working with data in a DataSet, you are
accessing its default DataTable object. When a DataSet is populated from a database, the DataTable is created with proper schema and data. On the
other hand, you may explicitly create a DataTable
object via a database query or using your own schema.

Weekly .NET tips in your inbox

TechRepublic’s free .NET newsletter, delivered each Wednesday, contains useful tips and coding examples on topics such as Web services, ASP.NET, ADO.NET, and Visual Studio .NET.

Automatically sign up today!

A DataTable object contains
everything a database table contains: columns, rows, and so forth. It has a
Columns property that is comprised of DataColumn
objects. They specify the name and type of the columns. Also, the Rows property
contains a set of DataRow objects. Each DataRow object has a value for each of the DataTable’s columns.

There are two ways to create a DataTable
object. First, you can create and populate a DataTable
object with a SQL Server query. This is the approach often used to create the DataTable objects in a DataSet.
Also, you may create a DataTable object statically by
specifying its structure and adding data via code. This approach is often used
when prototyping an application, but it may be applicable in other situations
depending on the project.

In this article, we focus on creating and populating DataTable objects via a SQL Server query. It’s very simple
to populate a DataTable. It is similar to working
with a DataSet, where a data adapter object is used
to populate it. Follow these steps to create and populate DataTable
objects via a SQL Server query:

  1. Establish
    a database connection.
  2. Create
    the command object using a SQL Server query and connection.
  3. A
    data adapter is created.
  4. The DataTable object is created.
  5. The DataTable is populated with the data adapter object.

Once the DataTable is created, it
contains the results of the SQL Server query. The C# code in Listing A follows these steps to create
and populate a DataTable object. Once the DataTable is created, a DataRowCollection
object is used to work with the rows of data returned by the query. (Listing B contains the equivalent
VB.NET code.)

You’ll notice that the results are accessed via index values
of the DataRowCollection, while individual columns
are accessed by their name (or you may use index values that begin at zero).
The individual elements of the DataRowCollection are
used to display an individual employee name and their title.

Note that you can use the DataTable
object in databinding, which means that you may use
it as the source for a DataGrid, Repeater, or other
data object. The example in Listing C
uses a DataTable as the source for a simple DataGrid object. (Listing D contains the equivalent VB.NET.)

Another great feature of the DataTable
class is that you have the ability to work with its data. A good example of
this is the Select method, which allows you to pick individual rows and columns
of data. The example in Listing E
changes the previous example and displays only the rows returned via the Select
method. (Listing F contains the
equivalent in VB.NET.)

The Select method returns an array of DataRow
objects that match the selection criteria. It has four signatures with no
parameters returning all rows of data. You may pass the selection statement and
include a sort column value and state value as well.

The Select method is just one of the many methods and
properties available in the DataTable class. The
exhaustive list is beyond the scope of this article, so take a peek at its
documentation for more information.

An alternative way to work with data

Data is an important aspect of every application. The .NET
Framework provides several ways to access and work with data in an application.
There are various data objects with the DataSet that
receive most of the attention, but other objects like the DataTable
are useful in many situations. The DataTable allows
you to work with single tables and requires much less overhead than its DataSet counterpart. Keep the DataTable
in mind during your next project.

Tony Patton began his professional career as an application developer earning Java, VB, Lotus, and XML certifications to bolster his knowledge.

Miss a column?

Check out the .NET Archive, and catch up on the most recent editions of Tony Patton’s column.