Specifying Primary Key column in the DataTable
To set the primary key column in the DataTable, you need to create arrays of column and store column you want as primary key for the DataTable and set its PrimaryKey property to the column arrays. See the code below.
// create primary key on this field
DataColumn[] pK = new DataColumn[1];
pK[0] = auto;
dTable.PrimaryKey = pK;
EX:
// Make the ID column the primary key column.
DataColumn[] PrimaryKeyColumns = new DataColumn[1];
PrimaryKeyColumns[0] = table.Columns["id"];
table.PrimaryKey = PrimaryKeyColumns;
or :
dt.PrimaryKey = new DataColumn[]{dt.Columns["id"]};
private System.Data.DataSet dataSet;
protected void Page_Load(object sender, EventArgs e)
{
MakeDataTables();
}
private void MakeDataTables()
{
MakeParentTable();
MakeChildTable();
MakeDataRelation();
LoadDataSet();
BindToDataGrid();
}
private void MakeParentTable()
{
// Create a new DataTable.
System.Data.DataTable table = new DataTable(“ParentTable”);
// Declare variables for DataColumn and DataRow objects.
DataColumn column;
// Create new DataColumn, set DataType,
// ColumnName and add to DataTable.
column = new DataColumn();
column.DataType = System.Type.GetType(“System.Int32″);
column.AllowDBNull = false;
column.AutoIncrement = true;
column.ColumnName = “id”;
column.ReadOnly = false;
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);
}
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.AllowDBNull = true;
column.AutoIncrement = false;
column.Caption = “ChildID”;
column.ReadOnly = false;
column.Unique = false;
// 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);
dataSet.Tables.Add(table);
}
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["ChildID"];
DataRelation relation = new
DataRelation(“parent2Child”, parentColumn, childColumn);
dataSet.Tables["ChildTable"].ParentRelations.Add(relation);
}
private void LoadDataSet()
{
DataTable dtParent = dataSet.Tables["ParentTable"];
DataTable dtChild = dataSet.Tables["childTable"];
DataRow drData;
for (int intCount = 1; intCount < 11; intCount++)
{
drData = dtParent.NewRow();
drData["ParentItem"] = string.Concat(“Item Number “, intCount);
dtParent.Rows.Add(drData);
drData = dtChild.NewRow();
drData["ChildID"] = dtParent.Rows[intCount - 1]["id"];
drData["ChildItem"] = string.Concat(“Child Item Number “, intCount);
dtChild.Rows.Add(drData);
}
}
private void BindToDataGrid()
{
//Create new table
DataTable dt = new DataTable(“Table3″);
dt.Columns.Add(new DataColumn(“id”, typeof(string)));
dt.Columns.Add(new DataColumn(“ParentItem”, typeof(string)));
dt.Columns.Add(new DataColumn(“ChildItem”, typeof(string)));
this.dataSet.Tables.Add(dt);
foreach (DataRow row in this.dataSet.Tables["childTable"].Rows)
{
DataRow parent = row.GetParentRow(“parent2Child”);
DataRow current = dataSet.Tables["Table3"].NewRow();
current["id"] = row["ChildId"];
current["ChildItem"] = row["ChildItem"];
current["ParentItem"] = parent["ParentItem"];
this.dataSet.Tables["Table3"].Rows.Add(current);
GridView1.DataSource = dataSet.Tables["Table3"];
GridView1.DataBind();
}
}
