Implement complex data query using LINQ to dataset [bottom]

Query1-all student scores:

Name: Zhang San, mathematics: 80, Chinese: 75, English: 78

Name: Wang Xia, mathematics: 88, Chinese: 80, English: 60

Name: Zhao Min, mathematics: 75, Chinese: 90, English: 80

Name: Wu an, mathematics: 59, Chinese: 80, English: 75

For some complex queries, it is difficult to use only one LINQ query, which requires the use of multiple queries. For example, if you want to query the information of students who have no scores, you can use either of the two methods.

As shown in sample code 5-5, query2 queries 1st methods. First, query scoreids to obtain all the score sets in Table dtscore, and then query2 finds all the students from Table dtstu, the score number is not among the students who query scoreids. These students have no score.

Query3 adopts the 2nd method. First, it queries scrstu through two parallel from clauses to query all the students with scores from the table dtstu and dtscore, which is the same as in the sample code 5-4. Query3 then removes students with scores from all student information through the student t () method in the form of method syntax, and the remaining students with no scores.

Sample Code 5-5

Static void querynonescorestu ()

{

Dataset DS = builddataset (); // get the DS Dataset

Datatable dtstu = Ds. Tables ["Students"]; // obtain the students table dtstu from the DS Dataset

Datatable dtscore = Ds. Tables ["scores"]; // obtain the scores table dtscore from the DS dataset.

VaR scoreids = // query the score numbers of all students with scores

From score in dtscore. asenumerable ()

Select score. Field <int> ("scoreid ");

VaR query2 = // query query2 Query Information of middle school students whose score numbers are not in scoreids

From Stu in dtstu. asenumerable ()

Where! Scoreids. Contains <int> (STU. Field <int> ("scoreid "))

Select Stu;

System. Console. writeline ("query2-students without scores :");

Foreach (VAR item in query2) // print the query result of query2

{

System. Console. writeline ("Name: {0}, Gender: {1}, age: {2 }",

Item. Field <string> ("name"), item. Field <string> ("xingbie"), item. Field <int> ("Age "));

}

VaR scrstu = // query scrstu to query all students with score Information

From Stu in dtstu. asenumerable ()

From score in dtscore. asenumerable ()

Where Stu. Field <int> ("scoreid") = score. Field <int> ("scoreid ")

Select Stu;

// Query3 is used to remove students with scores from all student records.

VaR query3 = dtstu. asenumerable (). Random T (scrstu );

System. Console. writeline ("query3-students without scores :");

Foreach (VAR item in query3) // print the query3 Query Result

{

System. Console. writeline ("Name: {0}, Gender: {1}, age: {2 }",

Item. Field <string> ("name"), item. Field <string> ("xingbie"), item. Field <int> ("Age "));

}

}

The output of sample code 5-5 is as follows. It can be seen that although the implementation methods of query2 and query3 are different, the final query results are the same, the student "Li Si" with no score is provided.

Query2-students without scores:

Name: Li Si, Gender: male, age: 19

Query3-students without scores:

Name: Li Si, Gender: male, age: 19

Tip: from the sample code 5-5, query2 and query3 can be seen that there are many different solutions to many problems, but the final results are the same. When solving the problem, you should also try to find more solutions and select the simplest, most efficient, and most suitable method. In addition, do not forget how to use the method syntax in LINQ.

5.2.4 use query to create a data table
Using the extension method copytodatatable () provided by the datatableextensions class, LINQ to dataset directly copies the query results obtained from the data table (type: ienumerable <datarow>) to a new data table (datatable) you can bind the query result to the interface control (datagridview, etc.), or use some features unique to the able.

Copytodatatable () includes three overloaded versions, which are defined as follows. Among them, the 1st versions are the simplest and most commonly used. Note that all types of T here are the datarow type and its subclass.

Public static datatable copytodatatable <t> (

This ienumerable <t> source) where T: datarow

Public static void copytodatatable <t> (

This ienumerable <t> source,

Datatable table,

Loadoption options) where T: datarow

