Using DataTables
Since a DataTable implements the same common interfaces as a DataSet, a lot of the functionality available via a DataSet is also available to a DataTable. For example, a DataTable can also be created in the same four ways that a DataSet can be created.
A DataTable is an XML representation of data in a table format and is made up of the properties listed and described in Table 7-2.
Property | Description |
|---|---|
Columns | Returns a DataColumnCollection type that can contain zero or more DataColumns. Each DataColumn defines the DataTable’s column properties. These include the name, datatype, and whether the column is a primary key. |
Constraints | Returns a ConstraintsCollection type that can hold zero or more System.Data. ForeignKeyContraint or System.Data.UniqueConstraint types. |
Rows | Returns a DataRowCollection type that can contain zero or more DataRows. A DataRow contains the actual data for a DataTable. Each row has a separate value for each DataColumn in the DataTable. |
A DataTable can be created by using the following code:
DataTable usersDt = new DataTable("Users");
We can use this DataTable on its own, or we can add this DataTable to a DataSet object by using the following code:
DataSet userTasksDs = new DataSet("UserTasks");
DataTable usersDt = new DataTable("Users");
userTasksDs.Tables.Add(usersDt);
A DataTable can also be created using the Tables.Add() method of the DataSet:
DataSet userTasksDs = new DataSet("UserTasks");
DataTable usersDt = userTasksDs.Tables.Add("Users");
Defining the Schema of a DataTable Using DataColumns
You can define the schema of a DataTable by using Columns and Constraints properties. Columns can represent a single column from a data source table. The following are some properties of a DataColumn:
Unique within a table
Case-sensitive column names
Can represent a primary key
Specifies the datatype of the column
Can define an expression
The following code shows how you can create a column for a DataTable:
DataTable usersDt = new DataTable("Users");
DataColumn userIDCol = usersDt.Columns.Add("UserId", typeof(int));
We could also use:
DataTable usersDt = new DataTable("Users");
DataColumn userIDCol = new DataColumn("UserId", typeof(int));
usersDt.Columns.Add(userIDCol);
| Note | You can simply call usersDt.Columns.Add() to create a column. The default value for the column header will be Column1, Column2, and so on, and the default datatype for the column will be System.String. |
Primary Keys and Auto-Incrementing Columns
We can set the column to be a primary key, to auto-increment, and to be read-only. When a column is defined as a primary key, a UniqueConstraint is automatically generated for that column. Constraints are explained in further detail later in this chapter. The following code shows how to create a primary key column:
DataTable usersDt = new DataTable("Users");
DataColumn userIDCol = usersDt.Columns.Add("UserId", typeof(int));
userIDCol.AutoIncrement = true;
userIDCol.AutoIncrementSeed = -1;
userIDCol.AutoIncrementStep = -1;
userIDCol.ReadOnly = true;
usersDt.PrimaryKey = new DataColumn[]{userIDCol};
| Note | In the preceding code, the identity seed and step have been set to –1, which me1, which means that inserted rows have a −ve identity value. The reason this was done is that, otherwise, there could be a conflict if the values were +ve and that ID already existed in the database. This way, each row has a unique identity value. When the DataTable is updated using a DataAdapter, it is possible to retrieve the new identity value generated by the database, which replaces the –ve value. This is explained later in this chapter. |
When setting the primary key on a DataTable, you need to pass in an array of a DataColumn type. This allows you to specify a primary key that is across multiple columns. This can be done using the following code:
usersDt.PrimaryKey = new DataColumn[]{col1, col2, col3};
Creating an Expression Column
A column can contain an expression, which means that its value can be derived from the value of another column, or multiple columns, within the DataTable. An expression can be set on a DataColumn by using the Expression property, which can also be passed into the constructor when creating a new column.
When referencing other columns from the DataTable, the ColumnName property is used, which is both unique and case sensitive.
| Note | An expression cannot be set on an auto-incrementing or unique column. |
The following table shows some basic examples of expressions that can be used when creating an expression column. These are simple expressions; more advanced expressions are not within the scope of this chapter.
Expression Type | Example Expression |
|---|---|
Aggregation | "Sum(OrderValue)" "Count(UserID)" |
Comparison | "Quantity < 10" "FirstName = 'Steve'" |
Computation | "ItemPrice * Quantity" |
The following code can be used to create a column expression. The code assumes that the DataTable already has two columns added, called ItemPrice and Quantity.
dt.Columns.Add("TotalOrder", typeof(int), "ItemPrice * Quantity");
or
col.Expression = "ItemPrice * Quantity";
Special Characters When creating expressions in which a referenced column contains a special character, the column name in the expression must be wrapped in brackets. The following table shows the special characters:
Special Characters |
|---|
\n, \t, \r, ~, (, ), #, \, /, =, <, >, +, -, *, %, &, |, ^,‘,“, , “, [, ] |
When a column name contains any of the preceding characters—for example, “Quantity+”—the following syntax must be used for the expression:
col.Expression = "ItemPrice * [Quantity+]";
| Note | Column expressions can reference other columns. Circular references are possible, which would result in an exception. |
Expression Operators When creating an expression, a number of operators are available to use. The following table shows the valid operators:
Valid Operators |
|---|
<, >, <=, >=, <>, =, IN, LIKE, +, –, *, /, % |
When using the LIKE operator, both the * and % characters can be used to define wildcard characters. The following are examples of using the LIKE operator and wildcards in an expression:
"FirstName LIKE 'St*'"
"FirstName LIKE '*ve'"
"FirstName LIKE '*ev*'"
| Note | When using wildcard characters with the LIKE operator, it is not possible to have a wildcard character in the middle of a string. "St*ve" is an example of an invalid use of the wildcard character. |
Parent-Child Relation Expressions When dealing with expressions in parent or child DataTables, we can precede the column name with Parent or Child. For example, Parent. FirstName can be used to reference the FirstName column in the parent DataTable.
A child DataTable column can be referenced in a parent DataTable expression, but because this can be a one-to-many relationship, with multiple child rows, the reference to the child column must be in an aggregate function. For example, Sum(Child.ItemPrice).
When a parent DataTable has more than one child DataTable, the Child reference can be taken from the relation name. (Relationships are discussed in more detail later in this chapter.) An example of this would be Sum(Child(CustomerItemsRelation).ItemPrice).
Expression Aggregates The following aggregates are valid for use in an expression:
Aggregates |
|---|
Avg, Count, Max, Min, StDev, Sum, Var |
If a child table has no rows, the value of the expression is returned as a null reference.
Expression Functions There are a number of built-in functions that can be used when building an expression. Table 7-3 explains these functions in more detail.
Function | Description | Syntax |
|---|---|---|
CONVERT | Converts an expression to a .NET datatype | "CONVERT(total, 'System.Int32'" |
IIF | Used to set a value depending on the result of a logical expression | "IIF(quantity > 10, 'Plenty in Stock', 'Need to order more')" |
ISNULL | Checks whether a value is Null and, if so, replaces it with another specified value | "IsNull(Quantity, 0)" |
LEN | Returns the length of a string | "LEN(FirstName)" |
TRIM | Removes the following leading and trailing characters from a string: \n, \r, \t, ' ' | "TRIM(FirstName)" |
SUBSTRING | Returns a substring of a string, given the starting position and the length | "SUBSTRING(FirstName, 0, 1)" |
Adding Constraints to DataTables
Constraints can be used to enforce integrity rules between parent and child DataTables and their data. There are two types of constraint, ForeignKeyConstraint and UniqueConstraint. These constraints define what action is taken when data is modified in a related table.
ForeignKeyConstraint
The ForeignKeyConstraint is used to enforce referential integrity between tables. You can also define the cascade action that occurs on the child data when the parent data is changed or deleted. For example, if the parent row is deleted, you may want to delete all the child rows.
There are four actions that can be taken, which can be set by using the ForeignKeyConstraint.UpdateRule and the ForeignKeyConstraint.DeleteRule properties. The values of these rules are explained in Table 7-4.
The following code can be used to create a ForeignKeyConstraint:
DataSet userTasksDs = new DataSet("UserTasksData");
DataTable usersDt = new DataTable("Users");
//code to create users columns
DataTable tasksDt = new DataTable("UserTasks");
//code to create user task columns
userTasksDs.Tables.Add(usersDt);
userTasksDs.Tables.Add(tasksDt);
//Create constraint
ForeignKeyConstraint fkConst = new ForeignKeyConstraint("Fk_Users_UserTasks"
, usersDt.Columns["UserId"], tasksDt.Columns["UserId"]);
fkConst.DeleteRule = Rule.Cascade;
fkConst.UpdateRule = Rule.Cascade;
fkConst.AcceptRejectRule = AcceptRejectRule.Cascade;
usersDt.Constraints.Add(fkConst);
userTasksDs.EnforceConstraints = true;
Rule Value | Description |
|---|---|
Cascade | The default value of both the UpdateRule and DeleteRule properties. Updates or deletes the rows in the child table. |
None | The child rows are not modified. |
SetDefault | Sets the value of the column in related rows to the DataColumn. DefaultValue value. |
SetNull | Sets the value of related rows to DBNull. |
UniqueConstraint
The UniqueConstraint is used to enforce that all values in the column are unique. Attempting to set an already existing value in another row for a constrained column will result in a ConstraintException. A UniqueConstraint is automatically generated for columns set as a primary key column.
|
No comments:
Post a Comment