DataTable C#

DataTable C#

DataTable:

DataTable: It is a mini in memory spread sheet which stores data like a database in form of Cells which combines rows and columns. Or we can treat it as a Excel Worksheet which stores data in Tabular format.

The DataTable class stores rows and columns of data. It is part of the System.Data namespace.

[SerializableAttribute]
public class DataTable : MarshalByValueComponent, IListSource, 
	ISupportInitializeNotification, ISupportInitialize, ISerializable, 
	IXmlSerializable

Code example:

using System.Data;
public DataTable GenerateTable()
{
	DataTable table = new DataTable();
	table.Columns.Add("Age", typeof(int));
	table.Columns.Add("FirstName", typeof(string));
	table.Columns.Add("LastName", typeof(string));
	table.Columns.Add("DOB", typeof(DateTime));
	// Here we add two DataRows.
	table.Rows.Add(10, "Shaquib", "Khan", DateTime.Now); 
	table.Rows.Add(20, "Nawazish", "Khan", DateTime.Now); return table; 
} 

Example: Adding DataColumn objects to an Order DataTable Object

//Add the DataColumn using all properties
DataColumn ordId = new DataColumn("OrderID");
ordId.DataType = typeof(int);
ordId.Unique = true;
ordId.AllowDBNull = false;
ordId.Caption = "ORDER ID";
order.Columns.Add(ordId);

//Add the DataColumn using defaults
DataColumn quanty = new DataColumn("quantyity");
quanty.MaxLength = 35;
quanty.AllowDBNull = false;
order.Columns.Add(quanty);
DataColumn dcDate = new DataColumn("DeliveryDate", typeof(DateTime));
dcDate.AllowDBNull = false;
order.Columns.Add(dcDate);
 
//Derived column using expression
DataColumn makingYear = new DataColumn("Year and Make");
makingYear.DataType = typeof(string);
makingYear.MaxLength = 70;
makingYear.Expression = "Year + ' ' + Make";
order.Columns.Add(makingYear);

Creating Primary Key Columns: The primary key of a DataTable object consists of a column or columns that make up a unique identity for each data row. The following code shows how to set the PrimaryKey property for the order DataTable object:

//Set the Primary Key
order.PrimaryKey = new DataColumn[] { OrderID };

Auto Numbering for the Primary Key Column: You can set up an auto-increment column, by setting the AutoIncrement property of your data column to true. After that, you set AutoIncrementSeed to the value of the first number you want and set AutoIncrementStep to the value you want to increment each time a new row is added.

The following example shows how to set the AutoIncrement, AutoIncrementSeed, and AutoIncrementStep properties.

private void AddAutoIncrementColumn()
{
    DataColumn column = new DataColumn();
    column.DataType = System.Type.GetType("System.Int32");
    column.AutoIncrement = true;
    column.AutoIncrementSeed = 1000;
    column.AutoIncrementStep = 10;
    // Add the column to a new DataTable.
    DataTable dataTable = new DataTable("tableName");
    dataTable.Columns.Add(column);
}

Delete Data Row: You can delete a DataRow from the DataRowCollection by calling the Remove method of the DataRowCollection, or by calling the Delete method of the DataRow object. The Remove method removes the row from the collection. In contrast, Delete marks the DataRow for removal. The actual removal occurs when you call AcceptChanges method. The DataRow object doesn’t have an Undelete method. However, in some situations, you can use the RejectChanges method to roll back to a previous state when the deleted row was still there. Be aware that executing the RejectChanges method copies the Original data row version to the Current data row version. Please refer below definitions for each property in detail.

Copy DataTable:

private void CopyDataTable(DataTable myDataTable)
{
    // Create an object variable for the copy.
    DataTable dtTable;
    dtTable = myDataTable.Copy();
    // Copid work comes here.
}

Cloning DataTable:

private void GetClone(DataTable myDataTable)
{
    // Get a clone of the original DataTable.
    DataTable cTable;
    cTable = myDataTable.Clone();
    // Put code to work with clone of the DataTable.
}

Constructors:

DataTable(): Initializes a new instance of the DataTable class with no arguments. DataTable(SerializationInfo, StreamingContext): Initializes a new instance of the DataTable class with the SerializationInfo and the StreamingContextDataTable(String): Initializes a new instance of the DataTable class with the specified table name. DataTable(String,String): Initializes a new instance of the DataTable class using the specified table name and namespace.

