DataTable Class
 

Represents one table of in-memory data.

Namespace:   System.Data
Assembly:  System.Data (in System.Data.dll)


[SerializableAttribute]
public class DataTable : MarshalByValueComponent, IListSource, 
	ISupportInitializeNotification, ISupportInitialize, ISerializable, 
	IXmlSerializable
[SerializableAttribute]
public ref class DataTable : MarshalByValueComponent, IListSource, 
	ISupportInitializeNotification, ISupportInitialize, ISerializable, 
	IXmlSerializable
[<SerializableAttribute>]
type DataTable = 
    class
        inherit MarshalByValueComponent
        interface IListSource
        interface ISupportInitializeNotification
        interface ISupportInitialize
        interface ISerializable
        interface IXmlSerializable
    end
<SerializableAttribute>
Public Class DataTable
	Inherits MarshalByValueComponent
	Implements IListSource, ISupportInitializeNotification, ISupportInitialize,
	ISerializable, IXmlSerializable
NameDescription
System_CAPS_pubmethod DataTable

Initializes a new instance of the DataTable class with no arguments.

System_CAPS_protmethod DataTable

Initializes a new instance of the DataTable class with the SerializationInfo and the StreamingContext.

System_CAPS_pubmethod DataTable

Initializes a new instance of the DataTable class with the specified table name.

System_CAPS_pubmethod DataTable

Initializes a new instance of the DataTable class using the specified table name and namespace.

NameDescription
System_CAPS_pubproperty

Indicates whether string comparisons within the table are case-sensitive.

System_CAPS_pubproperty

Gets the collection of child relations for this DataTable.

System_CAPS_pubproperty

Gets the collection of columns that belong to this table.

System_CAPS_pubproperty

Gets the collection of constraints maintained by this table.

System_CAPS_pubproperty

Gets the container for the component.(Inherited from MarshalByValueComponent.)

System_CAPS_pubproperty

Gets the DataSet to which this table belongs.

System_CAPS_pubproperty

Gets a customized view of the table that may include a filtered view, or a cursor position.

System_CAPS_pubproperty

Gets a value indicating whether the component is currently in design mode.(Inherited from MarshalByValueComponent.)

System_CAPS_pubproperty

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.

System_CAPS_protproperty

Gets the list of event handlers that are attached to this component.(Inherited from MarshalByValueComponent.)

System_CAPS_pubproperty

Gets the collection of customized user information.

System_CAPS_pubproperty

Gets a value indicating whether there are errors in any of the rows in any of the tables of the DataSet to which the table belongs.

System_CAPS_pubproperty

Gets a value that indicates whether the DataTable is initialized.

System_CAPS_pubproperty

Gets or sets the locale information used to compare strings within the table.

System_CAPS_pubproperty

Gets or sets the initial starting size for this table.

System_CAPS_pubproperty

Gets or sets the namespace for the XML representation of the data stored in the DataTable.

System_CAPS_pubproperty

Gets the collection of parent relations for this DataTable.

System_CAPS_pubproperty

Gets or sets the namespace for the XML representation of the data stored in the DataTable.

System_CAPS_pubproperty

Gets or sets an array of columns that function as primary keys for the data table.

System_CAPS_pubproperty

Gets or sets the serialization format.

System_CAPS_pubproperty

Gets the collection of rows that belong to this table.

System_CAPS_pubproperty

Gets or sets an System.ComponentModelISite for the DataTable.(Overrides .)

System_CAPS_pubproperty

Gets or sets the name of the DataTable.

NameDescription
System_CAPS_pubmethod AcceptChanges

Commits all the changes made to this table since the last time AcceptChanges was called.

System_CAPS_pubmethod BeginInit

Begins the initialization of a DataTable that is used on a form or used by another component. The initialization occurs at run time.

System_CAPS_pubmethod BeginLoadData

Turns off notifications, index maintenance, and constraints while loading data.

System_CAPS_pubmethod Clear

Clears the DataTable of all data.

System_CAPS_pubmethod Clone

Clones the structure of the DataTable, including all DataTable schemas and constraints.

System_CAPS_pubmethod Compute

Computes the given expression on the current rows that pass the filter criteria.

System_CAPS_pubmethod Copy

Copies both the structure and data for this DataTable.

System_CAPS_pubmethod CreateDataReader

Returns a DataTableReader corresponding to the data within this DataTable.

System_CAPS_protmethod CreateInstance

This API supports the product infrastructure and is not intended to be used directly from your code. Creates a new instance of DataTable.

