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:
  1. public DataColumn();
For example:
  1. DataColumn dtColumn = new DataColumn();
This is the format for creating a DataColumn with the column name:
  1. public DataColumn(string);
Where string is the column name. For example:
  1. // Create a quality column
  2. DataColumn qtCol= new DataColumn("Quantity);
This is the format for creating a DataColumn with the column name and its type:
  1. 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:
  1. 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:
  1. System.Type myDataType;
  2. myDataType = System.Type.GetType("System.String");
  3. 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:
  1. 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:
  1. // Creating an expression
  3. string strExpr = "price * Quantity";
  5. // Create a Total column, which is the result of Price*Quantity
  6. 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
  1. private void dcConstructorsTest() {
  2. // Create customers table
  3. DataTable custTable = new DataTable("Customers");
  4. DataSet dtSet = new DataSet();
  6. // Create Price column
  7. System.Type myDataType;
  8. myDataType = System.Type.GetType("System.Int32");
  9. DataColumn priceCol = new DataColumn("price", myDataType);
  10. priceCol.Caption = "Price";
  11. custTable.Columns.Add(priceCol);
  13. // Create Quantity column
  14. DataColumn qtCol = new DataColumn();
  15. qtCol.ColumnName = "Quantity";
  16. qtCol.DataType = System.Type.GetType("System.Int32");
  17. qtCol.Caption = "Quantity";
  18. custTable.Columns.Add(qtCol);
  20. // Creating an expression
  21. string strExpr = "Price * Quantity";
  23. // Create Total Column, which is result of Price*Quantity
  24. DataColumn totCol = new DataColumn("Total", myDataType, strExpr, MappingType.Attribute);
  25. totCol.Caption = "Total";
  27. // Add Name column to the table.
  28. custTable.Columns.Add(totCol);
  30. // Add cust table to Dataset
  31. dtSet.Tables.Add(custTable);
  33. // Bind dataset to the data grid
  34. dataGrid1.SetDataBinding(dtSet, "Customers");
  35. }
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
  1. //Create ID Column
  2. DataColumn IdCol = new DataColumn();
  3. IdCol.ColumnName = "ID";
  4. IDCol.DataType = Type.GetType("System.Int32");
  5. IdCol.ReadOnly = true;
  6. IdCol.AllowDBNull = false;
  7. IdCol.Unique = true;
  8. IdCol.AutoIncrement = true;
  9. IdCol.AutoIncrementSeed = 1;
  10. 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
  1. // Make the ID column the primary key column.
  2. DataColumn[] PrimaryKeyColumns= new DataColumn[1];
  3. PrimaryKeyColumn [0] = custTable.Columns["ID"];
  4. 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
  1. // Create a new DataTable
  2. DataTable custTable = new DataTable("Customers");
  3. // Create ID column
  4. DataColumn IdCol = new DataColumn();
  5. // Set column properties
  6. custTable.Columns.Add(IdCol);
  7. // Create Name column
  8. DataColumn nameCol = new DataColumn();
  9. // Set column properties
  10. 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
  1. private void CreateCustTable() {
  2. // Create a new DataTable
  3. DataTable custTable = new DataTable("customers");
  4. // Create ID column
  5. DataColumn IdCol = new DataColumn();
  6. IdCol.ColumnName = "ID";
  7. IdCol.DataType = Type.GetType("System.Int32");
  8. IdCol.ReadOnly = true;
  9. IdCol.AllowDBNull = false;
  10. IdCol.Unique = true;
  11. IdCol.AutoIncrement = true;
  12. IdCol.AutoIncrementSeed = 1;
  13. IdCol.AutoIncrementStep = 1;
  14. custTable.Columns.Add(IdCol);
  15. // Create Name column
  16. DataColumn nameCol = new DataColumn();
  17. nameCol.ColumnName = "Name";
  18. nameCol.DataType = Type.GetType("System.String");
  19. custTable.Columns.Add(nameCol);
  20. // Create Address column
  21. DataColumn addCol = new DataColumn();
  22. addCol.ColumnName = " Address";
  23. addCol.DataType = Type.GetType("System.String");
  24. custTable.Columns.Add(addCol);
  25. // Create DOB column
  26. DataColumn dobCol = new DataColumn();
  27. dobCol.ColumnName = "DOB";
  28. dobCol.DataType = Type.GetType("System.DateTime");
  29. custTable.Columns.Add(dobCol);
  30. // VAR column
  31. DataColumn fullTimeCol = new DataColumn();
  32. fullTimeCol.ColumnName = "VAR";
  33. fullTimeCol.DataType = Type.GetType("System.Boolean");
  34. custTable.Columns.Add(fullTimeCol);
  35. // Make the Id column the primary key column.
  36. DataColumn[] PrimaryKeyColumns = new DataColumn[1];
  37. PrimaryKeyColumns[0] = custTable.Columns["ID"];
  38. custTable.PrimaryKey = PrimaryKeyColumns;
  39. // Create a dataset
  40. DataSet ds = new DataSet("Customers");
  41. // Add Customers table to the dataset
  42. ds.Tables.Add(custTable);
  43. // Attach the Dataset to a DataGrid
  44. dataGrid1.DataSource = ds.DefaultViewManager;
  45. }
The output of listing looks like a figure, which shows empty columns in a data grid control.
Figure-5.9.jpg
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.