Adding restrictions to datatable and datacolumn objects phần 2

constraintName is the name you want to assign to your constraint. isPrimaryKey indicates whether the constraint is a primary key constraint or just a regular unique constraint.
Nội dung trích xuất từ tài liệu:
Adding restrictions to datatable and datacolumn objects phần 2 • constraintName is the name you want to assign to your constraint. • isPrimaryKey indicates whether the constraint is a primary key constraint or just a regular unique constraint.The following example uses the Add() method to add a primary key constraint to theProducts DataTable:myDataSet.Tables[Orders].Constraints.Add( Primary key constraint, myDataSet.Tables[Orders].Columns[OrderID], true);This example does the same thing as the previous example that added the primary keyconstraint using the PrimaryKey property. Notice the last parameter to the Add() methodis set to true, which indicates the constraint is for a primary key.Just as an aside, if you have a column that isnt a primary key but is unique, you can add aUniqueConstraint object to the ConstraintsCollection. For example:UniqueConstraint myUC = new UniqueConstraint(myDataTable.Columns[myColumn]);myDataTable.Constraints.Add(myUC);Adding a Primary Key to the OrderDetails DataTableLets consider an example of setting the PrimaryKey property for the Order DetailsDataTable. The primary for the Order Details table is made up of the OrderID andProductID columns, and the following example sets the PrimaryKey property of theOrder Details DataTable to these two columns:myDataSet.Tables[Order Details].PrimaryKey = new DataColumn[] { myDataSet.Tables[Order Details].Columns[OrderID], myDataSet.Tables[Order Details].Columns[ProductID] };The following example uses the Add() method to do the same thing:myDataSet.Tables[Order Details].Constraints.Add( Primary key constraint, new DataColumn[] { myDataSet.Tables[Order Details].Columns[OrderID], myDataSet.Tables[Order Details].Columns[ProductID] }, true);One thing to keep in mind when adding constraints to a DataTable is that it knows onlyabout the rows you store in it; it doesnt know about any other rows stored in the actualdatabase table. To see why this is an issue, consider the following scenario that involvesprimary keys: 1. You add a primary key constraint to a DataTable. 2. You retrieve a subset of the rows from a database table and store them in your DataTable. 3. You add a new DataRow to your DataTable with a primary key value not used in the subset of rows retrieved into your DataTable in the previous step-but that primary key value is already used in a row in the database table. Your new DataRow is added without any problem to the DataTable even though you added a primary key constraint to your DataTable in step 1. Your new DataRow is added successfully because the DataTable knows only about the rows stored in it, not the other rows stored in the database table that were not retrieved in step 2. 4. You attempt to push the new DataRow to the database, but you get a SqlException that states youve violated the primary key constraint in the database table. This is because a row in the database table already uses the primary key value.You need to keep this issue in mind when adding rows to a DataTable, which youll seehow to do shortly.That wraps up adding the primary key constraints to the DataTable objects. Next, youllsee how to add foreign key constraints.Adding Foreign Key Constraints to the Order Details DataTableIn this section, youll see how to add a foreign key constraint to the Order DetailsDataTable. To do this, you use the Add() method through the Constraints property of theDataTable.The following example adds a foreign key constraint from the OrderID DataColumn ofthe Order Details DataTable to the OrderID DataColumn of the Orders DataTable:ForeignKeyConstraint myFKC = new ForeignKeyConstraint( myDataSet.Tables[Orders].Columns[OrderID], myDataSet.Tables[Order Details].Columns[OrderID]);myDataSet.Tables[Order Details].Constraints.Add(myFKC);Note Notice that the parent DataColumn (OrderID of Orders) is specified before the child DataColumn (OrderID of Order Details).The next example adds a foreign key constraint from the ProductID DataColumn of theOrder Details DataTable to the ProductID DataColumn of the Products DataTable:myDataSet.Tables[Order Details].Constraints.Add( Foreign key constraint to ProductID DataColumn of the + Products DataTable, myDataSet.Tables[Order Details].Columns[ProductID], myDataSet.Tables[Products].Columns[ProductID]);That wraps up adding constraints to the DataTable objects. Next, youll see how to addrestrictions to DataColumn objects.Adding Restrictions to DataColumn ObjectsIn this section, youll see how to add restrictions to the DataColumn objects stored in aDataTable. Specifically, youll see how to set the AllowDBNull, AutoIncrement,AutoIncrementSeed, AutoIncrementStep, ReadOnly, and Unique properties of theProductID DataColumn of the Products DataTable. Youll also see how to set theMaxLength property of the ProductName DataColumn of the Products DataTable.The ProductID column of the Products database table is an identity column. The seed isthe initial value and the step is the increment added to the last number and they are bothset to 1 for ProductID. The ProductID identity values are therefore 1, 2, 3, and so on.Tip When you set the AutoIncrementSeed and AutoIncrementStep properties for a DataColumn that corresponds to a database identity column, you should always set them both to -1. That way, when you call the Fill() method, ADO.NET will automatically figure out what values to set the AutoIncrementSeed and AutoIncrementStep to, based on the values retrieved from the database, and you dont have to figure out these values yourself.The following code sets the properties of the ProductID DataColumn:DataColumn productIDDataColumn = myDataSet.Tables[Products].Columns[ProductID];product ...