Asp.net| data to display data in tabular form can offer many benefits. In this article, I'll explain how to calculate totals using the DataGrid, which is often used when working with values.
When discussing DataGrid control, you can often hear people scoff at this method. They often discard it and turn to using third-party tools. In fact, the DataGrid acts as. NET framework, has become a valuable tool in my development toolbox.
What is a total?
Using the DataGrid control in your application 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 application, users can view custom functions such as totals per column, average, and so on. These functions are not standard functions of the DataGrid, and you can write your own code to implement these functions easily.
In this case, I'll use the Northwind sample database that all versions of SQL Server can provide and extract the data from the sequential table. I'll calculate the total value for the cargo bar, which should be displayed consistently in the DataGrid. This is the C # code for this application.
<%@ Import namespace= "System.Data.SqlClient"%>
<%@ Import namespace= "System.Data"%>
<%@ Page language= "C #"%>
<! DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 transitional//en" >
</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 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></HTML>
Perhaps the first thing you notice is that this page does not use the Code-behind feature. All the code is placed in the ASPX file. The import directive required by the page header guarantees the availability of the code required for data interaction. Page event Page_Load calls the Binddata method, which is where you interact with the database. It connects to the database and creates a SqlDataReader object that contains the records returned by the SQL statement. Object SqlDataReader the DataGrid object into the page through the DataSource attribute of the object DataGrid. The DataBind method of the object DataGrid is responsible for loading the data. The HTML of the DataGrid defines the column and its format, including color, font, alignment, and so on.
The DataBind method also maintains a dynamic summation of the data columns from the data source. The following code returns the number of details for a row of data:
Double currentfreight = convert.todouble (Databinder._eval (e.Item.DataItem,
"Freight"));
The line code returns the value obtained by the _eval statement and converts it to the format necessary to keep the dynamic summation. Once this value is returned, it is added to the total variable. Each row in the DataGrid will do this. The following code defines a row:
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 quantity is displayed. The statement is executed when all rows (if the first part of the IF statement is false) are processed, unless the following statement begins:
else if (E.item.itemtype = = Listitemtype.footer)
This statement returns True when the footer is reached. Since we are not interested in headers, we have to determine if this is a footer. In this case, the total value will be displayed in the appropriate column of the DataGrid. You must remember that the column number starts at 0. As a result, we have to get 2 and 3, not columns 3 and column 4. You can do this by overwriting the Text property in the Cells property with the index value of item.
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 it is displayed.
Another way
You can use a different method when you don't need to get the total value of the data. This method calculates the total number of values in a column by using the SQL SUM command. The disadvantage of this approach is that it requires a separate database call, and the results must be stored in a dataset or other similar object.
Page
You might want to know how the DataGrid paging affects the total value. The sample code in this article will show you the sum of the data shown on the screen, so the results will be different for each page (you have to adjust the code to keep the sum of each variable, but this is beyond the scope of this article).
Since you cannot use SqlDataReader to turn the page, the code for this article will not be used for paging. But you can modify the code to use the DataSet object to take advantage of the paging options it provides. The following code changes will complete this work:
SqlCommand comm = new SqlCommand (SSQL, conn);
SqlDataAdapter da = new SqlDataAdapter ();
Da. SelectCommand = comm;
DataSet ds = new DataSet ();
Da. Fill (DS);
Dgnorthwind.datasource = ds;
