……………………………………………….Expertise in .NET Technologies

ADO.NET Core Classes:DataColumns – Part 7

Posted by Ravi Varma Thumati on November 18, 2009

The DataColumn defines the schema for a single column in a DataTable. The DataTable schema is defined by a collection of columns in the table along with any constraints. The DataColumn defines:

  • The type of data that can be stored in the column
  • The length of the column for text-based column types
  • Whether the data in the column can be modified
  • Whether the column values for each row must be unique
  • Whether the column in rows can contain null values
  • Whether the column values are automatically generated and the rules for generating those values
  • Whether the column value is calculated based on an expression

As with all disconnected classes, the DataColumn is independent of the column in the underlying data source. As a result, its data type is defined as a .NET Framework data type that is matched to the data type in the data source.

Creating DataColumns

When creating a new DataColumn, the overloaded constructor allows up to four optional arguments to be specified to initialize the ColumnName, DataType, Expression, and ColumnMapping properties. The following example creates a column and specifies both its name and data type:

// create the column and set the name and data type using properties

DataColumn col = new DataColumn();

col.ColumnName = “MyTextColumn”;

col.DataType = typeof(System.String);

// the code below is identical to the above three lines

DataColumn col = new DataColumn(“MyTextColumn”, typeof(System.String));

// set the maximum length in characters of the new column

col.MaxLength = 50;

The following example demonstrates another overloaded constructor and subsequently sets some properties of the column:

// create another column

DataColumn col = new DataColumn(“Id”, typeof(System.Int32));

col.AllowDBNull = false;   // the column doesn’t allow null values

col.DefaultValue = -1;     // set the default value

col.Caption = “ID #”;      // column title used by certain bound controls

col.Unique = true;         // the column value is each row must be unique

col.ReadOnly = true;       // the column value cannot be changed once the

// the row is added to a table

If the column name isn’t supplied, the names Column1, Column2, Column3, and so on, are assigned when the column is added to the table. An exception is raised if the name of the column being added to the table is the same as the name of an existing column in the table.

Creating AutoIncrement Columns

ADO.NET supports columns that increment automatically to ensure that values are unique when new rows are added to the table. These properties are AutoIncrement, AutoIncrementSeed, and AutoIncrementStep. The following code demonstrates creation of an AutoIncrement column:

DataColumn col = new DataColumn(“Id”, typeof(System.Int32));

col.AutoIncrement = true;

col.AutoIncrementSeed = -1;

col.AutoIncrementStep = -1;

The code uses seed and step values of -1. This causes the values for the added rows to start at -1, with subsequent values of -2, -3, and so on, which ensures that the automatically generated values will not already exist in a data source with an AutoIncrement key containing only positive values. When the data is updated back to the data source, the added records are correctly identified as new records, inserted into the data source, and at that point the AutoIncrement value can be returned from the data source and update the negative value in the DataSet.

Creating Expression Columns

An expression column can be created to perform calculations on columns in the row or aggregations on a collection of rows. No data is actually stored in the column, and the expression value is evaluated when the column is read. Setting the Expression property to anything other than an empty string automatically sets the ReadOnly property of the column to true. If the expression isn’t valid, an EvaluateException is raised.

// calculate the extended price for the the order detail line

DataColumn col1 = new DataColumn(“Quantity”, typeof(System.Int32));

DataColumn col2 = new DataColumn(“Price”, typeof(System.Decimal));

// create a column calculating the extended price

DataColumn col3 = new DataColumn(“ExtendedPrice”, typeof(System.Decimal));

Col3.Expression = “Quantity * Price”;

Handling Null Values

The AllowDBNull property determines whether null values can be stored in the column in rows. This value is true by default.

The System.DBNull class must be used to set a column value to null and can test whether a column contains a null value. Using the null keyword results in a runtime error. The following code demonstrates this concept:

DataRow row;

// … retrieve the DataRow

// set the value of the first column in the row to null

row[0] = DBNull.Value;

// test the first column to determine if it contains a null value

Boolean isNull = (row[0] == DBNull.Value);

The IsNull( ) method also allows the columns in the DataRow to be tested for null values using a more convenient syntax. The method returns a Boolean value indicating whether the value for the specified column in the row is null.

DataRow row;

// … retrieve the DataRow

// test the first column to determine if it contains a null value

Boolean isNull = row.IsNull(0);

ADO.NET disconnected classes are data-source independent. When data is retrieved from a data source into the .NET objects, the data is stored as .NET Framework types rather than data types specific to the data source. When data is retrieved, either by using the Fill( ) method of the DataAdapter or the GetValue( ) method of the DataReader, the .NET Framework type is inferred from the type returned from the .NET data provider. When specifying the data type for a column, a .NET Framework type compatible with the data type in the underlying data source must be specified.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: