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.
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:
- Establish a database connection.
- Create the command object using a SQL Server query and connection.
- A data adapter is created.
- The DataTable object is created.
- 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.
Tony Patton has worn many hats over his 15+ years in the IT industry while witnessing many technologies come and go. He currently focuses on .NET and Web Development while trying to grasp the many facets of supporting such technologies in a production environment on a daily basis.