Properties:

  • CaseSensitive: Indicates whether string comparisons within the table are case-sensitive.
  • ChildRelations: Gets the collection of child relations for this DataTable.
  • Columns: Gets the collection of columns that belong to this table.
  • Constraints: Gets the collection of constraints maintained by this table.
  • Container: Gets the container for the component.(Inherited from MarshalByValueComponent)
  • DataSet: Gets the DataSet to which this table belongs.
  • DefaultView: Gets a customized view of the table that may include a filtered view, or a cursor position.
  • DesignMode: Gets a value indicating whether the component is currently in design mode.(Inherited from MarshalByValueComponent)
  • DisplayExpression: 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.
  • Events: Gets the list of event handlers that are attached to this component.(Inherited from MarshalByValueComponent)
  • ExtendedProperties: Gets the collection of customized user information.
  • HasErrors: 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.
  • IsInitialized: Gets a value that indicates whether the DataTable is initialized.
  • Locale: Gets or sets the locale information used to compare strings within the table.
  • MinimumCapacity: Gets or sets the initial starting size for this table.
  • Namespace: Gets or sets the namespace for the XML representation of the data stored in the DataTable.
  • Namespace: Gets or sets the namespace for the XML representation of the data stored in the DataTable.
  • ParentRelations: Gets the collection of parent relations for this DataTable.
  • Prefix: Gets or sets the namespace for the XML representation of the data stored in the DataTable.
  • PrimaryKey: Gets or sets an array of columns that function as primary keys for the data table.
  • RemotingFormat: Gets or sets the serialization format.Rows: Gets the collection of rows that belong to this table.
  • Site: Gets or sets an System.ComponentModel.ISite for the DataTable.(Overrides MarshalByValueComponent.Site.)
  • TableName: Gets or sets the name of the DataTable.

