ADO.NET DataTable class properties and methods in VB.NET

In this article I will explain about the DataTable class properties and methods in ADO.NET.
  • 5889
 

In the previous articles you've already seen that columns and rows are the building block of a data table. You need to work with the DataColumn and DataRow objects to create data tables and add data to them. Besides creating a data table schema and adding rows to it, a data table has more to offer. The DataTable object represents a data table.

Before creating a data table, I'll show you the DataTable class properties and methods. Table 1 describes some of the common DataTable properties, and Table 2 summarizes some of the common DataTable methods.

Table 1: The Data Table class properties

PROPERTY

DESCRIPTION

Columns

Represents all table columns

Constraints

Represents all table constraints

DataSet

Returns the dataset for the table

DefaultView

Customized view of the data table

ChildRelation

Return child relations for the data table

ParentRelation

Returns parent relations for the data table

PrimaryKey

Represents an array of columns that function as primary key for the table

Rows

All rows of the data table

TableName

Name of the table


Table 2: The Data Table Class Methods
 

METHOD

DESCRIPTION

AcceptChanges

Commits all the changes made since last AcceptChanges was called

Clear

Deletes all data table data

Clone

Creates a clone of a DataTable including its schema

Copy

Copies a data table including its schema

NewRow

Creates a new row, which is later added by calling the Rows.Add method

RejectChanges

Reject all changed made after last AcceptChanges was called

Reset

Resets a data table's original state

Select

Gets an array of rows based on the criteria


The DataTable class provides methods and properties to remove, copy, and clone data tables. Not only that, but you can also apply filters on a DataTable. The Constraints property provides access to all the constraints that a data table has. You can also access the child and parent relationship using ChildRelation and ParentRelation.

To test this theory, I'll create two data tables, Customers and Orders, and set a relationship between them. To test this application, you build a Windows Application using VB.NET and add a data grid control to the form. After that you call the CreateCustomers Table, CreateOrdersTable, and BindData methods from the form constructors after InitializeComponent. The form constructor looks like listing 1.

Listing 1: Form's constructor calling CreateCustomers Table CreateOrdersTable, and BindData


    Public Sub New()
        InitializeComponent()
        CreateCustomersTable()
        CreateOrdersTable()
        BindData()
    End Sub


You also need to add a DataSet variable, dtSet, in the beginning of your form. See the following code:

    Public Class Form1
        Inherits System.Windows.Forms.Form
        ' Put the next line into the Declarations section.
        Private dtSet As System.Data.DataSet
    End Class

In listing 2, the CreateCustomersTable method creates the Customers data table with Id, Name, and Address columns and adds three data rows to it. The Create OrdersTable method creates the Orders table with order Id, cust Id, Name, and Description columns and adds data it. The BindData method creates a customer/ orders relationship and binds the data to a DataGrid control using DataSet.

Listing 2 shows All Three CreateCustomerTable, CreateOrderTable, and BindData methods.

