Silverlight study NOTE 2: Implementation of paging and sorting on the DataGrid Server

Let's take a look at the effect. The artist was not good. He smiled. Haha.
The Silverlight DataGrid does not provide the paging function, and its sorting is also performed on the content of the current page. These two functions must be included in actual projects. I searched the internet. It seems that no article has introduced how to implement these two common functions. It seems that I only need to do it myself. Haha.

Next we will implement these functions step by step. (The following uses the northwind database as an example)
Step 1: Implement Paging first.

To retrieve data by page, you must first add a method to obtain data by page on WCF. Run the code first.
1 [operationcontract]
2 public list <Products> getproductspaging (INT start, int limit, string sort, string Dir, out int totalpage)
3 {
4 northwindlinqdatacontext DB = new northwindlinqdatacontext ();
5 string SQL;
6 SQL = "select count (*) as C from products ";
7 int totalcount = dB. executequery <int> (SQL). Single ();
8 totalpage = convert. toint32 (math. Ceiling (double) totalcount/(double) limit ));
9 SQL = "with a as (select *, row_number () over (order by {0}) as rownumber from products) select * from a where rownumber between {1} and {2 }";
10 SQL = string. Format (SQL, sort + "" + Dir, start, start + limit-1 );
11 var query = dB. executequery <Products> (SQL );
12 Return query. tolist ();
13}
14

The code is relatively simple. If the paging method of sql2005 is used, it will not be explained. The efficiency of this paging method is not the focus of this study.

I used linqtosql to implement the paging function for database access. However, after a long time, I ended up failing to implement the paging function. The main problem is that I cannot implement dynamic sorting in LINQ, no suitable method is found on the Internet. You can only write n ifelse for sorting, just like the following code:

1 var products = from P in dB. Products
2 select P;
3 if (DIR = "productname ")
4 products = products. orderby (P => P. productname );
5 else if (DIR = "productid ")
6 Products = products. orderby (P => P. productid );
7 .......

 

In this case, although the function can be implemented, it is actually a bit mentally retarded. To write a lot of code, it is really better to write SQL. In the end, I still did not solve this problem, so I had to write SQL statements. If you have any good solutions, please leave a message to me. Thank you!

OK. The basic functions of this page are completed. The DataGrid can get the data of the page. However, this DataGrid is different from that on the Web. If there is no pager, you only need to manually write a pager control.

Step 2: Implement pager.

An old method before you start. First, I went online to find the ready-made products. Well, it's good. This time I finally got some results. There are many such examples on the Internet. I found a pager on silverlight.net. I'll talk about it first. Haha.
For more information, see http://www.13sides.com/page/a-generic-pager-control.aspx.
Now the code is integrated, OK, and the correct page is displayed. Haha. Next, we will implement sorting.

Step 3: Implement sorting.

To sort the columnheader, click columnheader. In the DataGrid, find the columnheader Click Event, dizzy, no. No, how does Microsoft implement it? Even though Silverlight is 2.0, it still seems like a semi-finished product. There are no such common events. Semi-finished products. Continue to implement them manually.
The old method is to find a ready-made method on the Internet. Two solutions are provided: Change the header to a template, and then put the button in it. There is also another method that someone mentioned to use the hittest method of the DataGrid. Haha, the first one is a little troublesome. Every header should be changed. The second one looks cool, so I chose the second one.
After testing, how does hittest become inaccessible and restricted by the protection level. Check msdn again. It turns out to be visualtreehelper. findelementsinhostcoordinates in 2.0. OK, the code below:
Write in the mouseleftbuttondown of the DataGrid. There is a strange problem here. Clicking columnheader does not trigger the mouseleftbuttonup event. I really don't understand.

1 private void dgdata_mouseleftbuttondown (Object sender, mousebuttoneventargs E)
2 {
3 // get the clicked columnheader
4 var u = from element in visualtreehelper. findelementsinhostcoordinates (E. getposition (null), dgdata)
5 where element is datagridcolumnheader
6 select element;
7
8 If (U. Count () = 1)
9 {
10 datagridcolumnheader header = (datagridcolumnheader) U. Single ();
11 // The reason for saving this header is described below.
12 sortheader = header;
13 // obtain the field name.
14 string headername = header. content. tostring ();
15 // compare it with the current sorting field to determine whether it should be DESC or ASC
16 string newsort = header. content. tostring ();
17 if (newsort = sort)
18 dir = "DESC "? "ASC": "DESC ";
19 else
20 dir = "ASC ";
21 sort = newsort;
22 // bind data
23 bindgrid ();
24 E. Handled = true;
25}
26 else
27 E. Handled = false;
28}

Well, the entire paging and sorting function is basically complete.
Well, after carefully using the program, we found that after sorting, The DataGrid would help us arrange our data intelligently, and the DataGrid supports column dragging by default, when we drag a column, the sorting will be triggered, resulting in a different result from the user's expectation. How can this problem be solved,
Set canusersortcolumns = "false" and canuserreordercolumns = "false" directly in the DataGrid ". You can solve the problem.

After testing, we found that there was no small arrow indicating the sorting direction on columnheader after sorting. After checking, when msdn introduced the DataGrid style and template, we mentioned that the statuses of the datagridcolumnheader include sortascending and sortdescending. OK, and then we wrote a method to set the header status. In the above mouseleftbuttondown, we saved the sorted headers to use them here. The Code is as follows:

1 private void setcolumnsortstate ()
2 {
3 if (DIR = "ASC ")
4 visualstatemanager. gotostate (sortheader, "sortascending", false );
5 else
6 visualstatemanager. gotostate (sortheader, "sortdescending", false );
7}

It is very simple, but there is a strange problem, that is, after sorting, setting the header status cannot be saved, move the mouse, the small arrow will disappear, I do not understand the specific cause. I am not quite clear about visualstatemanager. if the gotostate parameter sets whether the header state is associated with the sorting function of the DataGrid, the small arrow of the sorting will automatically disappear, or this method should not be used here. I have not considered this issue for the time being. In order to make the sorting arrow display correctly, I have to call setcolumnsortstate () in both the grid mousemove and layoutupdated events (), used to ensure that the arrows are correctly displayed.
So far, all the paging and sorting functions have been completed. In addition to the imperfect processing shown by the Small arrow, there is no big problem at all. I will solve this problem later.

In addition, we wish you a Merry Christmas !!!

 

 

Supplement:

The sorting status is modified again. See:

Silverlight study note 2 (continued)