One of LINQ to dataset (basic query)

5.2 implement complex data query using LINQ to Dataset

LINQ to dataset integrates LINQ with ADO. net. It obtains data through ADO. NET and then queries data through LINQ, so as to perform complex queries on the dataset. This section describes how to use LINQ to dataset to operate data in a dataset.

5.2.1 use LINQ to Dataset

You can simply understand the functionQuery the data stored in dataset through LINQIt is not much different from the LINQ query described in Chapter 7th. The usage of LINQ to dataset usually includes the following:Steps:

(1) obtain the dataset/datatable data source.To query data in dataset/able through LINQ, you must first prepare the dataset/datatable data source. You can use ADO. NET technology can be obtained from the database, XML technology can be obtained from the XML file, can also be obtained from any form of data sources, or even directly create and fill the dataset/able object in the memory.

(2) convert the datatable to ienumerable <t> type.From Chapter 7th, we learned that only query operations can be performed on ienumerable <t> or iqueryable <t> interface objects, while datatable does not implement these two interfaces and cannot directly query. In LINQ to dataset, get an equivalent ienumerable <t> object from datatable through the asenumerable () method extended by datatableextensions.

(3) Write a query using the LINQ syntax.You can use the query syntax and method syntax to compile a query in the LINQ to dataset. You can perform any query operations that are allowed by ienumerable <t>.

(4) use the query results.After the query results are generated, you can use the query results (an ienumerable <t> object). For example, you can use foreach to traverse all elements and use max () for numerical calculation, use it as a data source for secondary queries.

The following sections describe how to use the function of using the function of writing data to dataset. However, to make it easier to understandCodeIt is written directly in the memory and not obtained from the database.

Note: Because dataset itself is a collection of able, it can contain one or more datatable and their relationships, and LINQ to dataset actually queries the data of datatable, dataset is not queried.

5.2.2 query a single data table

A dataset usually contains one or more data tables and a set of relationships between them. In fact, it can be seen as a microcosm of the database.LINQ to dataset is also used to query one or more data tables,The datatable can be from a single dataset or multiple dataset.

Section 5.2.1 describes how to query the elements in a able. You must useAsenumerable ()This method converts a able to an enumerative data set of ienumerable <datarow>. Its definition is as follows:

Public static enumerablerowcollection <datarow> asenumerable (this datatable source)

Therefore, if the element type obtained from datatable is datarow, You need to further access the specific field data recorded in the data table, you need to use an extended generic method of datarow -- field <t> () to obtain the data of a field of datarow, which includes six overloaded versions, the following three are most commonly used.

Public static T field <t> (this datarow row, datacolumn column)

Public static T field <t> (this datarow row, int columnindex)

Public static T field <t> (this datarow row, string columnname)

The column parameter indicates the data column and the field to return data. The columnindex parameter indicates the index of the index column starting from 0. Columnname indicates the name of the field to return data. To make the code more generic, we recommend that you use a field name to specify the fields to be returned.

In the sample code 5-1, the buildonedtdataset () method creates a data set named "peopleds" in the memory. It contains only one data table named "peopledt" and the data table contains three fields: name, gender, and age ). In the useselect () method, first create a dataset through buildonedtdataset (), and then obtain the data table named "peopledt" through the dataset. Tables attribute. You can use the able. asenumerable () method to convert a datatable to an ienumerable <t> type data set in query1 and query2 for query. Query1 queries all elements, while query2 queries only the name field.

Sample Code 5-1

// Randomly create a dataset containing data

Static dataset buildonedtdataset ()

{

// Optional name, gender, and age, used to create student data to the data table

String [] nameset = {"Wang Xia", "Zhang San", "Li Si", "Li Hua", "Wang Wu", "Lu 6", "xia Qi ", "Wu ba "};

String [] xbset = {"female", "male", "male", "female", "male "};

Int [] ageset = {18, 20, 21, 22, 19, 20, 25, 24 };

Dataset DS = new dataset ("peopleds"); // create a DataSet object named peopleds

Datatable dt = new datatable ("peopledt"); // create a peopable object named peopledt

DS. Tables. Add (DT); // Add the data table DT to the data set Ds.

// Create a datatable column (field) information, including three fields:

// Name: name, string type

// Gender: xingbie, string type

// Age: age, int type

DT. 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 ")),

});

