Friday, October 31, 2014

Dynamic DataTable

                     DataSet ds = new DataSet();
            DataTable dtEmp = new DataTable();
            DataTable dtDept = new DataTable();

            DataColumn deptPK = new DataColumn { ColumnName = "DepartmentID", DataType = typeof(Int32), AutoIncrement = true };
            dtDept.Columns.Add(deptPK);
            dtDept.Columns.Add(new DataColumn { ColumnName = "DeptName", DataType = typeof(string) });
            dtDept.PrimaryKey = new DataColumn[] { deptPK };
            ds.Tables.Add(dtDept);

            DataColumn empPK = new DataColumn { ColumnName = "EmpID", DataType = typeof(Int32), AutoIncrement = true };
            DataColumn colName = new DataColumn { ColumnName = "Name", DataType = typeof(string), AutoIncrement = true };
            dtEmp.Columns.Add(empPK);
            dtEmp.Columns.Add(colName);
            dtEmp.Columns.Add(new DataColumn { ColumnName = "City", DataType = typeof(string), AutoIncrement = true });
            dtEmp.Columns.Add(new DataColumn { ColumnName = "Salary", DataType = typeof(double), AutoIncrement = true });
            dtEmp.Columns.Add(new DataColumn { ColumnName = "DOJ", DataType = typeof(DateTime), AutoIncrement = true });
            dtEmp.Columns.Add(new DataColumn { ColumnName = "DepartmentID", DataType = typeof(Int32), AutoIncrement = true });
            //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);