Share via

How to: Add Columns to a DataTable

  • Article

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 Table.

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.

Note

The dialog boxes and menu commands you see might differ from those described in Help depending on your active settings or edition. To change your settings, choose Import and Export Settings on the Tools menu. For more information, see Customizing Development Settings in Visual Studio.

Common Tasks

Task

Supporting Content

Add columns to tables: You can add columns by using the Dataset Designer, the Data Source Configuration Wizard, or the TableAdapter Configuration Wizard.

Configure DataColumns: Columns are added with default properties. You can customize settings for each DataColumn to specify its behavior. For example, you can specify the data type, a default value, or constraint a column to contain unique values. In addition, you can configure a data column to display a calculated value instead of a value that is read directly from the database.

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

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.

Note

A special consideration comes into play when you drag items from the Data Sources window onto a form in a Windows application: the column names in the caption labels are reformatted into a more readable string when two or more words are found to be concatenated together. For more information, see How to: Customize How Visual Studio Creates Captions for Data-bound Controls.

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.

Category

Topic

Overview information: Learn more about how to work with data in Visual Studio.

Additional Procedures: Learn about other procedures for working with data.

Reference: Learn about classes and members that you use when working with data.