Able data sorting, retrieval, merging, paging, and statistics

I. Sorting

1. Get the default view of datatable

2. Set the sorting expression for the view

3. Replace the able with the new datatable exported from the sorted view.

(ASC can be omitted in ascending order. Separate multiple columns with commas)

 
Dataview DV = DT. defaultview; DV. Sort = "id ASC, name DESC"; dt = DV. totable ();
 

 

 

Ii. Search

1. Set the query string

2. Use the select method to obtain an array of all data row objects that meet the conditions.

(Multiple query conditions are separated by and. Like % is used for fuzzy query)

Datarow [] matches = DT. select ("(ID> = 20) and (Title = 'conferencing ') and (yearstr like' % 2007% ')"); string strname = matches [0] ["name"]. tostring ();
 

You can also bind the retrieved new table to the gridview using the following method.

 
Dataview DV = DT. defaultview; DV. rowfilter = "id> = 2"; this. gridview1.datasource = DV; this. gridview1.databind ();
 

 

 

Iii. Merge

Assume that there are 2 datatable: dt1, dt2. Same table structure

This method can be used after dt2 is connected to dt1.

Dt1.merge (dt2 );
 

 

 

4. Paging

 
Pageddatasource PPS = new pageddatasource (); Pam. allowpaging = true; Pam. datasource = dvintegralexpense; Pam. allowpaging = true; Pam. pagesize = pager. pagesize; PPS. currentpageindex = pager. pageindex; rptintegralexpense. datasource = PPS; rptintegralexpense. databind ();
 

 

 

 

V:

When using SQL Server databases, we can easily calculate relevant results through sum, aver, count, and so on. What about dataset (datatable) that has retrieved data? In particular, dataset is obtained through the web service. At this time, there is no way to modify the SELECT statement to obtain these statistics. Can statistics be performed in dataset/datatable? The answer is yes.

In msdn, there is a statistical method recommended by MS, which is to calculate the sum of Data row by row, in fact, there is equal to none (maybe this method is only used for obtaining the subtotal for the DataGrid), because this method uses the itemdatabind event of the DataGrid to accumulate the data, and we manually writeCodeThere is no difference in statistics.

This article introduces a simple method. You can easily obtain the record statistics in the datatable without having to calculate records one by one. This simple method is to call the powerful datatable function compute.

1. Call description (C # is used as an example, the same below ):

 
Public object compute (string strexpression, string strfilter)
 

 

Parameters:

Strexpression: expression string to be calculated. It is basically similar to the statistical expression in SQL Server.

Strfilter: A Statistical filter string. Only records that meet this filter condition are counted.

 

Ii. Call example:

In the following example, assume a product sales table that describes the actual sales records of various promoters in a mall. The fields include name, sex, and 0, 1 is male, birthday, proid, quantity, and price ).

1. count the number of salespersons whose gender is female:

 
Table. Compute ("count (*)", "Sex = 0 ");
 

2. Count all sales staff older than 20

Table. Compute ("count (*)", "birthday <'" + today); // today is the date string of today.
 

 

3. Calculate the average prices of products sold

 
Table. Compute ("aver (price)", "true ");
 

 

4. count the number of products sold with the product code 1:

 
Table. Compute ("sum (Quantity)", "proid = 1 ");
 

 

5. Total sales of all products

To calculate the total sales amount, because the table does not contain the sales amount of a certain promoter of a product, we can obtain it through quantity * price. For example

 
Table. Compute ("sum (quantity * price)", "true ");
 

 

Here is a problem: datatable does not have a strong statistical function in sqlserver, which is incorrect because compute does not have the data function such as sum (quantity * price. What should we do?

For Statistics of such complex data, we can create a new field in the datatable, such as amount, and set the expression of this field to quantity * price, in this way, we can use the statistical function:

 
Table. Compute ("sum (amount)", "true ");