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.
Figure 1: Output of listing 2
If you click on the CustOrderRellation link, the output looks like figure 2 below.
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.