C # query with DataView,

C # query with DataView,

Newbie

In the development of a software, DataView queries in MyS are used. After the query, check whether the view has a returned value. After searching for a long time, I finally found its DataView XX. Count parameter.

 string table = String.Format("`Database` WHERE `XX` = '{0}';", XX);                                        DataView dv = mysqlUtile.MysqlDataAdapter(table);                    if (dv.Count==0)                    {                           ...                                     }                }

　　

 

 

Http://skybirdzw.blog.163.com/blog/static/725706262011233455240/

The DataView class is used to indicate customDataTable.DataTableAndDataViewThe relationship follows the famous design pattern-document/view pattern, whereDataTableIs a document, andDataviewIs a view.
At any time, you can have multiple views based on the same data. More importantly, you can process each view with its own set of attributes, methods, and events as an independent object. This also represents a huge leap over ADO.
Create DataView

Public DataView (); Public DataView (DataTable );

DataView is available only when it is connected to an existing, possibly non-empty able object. Generally, this connection is specified during construction.

DataView dv; Dv = new DataView (theDataSet. Tables ["Employees"]);

However, you can create a new view and associate it with the Table by using the Table attribute.

DataView dv = new DataView (); Dv. Table = theDataSet. Tables ["Employees"]; The DataView constructor allows you to get a DataView object from the able. If needed, or vice versa. In fact, the DefaultView attribute of the DataTable object returns a DataView object for the table. DataView dv = dt. DefaultView;

Once you haveDataViewObject, you can use its attributes to create the data row set you want the user to see. Generally, you can use the following attributes:

• RowFilter
• Sort

The former allows you to customize rules for matching visible data in the view. The latter uses expressions for sorting. Of course, you can use any combination of the two.

Set Filter RowFilterIs a read/write attribute used to read and set table filter expressions.

Public virtual string RowFilter {get; set ;}

You can use any legal combination of column names, logical and numeric operators, and constants to form an expression. Here are some examples:

Dv. RowFilter = "Country = 'usa '"; Dv. RowFilter = "EmployeeID> 5 AND Birthdate <#1/31/82 #" Dv. RowFilter = "Description LIKE '* product *'"

Let's take a look at the basic filter rules and operators.
The filter string is the logical connection of the expression. You can use AND, OR, NOT to connect to a short expression, OR you can use parentheses to form a clause to specify a priority operation.
The clauses that usually contain column names are compared with letters, numbers, dates, or other column names. Here, you can use Relational operators and arithmetic operators, such as >=, <,>, +, *, % (Modulo), and so on.
If the row to be selected cannot be easily expressed by arithmetic or logical operators, you can use the IN operator. The following code selects a random row:

Dv. RowFilter = "employeeID IN (2, 4, 5 )"

You can also use wildcards * and %, which are more useful when used together with the LIKE operator. They all represent any number of characters and can be used with each other.
Note that if the LIKE clause already contains * or % characters, you must enclose it in square brackets to avoid ambiguity. If, unfortunately, the Chinese brackets of the string also exist, they must also be enclosed. In this way, the matching statement is as follows:

Dv. RowFilter = "Description LIKE '[[] * [] product [[] * []"

Wildcard characters can only be used at the beginning or end of a filter string, but cannot appear in the middle of a string. For example, the following statement produces a runtime error:

Dv. RowFilter = "Description LIKE 'prod * ct"

The string must start with single quotes, and the date type must start with the # symbol. Numeric values can use decimal points and scientific notation.
RowFilter also supports aggregate functions, such as SUM, COUNT, MIN, MAX, and AVG. If there are no data rows in the table, the function returns NULL.
At the end of introducing the RowFilter expression, let's discuss three convenient functions: Len, IIF, and Substring.
As its name, Len () returns the length of a specific expression. This expression can be a column name or another legal expression.
Substring () returns the character Substring of a specified expression starting from a specific position.
I like IIF () most, which has one or two values according to the logical expression value. IIF is a compact expression of the if-THEN-ELSE statement. Syntax:

IIF (expression, if_true, if_false)

Through this function, you can create a very complex filter string. For example, assume that you obtain the Employees table from the SQL Server's Northwind database, the following expressions can be used to select employees whose employeeID is less than 6 and whose lastname is an even number of characters and whose employeeID is greater than 6 and whose lastname is an odd number of characters.

IIF (employeeID <6, Len (lastname) % 2 = 0, Len (lastname) % 2> 0)

Pre-arranged View
In the preceding example, the datagrid must be used to pre-arrange data rows in the view to refresh the user interface. The automatic mechanism is. NET.
The product of data binding. The Datagrid is throughDataSourceProperty to obtain data binding controls. DataView is a data binding class that can be builtDataSourceAttribute content.

What should you do if you want to use another control other than the datagrid? What if you don't want to use automatic data binding? How should we pre-arrange the selected data rows in the view?

DataViewTableAttribute pointing to the corresponding data table, but DAtaTableThe filter information is not saved. Therefore, the data in the pre-arranged table is destined to be unfeasible. Although DAtaTableAndDataViewIs closely related, but they are independent and execute independent functions.
The following Visual Basic. NET code snippet shows how to traverse all data rows in the view and add them to listbox.

Dim dv As New DataView () Dv = ds. Tables ("Employees"). DefaultView Dv. RowFilter = "employeeid> 5" ListBox1.Items. Clear () Dim buf As String Dim dr As DataRowView For Each dr In dv Buf = "" Buf & = dr ("lastname"). ToString () & "," & dr ("firstName"). ToString () ListBox1.Items. Add (buf) Next

As mentioned above,DataViewIs an enumerable class, so you can safely pass it to the For... Each statement. The Count attribute stores the number of rows in the view For use in the For... Next loop.
To access a row in the view, you can useDataRowViewClass.DataRowViewRepresentationDataRowView, just likeDataViewThe same is true for views customized by DataTable.
In general, DataRow can have up to four States: default, original, current, and proposed. These statuses are composedDataRowVersionEnumeration type settingsRowVersionAttribute expression.
DataRow views can only be in one of the states.
The default version of a data row is available only when the default value is set during column construction. The initial (original) version refers toAcceptChangesThen, you can obtain the data row or snapshot from the data source. The Current version refers to the Current data row, including all updates that occurred at the time. The Proposed status only exists when you call BeginEdit and EndEdit.
You can accessDataRowAccess with the same syntaxDataRowView. The most important attribute here is Item.

Sorting and other convenient features
DataView supports the Sort attribute and can be used to Sort the content in the view. Sort is sorted by column name expressions separated by commas. By adding ASC or DESC restrictions to any column name, you can arrange the fields in the ascending or descending order. If no direction limiting word exists, the default order is ASC.
DataView is an object in memory, so it is sorted locally without calling the database server.
RowStateFilterIt is another interesting property of DataView. It can use any predefined criteria to filter the content in the DataTable. The following table lists all values of the DataViewRowState Enumeration type:

CurrentRows	Includes all unupdated, new, and modified data rows
Deleted	All data rows deleted after the last AcceptChanges call
ModifiedCurrent	All data rows modified since the last call of AcceptChanges
ModifiedOriginal	All data lines of the original version after the last AcceptChanges call
New	All newly added rows since the last AcceptChanges call
OriginalRows	Returns the initial data row, including the unchanged and deleted
Unchanged	All unupdated data rows

If you want to operate on non-connected data, all updates areDataTableCallAcceptChanges. Updates to a single row are calledDataRowOfAcceptChanges. Similarly, these updates can be calledDataTableOrDataRowObjectRejectChangesTo cancel.
DataViewThe object also has some attributes, suchAllowEdit, AllowDeleteAndAllowNewTo get or set whether to allow updates. They are set to True by default and allow updates of any type. If you want to complete the update operation when the flag is set to False, a runtime error will occur.