System_CAPS_pubmethod Dispose

Releases all resources used by the MarshalByValueComponent.(Inherited from MarshalByValueComponent.)

System_CAPS_protmethod Dispose

Releases the unmanaged resources used by the MarshalByValueComponent and optionally releases the managed resources.(Inherited from MarshalByValueComponent.)

System_CAPS_pubmethod EndInit

Ends the initialization of a DataTable that is used on a form or used by another component. The initialization occurs at run time.

System_CAPS_pubmethod EndLoadData

Turns on notifications, index maintenance, and constraints after loading data.

System_CAPS_pubmethod Equals

Determines whether the specified object is equal to the current object.(Inherited from Object.)

System_CAPS_protmethod Finalize

Allows an object to try to free resources and perform other cleanup operations before it is reclaimed by garbage collection.(Inherited from MarshalByValueComponent.)

System_CAPS_pubmethod GetChanges

Gets a copy of the DataTable that contains all changes made to it since it was loaded or AcceptChanges was last called.

System_CAPS_pubmethod GetChanges

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.

System_CAPS_pubmethod System_CAPS_static GetDataTableSchema

This method returns an XmlSchemaSet instance containing the Web Services Description Language (WSDL) that describes the DataTable for Web Services.

System_CAPS_pubmethod GetErrors

Gets an array of DataRow objects that contain errors.

System_CAPS_pubmethod GetHashCode

Serves as the default hash function. (Inherited from Object.)

System_CAPS_pubmethod GetObjectData

Populates a serialization information object with the data needed to serialize the DataTable.

System_CAPS_protmethod GetRowType

This API supports the product infrastructure and is not intended to be used directly from your code. Gets the row type.

System_CAPS_protmethod GetSchema

This API supports the product infrastructure and is not intended to be used directly from your code. For a description of this member, see IXmlSerializableGetSchema.

System_CAPS_pubmethod GetService

Gets the implementer of the IServiceProvider.(Inherited from MarshalByValueComponent.)

System_CAPS_pubmethod GetType

Gets the Type of the current instance.(Inherited from Object.)

System_CAPS_pubmethod ImportRow

Copies a DataRow into a DataTable, preserving any property settings, as well as original and current values.

System_CAPS_pubmethod Load

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.

System_CAPS_pubmethod Load

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.

System_CAPS_pubmethod Load

Fills a DataTable with values from a data source using the supplied IDataReader using an error-handling delegate.

System_CAPS_pubmethod LoadDataRow

Finds and updates a specific row. If no matching row is found, a new row is created using the given values.

System_CAPS_pubmethod LoadDataRow

Finds and updates a specific row. If no matching row is found, a new row is created using the given values.

System_CAPS_protmethod MemberwiseClone

Creates a shallow copy of the current Object.(Inherited from Object.)

System_CAPS_pubmethod Merge

Merge the specified DataTable with the current DataTable.

System_CAPS_pubmethod Merge

Merge the specified DataTable with the current DataTable, indicating whether to preserve changes in the current DataTable.

System_CAPS_pubmethod Merge

Merge the specified DataTable with the current DataTable, indicating whether to preserve changes and how to handle missing schema in the current DataTable.

System_CAPS_pubmethod NewRow

Creates a new DataRow with the same schema as the table.

System_CAPS_protmethod NewRowArray

This API supports the product infrastructure and is not intended to be used directly from your code. Returns an array of DataRow.

System_CAPS_protmethod NewRowFromBuilder

Creates a new row from an existing row.

System_CAPS_protmethod OnColumnChanged

Raises the ColumnChanged event.

System_CAPS_protmethod OnColumnChanging

Raises the ColumnChanging event.

System_CAPS_protmethod OnPropertyChanging

Raises the PropertyChanged event.

System_CAPS_protmethod OnRemoveColumn

Notifies the DataTable that a DataColumn is being removed.

System_CAPS_protmethod OnRowChanged

Raises the RowChanged event.

System_CAPS_protmethod OnRowChanging

Raises the RowChanging event.

System_CAPS_protmethod OnRowDeleted

Raises the RowDeleted event.

System_CAPS_protmethod OnRowDeleting

Raises the RowDeleting event.

System_CAPS_protmethod OnTableCleared

Raises the TableCleared event.

System_CAPS_protmethod OnTableClearing

Raises the TableClearing event.

System_CAPS_protmethod OnTableNewRow

Raises the TableNewRow event.

System_CAPS_pubmethod ReadXml

Reads XML schema and data into the DataTable using the specified Stream.