Listing 2: Customer/orders relationship example


    ' This method creates customers table
    Private Sub CreateCustomersTable()
        ' Create a new Data Table.
        Dim custTable As System.Data.DataTable = New DataTable("Customers")
        Dim dtColumn As DataColumn
        Dim myDataRow As DataRow
        ' Create id Column
        dtColumn = New DataColumn()
        dtColumn.DataType = System.Type.[GetType]("System.Int32")
        dtColumn.ColumnName = "id"
        dtColumn.Caption = "Cust ID"
        dtColumn.[ReadOnly] = False
        dtColumn.Unique = True
        ' Add id column to the DataColumnCollection.
        custTable.Columns.Add(dtColumn)

        ' create Name column.
        dtColumn = New DataColumn()
        dtColumn.DataType = System.Type.[GetType]("System.String")
        dtColumn.ColumnName = "Name"
        dtColumn.Caption = "Cust Name"
        dtColumn.AutoIncrement = False
        dtColumn.[ReadOnly] = False
        dtColumn.Unique = False

        ' Add Name Column to the table.
        custTable.Columns.Add(dtColumn)
        ' Create Address column.
        dtColumn = New DataColumn()
        dtColumn.DataType = System.Type.[GetType]("System.String")
        dtColumn.ColumnName = "Address"
        dtColumn.Caption = "Address"
        dtColumn.[ReadOnly] = False
        dtColumn.Unique = False

        ' Add Address column to the table.
        custTable.Columns.Add(dtColumn)

        ' Make the ID column the primary key column.
        Dim PrimaryKeyColumns As DataColumn() = New DataColumn(0) {}
        PrimaryKeyColumns(0) = custTable.Columns("id")
        custTable.PrimaryKey = PrimaryKeyColumns
        ' Instantiate the DataSet variable.

        dtSet = New DataSet("Customers")

        ' Add the custTable to the DataSet.
        dtSet.Tables.Add(custTable)

        ' Add rows to the custTable using NewRow method
        ' I add three customers with their addresses, name and id
        myDataRow = custTable.NewRow()
        myDataRow("id") = 1001
        myDataRow("Address") = "43 Lanewood Road, cito, CA"
        myDataRow("Name") = "George Bishop"
        custTable.Rows.Add(myDataRow)
        myDataRow = custTable.NewRow()
        myDataRow("id") = 1002
        myDataRow("name") = "Rock joe"
        myDataRow("Address") = " kind of Prussia, PA"
        custTable.Rows.Add(myDataRow)
        myDataRow = custTable.NewRow()
        myDataRow("id") = 1003
        myDataRow("Name") = "Miranda"
        myDataRow("Address") = "279 P. Avenue, Bridgetown, PA"
        custTable.Rows.Add(myDataRow)
    End Sub

    ' This method creates orders table with
    Private Sub CreateOrdersTable()
        ' Create order Id column
        Dim ordersTable As New DataTable("Orders")
        Dim dtColumn As DataColumn
        Dim dtRow As DataRow

        ' Create OrderId column
        dtColumn = New DataColumn()
        dtColumn.DataType = System.Type.[GetType]("System.Int32")
        dtColumn.ColumnName = "OrderId"
        dtColumn.AutoIncrement = True
        dtColumn.Caption = "order ID"
        dtColumn.[ReadOnly] = True
        dtColumn.Unique = True
        ordersTable.Columns.Add(dtColumn)

        ' create Name column.
        dtColumn = New DataColumn()
        dtColumn.DataType = System.Type.[GetType]("System.String")
        dtColumn.ColumnName = "Name"
        dtColumn.Caption = "Item Name"
        ordersTable.Columns.Add(dtColumn)

        ' Create Cust Id column which Reprence Cust Id from
        ' The cust Table
        dtColumn = New DataColumn()
        dtColumn.DataType = System.Type.[GetType]("System.Int32")
        dtColumn.ColumnName = "CustId"
        dtColumn.AutoIncrement = False
        dtColumn.Caption = "CustId"
        dtColumn.[ReadOnly] = False
        dtColumn.Unique = False
        ordersTable.Columns.Add(dtColumn)

        ' Create Description column.
        dtColumn = New DataColumn()
        dtColumn.DataType = System.Type.[GetType]("System.String")
        dtColumn.ColumnName = "Description"
        dtColumn.Caption = "DescriptionName"
        ordersTable.Columns.Add(dtColumn)

        ' Add ordersTable to the dataset
        dtSet.Tables.Add(ordersTable)

        ' ADD two rows to the customer Id 1001
        dtRow = ordersTable.NewRow()
        dtRow("order Id") = 0
        dtRow("Name") = "ASP Book"
        dtRow("custId") = 1001
        dtRow("Description") = "Same Day"
        ordersTable.Rows.Add(dtRow)
        dtRow = ordersTable.NewRow()
        dtRow("OrderId") = 1
        dtRow("Name") = " C# Book"
        dtRow("custId") = 1001
        dtRow("description") = "2 DAY AIR"
        ordersTable.Rows.Add(dtRow)
        ' Add two rows to Customer id 1002
        dtRow = ordersTable.NewRow()
        dtRow("order Id") = 2
        dtRow("Name") = "Data Quest"
        dtRow("Description") = "Monthly magazine"
        dtRow("CustId") = 1002
        ordersTable.Rows.Add(dtRow)
        dtRow = ordersTable.NewRow()
        dtRow("OrderId ") = 3
        dtRow("Name") = "PC Magazine"
        dtRow("Description") = "Monthly Magazine"
        dtRow("CustId") = 1003
        ordersTable.Rows.Add(dtRow)
        ' Add two rows to Customer id 1003
        dtRow = ordersTable.NewRow()
        dtRow("OrderId") = 4
        dtRow("Name") = "PCMagazine"
        dtRow("Description") = "Monthly Magazine"
        dtRow("custId") = 1003
        ordersTable.Rows.Add(dtRow)
        dtRow = ordersTable.NewRow()
        dtRow("OrderId") = 5
        dtRow("Name") = "C# Book"
        dtRow("CustId") = 1003
        dtRow("Description") = "2 Day Air "
        ordersTable.Rows.Add(dtRow)
    End Sub

    ' This method creates a customer order relationship and data tables
    ' To the data grid control using dataSet.
    Private Sub BindData()
        Dim dtRelation As DataRelation
        Dim custCol As DataColumn = dtSet.Tables("Customers").Columns("id")
        Dim orderCol As DataColumn = dtSet.Tables("orders").Columns("custId")
        dtRelation = New DataRelation("CustOrderRelation ", custCol, orderCol)
        dtSet.Tables("orders").ParentRelations.Add(dtRelation)
        dataGrid1.SetDataBinding(dtSet, "Customers")
    End Sub

