Displaying data in a table can bring many benefits. In this article, I will explain how to use the DataGrid to calculate the total, which is often used when processing values.
|
|
When discussing DataGrid control, you can often hear other people laugh at this method. They often abandon it and turn to third-party tools. As a matter of fact, as the core part of the. NET Framework, DataGrid has become a very valuable tool in my development toolbox.
What is total?Using DataGrid Control in applications allows you to publish data in a format that is familiar to most users (raster formats are often used in spreadsheet applications such as Microsoft Excel ). With this type of applications, you can view custom functions such as the total and average values of each column according to your habits. These functions are not the standard functions of the DataGrid. You can compile your own code to easily implement these functions.
In this example, I will use the northwind sample database available for all SQL Server versions and extract data from the sequence table. I will calculate the total value for the cargo bar, which should be displayed in the DataGrid in a consistent manner. Here is the C # code of this application.
<% @ Import namespace = "system. Data. sqlclient" %>
<% @ Import namespace = "system. Data" %>
<% @ Page Language = "C #" %>
<! Doctype HTML public "-// W3C // dtd html 4.0 transitional // en">
<HTML>
</Head>
<Body ms_positioning = "gridlayout">
Double totalfreight = 0;
Private void page_load (Object sender, system. eventargs e ){
If (! Page. ispostback ){
Binddata ();
}}
Private void binddata (){
Const string sconn;
Sconn = "Server = (local); initial catalog = northwind; uid = ctester; Pwd = password ";
Try {
Sqlconnection conn = new sqlconnection (sconn );
Conn. open ();
String ssql = "select top 10 orderid, freight, shipname, shipcountry from
Orders ";
Sqlcommand comm = new sqlcommand (ssql, Conn );
Sqldatareader DR = comm. executereader ();
Dgnorthwind. datasource = Dr;
Dgnorthwind. databind ();
} Catch (exception e ){
Console. writeline (E. tostring ());
}}
Private void dototal (Object sender, datagriditemeventargs e ){
If (E. Item. itemtype = listitemtype. Item | E. Item. itemtype =
Listitemtype. alternatingitem ){
Double currentfreight = convert. todouble (databinder. _ eval (E. Item. dataitem,
"Freight "));
Totalfreight + = currentfreight;
} Else if (E. Item. itemtype = listitemtype. footer ){
E. Item. cells [2]. Text = "Total :";
E. Item. cells [3]. Text = convert. tostring (totalfreight );
}}
</SCRIPT>
<Form ID = "frmdatagridtotals" method = "Post" runat = "server">
<Asp: DataGrid id = "dgnorthwind"
Style = "Z-INDEX: 101; left: 24px; position: absolute; top: 32px"
Runat = "server" Height = "320px" width = "496px"
Autogeneratecolumns = "false"
Onfiltered = "dototal"
Showfooter = "true" cellpadding = "4" cellspacing = "0"
Borderstyle = "solid" borderwidth = "1" gridlines = "NONE"
Bordercolor = "black"
Itemstyle-font-name = "verdana"
Itemstyle-font-size = "9pt"
Headerstyle-font-name = "verdana"
Headerstyle-font-size = "10pt"
Headerstyle-font-bold = "true"
Headerstyle-forecolor = "white"
Headerstyle-backcolor = "gray"
Footerstyle-font-name = "verdana"
Footerstyle-font-size = "10pt"
Footerstyle-font-bold = "true"
Footerstyle-forecolor = "red"
Footerstyle-backcolor = "gray">
<Columns>
<Asp: boundcolumn datafield = "orderid" headertext = "#" itemstyle-width = "10%"
Headerstyle-horizontalalign = "center"/>
<Asp: boundcolumn datafield = "shipname" headertext = "customer" itemstyle
-Width = "50%"/>
<Asp: boundcolumn datafield = "shipcountry" headertext = "country" itemstyle
-Width = "20%"/>
<Asp: boundcolumn datafield = "freight" headertext = "freight" itemstyle-width = "20%"
/>
</Columns> </ASP: DataGrid>
</Form> </body>
First, you may notice that this page does not use the code-behind feature. All codes are stored in the aspx file. The required import commands at the page header ensure the availability of the Code required for data interaction. The page event page_load calls the binddata method, which is exactly where it interacts with the database. It connects to the database and creates the sqldatareader object, which contains records returned by SQL statements. The object sqldatareader puts the DataGrid object into the page through the datasource attribute of the object DataGrid. The databind method of the object DataGrid is used to load data. The html of the DataGrid defines the column and its format, including color, Font, and alignment.
|
|
The databind method also maintains a dynamic sum of the Data columns from the data source. The following code returns the detailed number of data rows:
Double currentfreight = convert. todouble (databinder. _ eval (E. Item. dataitem,
"Freight "));
This line of code returns the value obtained by the _ eval statement and converts it to the format required to maintain dynamic summation. Once this value is returned, it will be added to the total variable. This operation is performed on each row in the DataGrid. The following code defines a line:
If (E. Item. itemtype = listitemtype. Item |
E. Item. itemtype = listitemtype. alternatingitem)
This statement returns true for each row in the DataGrid. The other part of the statement determines when the total number is displayed. When all rows (the first part of the IF statement is false) are processed, the statement is executed unless it starts with the following statement:
Else if (E. Item. itemtype = listitemtype. footer)
When the footer is reached, this statement returns true. Since we are not interested in the header, we must determine whether this is the footer. In this case, the total value is displayed in the appropriate column of the DataGrid. You must remember that the column number starts from 0. Therefore, we have to output column 2 and column 3, instead of column 3 and column 4. You can use the item index value to overwrite the text attribute in the cells attribute.
E. Item. cells [2]. Text = "Total :";
E. Item. cells [3]. Text = convert. tostring (totalfreight );
Note that the total value is converted to a string value before display.
Another methodWhen you do not need to obtain the total value of the data, you can use another method. This method uses the SQL sum command to calculate the total number of values in the column. The disadvantage of this method is that it requires a separate database call and the results must be stored in dataset or other similar objects.
FlipYou may want to know how the page flip of the DataGrid affects the total value. The sample code in this article will show the sum of the data shown on the screen, so the results will be different for each page (you must adjust the code to maintain the sum of each variable, but this is beyond the scope of this article ).
Because you cannot use sqldatareader to flip pages, the code in this article cannot be used to flip pages. However, you can modify the code to use the DataSet object to take advantage of its paging options. The following code changes to complete this task:
Sqlcommand comm = new sqlcommand (ssql, Conn );
Sqldataadapter da = new sqldataadapter ();
Da. selectcommand = comm;
Dataset DS = new dataset ();
Da. Fill (DS );
Dgnorthwind. datasource = Ds;
Start building with 50+ products and up to 12 months usage for Elastic Compute Service
1 on 1 presale consultation
24/7 Technical Support 6 Free Tickets per Quarter Faster Response
Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.
A comprehensive suite of global cloud computing services to power your business
Payment Methods We Support