System_CAPS_pubmethod ReadXml

Reads XML schema and data into the DataTable from the specified file.

System_CAPS_pubmethod ReadXml

Reads XML schema and data into the DataTable using the specified TextReader.

System_CAPS_pubmethod ReadXml

Reads XML Schema and Data into the DataTable using the specified XmlReader.

System_CAPS_pubmethod ReadXmlSchema

Reads an XML schema into the DataTable using the specified stream.

System_CAPS_pubmethod ReadXmlSchema

Reads an XML schema into the DataTable from the specified file.

System_CAPS_pubmethod ReadXmlSchema

Reads an XML schema into the DataTable using the specified TextReader.

System_CAPS_pubmethod ReadXmlSchema

Reads an XML schema into the DataTable using the specified XmlReader.

System_CAPS_protmethod ReadXmlSerializable

This API supports the product infrastructure and is not intended to be used directly from your code. Reads from an XML stream.

System_CAPS_pubmethod RejectChanges

Rolls back all changes that have been made to the table since it was loaded, or the last time AcceptChanges was called.

System_CAPS_pubmethod Reset

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.

System_CAPS_pubmethod Select

Gets an array of all DataRow objects.

System_CAPS_pubmethod Select

Gets an array of all DataRow objects that match the filter criteria.

System_CAPS_pubmethod Select

Gets an array of all DataRow objects that match the filter criteria, in the specified sort order.

System_CAPS_pubmethod Select

Gets an array of all DataRow objects that match the filter in the order of the sort that match the specified state.

System_CAPS_pubmethod ToString

Gets the and , if there is one as a concatenated string.(Overrides MarshalByValueComponentToString.)

System_CAPS_pubmethod WriteXml

Writes the current contents of the DataTable as XML using the specified Stream.

System_CAPS_pubmethod WriteXml

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.

System_CAPS_pubmethod WriteXml

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.

System_CAPS_pubmethod WriteXml

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.

System_CAPS_pubmethod WriteXml

Writes the current contents of the DataTable as XML using the specified file.

System_CAPS_pubmethod WriteXml

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.

System_CAPS_pubmethod WriteXml

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.

System_CAPS_pubmethod WriteXml

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.

System_CAPS_pubmethod WriteXml

Writes the current contents of the DataTable as XML using the specified TextWriter.

System_CAPS_pubmethod WriteXml

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.

System_CAPS_pubmethod WriteXml

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.

System_CAPS_pubmethod WriteXml

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.

System_CAPS_pubmethod WriteXml

Writes the current contents of the DataTable as XML using the specified XmlWriter.

System_CAPS_pubmethod WriteXml

Writes the current contents of the DataTable as XML using the specified XmlWriter.

System_CAPS_pubmethod WriteXml

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.

System_CAPS_pubmethod WriteXml

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.

System_CAPS_pubmethod WriteXmlSchema

Writes the current data structure of the DataTable as an XML schema to the specified stream.

System_CAPS_pubmethod WriteXmlSchema

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.

System_CAPS_pubmethod WriteXmlSchema

Writes the current data structure of the DataTable as an XML schema to the specified file.

System_CAPS_pubmethod WriteXmlSchema

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.

System_CAPS_pubmethod WriteXmlSchema

Writes the current data structure of the DataTable as an XML schema using the specified TextWriter.

System_CAPS_pubmethod WriteXmlSchema

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.

System_CAPS_pubmethod WriteXmlSchema

Writes the current data structure of the DataTable as an XML schema using the specified XmlWriter.

System_CAPS_pubmethod WriteXmlSchema

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.

NameDescription
System_CAPS_protfield fInitInProgress

This API supports the product infrastructure and is not intended to be used directly from your code. Checks whether initialization is in progress. The initialization occurs at run time.

NameDescription
System_CAPS_pubevent ColumnChanged

Occurs after a value has been changed for the specified DataColumn in a DataRow.

System_CAPS_pubevent ColumnChanging

Occurs when a value is being changed for the specified DataColumn in a DataRow.

System_CAPS_pubevent Disposed

Adds an event handler to listen to the Disposed event on the component.(Inherited from MarshalByValueComponent.)

System_CAPS_pubevent Initialized

Occurs after the DataTable is initialized.

System_CAPS_pubevent RowChanged

Occurs after a DataRow has been changed successfully.

System_CAPS_pubevent RowChanging

Occurs when a DataRow is changing.

System_CAPS_pubevent RowDeleted

Occurs after a row in the table has been deleted.