As you can see from the CreateCustomersTable method in listing 2, it creates the Customers table using DataTable and adds the Id, Name and Address columns to the table. You use DataColumn to add these columns. The Id column has properties such as ReadOnly and Unique. As discussed earlier, to add a column to a DataTable, you create a DataColumn object, set its properties, and then call the DataTable.Columns.Add method. Similar to the id column, you add two more columns, Name and Address, of stgring type to the table. After that you make the id column the primary key by the setting DataTable.PrimaryKey as the id column:

PrimaryKeyColumns(0) = custTable.Columns("id")
custTable.PrimaryKey = PrimaryKeyColumns

After creating a DataTable you add it to a DataSet using the DataSet.Tables.Add method. This method takes one argument of type DataTable:


dtSet = New DataSet("customers")
dtSet.Tables.Add(custTable)

Now, the last step is to add data to DataTable. You add data using DataRow. First, you create a DataRow object using DataTable's NewRow method, add data to a DataRow's items, and add DataRow to the DataTable using the DataTable.Rows.Add method you'll follow the same method for the second table in createOrdersTable to create the orders table. The orders table has the fields OrderId, Name, Description, and custId. The BindData method creates a relationship by using DataRelation and binds the id column of the Customers tables to the custId column of the Orders table. The name of the relationship is CustOrderRelation. After that you bind DataTable to the DataGrid using the SetDataBinding method.

The output of listing 2 looks like figure 1 below.

datagrid1.JPG

Figure 1: Output of listing 2

If you click on the CustOrderRellation link, the output looks like figure 2 below.

datagrid2.JPG

Figure 2: Orders record for Customers id 1001

As you can see from figure 5-13. Data Grid shows all the orders for Customer id 1001.

Source Code

Source code attach in the top of the article.

Conclusion

Hope this article would have helped you in understanding the DataTable class properties and methods in ADO.NET. . See my other articles on the website on ADO.NET.

Categories

More Articles

© 2020 DotNetHeaven. All rights reserved.