Using ASP.NET's DataGrid

By Tony Patton , Builder.com on July 8, 2004

Summary

For rendering and ordering data in tables, ASP.NET's DataGrid can be a powerful tool.

Events

Echelon 2012
June 11 and 12, 2012

University Cultural Centre, National University of Singapore

Startup Asia Jakarta 2012
June 7 and 8, 2012

12th Floor, Annex Building, Wisma Nusantara Complex, Jl. M.H. Thamrin No. 59 Jakarta 10350, Indonesia

MMA Forum Singapore
April 23-25, 2012

Grand Hyatt Singapore

Submit

I talk to a lot of people who sneer when discussing the DataGrid control; they often discard it in favor of third-party alternatives. DataGrid, a core piece of the .NET Framework, is actually a valuable part of my development toolbox.

What is the total?
Utilising the DataGrid control in your applications allows you to present data in a format that's familiar to most users. (The grid format is often compared to a spreadsheet application such as Microsoft Excel.) With this type of application, users are accustomed to viewing custom functionality like column totals, averages, and so forth. While these functions aren't a standard part of the DataGrid, you can add them easily through custom coding.

In our example, I'll use the sample Northwind database that comes with every SQL Server installation, and I'll pull data from the orders table. A total value will be calculated for the freight column; this total should be displayed within the DataGrid for presentation consistency. Here's 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" >
<HTML><HEAD><title>Builder.com DataGrid Totals Example</title>
</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></HTML>

The first thing you may notice is that the page doesn't utilise the code-behind feature; all code is included within the aspx file. The necessary import directives begin the page to make the necessary code available for the database interaction. The page's Page_Load event calls the BindData method, which is where the database interaction occurs. It connects to the database and creates a SqlDataReader object that contains the records returned by the SQL statement. The SqlDataReader object is used to fill the DataGrid object on the page via its DataSource property. The DataGrid's DataBind method fills it with the data. The DataGrid's HTML specifies the columns and their formatting. This includes the colors, font, alignment, and so on.

The DataBind method also maintains a running total of the freight column from the data source. The following line retrieves a specific amount from a row:

double currentFreight = Convert.ToDouble(DataBinder._Eval(e.Item.DataItem,
"Freight"));

This line retrieves the value via the _Eval statement and converts it to the necessary format for maintaining the total. Once it's retrieved, it's added to the total variable. This is performed for each row in the DataGrid. A row is determined with the following line:

if(e.Item.ItemType==ListItemType.Item |
e.Item.ItemType==ListItemType.AlternatingItem)

This statement returns true for every row within the DataGrid. The else portion of the statement determines when to display the total amount. It's triggered when all rows (the first part of the if statement is false) are processed in addition to the start with the header:

else if (e.Item.ItemType == ListItemType.Footer)

It returns true when the footer is reached. Since we don't have an interest in the header, we must determine if it's the footer. At this point, the total value displays in the appropriate column in the DataGrid. You must remember that the column numbering begins at zero. For this reason, we populate columns two and three, and not columns three and four. You can accomplish this by populating the Text property of the Cells property with index value of the Item:

e.Item.Cells[2].Text = "Total:";
e.Item.Cells[3].Text = Convert.ToString(totalFreight);

Notice the total is converted to a string value before displaying.

An alternate approach
While totaling the data on the fly provides the desired results, you can use another approach. This entails utilising SQL to compute the total of column values by way of the SQL SUM statement. The drawback to this approach is that it requires a separate database call, and the results must be stored in a DataSet or comparable object.

