[ Team LiB ] Recipe 6.8 Creating Constraints, PrimaryKeys, Relationships Based on Multiple Columns Problem You need to create a constraint, primary key, or a relationship between two tables in a DataSet using more than one column. Solution Use the System.Data.
AMBIENT/
Chủ đề:
Nội dung Text: Creating Constraints, PrimaryKeys, Relationships Based on Multiple Columns
- [ Team LiB ]
Recipe 6.8 Creating Constraints, PrimaryKeys, Relationships Based on Multiple
Columns
Problem
You need to create a constraint, primary key, or a relationship between two tables in a
DataSet using more than one column.
Solution
Use the System.Data.UniqueConstraint and System.Data.ForeignKeyConstraint types.
The sample code creates a DataSet containing two tables: Parent and Child. A
multicolumn unique constraint and primary key are created for the Parent table. A
multicolumn foreign-key constraint is created on the Child table. Finally, a DataRelation
between the primary key in the Parent table and the foreign key in the Child table is
created.
The C# code is shown in Example 6-26.
Example 6-26. File: MultiColumnConstraintAndRelationForm.cs
// Namespaces, variables, and constants
using System;
using System.Text;
using System.Data;
// . . .
StringBuilder result = new StringBuilder( );
DataSet ds = new DataSet( );
// Create the parent table.
result.Append("Creating parent table." + Environment.NewLine);
DataTable dtParent = new DataTable("Parent");
DataColumnCollection pCols = dtParent.Columns;
pCols.Add("ParentKey1", typeof(Int32));
pCols.Add("ParentKey2", typeof(Int32));
pCols.Add("ParentData1", typeof(String));
pCols.Add("ParentData2", typeof(String));
- // Set the multicolumn unique constraint.
result.Append("Creating unique constraint on parent table." +
Environment.NewLine);
dtParent.Constraints.Add(new UniqueConstraint("UConstraint",
new DataColumn[] {pCols["ParentKey1"], pCols["ParentKey2"]}, false));
// Set the multicolumn primary key.
result.Append("Creating primary key on parent table." +
Environment.NewLine);
dtParent.PrimaryKey = new DataColumn[] {pCols["ParentKey1"],
pCols["ParentKey2"]};
// Add the parent table to the DataSet.
ds.Tables.Add(dtParent);
// Create the child table.
result.Append("Creating child table." + Environment.NewLine);
DataTable dtChild = new DataTable("Child");
DataColumnCollection cCols = dtChild.Columns;
cCols.Add("ChildIndex1", typeof(Int32)).Unique = true;
cCols.Add("ParentKey1", typeof(Int32));
cCols.Add("ParentKey2", typeof(Int32));
cCols.Add("ChildData1", typeof(String));
cCols.Add("ChildData2", typeof(String));
ds.Tables.Add(dtChild);
// Set the foreign-key constraint.
result.Append("Creating foreign key contraint." + Environment.NewLine);
dtChild.Constraints.Add("FKConstraint",
new DataColumn[] {pCols["ParentKey1"], pCols["ParentKey2"]},
new DataColumn[] {cCols["ParentKey1"], cCols["ParentKey2"]});
// Create the relation between parent and child.
result.Append("Creating relationship." + Environment.NewLine);
ds.Relations.Add("Relation",
new DataColumn[] {dtParent.Columns["ParentKey1"],
dtParent.Columns["ParentKey2"]},
new DataColumn[] {dtChild.Columns["ParentKey1"],
dtChild.Columns["ParentKey2"]},
true);
resultTextBox.Text = result.ToString( ) + Environment.NewLine + "Done.";
- Discussion
Unique and foreign-key constraints
The System.Data.Constraint class is an abstract class that is the base class for the two
.NET Framework built-in classes:
System.Data.UniqueConstraint
Ensures that all data in the specified column or columns in the row is unique
within the table. Defining a primary key for a DataTable by setting the
PrimaryKey property automatically creates a unique constraint for the specified
column or columns.
System.Data.ForeignKeyConstraint
Enforces rules about how updates of values in rows or row deletions are
propagated to related tables through the DeleteRule and UpdateRule properties,
which define the action to be taken.
By default, a UniqueConstraint is created on the parent table and a ForeignKeyConstraint
is created on the child table when a DataRelation object is created relating the two tables.
Constraints are only enforced if the EnforceConstraints property is true.
The Add( ) method of the ConstraintCollection of the DataTable, which is accessed using
the Constraints property, has two overloads that you can use to add a unique constraint to
a table. The prototypes are:
public void Add(String name, DataColumn col, Boolean isPrimaryKey);
public void Add(String name, DataColumn[] cols, Boolean isPrimaryKey);
The arguments are:
name
The name of the unique constraint
col
The unique column in the table
cols
- The array of unique data columns in the table
isPrimaryKey
Specifies whether the columns or columns represent the primary key for the table
There are two methods that you can use to add a foreign key constraint to a table. The
prototypes are:
public void Add(String name, DataColumn parentCol, DataColumn childCol);
public void Add(String name, DataColumn[] parentCols,
DataColumn childCols);
The arguments are:
name
The name of the foreign key constraint
parentCol
The column in the parent table
childCol
The column in the child table
parentCols
The array of columns in the parent table
childCols
The array of columns in the child table
There is also one method that you can use to add either a unique constraint or a foreign
key constraint to a table. The prototype is:
public void Add(Constraint constraint);
The argument is:
constraint
- The unique constraint or foreign key constraint object to add to the table
PrimaryKey
The PrimaryKey property of the DataTable is used to set or get the primary key for a
table. The prototypes are:
DataColumn[] pka = DataTable.PrimaryKey;
DataTable.PrimaryKey = pka;
The argument is:
pka
An array of columns making up the primary key
DataRelation
The Add( ) method of the DataRelationCollection of the DataSet, which is accessed using
the Relations property, has seven overloads that you can use to add a relationship
between two tables to the DataSet. The prototypes are:
public void Add(DataRelation dr);
public virtual DataRelation Add(DataColumn parentCol, DataColumn childCol);
public virtual DataRelation Add(DataColumn[] parentCols,
DataColumn[] childCols);
public virtual DataRelation Add(String name, DataColumn parentCol,
DataColumn childCol);
public virtual DataRelation Add(String name, DataColumn[] parentCols,
DataColumn[] childCols);
public virtual DataRelation Add(String name, DataColumn parentCol,
DataColumn childCol, Boolean createFKConstraint);
public virtual DataRelation Add(String name, DataColumn[] parentCols,
DataColumn[] childCols, Boolean createFKConstraint);
The arguments are:
dr
A data relationship between a parent and child table
parentCol
- A column in the parent table
childCol
A column in the child table
parentCols
An array of columns in the parent table
childCols
An array of columns in the child table
name
The name of the data relation
createFKConstraint
Specifies whether to create a foreign key constraint
[ Team LiB ]
Thêm vào BST
Báo xấu
Download
Vui lòng tải xuống để xem tài liệu đầy đủ