// Create multiple student information using the optional nameset, age set, and Gender xbset defined earlier

For (INT I = 0; I <nameset. length; I ++)

{

// Automatically create a row in the data table based on the current number and generate a row of data

// Add this row to the data table DT through datatable. Rows. Add ()

Datarow ROW = DT. newrow ();

Row ["name"] = nameset [I];

Row ["Age"] = ageset [I];

Row ["xingbie"] = xbset [I];

DT. Rows. Add (ROW); // Add to data table dt

}

Return Ds; // return Dataset

}

Static void useselect ()

{

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

Datatable dt = Ds. Tables ["peopledt"]; // obtain the "peopledt" data table dt from the dataset Ds.

// Querying query1 indicates querying all records in the datatable. This shows how to use asenumerable ().

VaR query1 =

From PL in DT. asenumerable ()

Select pl;

System. Console. writeline ("query1 :");

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

{

// Demonstrate the use of the field <t> Method

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

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

}

// Querying query2 indicates querying the names of all users in the datatable, demonstrating the use of asenumerable () and field <t>

VaR query2 =

From PL in DT. asenumerable ()

Select pl. Field <string> ("name ");

System. Console. writeline ("query2:"); // print the query result of query1

Foreach (VAR item in query2)

{

System. Console. Write ("{0}", item );

}

System. Console. writeline ();

}

The output of sample code 5-1 is as follows. The query result of query1 is all the complete records in the table, including name, gender, and age. Query results of query2 only include the set of "name" fields in the table.

Query1:

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

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

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

Name: Li Hua, Gender: female, age: 22

Name: Wang Wu, Gender: male, age: 19

Name: Lu 6, Gender: male, age: 20

Name: Xia Qi, Gender: male, age: 25

Name: Wu Ba, Gender: male, age: 24

Query2:

Wang Xia Zhang San Li Si Li Hua Wang Wu Lu Liu Xia Qi Wu Ba

In addition to the SELECT statement, you can perform operations such as where filtering, orderby sorting, and groupby grouping on datatable records. As shown in the sample code 5-2, the orderby and where clauses are used in both query3 and query4 queries, and the filtering and sorting operations are performed at the same time. Query3 queries all records older than 22 years old, sorted by age from low to high. Query4 query all ages between 20 and 20 ~ Records Between 25, sorted by age from high to low.

Sample Code 5-2

Static void useorderbywhere ()

{

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

Datatable dt = Ds. Tables ["peopledt"]; // obtain the "peopledt" data table dt from the dataset Ds.

// Query query3 query all the persons whose ages are greater than 22 and sort by age from low to high

VaR query3 =

From PL in DT. asenumerable ()

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

Where pl. Field <int> ("Age")> 22

Select pl;

System. Console. writeline ("query3 :");

Foreach (VAR item in query1) // 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 "));

}

// Query query4 query all the persons whose ages are greater than 20 and less than 25 in the data table, sorted by age from high to low

VaR query4 =

From PL in DT. asenumerable ()

Orderby pl. Field <int> ("Age") descending

Where pl. Field <int> ("Age")> 20

Where pl. Field <int> ("Age") <25

Select pl;

System. Console. writeline ("query4 :");

Foreach (VAR item in query2) // print the query4 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 the sample code 5-2 is as follows. The query3 output is a record older than 22 years old, and the query4 output is a record older than 20-22 years old ~ Between 25 records.

Query3:

Name: Wu Ba, Gender: male, age: 24

Name: Xia Qi, Gender: male, age: 25

Query4:

Name: Wu Ba, Gender: male, age: 24

Name: Li Hua, Gender: female, age: 22

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

Tip: to query the data of a able using a LINQ to dataset, you can simply divide the data into two parts. First, you can convert the data set into an ienumerable <t> data set, and then perform operations on ienumerable <t>, this step fully applies all the LINQ query operations described in Chapter 7th.

For your security, please only open the URL with reliable source

Cancel website opening

From: http://hi.baidu.com/2wixiao/blog/item/5e69ff0593b4b4c27b8947c6.html