C # Database Development reading Note 4---the use of DataView

1. Create DataView

There are two ways to create DataView, you can use the DataView constructor, or you can create a DefaultView property on a DataTable

The reference. The DataView constructor can be either empty or a DataTable can be used in the form of a single parameter or a DataTable

Filter with filter criteria, sort criteria, and row status.

The DataView index is generated when DataView is created or when any sort, rowfilter, or RowStateFilter property is modified.

Therefore, when creating DataView, the best performance is achieved by providing any initial sort order or filter criteria in the form of constructor parameters.

//Once the code uses the DataView constructor to create the DataView. RowFilter, sort, and dataviewrowstate will be provided with the DataTable

DataView CUSTDV = new DataView (custds.tables["Customers"], "country = ' USA '",

"ContactName", dataviewrowstate.currentrows);

//The following code uses the DefaultView property of the table to get a reference to the default DataView of the DataTable.

DataView CUSTDV = custds.tables["Customers"]. DefaultView;

2. Use DataView to sort and filter data

DataView provides several features for sorting and filtering data in a DataTable.

Using the Sort property, you can specify single or multiple column sort orders and include ascending ASC and descending desc parameters.

You can use the ApplyDefaultSort property to automatically create a sort of one or more primary key columns based on a table in ascending order. Only if the Sort property is empty

Or an empty string, or when a primary key is defined, ApplyDefaultSort applies.

Using the RowFilter property, you can specify a subset of rows based on the column values of the rows.

If you want to return the results of a data-specific query (rather than providing a dynamic view of the subset of data) for optimal performance, you need to use the DataView find

or FindRows method instead of setting the RowFilter property. Setting RowFilter causes the data to be rebuilt.

//Sample code: This view shows all products with inventory quantities less than or equal to reorder quantities, which are sorted first by SupplierID and then by ProductName

DataView Prodview = new DataView (prodds.tables["Products"], "Unitsinstoc <= reorderlevel",

"SupplierID, ProductName", dataviewrowstate.currentrows);

3. Use DataView to view data

3.1. View DataView Content

When viewed with DataView, the RowStateFilter property of DataView determines which row version of the underlying DataRow is exposed.

//The following code shows all current and initial values in a table

DataView Catview = new DataView (catds.tables["Categories"]);

Console.WriteLine ("Current Values");

Writeview (Catview);

Console.WriteLine ("Original Values");

Catview.rowstatefilter = dataviewrowstate.modifiedoriginal;

Writeview (Catview);

public static void Writeview (DataView myView)

{

foreach (DataRowView mydrv in MyView)

{

for (int i = 0; i < MyView.Table.Columns.Count; i++)

{

Console.Write (Mydrv[i] + "\ t");

}

Console.WriteLine ();

}

}

3.2. Search for DataView content

Using the DataView Find and FindRows methods, you can search for rows by the sort key value of the row.

The Find method returns an integer that represents the index of the DataRowView that matches the search criteria. If more than one row matches the search criteria,

Only the index of the first matching DataRowView is returned. Returns 1 if not found.

If you return search results that match more than one row, you can use the FindRows method. FindRows works in a similar way to the Find method, with different

Only FindRows returns an DataRowView array that references all matching rows in DataView. If no match is found, DataRowView

The array is empty.

If you use the Find or FindRows method, you must specify the sort order by setting ApplyDefaultSort to TRUE or by using the Sort property.

If no sort order is specified, an exception is thrown.

The Find and FindRows methods use an array of values as input, and the length of the array matches the array of columns that the sort order contains. To sort an individual column

, you can pass a single value. For a sort order that contains more than one column, an array of objects can be passed.

//When sorting multiple columns, the values in the object array must match the order of the columns specified in the DataView's Sort property.

The following code calls the Find method on DataView with a single column sort order.

DataView Custview = new DataView (custds.tables["Customers"], "" "," CompanyName ",

DataViewRowState.CurrentRows);

int rowIndex = Custview.find ("The Cracker Box");

if (RowIndex = =-1)

{

Console.WriteLine ("No Matching results");

}

Else

{

Console.WriteLine ("{0}, {1}", custview[rowindex]["CustomerID"]. ToString (),

custview[rowindex]["CompanyName"]. ToString ());

}

The following code calls the Find method on multiple column sort order DataView, and if the Sort property specifies more than one column, you must press

The Sort property specifies the order in which each column is passed with an array of search-worthy objects