System_CAPS_pubevent RowDeleting

Occurs before a row in the table is about to be deleted.

System_CAPS_pubevent TableCleared

Occurs after a DataTable is cleared.

System_CAPS_pubevent TableClearing

Occurs when a DataTable is cleared.

System_CAPS_pubevent TableNewRow

Occurs when a new DataRow is inserted.

NameDescription
System_CAPS_pubinterface System_CAPS_privmethod IListSourceGetList

For a description of this member, see IListSourceGetList.

System_CAPS_pubinterface System_CAPS_privmethod IXmlSerializableGetSchema

For a description of this member, see IXmlSerializableGetSchema.

System_CAPS_pubinterface System_CAPS_privmethod IXmlSerializableReadXml

For a description of this member, see IXmlSerializableReadXml.

System_CAPS_pubinterface System_CAPS_privmethod IXmlSerializableWriteXml

For a description of this member, see IXmlSerializableWriteXml.

System_CAPS_pubinterface System_CAPS_privproperty

For a description of this member, see .

NameDescription
System_CAPS_pubmethod AsDataView

Overloaded. Creates and returns a LINQ-enabled DataView object.(Defined by DataTableExtensions.)

System_CAPS_pubmethod AsEnumerable

Returns an IEnumerable object, where the generic parameter T is DataRow. This object can be used in a LINQ expression or method query.(Defined by DataTableExtensions.)

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 property value but different property values. For more information about working with DataTable objects, see Creating a DataTable.

If you are creating a DataTable programmatically, you must first define its schema by adding DataColumn objects to the DataColumnCollection (accessed through the property). For more information about adding DataColumn objects, see Adding Columns to a DataTable.

To add rows to a DataTable, you must first use the NewRow method to return a new DataRow object. The NewRow method returns a row with the schema of the DataTable, as it is defined by the table's DataColumnCollection. The maximum number of rows that a DataTable can store is 16,777,216. For more information, see Adding Data to 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.

There are many DataTable events that can be used to determine when changes are made to a table. These include RowChanged, RowChanging, RowDeleting, and RowDeleted. For more information about the events that can be used with a DataTable, see Handling DataTable Events.

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 DataTableDataTable constructor topic.

System_CAPS_noteNote

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.

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.

// 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");
}
' Put the next line into the Declarations section.
private dataSet As DataSet 

Private Sub MakeDataTables()
    ' Run all of the functions. 
    MakeParentTable()
    MakeChildTable()
    MakeDataRelation()
    BindToDataGrid()
End Sub

Private Sub MakeParentTable()
    ' Create a new DataTable.
    Dim table As DataTable = new DataTable("ParentTable")

    ' Declare variables for DataColumn and DataRow objects.
    Dim column As DataColumn 
    Dim row As DataRow 

    ' 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.
    Dim PrimaryKeyColumns(0) As DataColumn
    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
    Dim i As Integer
    For i = 0 to 2
       row = table.NewRow()
       row("id") = i
       row("ParentItem") = "ParentItem " + i.ToString()
       table.Rows.Add(row)
    Next i
End Sub

Private Sub MakeChildTable()
    ' Create a new DataTable.
    Dim table As DataTable = New DataTable("childTable")
    Dim column As DataColumn 
    Dim row As DataRow 

    ' 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.
    Dim i As Integer
    For i = 0 to 4
       row = table.NewRow()
       row("childID") = i
       row("ChildItem") = "Item " + i.ToString()
       row("ParentID") = 0 
       table.Rows.Add(row)
    Next i
    For i = 0 to 4
       row = table.NewRow()
       row("childID") = i + 5
       row("ChildItem") = "Item " + i.ToString()
       row("ParentID") = 1 
       table.Rows.Add(row)
    Next i
    For i = 0 to 4
       row = table.NewRow()
       row("childID") = i + 10
       row("ChildItem") = "Item " + i.ToString()
       row("ParentID") = 2 
       table.Rows.Add(row)
    Next i
End Sub

Private Sub MakeDataRelation()
    ' DataRelation requires two DataColumn 
    ' (parent and child) and a name.
    Dim parentColumn As DataColumn = _
        dataSet.Tables("ParentTable").Columns("id")
    Dim childColumn As DataColumn = _
        dataSet.Tables("ChildTable").Columns("ParentID")
    Dim relation As DataRelation = new _
        DataRelation("parent2Child", parentColumn, childColumn)
    dataSet.Tables("ChildTable").ParentRelations.Add(relation)
End Sub

