How to: Add Columns to a DataTable

You create the individual columns (DataColumn) that make up data tables (DataTable) by adding them to the DataColumnCollection for a data table. You can add columns to data tables visually with the Dataset Designer, or with one of the data wizards. For more information about how to add data columns to data tables programmatically, see Adding Columns to a DataTable (ADO.NET).

After you add the columns, you can configure them to meet your requirements. For example, you can specify a default value, or a uniqueness constraint for a column. You can also specify that a column should display a calculated value, instead of a value read directly from the database.

Common Tasks

Adding Columns to a Data Table

Before you can configure a DataColumn, you must first add it to a DataTable. You can do so by using any of the following procedures.

To add a column to a DataTable with the Dataset Designer

1. Open your dataset in the Dataset Designer. For more information, see How to: Open a Dataset in the Dataset Designer.

2. Right-click the DataTable to add the column. Point to Add, and then click Column.

3. Replace the default column name, if desired, by selecting the column name in the data table and editing the text.

4. Set the DataType property in the Properties window.

To add a column to a DataTable with the Data Source Configuration Wizard

1. In the Data Sources window, right-click the data source, and then click Configure DataSet with Wizard.

2. Expand the table you want to add a column to.

3. Select the columns to add to the table.

4. Click Finish.

To add a column to a DataTable with the TableAdapter Configuration Wizard

1. Open your dataset in the Dataset Designer. For more information, see How to: Open a Dataset in the Dataset Designer.

2. Right-click the data table, and then click Configure.

3. Add the column to the SELECT statement.

4. Click Finish.

Configuring DataColumns

After you add a DataColumn, you specify the behavior of that DataColumn. For example, you might want to change the data type or restrict it to only contain unique values. You can use any of the following procedures to modify DataColumn behavior.

• Setting the Data Type of a DataColumn

• Specifying the Caption of a DataColumn

• Specifying that a DataColumn Contain Auto-Generated Numbers

• Assigning a Default Value to a DataColumn

• Constraining a DataColumn to Contain Unique Values

• Setting a DataColumn to be the Primary Key in a DataTable

• Specifying the Return Value when the Value of a DataColumn is Null

Setting the Data Type of a DataColumn

Data columns (DataColumn) are created with a default data type of String. To change the data type of a column, set the DataType property in the Properties window.

To set the data type of a DataColumn

1. Open a dataset in the Dataset Designer.

2. Select the column you want to set the data type for.

3. In the Properties window, select the desired data type from the DataType list.

Specifying the Caption of a DataColumn

Data columns often have names that are not suited for displaying in a user interface. You can change the caption to a more suitable name by setting the Caption property in the Properties window.

To edit the caption of a data column

1. Open your dataset in the Dataset Designer. For more information, see How to: Open a Dataset in the Dataset Designer.

2. Select the column whose caption you want to edit.

3. In the Properties window, set the Caption property.

Specifying that a DataColumn Contain Auto-Generated Numbers

Data columns (DataColumn) often contain auto-generated sequential numbers; for example, primary keys and ID columns. You can load auto-generated numbers in data columns by setting the AutoIncrement, AutoIncrementSeed, and AutoIncrementStep properties of the data column.

When data columns are created by means of Visual Studio design tools, the columns are automatically marked as AutoIncrement. When you update a table in the database that contains an Identity column, the values created with the auto-numbering feature are not sent to the database.

To set a data column to contain auto-generated numbers

1. Open your dataset in the Dataset Designer.

2. Select the column you want to populate with auto-generated numbers.

3. In the Properties window, set the following properties:

   Property	Setting
   AutoIncrement	true
   AutoIncrementSeed	If you want to begin at a number different from the default value of 0, set this value to the starting number for this column.
   AutoIncrementStep	In each new row, the column value is incremented by this value.

   Note

   Setting a data column to auto-increment does not automatically designate the column as a primary key.

Assigning a Default Value to a DataColumn

Data columns can be assigned default values. The default value is assigned in the column as new rows (records) are created.

To assign a default value to a data column

1. Open a dataset in the Dataset Designer.

2. Select the column you want to assign a default value to.

3. In the Properties window, set the DefaultValue property to the desired default value.

Constraining a DataColumn to Contain Unique Values

You set a DataColumn to contain unique values by adding a unique constraint to a data table. Unique constraints are also called keys. Marking a column as a Primary Key in the Dataset designer will create a Unique Constraint

To constrain a column to contain unique values

1. Open your dataset in the Dataset Designer.

2. Right-click the column you want to constrain to unique values. Point to Add, and then click Key.

3. Verify that the correct column is selected in the Unique Constraint dialog box.

4. Replace the default name of the constraint with a more meaningful name, if desired.

Setting a DataColumn to be the Primary Key in a DataTable

Setting a data column as the primary key guarantees that each record contains a unique value. Only one primary key can be assigned to a data table. You create a primary key in a data table using the Dataset Designer.

To set a data column to be the primary key in a data table

1. Open your dataset in the Dataset Designer.

2. Right-click the column you want to set as the primary key. Click Set Primary Key.

   A key icon appears next to the selected column(s).

Specifying the Return Value when the Value of a DataColumn is Null

In a typed dataset, you can control the representation of columns that contain null values. This behavior is only available when retrieving column values through their typed accessors. If you retrieve the value by means of the table's row index or column index, you will not be able to set the return value. The reason for this is that the logic to return this value is built into the property setter of the specific data column in the generated dataset class.

To set the value to return when a DataColumn's value is null

1. Open the dataset in the Dataset Designer.

2. Select a column in a data table.

3. In the Properties window, select the NullValue property.

4. Do one of the following:

   • From the drop-down list, select the appropriate value:

     Value	Description
     (Empty)	Returns null values String.Empty.
     (Nothing)	Returns null values as DBNull.
     (Throw exception)	Null values throw an Exception.

     -or-

   • Set the property to any custom value valid for that column's data type. For example, if you specify the string No Value, for a column with a data type of String, any records that contain null values in that column will display "No Value".

Configure a DataColumn to Display an Expression

A data table in a dataset can contain columns in which the value is calculated rather than read from the database. For example, in an order details record, it might make sense to define an ExtendedPrice column by multiplying the quantity times the price, rather than actually storing the total in the record. Calculated columns can also add or count values in child records and filter other records. You can define calculated columns using expressions.

The syntax of the expression consists of standard operators. You can reference a data value using its column name (as you would in an SQL statement) and include aggregate functions such as Count.

For example, the expression for an ExtendedPrice column based on the UnitPrice times Quantity columns of the current record might simply be:

UnitPrice * Quantity

To refer to columns in child tables, you can use the reserved word Child, followed by the name of the column. For example, the following expression returns the average value of the Price column in all related child records:

Avg(Child.Price)

For more information, see the Expression property.

You can create expression columns in the Dataset Designer. For more information about creating columns, see How to: Add Columns to a DataTable.

To create column expressions in the Dataset Designer

1. Open your dataset in the Dataset Designer. For more information, see How to: Open a Dataset in the Dataset Designer.

2. In the data table that you want, select the column you want to add the expression to, or add a new column to the table. For more information, see How to: Add Columns to a DataTable.

3. In the Properties window, enter an expression in the Expression property.

Related Topics