Data Column in ADO.NET

To understand a data table, you must first understand data rows and data columns. The DataColumnCollection type returns a collection of columns that can be accessed through the Columns property of the DataTable. The DataColumnCollection object represents a collection of columns attached to a data table. You add a data column to the DataColumnCollection using its Add method. The DataColumn object represents a column of a DataTable. For example, say you want to create a customer table that consists of three columns: ID, Address, and Name. You create three DataColumn objects and these columns to the DataColumnCollection using the DataTable.Column.Add method.

 

After creating a data table schema, the next step is to add data to the data table by using the DataRow object.

 

The DataColumn has some properties. These properties describe a column, such as its uniqueness, what kind of data you can store in that column, default value, caption, name, and so on. Below Table describes some of the DataColumn class members.

 

Table: The DataColumn class properties

PROPERTY	DESCRIPTION
AllowDBNull	Both read and write, represent if the column can store null values or not
AutoIncrement	Represent if the column's value is auto increment or not
AutoIncrementSeed	Starting value of auto increment, applicable when AutoIncrement is true
AutoIncrementStep	Indicates the increment value
Caption	Caption of the column
ColumnMapping	Represent the MappingType of the column
ColumnName	Name of the column
DataType	Data type stored by the column
DefaultValue	Default value of the column
Expression	Represents the expression used to filter rows, calculate values, and so on
MaxLength	Represents maximum length of a text column
ReadOnly	Represents if a column is read-only or not
Unique	Indicates whether the values in a column must be unique or not

 

Creating a DataColumn

 

The DataColumn class provides five overloaded constructors to create a data column. By using these constructors you can initialize a DataColumn its name, data type, expressions, attributes, and any combination of these.

 

This is the format for creating a DataColumn with no arguments:

public DataColumn();

For example:

DataColumn dtColumn = new DataColumn();

This is the format for creating a DataColumn with the column name:

public DataColumn(string);

Where string is the column name. For example:

// Create a quality column
DataColumn qtCol= new DataColumn("Quantity);

This is the format for creating a DataColumn with the column name and its type:

public DataColumn(string, Type, string);

Where string is the column name and type is the column data type.

 

This is the format for creating a DataColumn with the column name, its type, and expression:

public DataColumn (string, Type);

Where the first string is the column name, Type is the data type, and the second string is an expression.

 

For Example:

System.Type myDataType;
myDataType = System.Type.GetType("System.String");
DataColumn dtColumn = new DataColumn("Name", myDataType);

Where string is the column name, Type is the data type and the string is an expression.

 

This is the format for creating a DataColumn with the column name, expression, and MappingType:

Public DataColumn(string,Type, string, MappingType);

Where string is the column name, Type is the data type string is an expression, and MappingType is an attribute.

 

In the following example, strExpr is an expression, which is the result of the Price and the Quality column Multiplication:

// Creating an expression

string strExpr = "price * Quantity";

// Create a Total column, which is the result of Price*Quantity
DataColumn totCol = new DataColumn("Total", myDataType, strExpr, MappingType.Attribute);

Note: As you can see from the previous code, the expression strExpr is a multiplication of the Price and Quantity columns. The price and Quantity columns must exist in the table before you use them in an expression. Otherwise, the compiler will throw an exception of "column not found."

 

Listing below summarizes all the constructors. As you can see dcConstructorsTest creates the Price, Quantity, and Total columns of a DataTable, which later is added to a DataSet. The DataSet binds to a Data Grid using the SetDataBinding method. To test source code, you need to create a Windows application with a form and a DataGrid control on it. After that, you can call dcConstructorsTest from either Form_Load or the button-click event handler.

 

Listing: Creating column using different DataColumn constructors

private void dcConstructorsTest() {
      // Create customers table
      DataTable custTable = new DataTable("Customers");
      DataSet dtSet = new DataSet();

      // Create Price column
      System.Type myDataType;
      myDataType = System.Type.GetType("System.Int32");
      DataColumn priceCol = new DataColumn("price", myDataType);
      priceCol.Caption = "Price";
      custTable.Columns.Add(priceCol);

      // Create Quantity column
      DataColumn qtCol = new DataColumn();
      qtCol.ColumnName = "Quantity";
      qtCol.DataType = System.Type.GetType("System.Int32");
      qtCol.Caption = "Quantity";
      custTable.Columns.Add(qtCol);

      // Creating an expression
      string strExpr = "Price * Quantity";

      // Create Total Column, which is result of Price*Quantity
      DataColumn totCol = new DataColumn("Total", myDataType, strExpr, MappingType.Attribute);
      totCol.Caption = "Total";

      // Add Name column to the table.
      custTable.Columns.Add(totCol);

      // Add cust table to Dataset
      dtSet.Tables.Add(custTable);

      // Bind dataset to the data grid
      dataGrid1.SetDataBinding(dtSet, "Customers");
}

Setting DataColumn Properties

 

The DataColumn class provides properties to set a column type, name, constraints, caption, and so on. Table in the article describes the DataColumn properties. Most of these properties are self-explanatory. After creating a DataColumn object, you set DataColumn properties.

 

Listing below creates a column with a name ID and sets its DataType, ReadOnly, AllowDBNull, Unique, AutoIncrementSeed, and AutoIncrementStep properties.

 

Listing: Creating a DataColumn and setting its properties

//Create ID Column
DataColumn IdCol = new DataColumn();
IdCol.ColumnName = "ID";
IDCol.DataType = Type.GetType("System.Int32");
IdCol.ReadOnly = true;
IdCol.AllowDBNull = false;
IdCol.Unique = true;
IdCol.AutoIncrement = true;
IdCol.AutoIncrementSeed = 1;
IdCol.AutoIncrementStep = 1;

As you can see from the listing, I set the AutoIncrement properties as true along with the AutoIncrementSeed and AutoIncrementStep properties. The AutoIncrement property sets a column value as an auto number. When you add a new row to the table, the value of this column is assigned automatically depending on the values of AutoIncrementStep and AutoIncrementSeed. The first value of the column starts with AutoIncrementSeed, and the next value will be the previous column value added to the AutoIncrementStep. In this code, the ID number value starts with 1 and increases by 1 if you add a new row to the table. If you set the AutoIncrementStep value to 10, the value of the auto number column will increase by 10.

 

Having a primary key in a table is a common practice to maintain the integrity of the data. A primary key in a table is a unique key that identifies a data row. For example, in the Customer table, each customer should have a unique ID. So, it's always a good idea to apply the primary key constraint on the ID table. The properties AllowDBNull as false and Unique as true set a key-value as the primary key, and you use the PrimaryKey property of DataTable to assign a DataTable's primary key. I have already set AllowDBNull as false and the Unique as true in the listing above. Now you'll set DataTable's Primary Key property as the ID column (see listing below).

 

Listing: Setting a Data Column as the primary key

// Make the ID column the primary key column.
DataColumn[] PrimaryKeyColumns= new DataColumn[1];
PrimaryKeyColumn [0] = custTable.Columns["ID"];
custTable.PrimaryKey = PrimarykeyColumns;

Adding a DataColumn to a DataTable

 

You add a DataColumn to a DataTable using the DataTable.Column.Add method. The Add method takes one argument of the DataColumn type. Listing below creates two data columns, id, and Name, and adds them to the DataTable custTable.

 

Listing: Creating the Id and Name data columns of the Customers table

// Create a new DataTable
DataTable custTable = new DataTable("Customers");
// Create ID column
DataColumn IdCol = new DataColumn();
// Set column properties
custTable.Columns.Add(IdCol);
// Create Name column
DataColumn nameCol = new DataColumn();
// Set column properties
custTable.Columns.Add(nameCol);

Now you'll put all the pieces together in the listing below. In the listing below, you create a Customer table with the columns ID, Name, Address, DOB, and VAR where ID is a primary key. Name and Address are string types. DOB is a data type field, and VAR Is a Boolean type field.

 

Note: To Test this program, create a windows application and add a DataGrid control to the form.

 

Listing: Creating a table using DataTable and DataColumn

private void CreateCustTable() {
      // Create a new DataTable
      DataTable custTable = new DataTable("customers");
      // Create ID column
      DataColumn IdCol = new DataColumn();
      IdCol.ColumnName = "ID";
      IdCol.DataType = Type.GetType("System.Int32");
      IdCol.ReadOnly = true;
      IdCol.AllowDBNull = false;
      IdCol.Unique = true;
      IdCol.AutoIncrement = true;
      IdCol.AutoIncrementSeed = 1;
      IdCol.AutoIncrementStep = 1;
      custTable.Columns.Add(IdCol);
      // Create Name column
      DataColumn nameCol = new DataColumn();
      nameCol.ColumnName = "Name";
      nameCol.DataType = Type.GetType("System.String");
      custTable.Columns.Add(nameCol);
      // Create Address column
      DataColumn addCol = new DataColumn();
      addCol.ColumnName = " Address";
      addCol.DataType = Type.GetType("System.String");
      custTable.Columns.Add(addCol);
      // Create DOB column
      DataColumn dobCol = new DataColumn();
      dobCol.ColumnName = "DOB";
      dobCol.DataType = Type.GetType("System.DateTime");
      custTable.Columns.Add(dobCol);
      // VAR column
      DataColumn fullTimeCol = new DataColumn();
      fullTimeCol.ColumnName = "VAR";
      fullTimeCol.DataType = Type.GetType("System.Boolean");
      custTable.Columns.Add(fullTimeCol);
      // Make the Id column the primary key column.
      DataColumn[] PrimaryKeyColumns = new DataColumn[1];
      PrimaryKeyColumns[0] = custTable.Columns["ID"];
      custTable.PrimaryKey = PrimaryKeyColumns;
      // Create a dataset
      DataSet ds = new DataSet("Customers");
      // Add Customers table to the dataset
      ds.Tables.Add(custTable);
      // Attach the Dataset to a DataGrid
      dataGrid1.DataSource = ds.DefaultViewManager;
}

The output of listing looks like a figure, which shows empty columns in a data grid control.

 

 

Figure: The output of the listing above

 

Conclusion

 

Hope this article would have helped you in understanding Data Colum in ADO.Net. See my other articles on the website on ADO.NET.