DataView Custview = new DataView (custds.tables["Customers"], "" "," CompanyName,

ContactName ", dataviewrowstate.currentrows);

datarowview[] FoundRows = custview.findrows (New object[]{"The Cracker Box", "Liu Wong"});

if (Foundrows.length = = 0)

{

Console.WriteLine ("No Matching results");

}

Else

{

foreach (DataRowView mydrv in FoundRows)

{

Console.WriteLine ("{0}, {1}", mydrv["CompanyName"]. ToString (),

mydrv["CompanyName"]. ToString ());

}

}

3.3. Use DataView to navigate the relationship

If there is a relationship between the tables in the dataset, you can use the CreateChildView method of DataRowView to create a DataView for the rows in the parent table.

The DataView contains rows from the related child table.

//For example, the following code sorts categories and its related products in alphabetical order by CategoryName and ProductName.

DataTable cattable = catds.tables["Categories"];

DataTable ProdTable = catds.tables["Products"];

//create relationships between the Categories table and the Products table

DataRelation Catprodrel = CatDS.Relations.Add ("Catprodrel", cattable.columns["CategoryID"],

prodtable.columns["CategoryID"]);

//Create DataView for categories and products tables

DataView Catview = new DataView (cattable, "", "CategoryName", dataviewrowstate.currentrows);

DataView Prodview;

//Traverse Categories table

foreach (DataRowView catdrv in Catview)

{

Console.WriteLine (catdrv["CategoryName"]);

//Create a child view of the product record

Prodview = Catdrv.createchildview (Catprodrel);

Prodview.sort = "ProductName";

foreach (DataRowView proddrv in Prodview)

{

Console.WriteLine ("\ T" + proddrv["ProductName"]);

}

}

4. Modify data using DataView

By default, DataView is a read-only view of the data, but you can use DataView to add, delete, or modify data rows in the underlying table.

The DataView method is to set one of the 3 Boolean properties of a. These properties are AllowNew, AllowEdit, AllowDelete.

//Sample code

DataTable custTable = custds.tables["Customers"];

DataView Custview = Custtable.defaultview;

Custview.sort = "CompanyName";

Custview.allowdelete = false;

DataRowView newdrv = Custview.addnew ();

newdrv["CustomerID"] = "ABCDE";

newdrv["CompanyName"] = "ABC products";

Newdrv.endedit ();

5. Using the DataView Event

You can use the DataView ListChanged event to determine whether the view has been updated for a reason: adding, deleting, or modifying rows in the underlying table;

The ListChanged event also notifies you whether the list of rows you are viewing has undergone significant changes due to the new sort order or filter being applied.

//Sample code: How to add a ListChanged event handler

Custview.listchanged + = new System.ComponentModel.ListChangedEventHander (onlistchanged);

//listchanged Event Definition

protected static void Onlistchanged (object sender, System.ComponentModel.ListChangedEventArgs args)

{

Console.WriteLine ("ListChanged:");

Console.WriteLine ("\ t Type =" +args. ListChangedType);//Type of change

Console.WriteLine ("\ t oldindex =" +args. OLDINDEX);//JIU di index

Console.WriteLine ("\ t newindex =" +args. NEWINDEX);//New Index

}

6. Set the default table view using DataViewManager

DataViewManager is used to manage the view settings for all tables in the dataset. DataViewManager is suitable for situations where one control is bound to more than one table.

DataViewManager contains a collection of DataViewSetting objects that are used to set the view settings for each table in the dataset. For a dataset in the

Each table, Dataviewsettingcollection, contains a DataViewSetting object that you can use to set the table using the dataviewseting of the referenced table

The default ApplyDefaultSort, Sort, RowFilter, and RowStateFilter properties, which can be referenced by name or ordinal, or by passing a reference to a particular Table object

The dataviewsetting of the table; You can use the DataViewSetting property to access the collection of DataViewSetting objects in DataViewManager.

//The following code example populates a dataset with SQL Server's Noerthwind database Customers table, Orders table, and Order Details table, and creates a table

, use DataViewManager to set the default DataView settings, and bind the DataGrid to DataViewManager. The example sets the dataset in the

The default DataView settings for multiple tables are sorted by the table's primary key (ApplyDefaultSort = True), and then the sort order of the Customers table is modified to sort by CompanyName.

Creating Connection,dataadapter and Datasets

SqlConnection nwindconn = new SqlConnection ("...");

SqlDataAdapter custda = new SqlDataAdapter ("Select CustomerID, CompanyName from Customers", nwindconn);

SqlDataAdapter Orderda = new SqlDataAdapter ("Select OrderID, CustomerID from Orders", nwindconn);

SqlDataAdapter orddetda = new SqlDataAdapter ("Select OrderID, ProductID, Quantity from [Order Details]", nwindconn);

DataSet custDS = new DataSet ();

//Open Connection

Nwindconn.open ();

//Populating data sets with schema information

Custda.missingschemaaction = MissingSchemaAction.AddWithKey;

Orddetda.missingschemaaction = MissingSchemaAction.AddWithKey;

Orddetda.missingschemaaction = MissingSchemaAction.AddWithKey;

Custda.fill (custDS, "Customers");

Orderda.fill (CUSTDA, "Orders");

Orddetda.fill (custDS, "OrderDetails");

//Close connection

Nwindconn.close ();

Create a dataset relationship

CUSTDS.RELATIONS.ADD ("CustomerOrders", custds.tables["Customers"]. columns["CustomerID"],

custds.tables["Orders"]. columns["CustomerID"]);

CUSTDS.RELATIONS.ADD ("OrderDetails", custds.tables["Orders"]. columns["OrderID"],

custds.tables["OrderDetails"]. columns["OrderID"]);

//Create a DataViewManager for the dataset

DataViewManager MYDVM = new DataViewManager (CUSTDS);

foreach (dataviewsetting mydvs in mydvm.dataviewsettings)

{

Mydvs.applydefaultsort = true;

}

mydvm.dataviewsettings["Customers"]. Sort = "CompanyName";

//bind to DataGrid

System.Windows.Forms.DataGrid Mygrid = new System.Windows.Forms.DataGrid ();

Mygrid.setdatabinding (MYDVM, "custo

This article is from the "Eight Days Dragon" blog, please make sure to keep this source http://1224517743.blog.51cto.com/2643474/1610236

C # Database Development reading Note 4---the use of DataView