Methods:

  • AcceptChanges(): Commits all the changes made to this table since the last time AcceptChanges was called.
  • BeginInit(): Begins the initialization of a DataTable that is used on a form or used by another component. The initialization occurs at run time.
  • BeginLoadData(): Turns off notifications, index maintenance, and constraints while loading data.
  • Clear(): Clears the DataTable of all data.
  • Clone(): Clones the structure of the DataTable, including all DataTable schemas and constraints.
  • Compute(String,String): Computes the given expression on the current rows that pass the filter criteria.
  • Copy(): Copies both the structure and data for this DataTable.
  • CreateDataReader(): Returns a DataTableReader corresponding to the data within this DataTable.
  • CreateInstance(): This API supports the product infrastructure and is not intended to be used directly from your code. Creates a new instance of DataTable.
  • Dispose(): Releases all resources used by the MarshalByValueComponent.(Inherited from MarshalByValueComponent.)
  • Dispose(Boolean): Releases the unmanaged resources used by the MarshalByValueComponent and optionally releases the managed resources.(Inherited from MarshalByValueComponent.)
  • EndInit(): Ends the initialization of a DataTable that is used on a form or used by another component. The initialization occurs at run time.
  • EndLoadData(): Turns on notifications, index maintenance, and constraints after loading data.
  • Equals(Object): Determines whether the specified object is equal to the current object.(Inherited from Object.)
  • Finalize(): Allows an object to try to free resources and perform other cleanup operations before it is reclaimed by garbage collection.(Inherited from MarshalByValueComponent.)
  • GetChanges(): Gets a copy of the DataTable that contains all changes made to it since it was loaded or AcceptChanges was last called.
  • GetChanges(DataRowState): 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.
  • GetDataTableSchema(XmlSchemaSet): This method returns an XmlSchemaSet instance containing the Web Services Description Language (WSDL) that describes the DataTable for Web Services.
  • GetErrors(): Gets an array of DataRow objects that contain errors.
  • GetHashCode(): Serves as the default hash function. (Inherited from Object.)
  • GetObjectData(SerializationInfo,StreamingContext): Populates a serialization information object with the data needed to serialize the DataTable.
  • GetRowType(): This API supports the product infrastructure and is not intended to be used directly from your code. Gets the row type.
  • 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 IXmlSerializable.GetSchema.
  • GetService(Type): Gets the implementer of the IServiceProvider.(Inherited from MarshalByValueComponent.)
  • GetType(): Gets the Type of the current instance.(Inherited from Object.)
  • ImportRow(DataRow): Copies a DataRow into a DataTable, preserving any property settings, as well as original and current values.
  • Load(IDataReader): 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.
  • Load(IDataReader,LoadOption): 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.
  • Load(IDataReader,LoadOption,FillErrorEventHandler): Fills a DataTable with values from a data source using the supplied IDataReader using an error-handling delegate.
  • LoadDataRow(Object[],Boolean): Finds and updates a specific row. If no matching row is found, a new row is created using the given values.
  • LoadDataRow(Object[],LoadOption): Finds and updates a specific row. If no matching row is found, a new row is created using the given values.
  • MemberwiseClone(): Creates a shallow copy of the current Object.(Inherited from Object.)
  • Merge(DataTable): Merge the specified DataTable with the current DataTable.
  • Merge(DataTable,Boolean): Merge the specified DataTable with the current DataTable, indicating whether to preserve changes in the current DataTable.
  • Merge(DataTable,Boolean,MissingSchemaAction): Merge the specified DataTable with the current DataTable, indicating whether to preserve changes and how to handle missing schema in the current DataTable.
  • NewRow(): Creates a new DataRow with the same schema as the table.
  • NewRowArray(Int32): This API supports the product infrastructure and is not intended to be used directly from your code. Returns an array of DataRow.
  • NewRowFromBuilder(DataRowBuilder): Creates a new row from an existing row.
  • ReadXml(Stream): Reads XML schema and data into the DataTable using the specified Stream.
  • ReadXml(String): Reads XML schema and data into the DataTable from the specified file.
  • ReadXml(TextReader): Reads XML schema and data into the DataTable using the specified TextReader.
  • ReadXml(XmlReader): Reads XML Schema and Data into the DataTable using the specified XmlReader.
  • ReadXmlSchema(Stream): Reads an XML schema into the DataTable using the specified stream.
  • ReadXmlSchema(String): Reads an XML schema into the DataTable from the specified file.
  • ReadXmlSchema(TextReader): Reads an XML schema into the DataTable using the specified TextReader.
  • ReadXmlSchema(XmlReader): Reads an XML schema into the DataTable using the specified XmlReader.
  • ReadXmlSerializable(XmlReader): This API supports the product infrastructure and is not intended to be used directly from your code. Reads from an XML stream.
  • RejectChanges(): Rolls back all changes that have been made to the table since it was loaded, or the last time AcceptChanges was called.
  • 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.
  • Select(): Gets an array of all DataRow objects.
  • Select(String): Gets an array of all DataRow objects that match the filter criteria.
  • Select(String,String): Gets an array of all DataRow objects that match the filter criteria, in the specified sort order.
  • Select(String,String,DataViewRowState): Gets an array of all DataRow objects that match the filter in the order of the sort that match the specified state.
  • ToString(): Gets the TableName and DisplayExpression, if there is one as a concatenated string.(Overrides MarshalByValueComponent.ToString().)
  • WriteXml(Stream): Writes the current contents of the DataTable as XML using the specified Stream.
  • WriteXml(Stream,Boolean): 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.
  • WriteXml(Stream,XmlWriteMode): 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.
  • WriteXml(Stream,XmlWriteMode,Boolean): 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.
  • WriteXml(String): Writes the current contents of the DataTable as XML using the specified file.
  • WriteXml(String,Boolean): 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.
  • WriteXml(String,XmlWriteMode): 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.
  • WriteXml(String,XmlWriteMode,Boolean): 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.
  • WriteXml(TextWriter): Writes the current contents of the DataTable as XML using the specified TextWriter.
  • WriteXml(TextWriter,Boolean): 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.
  • WriteXml(TextWriter,XmlWriteMode): 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.
  • WriteXml(TextWriter,XmlWriteMode,Boolean): 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.
  • WriteXml(XmlWriter): Writes the current contents of the DataTable as XML using the specified XmlWriter.
  • WriteXml(XmlWriter,Boolean): Writes the current contents of the DataTable as XML using the specified XmlWriter.
  • WriteXml(XmlWriter,XmlWriteMode): 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.
  • WriteXml(XmlWriter,XmlWriteMode,Boolean): 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.
  • WriteXmlSchema(Stream): Writes the current data structure of the DataTable as an XML schema to the specified stream.
  • WriteXmlSchema(Stream,Boolean): 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.
  • WriteXmlSchema(String): Writes the current data structure of the DataTable as an XML schema to the specified file.
  • WriteXmlSchema(String,Boolean): 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.
  • WriteXmlSchema(TextWriter): Writes the current data structure of the DataTable as an XML schema using the specified TextWriter.
  • WriteXmlSchema(TextWriter,Boolean): 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.
  • WriteXmlSchema(XmlWriter): Writes the current data structure of the DataTable as an XML schema using the specified XmlWriter.
  • WriteXmlSchema(XmlWriter,Boolean): 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.

DataTable Events:

  • OnColumnChanged(DataColumnChangeEventArgs): Raises the ColumnChanged event.
  • OnColumnChanging(DataColumnChangeEventArgs): Raises the ColumnChanging event.
  • OnPropertyChanging(PropertyChangedEventArgs): Raises the PropertyChanged event.
  • OnRemoveColumn(DataColumn): Notifies the DataTable that a DataColumn is being removed.
  • OnRowChanged(DataRowChangeEventArgs): Raises the RowChanged event.
  • OnRowChanging(DataRowChangeEventArgs): Raises the RowChanging event.
  • OnRowDeleted(DataRowChangeEventArgs): Raises the RowDeleted event.
  • OnRowDeleting(DataRowChangeEventArgs): Raises the RowDeleting event.
  • OnTableCleared(DataTableClearEventArgs): Raises the TableCleared event.
  • OnTableClearing(DataTableClearEventArgs): Raises the TableClearing event.
  • OnTableNewRow(DataTableNewRowEventArgs): Raises the TableNewRow event.

Next >> Deserialize Json to DataTable C#

Leave a Reply

Your email address will not be published.