The DataTable class in C# ADO.NET is a database table representation and provides a collection of columns and rows to store data in a grid form. The code sample in this artilce explains how to create a DataTable at run-time in C#. You will also learn how to create a DataTable columns and rows, add data to a DataTable and bind a DataTable to a DataGridView control using data binding.
Table 1 describes some of the common DataTable properties.
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 describes some of the common DataTable methods.
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. You can also apply filters and sorting 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 objects.
In this code, I'll create two DataTable objects, Customers and Orders, and set a relationship between them. After that, I will add data to the DataTables and bind a DataTable to a DataGridView control to load and display data in the control.
Steps:
Open Visual Studio 2017 or later version and create a Windows Forms application using C#.
Add a DataGridView control to the Form and resize your control as you see it fits.
Now, on the Form's constructor, call the following three methods, CreateCustomersTable(), CreateOrdersTable(), and BindData() methods after InitializeComponent() method.
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
{
private 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
// Create Customers table
private void CreateCustomersTable()
{
// Create a new DataTable.
DataTable custTable = new DataTable("Customers");
DataColumn dtColumn;
DataRow myDataRow;
// Create id column
dtColumn = new DataColumn();
dtColumn.DataType = typeof(Int32);
dtColumn.ColumnName = "id";
dtColumn.Caption = "Cust ID";
dtColumn.ReadOnly = false;
dtColumn.Unique = true;
// Add column to the DataColumnCollection.
custTable.Columns.Add(dtColumn);
// Create Name column.
dtColumn = new DataColumn();
dtColumn.DataType = typeof(String);
dtColumn.ColumnName = "Name";
dtColumn.Caption = "Cust Name";
dtColumn.AutoIncrement = false;
dtColumn.ReadOnly = false;
dtColumn.Unique = false;
/// Add column to the DataColumnCollection.
custTable.Columns.Add(dtColumn);
// Create Address column.
dtColumn = new DataColumn();
dtColumn.DataType = typeof(String);
dtColumn.ColumnName = "Address";
dtColumn.Caption = "Address";
dtColumn.ReadOnly = false;
dtColumn.Unique = false;
// Add column to the DataColumnCollection.
custTable.Columns.Add(dtColumn);
// Make id column the primary key column.
DataColumn[] PrimaryKeyColumns = new DataColumn[1];
PrimaryKeyColumns[0] = custTable.Columns["id"];
custTable.PrimaryKey = PrimaryKeyColumns;
// Create a new DataSet
dtSet = new DataSet();
// Add custTable to the DataSet.
dtSet.Tables.Add(custTable);
// Add data rows to the custTable using NewRow method
// I add three customers with their addresses, names and ids
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);
}
// Create Orders table
private void CreateOrdersTable()
{
// Create a DataTable
DataTable ordersTable = new DataTable("Orders");
DataColumn dtColumn;
DataRow dtRow;
// Create OrderId column
dtColumn = new DataColumn();
dtColumn.DataType = 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 = Type.GetType("System.String");
dtColumn.ColumnName = "Name";
dtColumn.Caption = "Item Name";
ordersTable.Columns.Add(dtColumn);
// Create CustId column which Reprence Cust Id from
// The cust Table
dtColumn = new DataColumn();
dtColumn.DataType = 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 = Type.GetType("System.String");
dtColumn.ColumnName = "Description";
dtColumn.Caption = "DescriptionName";
ordersTable.Columns.Add(dtColumn);
// Add ordersTable to DataSet
dtSet.Tables.Add(ordersTable);
// ADD two rows to the customer Id 1001
dtRow = ordersTable.NewRow();
dtRow["OrderId"] = 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["OrderId"] = 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
// Also displays Customers table data in a DataGridView control
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);
// Create a BindingSource
BindingSource bs = new BindingSource();
bs.DataSource = dtSet.Tables["Customers"];
// Bind data to DataGridView.DataSource
dataGridView1.DataSource = bs;
}
As you can see from the CreateCustomersTable method in listing 2, it creates the Customers table using DataTable and adds 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 string 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.