Post
Ask Question

DataTable And DataTableCollection Class In ADO.NET

Introduction

 
The DataTable is an in-memory representation of a table including columns, rows, and unique and foreign key constraints. It is used for working with and saving data in the data source in a disconnected environment. The DataTable objects can work with any of the data providers.
 

Properties, Methods and Events of DataTable Class

  • Constraints - This property retrieves constraints of the current table.
  • DefaultView - This property retrieves a customized view of the table that includes a filtered view, or a cursor position.
  • Locale - This property only retrieves the locale details and information for comparing strings in the table.
  • PrimaryKey - This property specifies or retrieves primary key columns.
  • GetErrors - This method retrieves an array of DataRow objects having errors.
  • ImportRow - This method copies the specified DataRow into a DataTable along with its property settings, original and current values.
  • Merge - This method merges the specified DataTable with the present DataTable.
  • RejectChanges - This method discards or rejects all the changes made to the table since it was loaded or since the last time the AcceptChanges() method was called.
  • TableCleared - Event occurs after a DataTable is cleared.
Source code demonstrates how to use the DataTable object along with the SqlCommandBuilder class to add a new column and row into a table of the database.
  1. SqlConnection sqlconAirspeed=new SqlConnection(“Data source=MYDATA\\SQLEXPRESS; Initial Catalog=Airspeed; Integrated Security="SSIP”);  
  2. SqlDataAdapter sqldaAirSpeed=new SqlDataAdapter(“SELECT * FROM Employee”, sqlconAirspeed);  
  3. SqlCommandBuilder sqlcombEmployee=new SqlCommandBuilder(sqldaAirSpeed);  
  4. DataSet dsetAirSpeed=new DataSet();  
  5. sqldaAirSpeed.Fill(dsetAirSpeed,”Employee”);  
  6. DataGridView dgvwEmployee=new DataGridView();  
  7. DataTable dtableEmployee=dsetAirSpeed.Tables(“Employee”);  
  8. DataColumn[] dcolKey=new DataColumn[1];  
  9. dcolKey[0]=dtableEmployee.Columns[“EmpID”];  
  10. dtableEmployee.PrimaryKey=dcolKey;  
  11. DataRow drowEmployee=dsetAirSpeed.Tables[“Employee”].NewRow();  
  12. drowEmployee[0]=”A001”;  
  13. drowEmployee[1]=”Ashish”;  
  14. drowEmployee[2]=”3432-432-7438”;  
  15. dtableEmployee.Rows.Add[drowEmployee);  
  16. sqldaAirSpeed.Update(dtableEmployee);  
  17. dgvwEmployee.DataSource=dvwEmployee.Table;  
  18. dgvwEmployee.Sort=EName DESC”;  
In this code, the SqlDataAdapter object is passed to the constructor of SqlCommandBuilder class. This class generates commands to update the table with the changes, made to the DataSet. The DataSet is filled with the records of Employee table . An instance of the DataTable class is created and is populated with the records of Employee table. An array object of the DataColumn class is created and refers to the EmpID column of the Employee table. This column is set as the primary key in the DataSet by using the PrimaryKey Property. A new row is added to the table in the DataSet using the NewRow() method. Finally , the new row is added to the table using the Update() method of SqlDataAdapter class. The DataGridView control is used to display the records of the table in descending order of employee names. This is specified using the Sort property of DataGridView class.
 

DataTableCollection Class

 
The DataTableCollection class consists of a collection of tables used by the object of the DataSet class. The DataTableCollection object can be accessed by using the Tables property of the DataSet class. The class manages the tables using the Add(), Remove() and Clear() methods.
 
Following are the properties, methods and events of DataTableCollection class.
  • IsReadOnly - This property retrieves a value that indicates whether the InternalDataCollectionBase class is read-only.
  • AddRange - This method copies the DataTable elements to the end of the collection.
  • IndexOf - This method retrieves the index in the DataTable object collection.
  • RemoveAt - This method removes the DataTable object from the specific index in the collection.
  • CollectionChanging - This event occurs when the DataTable objects are added or removed in the DataTableCollection. 
Source code demonstrate how to retrieve the table names existing in the DataTableCollection object.
  1. SqlConnection sqlconABMotors = new SqlConnection(“Data Source = MYDATA\\ SQLEXPRESS; Initial Catalog = ABMotors; Integrated Security = SSIP”);  
  2. SqlDataAdapter sqldaABMotors = new SqlDataAdapter(“SELECT * FROM TwoWheeler”, sqlconABMotors);  
  3. DataSet dsetABMotors = new DataSet();  
  4. sqldaABMotors.Fill(dsetABMotors, ”FourWheeler”);  
  5. ListBox lstIndex = new ListBox();  
  6. DataTableCollection dtableCollection = dsetABMotors.Tables;  
  7. foreach(DataTable dtableIndex in dtableCollection) {  
  8.     lstIndex.Items.Add(dtableIndex.TableName);  
  9. }  
In this source code, the DataSet object is filled with the records of two tables namely, TwoWheeler and FourWheeler. The DataTableCollection object refers
to these tables. This is done by using the Tables property of the DataSet . All the tables existing in the collection are added to the ListBox using the ForEach loop and the TableName property of the DataTable class.
 

Setting Relation between Tables

 
A DataRelation object can be used to automatically generated and apply a ForeignKeyConstraint or to navigate through records or tables. The DataRelation object allows users to navigate through both parent and child records of the related tables even in the disconnected architecture. For example, if a parent row is selected, the DataRelation object can be used to retrieve its child rows.
 
Source code below demonstrate how to display two related tables
  1. SqlConnection sqlAutomobileInverntory=new SqlConnection(“Data Source=MYDATA\\SQLEXPRESS;Initial Catalog=AutomobileInventory;Integrated Security=SSIP”);    
  2. DataSet dsetAutomobileInventory=new DataSet();    
  3. SqlDataAdapter sqldaAutomobileInventory=new SqlDataAdapter(“SELECT * FROM Customers”, sqlconAutomobileInventory);    
  4. sqldaAutomobileInventory.Fill(dsetAutomobileInventory,”Customers”);    
  5. sqldaAutomobileInventory=new SqlDataAdapter(“SELECT * from Orders”,sqlconAutomobileInventory);    
  6. sqldaAutomobileInventory.Fill(dsetAutomobileInventory,”Orders”);    
  7. DataColumn dcolParCol=dsetAutomobileInventory.Tables[“Customers”].Columns[“CustomerID”];    
  8. DataColumn dcolChiCol=dsetAutomobileInventory.Tables[“Orders”].Columns[“CustomerID”];    
  9. DataRelation drelCustomer=new DataRelation(“CustRelation”,dcolParCol,dcolChicol);    
  10. dsetAutomobileInventory.Relations.Add(drelCustomer);    
  11. DataGrid dgridCustomers=new DataGrid();    
  12. dgridCustomers.Size=new Size(300,300);    
  13. dgridCustomers.DataSource=dsetAutomobileInventory.Tables(“Customers”);    
In this source code, the DataSet object is filled with records of Customers and Orders table. Two instances of the DataColumn calss are created , which refers to the common column in both the tables. An instance of the DataRelation class is created . The Constructor of this class takes the name of the relation and the two DataColumn objects as the parameter. The Relations property is used to add the relation in the DataSet. The DataGrid control is used to display the related tables in a tabular format. This is done by linking the control to the DataSet using the DataSource property of the DataGrid class.
 

Summary

 
The DataTable is an in-memory representation of a table including columns, rows, and unique and foreign key constraints. The DataTableCollection class consists of a collection of tables used by the object of the DataSet class. A DataRelation object can be used to automatically generated and apply a ForeignKeyConstraint or to navigate through records or tables.


Similar Articles