Private Sub BindToDataGrid()
    ' Instruct the DataGrid to bind to the DataSet, with the 
    ' ParentTable as the topmost DataTable.
    DataGrid1.SetDataBinding(dataSet,"ParentTable")
End Sub

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.

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();
   }
}
Imports System.Data

Class Program
   Public Shared Sub Main(args As String())
      ' Create two tables and add them into the DataSet
      Dim orderTable As DataTable = CreateOrderTable()
      Dim orderDetailTable As DataTable = CreateOrderDetailTable()
      Dim salesSet As 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
         Dim errorRow As DataRow = orderDetailTable.NewRow()
         errorRow(0) = 1
         errorRow(1) = "O0007"
         orderDetailTable.Rows.Add(errorRow)
      Catch e As Exception
         Console.WriteLine(e.Message)
      End Try
      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.
      Dim colSub As New DataColumn("SubTotal", GetType([Decimal]), "Sum(Child.LineTotal)")
      orderTable.Columns.Add(colSub)

      ' Compute the tax by referencing the SubTotal expression column.
      Dim colTax As New DataColumn("Tax", GetType([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.
      Dim colTotal As New DataColumn("TotalDue", GetType([Decimal]), "IIF(OrderId='Total',Sum(SubTotal)+Sum(Tax),SubTotal+Tax)")
      orderTable.Columns.Add(colTotal)

      Dim row As DataRow = 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()
   End Sub

   Private Shared Function CreateOrderTable() As DataTable
      Dim orderTable As New DataTable("Order")

      ' Define one column.
      Dim colId As New DataColumn("OrderId", GetType([String]))
      orderTable.Columns.Add(colId)

      Dim colDate As New DataColumn("OrderDate", GetType(DateTime))
      orderTable.Columns.Add(colDate)

      ' Set the OrderId column as the primary key.
      orderTable.PrimaryKey = New DataColumn() {colId}

      Return orderTable
   End Function

   Private Shared Function CreateOrderDetailTable() As DataTable
      Dim orderDetailTable As New DataTable("OrderDetail")

      ' Define all the columns once.
      Dim cols As DataColumn() = {New DataColumn("OrderDetailId", GetType(Int32)), New DataColumn("OrderId", GetType([String])), New DataColumn("Product", GetType([String])), New DataColumn("UnitPrice", GetType([Decimal])), New DataColumn("OrderQty", GetType(Int32)), New DataColumn("LineTotal", GetType([Decimal]), "UnitPrice*OrderQty")}

      orderDetailTable.Columns.AddRange(cols)
      orderDetailTable.PrimaryKey = New DataColumn() {orderDetailTable.Columns("OrderDetailId")}
      Return orderDetailTable
   End Function

   Private Shared Sub InsertOrders(orderTable As DataTable)
      ' Add one row once.
      Dim row1 As DataRow = orderTable.NewRow()
      row1("OrderId") = "O0001"
      row1("OrderDate") = New DateTime(2013, 3, 1)
      orderTable.Rows.Add(row1)

      Dim row2 As DataRow = orderTable.NewRow()
      row2("OrderId") = "O0002"
      row2("OrderDate") = New DateTime(2013, 3, 12)
      orderTable.Rows.Add(row2)

      Dim row3 As DataRow = orderTable.NewRow()
      row3("OrderId") = "O0003"
      row3("OrderDate") = New DateTime(2013, 3, 20)
      orderTable.Rows.Add(row3)
   End Sub

   Private Shared Sub InsertOrderDetails(orderDetailTable As DataTable)
      ' 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.
      Dim rows As [Object]() = {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}}

      For Each row As [Object]() In rows
         orderDetailTable.Rows.Add(row)
      Next
   End Sub

   Private Shared Sub ShowTable(table As DataTable)
      For Each col As DataColumn In table.Columns
         Console.Write("{0,-14}", col.ColumnName)
      Next
      Console.WriteLine()

      For Each row As DataRow In table.Rows
         For Each col As DataColumn In table.Columns
            If col.DataType.Equals(GetType(DateTime)) Then
               Console.Write("{0,-14:d}", row(col))
            ElseIf col.DataType.Equals(GetType([Decimal])) Then
               Console.Write("{0,-14:C}", row(col))
            Else
               Console.Write("{0,-14}", row(col))
            End If
         Next
         Console.WriteLine()
      Next
      Console.WriteLine()
   End Sub
End Class
.NET Framework
Available since 1.1

This type is safe for multithreaded read operations. You must synchronize any write operations.

Return to top