DataTable and Dataset

The Data Table class properties

Table 1:
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



The Data Table Class Methods 

Table 2:

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


DataTable.Copy Vs DataTable.Clone

Both the methods DataTable.Clone and DataTable.Copyare are used to make copies of existing datatables, the difference is

DataTable.Clone() - Copies only the structure of the source table with the constraints, the data is not copied from the source table.


DataTable.Copy() - Copies the structure and data from the source table.



Example: DataTable.Clone()


DataTable targetTable;
targetTable = sourceTable.Clone();

Here, the targetTable is created with only the structure of the sourceTable
targetTable.Rows.Count() //returns 0.

Example: DataTable.Copy()

DataTable targetTable;
targetTable = sourceTable.Copy();

Here, the targetTable is created with a copy of the structure and data from the sourceTable
targetTable.Rows.Count() returns the number of rows which were present in the sourceTable


Example:

            DataSet ds = new DataSet();
            DataTable dtDept = new DataTable("tblDepartment");
            //or: DataTable dtDept = new DataTable();
            DataTable dtEmp = new DataTable("tblEmployee");   
            //or: DataTable dtEmp = new DataTable();



//-----------------------------------------Creating Dept Table---------------

            DataColumn deptPK = new DataColumn {
                                    ColumnName = "DepartmentID",   
  DataType = typeof(Int32),
  AutoIncrement = true,
  AutoIncrementSeed = 1,
  AutoIncrementStep = 1 };

            dtDept.Columns.Add(deptPK);

            dtDept.Columns.Add(new DataColumn {
ColumnName = "DeptName",
DataType = typeof(string) });

            dtDept.PrimaryKey = new DataColumn[] { deptPK };

            ds.Tables.Add(dtDept);


//-----------------------------------------Creating Emp Table----------------------------

            DataColumn empPK = new DataColumn {
ColumnName = "EmpID",
DataType = typeof(Int32),
AutoIncrement = true,
AutoIncrementSeed = 1,
AutoIncrementStep = 1 };

            DataColumn colName = new DataColumn {
  ColumnName = "Name",
  DataType = typeof(string) };

            dtEmp.Columns.Add(empPK);

            dtEmp.Columns.Add(colName);

            dtEmp.Columns.Add(new DataColumn {
      ColumnName = "City",
      DataType = typeof(string),
      AllowDBNull = true });

           
dtEmp.Columns.Add(new DataColumn {
 ColumnName = "Salary",
 DataType = typeof(double),
 AllowDBNull = true });

            dtEmp.Columns.Add(new DataColumn {
      ColumnName = "DOJ",
      DataType = typeof(DateTime),
      AllowDBNull = true });

            dtEmp.Columns.Add(new DataColumn {
                                 ColumnName = "DepartmentID",
                                 DataType = typeof(int),
      AllowDBNull = true });

           
//PK, UK and Fk Constraints

            dtEmp.PrimaryKey = new DataColumn[] { empPK };

            UniqueConstraint unq = new UniqueConstraint(new DataColumn[] { colName });

            dtEmp.Constraints.Add(unq);

            ds.Tables.Add(dtEmp);

            ForeignKeyConstraint fk = new ForeignKeyConstraint("ForeignKey", dtDept.Columns["DepartmentID"], dtEmp.Columns["DepartmentID"]);

            dtEmp.Constraints.Add(fk);


//-----------------------------------------Adding Data to the Tables---------

            DataRow dr = ds.Tables[0].NewRow();
            // or: ds.Tables["tblDepartment"].NewRow();
            dr["DeptName"] = "IT";
            ds.Tables[0].Rows.Add(dr);

            dr = ds.Tables[0].NewRow();
            dr["DeptName"] = "HR";
            ds.Tables[0].Rows.Add(dr);


            dr = ds.Tables[1].NewRow();
            // or: ds.Tables["tblEmployee"].NewRow();
            dr["Name"] = "Junaid";
            dr["City"] = "Hyderabad";
            dr["Salary"] = 12000;
            dr["DOJ"] = new DateTime(2014, 01, 29);
            dr["DepartmentID"] = 1;
            ds.Tables[1].Rows.Add(dr);

            dr = ds.Tables[1].NewRow();
            dr["Name"] = "Taha";
            dr["City"] = "Hyderabad";
            dr["Salary"] = 12000;
            dr["DOJ"] = new DateTime(2014, 01, 29);
            dr["DepartmentID"] = 2;
            ds.Tables[1].Rows.Add(dr);

            dr = ds.Tables[1].NewRow();
            dr["Name"] = "Goa";
            dr["City"] = "Hyderabad";
            dr["Salary"] = 12000;
            dr["DOJ"] = new DateTime(2014, 01, 29);
            dr["DepartmentID"] = 2;
            ds.Tables[1].Rows.Add(dr);


            //  ds.Tables[1].Rows.Add(new object[] { 1, "Dylan", "Hyderabad", 20000, new DateTime(2014, 01, 29), 1 });