Paging
You may wonder how DataGrid paging affects the totals. The example code in this article will display a total for the amounts (freight) displayed on the screen, so the total is different for every page. (You must alter the code to maintain a total for every value, but that's beyond the scope of this article.)

The code in this article will not work for paging since you can't use a SqlDataReader for paging. However, you can change the code to utilise a DataSet object to offer the paging option. The following code changes would make this possible:

SqlCommand comm = new SqlCommand(sSQL, conn);
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = comm;
DataSet ds = new DataSet();
da.Fill(ds);
dgNorthwind.DataSource = ds;

Tony Patton began his professional career as an application developer earning Java, VB, Lotus, and XML certifications to bolster his knowledge.

Talkback

SSSSSSSSSSSSSSSSSS

AAA April 17, 2006

Add your opinion

In order to post a comment, you need to be registered. (Sign In or register below)

E-mail* (Will not be displayed)

Password*

Username*

Retype your password*

Comment

Post your comment

ZDNet Asia Live

Malaysia organizations don't realize severity of cyberattacks http://t.co/PUCv68Rd

22 minutes ago by ALLsecuritySoft on twitter

News: Radio Costa Rica by EnjoyIT 1.0: Radio Costa Rica allows you to listen to a great var... http://t.co/BLzVT5As http://t.co/1Dhcy6ki

22 minutes ago by CostaRica_VIP on twitter

The key for mobile operators is identifying the applications that are popular with subscribers on their network. They can then work partn...

1 hour ago by camcullen on Experience trumps content in apps monetization

Experience trumps content in apps monetization | ZDNet http://t.co/gBXcjbGd

2 hours ago by DennisOosterman on twitter

Experience trumps content in apps monetization - ZDNet Asia News: "What we are doing currently is not to monetiz... http://t.co/S2EZtd8m

2 hours ago by kennyfabre1 on twitter

Malaysia organizations don't realize severity of cyberattacks: "Minister Maximus Johnity Ongkili said at the Sec... http://t.co/bgVlOBvx

4 hours ago by Bug2Hunt on twitter

#security Malaysia organizations don't realize severity of cyberattacks: "Minister Maximus Johnity Ongkili said ... http://t.co/hkFb4zrI

4 hours ago by Wiredsec on twitter

Malaysia organizations don't realize severity of cyberattacks http://t.co/EEEmRM3j via @zdnetasia

4 hours ago by RedDragon1949 on twitter

Malaysia organizations don't realize severity of cyberattacks - ZDNet Asia News http://t.co/YpNMYgb5

4 hours ago by RedDragon1949 on twitter

Malaysia organizations don't realize severity of cyberattacks http://t.co/FFems54Q

4 hours ago by mytech_pro on twitter

China solar cell makers seek Taiwan partnerships http://t.co/p5Hh7kJD

5 hours ago by Export2China on twitter

Big data acquisitions pave way to fast, effective innovation http://t.co/hdiEfBsz via @zdnetasia

5 hours ago by jowoodley on twitter

Integration, focused investments to propel Windows Phone: By Kevin Kwang , ZDNet Asia on May 23, 2012 (2 hours a... http://t.co/E7tsZbHJ

7 hours ago by Easyforexdotcom on twitter

Integration, focused investments to propel Windows Phone http://t.co/u9TqjQ8C

7 hours ago by ashvin_9 on twitter

ZDNet Asia IT Salary Benchmark 2012 http://t.co/rVwYlV7H

7 hours ago by sarwan1991 on twitter

AsiaClassifiedToday. Integration, focused investments to propel Windows Phone - ZDNet Asia: S... http://t.co/47tdjZyG #asia #google #biz

8 hours ago by ChemarieMonica on twitter

So much as we know , MTK6575 extremely integrated frequency1GHz ARM Cortex-A9 processor, the superiority of 3G / HSPA Modem, and help the...

1 day ago by y15822137359 on 5 SaaS adoption speed bumps to avoid

I reckon your view: "CRM is strategy, not software", if a company replicating the approach uses in ERP implementation into CRM, what they...

2 days ago by wykoong on Gartner: Mobile CRM gives better ROI than social

This video will teach you about the Excel fill handle but also provide you with a workook to download... http://www.youtube.com/watch?v=...

3 days ago by TradeBrother on A quick fill handle trick for Microsoft Excel

waiting...

5 days ago by eapete on What should count in a company's market value?

Boy, you've opened a can of worms now.

Wait for the rants & raves.

5 days ago by eapete on What should count in a company's market value?

I was puzzling before this whether to replicate the success formula we executed for a financial institute, and come out with a standard s...

5 days ago by wykoong on Drop the egos, copy ideas, then innovate

ZDNet / Techguide / Database

Using ASP.NET's DataGrid

Summary

Events

Echelon 2012 June 11 and 12, 2012University Cultural Centre, National University of Singapore

Startup Asia Jakarta 2012 June 7 and 8, 201212th Floor, Annex Building, Wisma Nusantara Complex, Jl. M.H. Thamrin No. 59 Jakarta 10350, Indonesia

MMA Forum Singapore April 23-25, 2012Grand Hyatt Singapore

Email

Talkback

Resource Centre Useful content from our premier sponsors

Database TechGuides

ZDNet Asia Live

Keep up with ZDNet Asia

Whitepapers

Downloads

Database News

Inside ZDNet Asia

Sponsored

Services

Echelon 2012
June 11 and 12, 2012

University Cultural Centre, National University of Singapore

Startup Asia Jakarta 2012
June 7 and 8, 2012

12th Floor, Annex Building, Wisma Nusantara Complex, Jl. M.H. Thamrin No. 59 Jakarta 10350, Indonesia

MMA Forum Singapore
April 23-25, 2012

Grand Hyatt Singapore