Public static void copytodatatable <t> (

This ienumerable <t> source,

Datatable table,

Loadoption options,

Fillerroreventhandler errorhandler) where T: datarow

Table indicates the target data table object, which is used to save data. Options is used to specify the loading attribute of the datatable. Errorhandler is a function delegate that allows developers to specify custom exception handling operations. The copytodatatable () method uses the following process to create a datatable copy by querying:

(1) copytodatatable () method datatable in the clone source table (datatable object that implements the iqueryable <t> interface ). The ienumerable source is usually derived from the LINQ to dataset expression or method query.

(2) The structure of the target datatable is generated from the first datarow object column in the source table. The name of the cloned table is the name of the source table plus the word "query ".

(3) For each row in the source table, copy the row content to the new datarow object and insert the object into the target ale.

(4) After copying all the datarow objects in the source table, the cloned datatable is returned. If the source sequence does not contain any datarow object, this method returns an empty datatable.

The example code 5-6 demonstrates the use of the copytodatatable () method. query query1 to query information of all students with both scores and ages greater than 20. In this case, query1 is ienumerable <datarow>. Use the copytodatatable () method of query1 to create a datatable copy newdt, and print the newdt data.

Sample Code 5-6

Static void usecopytodtsimple ()

{

Dataset DS = builddataset (); // get the DS Dataset

Datatable dtstu = Ds. Tables ["Students"]; // obtain the students table dtstu from the DS Dataset

Datatable dtscore = Ds. Tables ["scores"]; // obtain the scores table dtscore from the DS dataset.

VaR query1 = // query the scores of students whose age is greater than 20

From Stu in dtstu. asenumerable ()

From score in dtscore. asenumerable ()

Where Stu. Field <int> ("scoreid") = score. Field <int> ("scoreid ")

Where (INT) STU ["Age"]> 20

Select Stu;

// Create a new copy using the copytodatatable () method
// Print the copy information

Datatable newdt = query1.copytodatatable <datarow> ();

System. Console. writeline ("student list :");

Foreach (VAR item in newdt. asenumerable () // print the copy information

{

System. Console. writeline ("Name: {0}, Gender: {1}, age: {2 }",

Item ["name"], item ["xingbie"], item ["Age"]);

}

}

The output of sample code 5-6 is as follows. The newdt copy of query1 contains the same data as query1. Note that the data source in this example is generated by sample code 5-3.

Student list:

Name: Wang Xia, Gender: female, age: 21

Name: Zhao Min, Gender: female, age: 22

Tip: As this example is a console application, the newdt record is simply printed. In actual development, a copy of copytodatatable () is usually used for interface binding.

5.2.5 Modify Field data in the table
In the sample code in the previous chapter, only datarowextensions is used. the field () method is used to obtain the data of fields in the data table. Of course, you may need to modify the data in the data table by using the LINQ to dataset method. This section describes how to use setfield () to modify the data.

In LINQ to dataset, the datarowextensions class provides the generic Extension Method setfield (), which is used to set the data of the specified columns in a data table and specify a specific data type. The datarowextensions. setfield () method has three overloaded versions. Their definitions are as follows:

Public static void setfield <t> (

This datarow row,

Datacolumn column,

T value)

Public static void setfield <t> (

This datarow row,

Int columnindex,

T value)

Public static void setfield <t> (

This datarow row,

String columnname,

T value)

Column indicates the column object for data setting (datacolumn type), columnindex indicates the column index for data setting starting from 0, and columnname indicates the column name for data setting. Generally, the names of columns in a data table are fixed. Therefore, we recommend that you use the column name as much as possible to specify the columns to be set. This code is more extensible.

The example code 5-7 demonstrates the use of the setfield () method. The builddataset () method is used to obtain the data table. The 1st foreach statements traverse the data table records. The setfield <int> () method increases the student's age by 2 years. Because the student's age is int type, t in setfield <t> () is represented by INT type. Finally, a list of all students is printed.

Sample Code 5-7

Static void usesetfield ()

{

Dataset DS = builddataset (); // get the DS Dataset

Datatable dtstu = Ds. Tables ["Students"]; // obtain the students table dtstu from the DS Dataset

// Traverse all the students in the table dtstu and increase their ages by using the setfield () method.

Foreach (VAR row in dtstu. asenumerable ())

{

Int age = row. Field <int> ("Age ");

Row. setfield <int> ("Age", age + 2 );

}

System. Console. writeline ("student list :");

Foreach (VAR item in dtstu. asenumerable () // print the student information after the age change

{

System. Console. writeline ("Name: {0}, Gender: {1}, age: {2 }",

Item ["name"], item ["xingbie"], item ["Age"]);

}

}

The output of sample code 5-7 is as follows. It can be seen that the age of all students is increased by 2 years.

Student list:

Name: Zhang San, Gender: male, age: 22

Name: Li Si, Gender: male, age: 21

Name: Wang Xia, Gender: female, age: 23

Name: Zhao Min, Gender: female, age: 24

Name: Wu an, Gender: male, age: 20

Note: The setfield () method directly modifies the records in the data table. To keep the original data unchanged, you should back up the source data table before using setfield (). The author suggests using copytodatatable () method to back up the source data table.

5.2.6 use dataview
Dataview is a commonly used form of data storage in read-only mode. It can be bound to the UI to provide users with Dynamic Data Query functions. Dataview can be obtained from datatable or dataset. In addition, in. net3.5, dataview can also be obtained through LINQ queries.

In LINQ to dataset, you can use ableableextensions. asdataview () Extension Method to create a dataview object corresponding to the data source from the datatable or LINQ query. The asdataview () method includes two overloaded versions, which are defined as follows. They only have different types of extensions (I .e., different data sources ).

Public static dataview asdataview (

This datatable table

)

Public static dataview asdataview <t> (

This enumerablerowcollection <t> Source

) Where T: datarow

In asdataview () 2nd overloaded versions, the enumerablerowcollection <datarow> type data source is required. This type indicates the set of datarow objects returned from the LINQ query and can be obtained from the LINQ query.

The example code 5-8 demonstrates the use of the asdataview () method. Builddataset () is only used to create a data source. In the createdataview () method, dvdt creates a dataview by using the able class Extension Method asdataview (), which contains all the data records in the data table. Among them, query query1, query2, and query3 are all LINQ to dataset queries, and the explicit type is enumerablerowcollection <datarow>. Of course, the VaR variable type can also be used here, And the compiler will automatically convert.

Sample Code 5-8

Static dataset builddataset ()

{

Dataset DS = new dataset ("Students"); // create a students Dataset

// Create a students data table and add it to the dataset

// Students data table contains student information

Datatable dtstu = new datatable ("Students ");

DS. Tables. Add (dtstu );

// Add the column (field) information of the student information record, which includes three fields:

// Name: name, string type

// Gender: xingbie, string type

// Age: age, int type

Dtstu. Columns. addrange (New datacolumn [] {

New datacolumn ("name", type. GetType ("system. String ")),

New datacolumn ("xingbie", type. GetType ("system. String ")),

New datacolumn ("Age", type. GetType ("system. int32 ")),

});

// Add row information for student information, including name, gender, and age

Dtstu. Rows. Add ("James", "male", 20 );

Dtstu. Rows. Add ("Li Si", "male", 19 );

Dtstu. Rows. Add ("Wang Xia", "female", 21 );

Dtstu. Rows. Add ("Zhao Min", "female", 22 );

Dtstu. Rows. Add ("Wu an", "male", 18 );

Dtstu. Rows. Add ("Yang Hua", "female", 23 );

Return Ds; // return the dataset

}

Static void createdataveiw ()

{

Dataset DS = builddataset (); // get the DS Dataset

Datatable dt = Ds. Tables ["Students"]; // obtain the students table dt from the data set Ds.

// Use the datatable. asdataview () method to create a dataview object dvdt from the data table dt

Dataview dvdt = DT. asdataview ();

// Query1 create a dataview object dvdt using the LINQ query and query all its elements

Enumerablerowcollection <datarow> query1 =

From Stu in DT. asenumerable ()

Select Stu;

Dataview dvnml = query1.asdataview (); // obtain the dataview generated by query1

// Query2 creates a dataview with filtered information by using the LINQ query, and queries all students with the surname "yang"

Enumerablerowcollection <datarow> query2 =

From Stu in DT. asenumerable ()

Where Stu. Field <string> ("name"). startswith ("yang ")

Select Stu;

Dataview dvfilter = query2.asdataview (); // obtain the dataview generated by query2.

// Query3 create a dataview with sorting information by using LINQ query, and sort students in ascending order

Enumerablerowcollection <datarow> query3 =

From Stu in DT. asenumerable ()

Orderby Stu. Field <int> ("Age ")

Select Stu;

Dataview dvsort = query3.asdataview (); // obtain the dataview generated by query3.

}

In many cases, you need to sort and filter data. There are two ways to do this in LINQ to dataset. One is to create a dataview through a LINQ query with sorting and filtering operations. For example, in the sample code 5-8, dvfilter and dvsort, the former is created by querying query2 with the WHERE clause filter function, and the latter is created by querying query3 with the orderby clause sorting function, the data obtained by dvfilter and dvsort is naturally a set of filtered and sorted data. Another method is to filter and sort data by using the rowfilter and sort attributes of the dataview class. Both attributes are of the string type.

The rowfilter attribute receives a string that represents a filtering condition. The format is: the name of the specified column followed by an operator and a value to be filtered. Operators can be equal to (=), greater than (>), less than (<), and so on. By setting the value of the rowfilter attribute, you can clear the filter conditions on dataview in two different ways:

Q sets the rowfilter attribute to null.

Q sets the rowfilter attribute to an empty string. The sort property receives a string that indicates the sorting information. It contains the column name, followed by "ASC" (ascending) or "DESC" (descending ). By default, the following are sorted in ascending order. Multiple columns can be separated by commas. There are two ways to clear the sorting information in dataview:

Q: Set the sort attribute to null.

Q: Set the sort attribute to an empty string.

The sample code 5-9 demonstrates the use of the rowfilter and sort attributes. The dataview object dvdt is created directly from the dtable object dtstu and contains all records in dtstu. In this example, if rowfilter is set to "age> 20", only records with the age field greater than 20 are required. If sort is set to "Age ASC, name DESC", the system first sorts the age field from low to high and then by name from high to low.

Sample Code 5-9

Static void usedataview ()

{

Dataset DS = builddataset (); // get the DS Dataset

Datatable dt = Ds. Tables ["Students"]; // obtain the students table dt from the data set Ds.

// Use the datatable. asdataview () method to create a dataview object dvdt from the data table dt

Dataview dvdt = DT. asdataview ();

// Use the rowfilter attribute to set the dataview filter information, which only records students older than 20 years old.

Dvdt. rowfilter = "age> 20 ";

// Set rowfilter to null or a null string to clear the filter information. Select either.

Dvdt. rowfilter = string. empty;

Dvdt. rowfilter = NULL;

// Set the dataview sorting information through the sort attribute. The age is sorted in ascending order and the name is sorted in ascending order.

Dvdt. Sort = "Age ASC, name DESC ";

// Set rowfilter to null or a null string to clear the filter information. Select either.

Dvdt. Sort = string. empty;

Dvdt. Sort = NULL;

}

From the sample code above, we can see that the rowfilter and sort attributes of the LINQ to dataset query and dataview can be sorted and filtered. However, the sorting and filtering functions provided by the LINQ query are more powerful. developers can write custom filtering functions and sorting methods. The rowfilter and sort attributes can only be field-based expressions and can only be sorted and filtered in a simple manner. They can be cleared, so they are more flexible.

Tip: when the data to be displayed by dataview is complex and complicated filtering or sorting operations are required, we recommend that you use a LINQ query to create a dataview as a final or temporary view. Then, use the rowfilter and sort attributes to flexibly sort and filter dataview.