ADO.NET: DataTables, An Introduction
One of my favorite classes in the .NET Framework is the System.Data.DataTable class. Out of the box, this class is so rich in features, that it is hard to figure out why someone would choose any other class for working with data.
Some of the built-in features include iteration support, data-binding support, error information support, reading to and writing from xml files, and serialization support. Additionally, the DataTable’s Default DataView supports sorting, searching and filtering!
To begin with, an easy way to think about a DataTable is to think about an Excel SpreadSheet. An Excel SpreadSheet can have as many columns as you want, and as many rows as you want – it’s up to you! The columns run from Left-to-Right, while the rows run from Top-to-Bottom.
Looking at the Excel SpreadSheet, you’ll notice the following:
- The Columns are named “A”, “B”, “C”, etc.
- The Rows are numbered “1”, “2”, “3”, etc.
- The SpreadSheet is named “Employees”.
Some additional things you’ll notice is that column “A” is storing First Names, while column “B” is storing Last Names.
Using the DataTable class, we can create a table just like the Excel SpreadSheet above:
Note: The rows are automatically numbered (indexed) in a DataTable, just like they are in an Excel SpreadSheet, so we do not need to number the rows ourselves.
At this point, we have a fully loaded table, like the Excel SpreadSheet. All we need to do now is use a control to view the data in the table. To do that, add a DataGridView to a Windows Form (named DataGridView1), then add the
following line of code just underneath the code above:
One of the first questions you’re sure to ask is, How do you sort the data? Although DataTable’s themselves do not support sorting, the DataTable has a DefaultView property, which returns a DataView that represents a view of the data, and it does! And fortunately, a DataView can be used just like a DataTable, for the most-part:
Another very useful feature of the DataView is the ability to filter the data. Filtering will hide all of the rows that do not meet the filter criteria, and show all of the rows that do. So as an example, we can filter for an employee with the first name of “Gary” and the last name of “Lima”:
Iteration is looping through all of the rows in the DataTable’s or DataView’s Rows collection so you can analyze or process each row on an individual basis.
Here’s an example of how to iterate through the DataTable’s Rows collection. As a note, even though our example above sorted the rows in the DataView, when we loop through the DataTable, the rows will not be sorted.
Here’s an example of how to iterate through the DataView’s Rows collection. As a note, because we sorted the DataView by columns “A” and “B”, when we loop through the DataView, all of the rows will be sorted.
This example demonstrated the simplicity of creating a DataTable, adding columns and rows of data; plus sorting and filtering a DataTable’s data using it's Default DataView, plus how to iterate through a DataTable and a DataView.
I will endeavor to cover both the DataTable and the DataView in a more in-depth look to come…