DataView query details and syntax

Dataview query details and syntax

Dataview allows data binding on windows and web forms.

In addition, you can customize dataview to represent a subset of data in the able. This feature allows you to have two controls bound to the same able but displaying different data versions. For example, one control may be bound to dataview of all rows in the display table, and the other control may be configured to display only the rows that have been deleted from the datatable. Datatable also has the defaultview attribute. It returns the default dataview of 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 data filtering and sorting view, set the rowfilter and sort attributes. Then, a single datarowview is returned using the item attribute.

You can also use the addnew and delete methods to add and delete rows from the collection. When using these methods, you can set the rowstatefilter attribute to specify that only deleted rows or new rows can be displayed by dataview.

 

User-Defined values can be used in expressions that compare column values. The string value should be enclosed in single quotes. The date value should be placed in the pound sign. Decimal and scientific notation are allowed for numeric values. For example:
"Firstname = 'john '"
"Price <= 50.00"
"Birthdate <#1/31/82 #"

For columns that contain enumerated values, convert the values to integer data types. For example:
"Enumcolumn = 5"

Operator
Concatenation is allowed when the and, or, and not operators are used. You can use parentheses to combine clauses and force priorities. The and operator takes precedence over other operators. For example:
 
(Lastname = 'Smith 'or lastname = 'Jones') and firstname = 'john'

When creating a comparison expression, you can use the following operators:
<
>
<=
> =
<>
=
In
Like

The following Arithmetic Operators are also supported in expressions:
 
+ (Plus)
-(Minus)
* (Multiplication)
/()
% (Module)

String Operators

To connect strings, use + characters. Whether string comparison is case sensitive is determined by the casesensitive attribute value of the dataset class. However, you can use the casesensitive attribute of the datatable class to override this value.

Wildcard
In the like comparison, * and % can be exchanged as wildcards. If the string in the like clause contains * or %, these characters are escaped using brackets. If the clause contains brackets, the brackets are used to escape the brackets (for example, [[] or []). Wildcard characters are allowed at the beginning and end of a pattern, at the end of a pattern, or at the beginning of a pattern. For example:
"Itemname like '* product *'"
"Itemname like '* product '"
"Itemname like 'product *'"

Wildcards are not allowed between strings. For example, 'TE * xt 'is not allowed '.

Parent/child relationship reference

By adding parent to the column name, you can reference the parent table in the expression. For example, parent. price references the column named price in the parent table.
 
By adding a child to the column name, you can reference columns in the child table in the expression. However, because the child relationship can return multiple rows, the reference to the Child column must be included in the aggregate function. For example, sum (child. price) returns the sum of the columns named price in the subtable.
If a table has multiple sub-tables, the syntax is child (relationname ). For example, if a table has two sub-tables named MERs and orders respectively, The datarelation object is named customers2orders and the reference is:

Avg (child (customers2orders). quantity)

Aggregation

The following aggregation types are supported:

Sum (sum)
Avg (average)
Min (minimum)
Max (maximum)
Count)
Stdev (statistical standard deviation)
Var (statistical variance ).

Aggregation is usually executed along the link. Create an aggregate expression by using one of the functions listed above and the child table column detailed in the parent/child relationship reference above. For example:

Avg (child. price)
Avg (child (orders2details). price)
Aggregation can also be performed on a single table. For example, to create a summary for a number in a column named "price", use:
Sum (price)

Sum (price)

Note:
If you use a table to create an aggregate, no combination function is available. On the contrary, all rows show the same value in the column.
 

If the table does not have rows, the aggregate function returns the nullnothingnullptrnull reference (nothing in visual basic ).

The data type can always be determined by checking the datatype attribute of the column. You can also use the convert function to convert data types, as shown in the following section.

Functions

The following functions are also supported:

Convert

Description
Converts a specific expression to a specified. net framework type.
 
Syntax
Convert (expression, type)
 
Parameters
Expression-the expression to be converted.

Type-value is converted to the. net framework type.
 

Example: mydatacolumn. expression = "convert (total, 'System. int32 ')"

All conversions are valid, except in the following cases: 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. Datetime can only be converted to string and itself. Timespan can only be converted to a string and itself.

Len

Description
Returns the length of a string.
 
Syntax
Len (expression)
 
Parameters
Expression-the string to be calculated.
 

Example: mydatacolumn. expression = "len (itemname )"

Isnull

Description
Check the expression and return the expression that has been checked or the replacement value.
 
Syntax
Isnull (expression, replacementvalue)
 
Parameters
Expression-the expression to be checked.

Replacementvalue-if the expression is nullnothingnullptrnull reference (nothing in visual basic), replacementvalue is returned.
 

Example: mydatacolumn. expression = "isnull (price,-1 )"

Iif

Description
Obtain one of the two values based on the results of the logical expression.
 
Syntax
Iif (expr, truepart, falsepart)
 
Parameters
Expr-expression to be calculated.

Value returned when the truepart-expression is true.

Falsepart-the value returned when the expression is false.
 

For example: mydatacolumn. expression = "iif (total> 1000, 'expension', 'Demo ')

Trim

Description
Remove leading and suffix space characters, such as r, n, t ,''
 
Syntax
Trim (expression)
 
Parameters
Expression-the expression to be cropped.
 

Substring

Description
Obtains a substring of the specified length starting from the specified vertex in the string.
 
Syntax
Substring (expression, start, length)
 
Parameters
Expression-the source string of the substring.

Start-an integer at the start of the substring.

Length-refers to the integer of the length of the substring.
 

Example: mydatacolumn. expression = "substring (phone, 7, 8 )"

The following example shows how to create a dataview for an online order sorted by the total payable amount based on the query result of linq to dataset:

Datatable orders = dataset. tables ["salesorderheader"];

Enumerablerowcollection <datarow> query =
From order in orders. asenumerable ()
Where order. field <bool> ("onlineorderflag") = true
Orderby order. field <decimal> ("totaldue ")
Select order;

Dataview view = query. asdataview ();

Bindingsource1.datasource = view;

The following example creates a able with one column and five rows. Create two dataview objects and set rowstatefilter for each object to display different views of table data. Then print these values.

Private void demonstratedataview ()
{
// Create one datatable with one column.
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 first dataview to show only modified
// Versions of original rows.
Firstview. rowstatefilter = dataviewrowstate. modifiedoriginal;

// Print values.
Printtableorview (firstview, "first 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
// 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 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 ();
}