Use Run-Time-Created DataTables in Place of Collections

Provided by Jim Ferguson, Project Manager

Microsoft's .NET framework includes a variety of built-in collection objects such as the HashTable and ArrayList objects. These collection objects may be used as-is, or may be inherited from to create other collections, including strongly-typed collections of specific object types.

In some cases writing a fully formed collection class as a container for a custom object is overkill.

Consider using the built-in DataTable object for your collections. It lets you design the number of columns and their data types at run-time, and you are not limited to a single key/value pair. You can have as many columns as necessary, including calculated columns. The DataTable has built-in sorting and filtering functionality, and may be easily bound to such user-interface objects as lists and grids. It can also be easily serialized for use in web Session state, or persisted as XML data.

Creating the custom DataTable is as simple as adding the desired columns.

Consider this example of a table used on a web site to collect user comments:

Dim userComment As New DataTable("Comments")

userComment.Columns.Add(New DataColumn("User", GetType(String)))

userComment.Columns.Add(New DataColumn("Comment", GetType(String)))

userComment.Columns.Add(New DataColumn("CommentDate", GetType(Date)))

userComment.Columns.Add(New DataColumn("Email", GetType(String)))

To add a single row to the table, just treat it like any other DataTable object:

Dim dr As DataRow
dr = userComment.NewRow

dr.BeginEdit()
dr.Item("User") = "Jim"
dr.Item("Comment") = "Service was excellent!"
dr.Item("CommentDate") = DateTime.Today
dr.Item("Email") = "Jim@somecompany.com"

dr.EndEdit()

userComment.Rows.Add(dr)

You can add as many rows as you like to the collection. Use the DataTable Select method to select a subset of rows, or create a DataView object for filtering or sorting the data.

A DataTable itself cannot be serialized, but by adding the DataTable to a DataSet object, the DataSet can be serialized. In this example the DataTable is merged into a DataSet, and the DataSet is saved as an XML file:

Dim dsXML As New DataSet("UserComment")
dsXML.Merge(userComment)
dsXML.WriteXml("c:\temp\usercomment.xml")

This is the XML file that is created from the above code:

<?xml version="1.0" standalone="yes"?>
  <UserComment>
    <Comments>
      <User>Jim</User>
      <Comment>Service was excellent!</Comment>
      <CommentDate>2002-12-17T00:00:00.0000000-05:00</CommentDate>
      <Email>Jim@somecompany.com</Email>
    </Comments>
  </UserComment>


Additional Resources

 

 

Thank you! Thank you! I just finished reading this document, which was part of a link in the recent Buzz newsletter. I have printed it for others to read, especially those skeptical on the powers of Access and its capabilities.

Darren D.


View all FMS products for Microsoft Access All Our Microsoft Access Products

 

 

Free Product Catalog from FMS