DataTable in C#

This chapter has been excerpted from book "A Programmer's Guide to ADO.NET in C#".

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 C# 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 Form1()
        {
            InitializeComponent();
            CreateCustomersTable();
            CreateOrdersTable();
            BindData();
        }

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

public class Form1 : System.Windows.Forms.Form
        {
            // Put the next line into the Declarations section.
            private System.Data.DataSet dtSet;
        }

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 void CreateCustomersTable()
        {
            // Create a new Data Table.
            System.Data.DataTable custTable = new DataTable("Customers");
            DataColumn dtColumn;
            DataRow myDataRow; 
            // 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.
            DataColumn[] PrimaryKeyColumns = new DataColumn[1];
            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);
         }

        // This method creates orders table with
        private void CreateOrdersTable()
        {
            // Create order Id column
            DataTable ordersTable = new DataTable("Orders");
            DataColumn dtColumn;
            DataRow dtRow;

            // 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);
        }

        // This method creates a customer order relationship and data tables
        // To the data grid control using dataSet.
        private void BindData()
       {
            DataRelation dtRelation;
            DataColumn custCol = dtSet.Tables["Customers"].Columns["id"];
            DataColumn orderCol = dtSet.Tables["orders"].Columns["custId"];
            dtRelation = new DataRelation("CustOrderRelation ", custCol, orderCol);
            dtSet.Tables["orders"].ParentRelations.Add(dtRelation);
            dataGrid1.SetDataBinding(dtSet, "Customers");
        }

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.

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.