Adding Restrictions to DataTable and DataColumn Objects
As you know, a DataSet object is used to store a copy of a subset of the database. For
example, you can store a copy of the rows from database tables into a DataSet, with each
table represented by a DataTable object. A DataTable stores columns in DataColumn
objects.
In addition to storing rows retrieved from a database table, you can also add restrictions
to a DataTable and its DataColumn objects. This allows you to model the same
restrictions placed on the database tables and columns in your DataTable and
DataColumn objects. For example, you can add the following constraints to a DataTable:
•
Unique
•
Primary key
•
Foreign key
In addition, you can add the following restrictions to a DataColumn:
•
Whether the column can accept a null value-which you store in the AllowDBNull
property of the DataColumn.
•
Any auto-increment information-which you store in the AutoIncrement,
AutoIncrementSeed, and AutoIncrementStep properties of the DataColumn. You
set these properties when adding rows to a DataTable with a corresponding
database table that contains an identity column. The ProductID column of the
Products table is an example of an identity column.

Note ADO.NET will not automatically generate values for identity columns in a
new row. Only the database can do that. You must read the generated
identity value for the column from the database. You'll see how to do that
later in the sections "Retrieving New Identity Column Values
" and "Using

You can use one of following ways to add restrictions to DataTable and DataColumn
objects:
•
Add the restrictions yourself by setting the properties of your DataTable and
DataColumn objects. This results in the fastest executing code.
•
Call the FillSchema() method of your DataAdapter to copy the schema
information from the database to your DataSet. This populates the properties of
the DataTable objects and their DataColumn objects automatically. Although
simple to call, the FillSchema() method takes a relatively long time to read the
schema information from the database and you should avoid using it.
You'll learn the details of both these techniques in the following sections.
Adding the Restrictions Yourself
You can add restrictions to your DataTable and DataColumn objects yourself using the
properties of the DataTable and DataColumn objects.
For example, assume you have a DataSet object named myDataSet that contains three
DataTable objects named Products, Orders, and Order Details that have been populated
using the following code:
SqlCommand mySqlCommand = mySqlConnection.CreateCommand();
mySqlCommand.CommandText =
"SELECT ProductID, ProductName " +
"FROM Products;" +
"SELECT OrderID " +
"FROM Orders;" +
"SELECT OrderID, ProductID, UnitPrice " +
"FROM [Order Details];";
SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter();
mySqlDataAdapter.SelectCommand = mySqlCommand;
DataSet myDataSet = new DataSet();
mySqlConnection.Open();

PrimaryKey property, which you set to an array of DataColumn objects that make up the
primary key. An array is required because the primary key of a database table can be
made up of multiple columns. As you'll see in the examples, this is simpler than using the
Add() method to add a primary key constraint.

CALLING THE Fill() METHOD OF A DataAdapter MORE THAN ONCE
The Fill() method retrieves all of the rows from the database table, as specified in your
DataAdapter object's SelectCommand property. If you add a primary key to your
DataTable, then calling the Fill() method more than once will put the retrieved rows in
your DataTable and throw away any existing rows with matching primary key column
values already in your DataTable.
If you don't add a primary key to your DataTable, then calling the Fill() method more
than once will simply add all the retrieved rows to your DataTable again, duplicating the
rows already there.
This is another reason for adding a primary key constraint to your DataTable because you
don't want duplicate rows.

Adding a Primary Key to the Products DataTable
Let's take a look at adding a primary key to the Products DataTable. First, the following
example creates a DataTable object named productsDataTable and sets it to the Products
DataTable retrieved from myDataSet:
DataTable productsDataTable = myDataSet.Tables["Products"];
Now, the primary key for the Products database table is the ProductID column; therefore,
you need to set the PrimaryKey property of productsDataTable to an array containing the
ProductID DataColumn object. The following example shows how you do this. It creates
an array of DataColumn objects named productsPrimaryKey and initializes it to the
ProductID column of productsDataTable, then sets the PrimaryKey property of
productsDataTable to the array:
DataColumn[] productsPrimaryKey =
new DataColumn[]

DataColumn childColumn) // adds a foreign key constraint
void Add(string constraintName, DataColumn[] myDataColumn,
bool isPrimaryKey) // adds a primary key or unique constraint
void Add(string cosntraintName, DataColumn[] parentColumns,
DataColumn[] childColumns) // adds a foreign key constraint
where