DataView Query and Grammar

DataView Query and Grammar

One of the main features of DataView is to allow data binding on Windows Forms and Web Forms.

Alternatively, you can customize the DataView to represent a subset of the data in the DataTable. This feature lets you have two controls that are bound to the same DataTable but display different data versions. For example, a control might be bound to a DataView that displays all the rows in a table, while another control might be configured to display only rows that have been deleted from the DataTable. The DataTable also has the DefaultView attribute. It returns the default DataView for the table. For example, if you want to create a custom view on a table, set RowFilter on the DataView returned by DefaultView.

To create a filtered and sorted view of your data, set the RowFilter and sort properties. Then, use the Item property to return a single DataRowView.

You can also use the AddNew and Delete methods to add and delete from a collection of rows. When using these methods, you can set the RowStateFilter property to specify that only rows or new rows that have been deleted can be displayed by DataView.

User-defined values can be used in expressions that will be compared to column values. String values should be enclosed in single quotes. Date values should be placed in the pound sign (#). For numeric values, decimal and scientific notation are allowed. For example:
"FirstName = ' John '"
"Price <= 50.00"
"Birthdate < #1/31/82#"

Casts a value to an integer data type for the column that contains the enumeration value. For example:
"Enumcolumn = 5"

Operator
Concatenation is allowed when Boolean and, or, and not operators are used. You can use parentheses to combine clauses and force precedence. The AND operator takes precedence over other operators. For example:

(LastName = ' Smith ' or LastName = ' Jones ') and FirstName = ' John '

The following operators are allowed when you create a comparison expression:
<
>
<=
>=
<>
=
In
Like

The following arithmetic operators are also supported in an expression:

+ (plus)
-(minus)
* (multiply)
/(except)
% (modulus)

String operators

To connect strings, use the + character. Whether string comparisons are case-sensitive is determined by the value of the CaseSensitive property of the DataSet class. However, you can override this value with the CaseSensitive property of the DataTable class.

Wildcard characters
In like comparisons, both * and% are interchangeable as wildcard characters. If the string in the LIKE clause contains * or%, the characters are escaped using brackets ([]). If there is a bracket in the clause, the bracket character is used to escape the brackets (for example, [[] or []]). Wildcard characters are allowed at the beginning and end of the pattern, or at the end of the pattern, or at the beginning of the pattern. For example:
"ItemName like ' *product* '"
"ItemName like ' *product '"
"ItemName like ' product* '"

Wildcard characters are not allowed in the middle of a string. For example, ' te*xt ' is not allowed.

Parent/child Relationship Reference

By adding parent to the column name, you can refer to the parent table in an expression. For example, Parent.price refers to a column named Price in the parent table.

By adding a child to the column name, you can refer to the columns in the Children table in an expression. However, because a child relationship can return multiple rows, you must include a reference to the subreport in the aggregate function. For example, SUM (child.price) returns the sum of the columns named price in the child table.
If a table has more than one table, the syntax is: Child (RelationName). For example, if a table has two child tables with their names customers and orders, the DataRelation object is named Customers2orders, and the reference will be:

AVG (Child (customers2orders). Quantity)

Polymerization

The following aggregation types are supported:

SUM (SUM)
AVG (average)
MIN (minimum value)
Max (max)
Count (Count)
STDEV (statistical standard deviation)
VAR (statistical variance).

Aggregations are usually executed along a relationship. Create an aggregate expression by using one of the functions listed above and the child table columns detailed in the parent/child relationship reference above. For example:

AVG (Child.price)
AVG (orders2details). Price)
Aggregations can also be executed on a single table. For example, to create a rollup for a number in a column named "Price", you would use:
SUM (Price)

SUM (Price)

Description
If you use a table to create aggregations, there will be no combined functionality. Instead, all rows display the same value in the column.

If the table does not have rows, the aggregate function returns the Nullnothingnullptrnull reference (Nothing in Visual Basic).

Data types can always be determined by examining the DataType property of a column. You can also use the CONVERT function to convert the data type, as shown in the following section.

Functions

The following functions are also supported:

Convert

Description
Converts a specific expression to the specified. NET Framework type.

Grammar
Convert (expression, type)

Parameters
expression-the expression to be converted.

The. NET Framework type to which the type-value will be converted.

For example: mydatacolumn.expression= "convert (Total, ' System.Int32 ')"