//---------------------------------Data Relation on DS-----------------------

            DataRelation parentChildRelation = new DataRelation("ParentChild", dtDept.Columns["DepartmentID"], dtEmp.Columns["DepartmentID"]);
            ds.Relations.Add(parentChildRelation);


            Console.WriteLine("Get Child Rows");

            foreach (DataRow drdept in dtDept.Rows)
            {
                Console.WriteLine("Dept Name: {0}", drdept["DeptName"].ToString());

                foreach (DataRow drEmp in drdept.GetChildRows(parentChildRelation))
                {

                    Console.WriteLine("Emp Name: {0}", drEmp["Name"].ToString());
                }
            }


            Console.WriteLine("Get Parent Rows");

            foreach (DataRow drEmp in dtEmp.Rows)
            {
                Console.WriteLine("Emp Name: {0}", drEmp["Name"].ToString());

                foreach (DataRow drDept in drEmp.GetParentRows(parentChildRelation))
                {

                    Console.WriteLine("Dept Name: {0}", drDept["DeptName"].ToString());
                }
            }



            Console.ReadKey();


Note: Using DataTable we can set Primary key and Unique Key Constraints. But the Foreign Key – primary key relation and Data Relations will work only with the Data Set. The foreign key relation will not work in the below example.

Example:
                DataTable dtEmp = new DataTable();
                DataTable dtDept = new DataTable();

              

                DataColumn deptPK = new DataColumn {
      ColumnName = "DepartmentID",
      DataType = typeof(Int32),
      AutoIncrement = true,
      AutoIncrementSeed = 1,
      AutoIncrementStep = 1};

                dtDept.Columns.Add(deptPK);

                dtDept.Columns.Add(new DataColumn {
                                       ColumnName = "DeptName",
     DataType = typeof(string) });

                dtDept.PrimaryKey = new DataColumn[] { deptPK };



            


                DataColumn empPK = new DataColumn {
    ColumnName = "EmpID",
    DataType = typeof(Int32),
    AutoIncrement = true,
    AutoIncrementSeed = 1,
    AutoIncrementStep = 1 };

                DataColumn colName = new DataColumn {
       ColumnName = "Name",
DataType = typeof(string) };

                dtEmp.Columns.Add(empPK);

                dtEmp.Columns.Add(colName);

                dtEmp.Columns.Add(new DataColumn {
   ColumnName = "City",
   DataType = typeof(string),
   AllowDBNull = true });

                dtEmp.Columns.Add(new DataColumn {
   ColumnName = "Salary",
   DataType = typeof(double),
   AllowDBNull = true });

               
          
                dtEmp.Columns.Add(new DataColumn {
                 ColumnName = "DOJ",
                 DataType = typeof(DateTime),
                 AllowDBNull = true });

                dtEmp.Columns.Add(new DataColumn {
    ColumnName = "DepartmentID",
    DataType = typeof(int),
    AllowDBNull = true });
            

                dtEmp.PrimaryKey = new DataColumn[] { empPK };

               UniqueConstraint unq = new UniqueConstraint(new DataColumn[] { colName });

                dtEmp.Constraints.Add(unq);

                ForeignKeyConstraint fk = new ForeignKeyConstraint("ForeignKey",   dtDept.Columns["DepartmentID"], dtEmp.Columns["DepartmentID"]);
                dtEmp.Constraints.Add(fk);


                DataRow dr = dtDept.NewRow();
                // or: ds.Tables["tblDepartment"].NewRow();
                dr["DeptName"] = "IT";
                dtDept.Rows.Add(dr);

                dr = dtDept.NewRow();
                dr["DeptName"] = "HR";
                dtDept.Rows.Add(dr);


                dr = dtEmp.NewRow();      
  // or: ds.Tables["tblEmployee"].NewRow();
                dr["Name"] = "Junaid";
                dr["City"] = "Hyderabad";
                dr["Salary"] = 12000;
                dr["DOJ"] = new DateTime(2014, 01, 29);
                dr["DepartmentID"] = 1;
                dtEmp.Rows.Add(dr);

                dr = dtEmp.NewRow();
                dr["Name"] = "Taha";
                dr["City"] = "Hyderabad";
                dr["Salary"] = 12000;
                dr["DOJ"] = new DateTime(2014, 01, 29);
                dr["DepartmentID"] = 2;
                dtEmp.Rows.Add(dr);

                dr = dtEmp.NewRow();
                dr["Name"] = "Goa";
                dr["City"] = "Hyderabad";
                dr["Salary"] = 12000;
                dr["DOJ"] = new DateTime(2014, 01, 29);
                dr["DepartmentID"] = 3;
//there is no Department with ID 3. But still we are not getting any error.

                dtEmp.Rows.Add(dr);

0 comments

Post a Comment