The IndexOf method returns the index of the specified table. The method accepts either a string representing a DataTable’s name or a DataTable object as input parameters, as shown in the following example:

Dim iIndex As Integer

iIndex = oDS.Tables.IndexOf(“t_bands”)

The Clear method, as the name implies, removes all tables from the

DataTableCollection object.

CanRemove method

The CanRemove method returns a Boolean value indicating whether a specified table can be removed from the DataTableCollection object. The CanRemove method accepts a DataTable object as its input parameter.

Remove method

The Remove method removes the specified table from the DataTableCollection object. The Remove method can accept either a table’s name or a DataTable object as its input parameter. Here’s an example:

oDS.Tables.Remove(“t_bands”)

Add method

The Add method adds a table to the DataTableCollection. The Add method can be used in one of three ways. You can call the Add method and not pass it any input parameters. In this case a DataTable is added to DataTableCollection and assigned a default name. You can also pass only a string representing the table’s name. In this case, a DataTable is added to the DataTableCollection and assigned the specified name. Or you can call the Add method and pass it a DataTable object. In this case, the specified DataTable is added to the DataTableCollection. Here’s a quick example:

oDS.Tables.Add(“Bands”)

That’s it for the DataTableCollection object. Basically the DataTableCollection provides access to the DataTable object’s in a DataSet.

Now that you know how to get to your tables, let’s see what you can do with them.

DataTable Objects

The DataTable object is a central object in the ADO.NET library and effectively represents a data source’s data. You can manually fabricate a DataTable using its properties and methods, or you can have it automatically filled using DataSet commands.

In order to manually construct a DataTable we can use the following code:

Dim oDT As DataTable

oDT = New DataTable()

You can also pass a string representing the DataTable’s name to the constructor as follows:

oDT = New DataTable(“MyTable”)

As will all ADO.NET objects, the DataTable object has a variety of properties and methods. I’ll start with the properties.

CaseSensitive property

The CaseSensitive property is a Boolean value that indicates whether string comparison within a table is case sensitive. The property’s default value is set to the parent DataSet object’s CaseSensitive property. The CaseSensitive property affects string comparisons in sorting, searching, and filtering. Here’s an example:

oDS.Tables.Item(0).CaseSensitive = False

In this statement you are going through a DataSet object’s Tables property to access a DataTableCollection object. You will then use the DataTableCollection’s Item property to access the DataTable in the collection with an index of zero. Once you have drilled down to the DataTable, you can access its properties, in this case the CaseSensitive property.

ChildRelations property

If you have more than one table in your DataSet, chances are that you’ll want to relate them in some way. The ChildRelations property gets a collection of child relations for a DataTable in the form of a DataRelationCollection object. This is a little complicated and we can’t go into it too much in this session. Data relationships are further discussed in Session 25, “Data Shaping with ADO.NET.”

Columns property

The Columns property gets the collection of columns that belong to a DataTable in the form of a DataColumnCollection object. The relationship between the Columns property and a DataTable is very similar to the relationship between the Tables property and a DataSet. The Columns property exposes a DataColumnCollection through which you can access a DataTable’s columns. Here’s sample syntax demonstrating how you can access a DataTable’s columns using the Columns property:

Dim oDCC As DataColumnCollection oDCC = oDS.Tables(0).Columns

Constraints property

The Constraints property gets the collection of constraints maintained by a DataTable object in the form of a ConstraintCollection object. If you’ll recall from our discussion on database design in Session 3, “Designing A Database,” a table can have zero, one, or multiple constraints. Some typical constraints you’ll find on a given table are foreign key and unique constraints. We’ll return to the Constraints property in Session 25, “Data Shaping with ADO.NET.”

DataSet property

If a DataTable belongs to a DataSet, the DataSet property returns a reference to the parent DataSet. You might find this property useful when processing a form. In the following code snippet, assume that you have submitted an HTML form that contains a DataGrid control, dgExample, which is bound to a DataTable. Here’s the syntax:

Dim oDS As DataSet

Dim oDT As DataTable

oDT = cType(dgExample.DataSource, DataTable) oDS = oDT.DataSet

DefaultView property

The DefaultView property gets a customized view of the DataTable in the form of a DataView object. A DataView object is a databindable, customized view of a DataTable used for sorting, filtering, searching, editing, and navigating a DataTable. We’ll talk more about this in Session 22, “Introducing Data Binding.”

ParentRelations property

The ParentRelations property is very similar to the ChildRelations property except that it gets the parent relationships rather than the child relationships. (We guess that kind of makes sense.) The ParentRelations property gets a collection of parent relations for a DataTable in the form of a DataRelationCollection object. More in Session 25, “Data Shaping with ADO.NET.”

PrimaryKey property

The PrimaryKey property gets or sets an array of columns that function as primary keys for a DataTable. In most cases you’ll be getting the primary key columns rather than setting them. In cases where you need to manually create a custom DataTable object, you’ll set the PrimaryKey property. The PrimaryKey property returns an array of DataColumn objects. Likewise, when you are setting the PrimaryKey property, you need to pass it an array of DataColumn objects. Here’s an example of getting the primary key columns on a DataTable:

Dim aPK() As DataColumn

Dim x As Integer

oDT = oDS.Tables(“t_bands”) aPK = oDT.PrimaryKey

For x = LBound(aPK) to UBound(aPK)

Response.Write(“PRIMARY KEY “ & x & “: “ & aPK(x).ColumnName & “<BR/>” & chr(13))

Next

In this example, you gain access to the Tables collection via the DataSet and then initialize an array, aPK, using the DataTable object’s PrimaryKey property. We then use a For loop to iterate through the array.

Rows property

The Rows property provides access to the collection of rows that belong to the table. This property is similar to the Columns property, but instead of returning a collection of DataColumn objects, it returns a collection of DataRow objects. It is through the Rows property that you can gain access to a DataTable’s constituent DataRow objects’ properties and methods. A DataTable’s DataColumn objects represent the DataTable’s structure whereas the DataRow objects represent the DataTable’s data.

Additionally, it is through the DataRow object that you can gain access to the data in your DataTable objects. Listing 21-2 demonstrates iterating through the DataRowCollection (returned by the Rows property) in a DataTable and writing its contents to a .NET Table control.

Listing 21-2 Iterating through a table’s rows

<%@ Page Language=”VB” %>

<%@ Import Namespace=”System.Data” %>

<%@ Import Namespace=”System.Data.OleDb” %> <SCRIPT LANGUAGE=”VB” RUNAT=”server”>

Sub Page_Load(Sender As Object, E As EventArgs) Dim oConn As OleDbConnection

Dim oDA As OleDbDataAdapter Dim oDS As DataSet

Dim oDT As DataTable

Dim oDR As DataRow Dim oDC As DataColumn Dim oTR As TableRow

Dim oTHC As TableHeaderCell Dim oTC As TableCell

oConn = New OleDbConnection(“Provider=SQLOLEDB;Data

Source=(local);Initial Catalog=Music;User ID=music;Password=music”)

oConn.Open

Continued

oDA = New OleDbDataAdapter(“SELECT * FROM t_bands”, oConn)

oDS = New DataSet(“Music”)

oDA.Fill(oDS, “t_bands”)

oDT = oDS.Tables(“t_bands”)

oTR = New TableRow

oTR.BackColor = System.Drawing.Color.LightGray For Each oDC In oDT.Columns

oTHC = New TableHeaderCell oTHC.Text = oDC.ColumnName oTR.Cells.Add(oTHC)

Next tblBands.Rows.Add(oTR)

For Each oDR In oDT.Rows oTR = New TableRow

For Each oDC In oDT.Columns

oTC = New TableCell oTC.Text = oDR.Item(oDC) oTR.Cells.Add(oTC)

Next tblBands.Rows.Add(oTR)

Next

oTC.Dispose oTC = Nothing oTR.Dispose oTR = Nothing oDT.Dispose oDT = Nothing oDS.Dispose oDS = Nothing oDA.Dispose oDA = Nothing oConn.Close oConn = Nothing

End Sub </SCRIPT> <HTML> <BODY>

<asp:Table id=”tblBands” BorderWidth=”1” GridLines=”both” runat=”server”/> </BODY>

</HTML>