All conversions are valid except for the following exceptions: A Boolean can only be converted to byte, SByte, Int16, Int32, Int64, UInt16, UInt32, UInt64, String, and itself. Char can only be converted to Int32, UInt32, String, and itself. A datetime can only be converted to and from a string and itself. TimeSpan can only be converted to and from a string and itself.

Len

Description
Gets the length of the string

Grammar
Len (expression)

Parameters
expression-the string to be evaluated.

For example: mydatacolumn.expression= "Len (itemname)"

IsNull

Description
Checks the expression and returns the checked expression or returns the replacement value.

Grammar
IsNull (expression, replacementvalue)

Parameters
expression-an expression to check.

replacementvalue-returns Replacementvalue If the expression is a nullnothingnullptrnull reference (Nothing in Visual Basic).

For example: mydatacolumn.expression= "IsNull (Price,-1)"

Iif

Description
Gets one of two values, based on the result of the logical expression.

Grammar
IIF (expr, Truepart, Falsepart)

Parameters
expr-an expression to evaluate.

The value returned when the truepart-expression is true.

The value returned when the falsepart-expression is false.

For example: mydatacolumn.expression = "IIf" (total>1000, ' expensive ', ' dear ')

Trim

Description
Removes all leading and suffix whitespace characters, such as R, N, T, '

Grammar
Trim (expression)

Parameters
expression-an expression to trim.

Substring

Description
Gets a substring with the specified length, starting at a specified point in the string.

Grammar
SUBSTRING (expression, start, length)

Parameters
The source string for the expression-substring.

start-The integer that specifies the starting position of the substring.

length-an integer specifying the length of a substring.

For example: Mydatacolumn.expression = "substring (phone, 7, 8)"

The example of the face creates the DataView of an online order sorted by the total due amount according to the LINQ to DataSet query:

DataTable orders = dataset.tables["SalesOrderHeader"];

enumerablerowcollection<datarow> query =
From order in orders.asenumerable ()
where order.field<bool> ("onlineorderflag") = = True
by order.field<decimal> ("TotalDue")
Select Order;

DataView view = Query.asdataview ();

Bindingsource1.datasource = view;

The following example creates a DataTable that has one column and five rows. Create two DataView objects and set RowStateFilter for each object to display different views of the table data. The values are then printed.

private void Demonstratedataview ()
{
   //Create one DataTable with one column.
 &nbs p;  datatable table = new DataTable ("Table");
    DataColumn colitem = new DataColumn ("Item",
        Type.GetType ("System.String"));
    Table.columns.add (colitem);

Add five items.
DataRow newrow;
for (int i = 0; I <5; i++)
{
NewRow = Table.newrow ();
newrow["item"] = "item" + I;
Table.rows.add (NewRow);
}
Change the values in the table.
table.rows[0]["Item"]= "Cat";
table.rows[1]["item"] = "dog";
Table.acceptchanges ();

Create two DataView objects with the same table.
DataView Firstview = new DataView (table);
DataView Secondview = new DataView (table);

Print current table values.
Printtableorview (table, "Current values in table");

Set DataView to show only modified
Versions of original rows.
firstview.rowstatefilter=dataviewrowstate.modifiedoriginal;

Print values.
Printtableorview (Firstview, "the Dataview:modifiedoriginal");

Add one new row to the second view.
DataRowView Rowview;
Rowview=secondview.addnew ();
rowview["item"] = "fish";

Set second DataView to show modified versions of
Current rows, or new rows.
Secondview.rowstatefilter=dataviewrowstate.modifiedcurrent
| dataviewrowstate.added;
Print modified and added rows.
Printtableorview (Secondview,
"Second Dataview:modifiedcurrent | Added ");
}

private void Printtableorview (DataTable table, string label)
{
This function prints values in the table or DataView.
Console.WriteLine ("N" + label);
for (int i = 0; i<table.rows.count;i++)
{
Console.WriteLine ("Table" + table.rows[i]["item"]);
}
Console.WriteLine ();
}

private void Printtableorview (DataView view, string label)
{

This is overload prints values in the table or DataView.
Console.WriteLine ("N" + label);
for (int i = 0; i<view.count;i++)
{
Console.WriteLine ("Table" + view[i]["item"]);
}
Console.WriteLine ();
}