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:
'Create a new DataTable, and name it "Employees"
Dim table As New DataTable("Employees")
'Add columns "A" and "B" to the table
table.Columns.Add("A")
table.Columns.Add("B")
'Add rows to the table, and populate the data:
'Declare a variable to store a reference to a
' new row when created
Dim row As DataRow
'Use the NewRow method of the DataTable to create
' a new row that has the same schema as the
' DataTable (all of the columns that are in the
' table will be in it)
'
' Note: Creating a new row does NOT add it to
' the table automatically.
'
row = table.NewRow()
row.Item("A") = "Bob"
row.Item("B") = "Jones"
table.Rows.Add(row)
'Add a new row by passing an object array with
' the values for each of the columns
table.Rows.Add(New Object() {"Mike", "Evans"})
'Add a new row by supplying an object array,
' without specifying "New Object()", and pass
' the values for each of the columns.
table.Rows.Add("Tom", "Messner")
'Add a new row using the DataTable's LoadDataRow
' method.
'
' Note: The parameter of False here causes
' RowState of the new row to be set to "Added",
' just like when the Table.Rows.Add method is
' used.
table.LoadDataRow(New Object() {"Kim", "Pham"}, _
False)
'Add a new row using the DataTable's InsertAt
' method and pass it the index to insert the
' row at (in this case we are adding it to
' the very end of the Rows collection).
row = table.NewRow()
row.ItemArray = {"Gary", "Lima"}
table.Rows.InsertAt(row, table.Rows.Count)
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:
DataGridView1.DataSource = table
If we performed the above step correctly, when we run our program, our DataGridView should look like the following:
Sorting
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:
'Create a variable and get the reference to the
' table's Default DataView
Dim view As DataView = table.DefaultView
'Sort the data by column "A", then column "B"
view.Sort = "A ASC, B ASC"
'Use the DataView just like the DataTable
DataGridView1.DataSource = view
After sorting has been applied to the DataView, we will find the following results when we run our program:
Filtering
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”:
'Create a variable and get the reference to the
' table's Default DataView
Dim view As DataView = table.DefaultView
'Filter the data for a first name of "Gary" and a
' last name of "Lima"
view.RowFilter = "A='Gary' AND B='Lima'"
'Use the DataView just like the DataTable
DataGridView1.DataSource = view
After filtering has been applied to the DataView, we will find the following results when we run our program:
Iteration
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.
'Declare a variable to store a reference to each
' row as we process it.
Dim row As DataRow = Nothing
'Loop through the DataTable's Rows collection
For i As Integer = 0 To table.Rows.Count - 1
'Get a reference to the current row
row = table.Rows(i)
'Write the data to the Output window
Debug.WriteLine("{0} {1}", _
row.Item("A"), row.Item("B"))
Next
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.
'Declare a variable to store a reference to each row
' as we process it.
'
' NOTE: The DataRowView declaration
' (Not DataRow as above)
Dim rowView As DataRowView = Nothing
'Loop through the DataView's Rows collection
For i As Integer = 0 To view.Count - 1
'Get a reference to the current row
rowView = view(i)
'Write the data to the Output window
Debug.WriteLine("{0} {1}", _
rowView.Item("A"), rowView.Item("B"))
Next
Conclusion
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…
0 comments: