Recipe 13.8. Creating In-Memory Data Tables Manually

Problem

You want to manage some data in a database-table-like fashion, but the source data is not coming from a database, or from anything that looks like a table.

Solution

Build a DataTable manually, and fill in all the table details and data yourself.

Discussion

The following code builds a simple table of state information and adds two records:

 Dim stateTable As DataTable = New DataTable("UnitedStates") ' ----- Use the abbreviation as the primary key. Dim priKeyCol(0) As Data.DataColumn priKeyCol(0) = stateTable.Columns.Add("ShortName", GetType(String)) stateTable.PrimaryKey = priKeyCol ' ----- Add other data columns. stateTable.Columns.Add("FullName", GetType(String)) stateTable.Columns.Add("Admission", GetType(Date)) stateTable.Columns.Add("Population", GetType(Long)) ' ----- Add a record. Dim stateInfo As Data.DataRow = stateTable.NewRow() stateInfo!ShortName = "WA" stateInfo!FullName = "Washington" stateInfo!Admission = #11/11/1889# stateInfo!Population = 5894121 stateTable.Rows.Add(stateInfo) ' ----- Add another record. stateInfo = stateTable.NewRow() stateInfo!ShortName = "MT" stateInfo!FullName = "Montana" stateInfo!Admission = #11/8/1889# stateInfo!Population = 902195 stateTable.Rows.Add(stateInfo) ' ----- Prove that the data arrived. MsgBox(stateTable.Rows.Count)       ' Displays "2" MsgBox(stateTable.Rows(0)!FullName) ' Displays "Washington"

ADO.NET defines the basic structures for tables, columns (fields), and rows (records), and it's pretty easy to use them to build your own tables by hand. To create a table, simply create a DataTable object:

 Dim stateTable As DataTable = New DataTable("UnitedStates")

The table isn't of much use yet because it doesn't have any column definitions, but they are simple to add as well. Columns, at their most basic structure, are composed of a name and a data type. While the columns in your database may be limited to just a few basic data types, ADO.NET table columns can be defined using any data type or class that you can build in .NET. You could even store an entire DataSet object in a column of a DataTable record, although that would be a little strange. To add a column, use the table's Columns.Add() method:

 stateTable.Columns.Add("FullName", GetType(String))

To add a record, use the table's NewRow() method, which generates a DataRow object. You could create a new DataRow from scratch, but it wouldn't have any of the column definitions already added to the DataTable. Using NewRow() takes care of setting up that structure for you. Once you've updated each record field, add it to the table using the table's Rows.Add() method:

 stateTable.Rows.Add(stateInfo)

Once your records are in your table, you can manipulate them just like you would in a real database table. For instance, you can sort the records based on one of the fields using the Select() method:

 ' ----- Process an array of states sorted by name. For Each stateInfo In stateTable.Select("", "FullName")    MsgBox(stateInfo!FullName) Next stateInfo

If you want to go all the way and add your table to a DataSet object, use code similar to the following:

 Dim fullDataSet As New Data.DataSet fullDataSet.Tables.Add(stateTable)

Problem

Solution

Discussion

See Also