System.Data Namespace Component DataTable Class Definition In this article Examples Remarks Constructors Fields Properties Methods Events Explicit Interface Implementations Extension Methods Applies to .NET Core .NET Framework .NET Standard Xamarin.Android Xamarin.iOS Xamarin.Mac Thread Safety See also Examples Source/Reference
System.Data Namespace Component
The System.Data namespace provides access to classes that represent the ADO.NET architecture. ADO.NET lets you build components that efficiently manage data from multiple data sources.
[System.Serializable]
public class DataTable : System.ComponentModel.MarshalByValueComponent, System.ComponentModel.IListSource, System.ComponentModel.ISupportInitializeNotification, System.Runtime.Serialization.ISerializable, System.Xml.Serialization.IXmlSerializable
The following example creates two DataTable objects and one DataRelation object, and adds the new objects to a DataSet. The tables are then displayed in a DataGridView control.
C#
// Put the next line into the Declarations section.
private System.Data.DataSet dataSet;
private void MakeDataTables()
{
// Run all of the functions.
MakeParentTable();
MakeChildTable();
MakeDataRelation();
BindToDataGrid();
}
private void MakeParentTable()
{
// Create a new DataTable.
System.Data.DataTable table = new DataTable("ParentTable");
// Declare variables for DataColumn and DataRow objects.
DataColumn column;
DataRow row;
// Create new DataColumn, set DataType,
// ColumnName and add to DataTable.
column = new DataColumn();
column.DataType = System.Type.GetType("System.Int32");
column.ColumnName = "id";
column.ReadOnly = true;
column.Unique = true;
// Add the Column to the DataColumnCollection.
table.Columns.Add(column);
// Create second column.
column = new DataColumn();
column.DataType = System.Type.GetType("System.String");
column.ColumnName = "ParentItem";
column.AutoIncrement = false;
column.Caption = "ParentItem";
column.ReadOnly = false;
column.Unique = false;
// Add the column to the table.
table.Columns.Add(column);
// Make the ID column the primary key column.
DataColumn[] PrimaryKeyColumns = new DataColumn[1];
PrimaryKeyColumns[0] = table.Columns["id"];
table.PrimaryKey = PrimaryKeyColumns;
// Instantiate the DataSet variable.
dataSet = new DataSet();
// Add the new DataTable to the DataSet.
dataSet.Tables.Add(table);
// Create three new DataRow objects and add
// them to the DataTable
for (int i = 0; i<= 2; i++)
{
row = table.NewRow();
row["id"] = i;
row["ParentItem"] = "ParentItem " + i;
table.Rows.Add(row);
}
}
private void MakeChildTable()
{
// Create a new DataTable.
DataTable table = new DataTable("childTable");
DataColumn column;
DataRow row;
// Create first column and add to the DataTable.
column = new DataColumn();
column.DataType= System.Type.GetType("System.Int32");
column.ColumnName = "ChildID";
column.AutoIncrement = true;
column.Caption = "ID";
column.ReadOnly = true;
column.Unique = true;
// Add the column to the DataColumnCollection.
table.Columns.Add(column);
// Create second column.
column = new DataColumn();
column.DataType= System.Type.GetType("System.String");
column.ColumnName = "ChildItem";
column.AutoIncrement = false;
column.Caption = "ChildItem";
column.ReadOnly = false;
column.Unique = false;
table.Columns.Add(column);
// Create third column.
column = new DataColumn();
column.DataType= System.Type.GetType("System.Int32");
column.ColumnName = "ParentID";
column.AutoIncrement = false;
column.Caption = "ParentID";
column.ReadOnly = false;
column.Unique = false;
table.Columns.Add(column);
dataSet.Tables.Add(table);
// Create three sets of DataRow objects,
// five rows each, and add to DataTable.
for(int i = 0; i <= 4; i ++)
{
row = table.NewRow();
row["childID"] = i;
row["ChildItem"] = "Item " + i;
row["ParentID"] = 0 ;
table.Rows.Add(row);
}
for(int i = 0; i <= 4; i ++)
{
row = table.NewRow();
row["childID"] = i + 5;
row["ChildItem"] = "Item " + i;
row["ParentID"] = 1 ;
table.Rows.Add(row);
}
for(int i = 0; i <= 4; i ++)
{
row = table.NewRow();
row["childID"] = i + 10;
row["ChildItem"] = "Item " + i;
row["ParentID"] = 2 ;
table.Rows.Add(row);
}
}
private void MakeDataRelation()
{
// DataRelation requires two DataColumn
// (parent and child) and a name.
DataColumn parentColumn =
dataSet.Tables["ParentTable"].Columns["id"];
DataColumn childColumn =
dataSet.Tables["ChildTable"].Columns["ParentID"];
DataRelation relation = new
DataRelation("parent2Child", parentColumn, childColumn);
dataSet.Tables["ChildTable"].ParentRelations.Add(relation);
}
private void BindToDataGrid()
{
// Instruct the DataGrid to bind to the DataSet, with the
// ParentTable as the topmost DataTable.
dataGrid1.SetDataBinding(dataSet,"ParentTable");
}
This sample demonstrates how to create a DataTable manually with specific schema definitions:
Create multiple DataTables and define the initial columns.
Create the table constraints.
Insert the values and display the tables.
Create the expression columns and display the tables.
C# and Visual Basic projects with this code sample can be found on Developer Code Samples.
C#
using System;
using System.Data;
class Program {
static void Main(string[] args) {
// Create two tables and add them into the DataSet
DataTable orderTable = CreateOrderTable();
DataTable orderDetailTable = CreateOrderDetailTable();
DataSet salesSet = new DataSet();
salesSet.Tables.Add(orderTable);
salesSet.Tables.Add(orderDetailTable);
// Set the relations between the tables and create the related constraint.
salesSet.Relations.Add("OrderOrderDetail", orderTable.Columns["OrderId"], orderDetailTable.Columns["OrderId"], true);
Console.WriteLine("After creating the foreign key constriant, you will see the following error if inserting order detail with the wrong OrderId: ");
try {
DataRow errorRow = orderDetailTable.NewRow();
errorRow[0] = 1;
errorRow[1] = "O0007";
orderDetailTable.Rows.Add(errorRow);
} catch (Exception e) {
Console.WriteLine(e.Message);
}
Console.WriteLine();
// Insert the rows into the table
InsertOrders(orderTable);
InsertOrderDetails(orderDetailTable);
Console.WriteLine("The initial Order table.");
ShowTable(orderTable);
Console.WriteLine("The OrderDetail table.");
ShowTable(orderDetailTable);
// Use the Aggregate-Sum on the child table column to get the result.
DataColumn colSub = new DataColumn("SubTotal", typeof(Decimal), "Sum(Child.LineTotal)");
orderTable.Columns.Add(colSub);
// Compute the tax by referencing the SubTotal expression column.
DataColumn colTax = new DataColumn("Tax", typeof(Decimal), "SubTotal*0.1");
orderTable.Columns.Add(colTax);
// If the OrderId is 'Total', compute the due on all orders; or compute the due on this order.
DataColumn colTotal = new DataColumn("TotalDue", typeof(Decimal), "IIF(OrderId='Total',Sum(SubTotal)+Sum(Tax),SubTotal+Tax)");
orderTable.Columns.Add(colTotal);
DataRow row = orderTable.NewRow();
row["OrderId"] = "Total";
orderTable.Rows.Add(row);
Console.WriteLine("The Order table with the expression columns.");
ShowTable(orderTable);
Console.WriteLine("Press any key to exit.....");
Console.ReadKey();
}
private static DataTable CreateOrderTable() {
DataTable orderTable = new DataTable("Order");
// Define one column.
DataColumn colId = new DataColumn("OrderId", typeof(String));
orderTable.Columns.Add(colId);
DataColumn colDate = new DataColumn("OrderDate", typeof(DateTime));
orderTable.Columns.Add(colDate);
// Set the OrderId column as the primary key.
orderTable.PrimaryKey = new DataColumn[] { colId };
return orderTable;
}
private static DataTable CreateOrderDetailTable() {
DataTable orderDetailTable = new DataTable("OrderDetail");
// Define all the columns once.
DataColumn[] cols ={
new DataColumn("OrderDetailId",typeof(Int32)),
new DataColumn("OrderId",typeof(String)),
new DataColumn("Product",typeof(String)),
new DataColumn("UnitPrice",typeof(Decimal)),
new DataColumn("OrderQty",typeof(Int32)),
new DataColumn("LineTotal",typeof(Decimal),"UnitPrice*OrderQty")
};
orderDetailTable.Columns.AddRange(cols);
orderDetailTable.PrimaryKey = new DataColumn[] { orderDetailTable.Columns["OrderDetailId"] };
return orderDetailTable;
}
private static void InsertOrders(DataTable orderTable) {
// Add one row once.
DataRow row1 = orderTable.NewRow();
row1["OrderId"] = "O0001";
row1["OrderDate"] = new DateTime(2013, 3, 1);
orderTable.Rows.Add(row1);
DataRow row2 = orderTable.NewRow();
row2["OrderId"] = "O0002";
row2["OrderDate"] = new DateTime(2013, 3, 12);
orderTable.Rows.Add(row2);
DataRow row3 = orderTable.NewRow();
row3["OrderId"] = "O0003";
row3["OrderDate"] = new DateTime(2013, 3, 20);
orderTable.Rows.Add(row3);
}
private static void InsertOrderDetails(DataTable orderDetailTable) {
// Use an Object array to insert all the rows .
// Values in the array are matched sequentially to the columns, based on the order in which they appear in the table.
Object[] rows = {
new Object[]{1,"O0001","Mountain Bike",1419.5,36},
new Object[]{2,"O0001","Road Bike",1233.6,16},
new Object[]{3,"O0001","Touring Bike",1653.3,32},
new Object[]{4,"O0002","Mountain Bike",1419.5,24},
new Object[]{5,"O0002","Road Bike",1233.6,12},
new Object[]{6,"O0003","Mountain Bike",1419.5,48},
new Object[]{7,"O0003","Touring Bike",1653.3,8},
};
foreach (Object[] row in rows) {
orderDetailTable.Rows.Add(row);
}
}
private static void ShowTable(DataTable table) {
foreach (DataColumn col in table.Columns) {
Console.Write("{0,-14}", col.ColumnName);
}
Console.WriteLine();
foreach (DataRow row in table.Rows) {
foreach (DataColumn col in table.Columns) {
if (col.DataType.Equals(typeof(DateTime)))
Console.Write("{0,-14:d}", row[col]);
else if (col.DataType.Equals(typeof(Decimal)))
Console.Write("{0,-14:C}", row[col]);
else
Console.Write("{0,-14}", row[col]);
}
Console.WriteLine();
}
Console.WriteLine();
}
}
Remarks
The DataTable is a central object in the ADO.NET library. Other objects that use the DataTable include the DataSet and the DataView.
When accessing DataTable objects, note that they are conditionally case sensitive. For example, if one DataTable is named "mydatatable" and another is named "Mydatatable", a string used to search for one of the tables is regarded as case sensitive. However, if "mydatatable" exists and "Mydatatable" does not, the search string is regarded as case insensitive. A DataSet can contain two DataTable objects that have the
same TableName property value but different Namespace property values. For more information about working with DataTable objects, see Creating a DataTable.
The DataTable also contains a collection of Constraint objects that can be used to ensure the integrity of the data. For more information, see DataTable Constraints.
When an instance of DataTable is created, some of the read/write properties are set to initial values. For a list of these values, see the DataTable.DataTable constructor topic.
Note
The DataSet and DataTable objects inherit from MarshalByValueComponent, and support the ISerializable interface for .NET Framework remoting. These are the only ADO.NET objects that you can use for .NET Framework remoting.
Gets or sets the expression that returns a value used to represent this table in the user interface. The DisplayExpression property lets you display the name of this table in a user interface.
Gets a copy of the DataTable containing all changes made to it since it was last loaded, or since AcceptChanges() was called, filtered by DataRowState.
Fills a DataTable with values from a data source using the supplied IDataReader. If the DataTable already contains rows, the incoming data from the data source is merged with the existing rows.
Fills a DataTable with values from a data source using the supplied IDataReader. If the DataTable already contains rows, the incoming data from the data source is merged with the existing rows according to the value of the loadOption parameter.
Merge the specified DataTable with the current DataTable, indicating whether to preserve changes and how to handle missing schema in the current DataTable.
Resets the DataTable to its original state. Reset removes all data, indexes, relations, and columns of the table. If a DataSet includes a DataTable, the table will still be part of the DataSet after the table is reset.
Writes the current contents of the DataTable as XML using the specified Stream. To save the data for the table and all its descendants, set the writeHierarchy parameter to true.
Writes the current data, and optionally the schema, for the DataTable to the specified file using the specified XmlWriteMode. To write the schema, set the value for the mode parameter to WriteSchema.
Writes the current data, and optionally the schema, for the DataTable to the specified file using the specified XmlWriteMode. To write the schema, set the value for the mode parameter to WriteSchema. To save the data for the table and all its descendants, set the writeHierarchy parameter to true.
Writes the current contents of the DataTable as XML using the specified file. To save the data for the table and all its descendants, set the writeHierarchy parameter to true.
Writes the current data, and optionally the schema, for the DataTable using the specified file and XmlWriteMode. To write the schema, set the value for the mode parameter to WriteSchema.
Writes the current data, and optionally the schema, for the DataTable using the specified file and XmlWriteMode. To write the schema, set the value for the mode parameter to WriteSchema. To save the data for the table and all its descendants, set the writeHierarchy parameter to true.
Writes the current contents of the DataTable as XML using the specified TextWriter. To save the data for the table and all its descendants, set the writeHierarchy parameter to true.
Writes the current data, and optionally the schema, for the DataTable using the specified TextWriter and XmlWriteMode. To write the schema, set the value for the mode parameter to WriteSchema.
Writes the current data, and optionally the schema, for the DataTable using the specified TextWriter and XmlWriteMode. To write the schema, set the value for the mode parameter to WriteSchema. To save the data for the table and all its descendants, set the writeHierarchy parameter to true.
Writes the current data, and optionally the schema, for the DataTable using the specified XmlWriter and XmlWriteMode. To write the schema, set the value for the mode parameter to WriteSchema.
Writes the current data, and optionally the schema, for the DataTable using the specified XmlWriter and XmlWriteMode. To write the schema, set the value for the mode parameter to WriteSchema. To save the data for the table and all its descendants, set the writeHierarchy parameter to true.
Writes the current data structure of the DataTable as an XML schema to the specified stream. To save the schema for the table and all its descendants, set the writeHierarchy parameter to true.
Writes the current data structure of the DataTable as an XML schema to the specified file. To save the schema for the table and all its descendants, set the writeHierarchy parameter to true.
Writes the current data structure of the DataTable as an XML schema using the specified TextWriter. To save the schema for the table and all its descendants, set the writeHierarchy parameter to true.
Writes the current data structure of the DataTable as an XML schema using the specified XmlWriter. To save the schema for the table and all its descendants, set the writeHierarchy parameter to true.