When you think ASP, think...
Recent Articles
All Articles
ASP.NET Articles
ASPFAQs.com
Related Web Technologies
User Tips!
Coding Tips
Search

Sections:
Book Reviews
•Sample Chapters
JavaScript Tutorials
MSDN Communities Hub
Official Docs
Security
Stump the SQL Guru!
Web Hosts
XML
Information:
Advertise
Feedback
Author an Article

ASP ASP.NET ASP FAQs Feedback
 
Print this Page!
Published: Wednesday, August 20, 2003

Computing Values from a DataTable

By Scott Mitchell

Introduction


When checking out the blogs at weblogs.asp.net, I found Darren Neimke's interesting entry for today on computing aggregated data in a DataTable. In his blog entry, Darren mentions two not well-known features of the DataTable: the Expression property of the DataColumn class and the DataTable's own Compute() method.

- continued -

In this short article we'll briefly examine the DataTable, the DataColumn's Expression property, and the Compute() method. Following this, we'll dive into a couple short examples illustrating the utility of this property and method.

A Quick Overview of DataTables


The DataTable class can be found in the System.Data namespace, and provides an in-memory representation for a single table. If you have used the DataSet before, then you have (implicitly) used the DataTable, as a DataSet is a set of DataTables.

Take a moment to think about what a table is comprised of: a set of predefined columns and a variable number of rows. Not surprisingly, the DataTable has a Columns property that is a set of DataColumn objects. These DataColumn objects specify the name and type of the columns of the DataTable. Additionally, the DataTable has a Rows property, which is set of DataRow objects. Each DataRow object has a value for each of the DataTable's columns.

DataTables can be constructed statically, with pre-defined, hard-coded data, or can be populated from a database query. Oftentimes, when prototyping a Web application or testing some functionality, it suffices to create a DataTable and populate it with static data. To accomplish this, you need to perform the following steps:

  1. Create the DataTable object,
  2. Create the DataColumn objects and add them to the DataTable's Columns collection, and
  3. Add a number of DataRows

An example of creating a simple DataTable and populating it with three rows can be seen below. Note that the DataTable has two columns: Name and Age, with Name having string values, and Age having integer values. 

<@ Import Namespace="System.Data" >
<script language="VB" runat="server">
  Sub Page_Load(sender as Object, e as EventArgs)
    If Not Page.IsPostBack then
      'Create the DataTable
      Dim dt as New DataTable()
      
      'Create the columns
      Dim dcName as New DataColumn("Name", GetType(String))
      Dim dcAge as New DataColumn("Age", GetType(Integer))
      
      'Add the columns to the DataTable's Columns collection
      dt.Columns.Add(dcName)
      dt.Columns.Add(dcAge)
      
      'Add some rows
      Dim dr as DataRow
      dr = dt.NewRow()
      dr("Name") = "Scott"
      dr("Age") = 25
      dt.Rows.Add(dr)
      
      dr = dt.NewRow()
      dr("Name") = "Jisun"
      dr("Age") = 24
      dt.Rows.Add(dr)
      
      dr = dt.NewRow()
      dr("Name") = "Sam"
      dr("Age") = 5
      dt.Rows.Add(dr)
      
      
      'Bind the DataTable to the DataGrid
      dgPeople.DataSource = dt
      dgPeople.DataBind()
    End If      
  End Sub
</script>

<asp:DataGrid runat="server" id="dgPeople"
	HeaderStyle-BackColor="LightGray"
	HeaderStyle-Font-Bold="True" />
[View a Live Demo!]

Note that the DataTable can be bound to a data Web control just like a DataSet or DataReader. In fact, when binding a DataSet to a data Web control, in actuality, the DataSet's default DataTable is what is actually being bound to the control.

DataTables can also be populated via a SQL query using the same syntax one would use to populate a DataSet. The following code sample and live demo show how to populate a DataTable from a SQL query (specifically, a stored procedure). 

<%@ Import Namespace="System.Data" %>
<script language="VB" runat="server">
  Sub Page_Load(sender as Object, e as EventArgs)
    If Not Page.IsPostBack then
      'Create the DataTable
      Dim dt as New DataTable()
     
      Dim myConnection as New SqlConnection(Connection String)
    
      Const strSQL as String = "sp_Popularity"
      Dim myCommand as New SqlCommand(strSQL, myConnection)

      Dim myAdapter as New SqlDataAdapter(myCommand)
      myAdapter.Fill(dt)
      
      
      'Bind the DataTable to the DataGrid
      dgPeople.DataSource = dt
      dgPeople.DataBind()
    End If      
  End Sub
</script>

<asp:DataGrid runat="server" id="dgPeople"
	HeaderStyle-BackColor="LightGray"
	HeaderStyle-Font-Bold="True" />
[View a Live Demo!]

Using the Expression Property to Create Computed Columns


In the previous example we saw how to display the contents of the sp_Popularity stored procedure in a DataGrid using a DataTable. This stored procedure returns the top 10 most viewed FAQs at ASPFAQs.com, including the number of views for each of those FAQs. Now, imagine that we wanted to also display a column in our DataGrid that showed the estimated number of views in a month, and perhaps we determined that this number was 10% more than the current number.

Adding such a computed column would be a breeze if we were querying the database with a specific SQL query. That is, if, instead of using the sp_Popularity stored procedure, we were using the query: 

SELECT TOP 10 FAQID, ViewCount, ...
FROM tblFAQs
ORDER BY ViewCount DESC

We could include this computed column by merely adding an extra column to the column list in the SELECT clause like so: 

SELECT TOP 10 FAQID, ViewCount, ..., FutureClicks = ViewCount * 1.1
FROM tblFAQs
ORDER BY ViewCount DESC

Since we are working with a stored procedure, however, we must come up with our own way to compute this information. We can tackle this task by adding a computed column to the DataTable after we have populated the DataTable from the database. The syntax for this is simple: we simply create a new DataColumn and then set its Expression property to the value we wish to compute for each row, in this case, ViewCount * 1.1. We then add this column to the DataTable. Essentially, we just need to add these three lines of code to the previous example after the SqlDataAdapter's Fill() method has been called, but before the actual data binding. 

    ...
    Dim myAdapter as New SqlDataAdapter(myCommand)
    myAdapter.Fill(dt)

    Dim dcFutureViews as New DataColumn("Future Views", GetType(Integer))
    dcFutureViews.Expression = "ViewCount * 1.1"
    dt.Columns.Add(dcFutureViews)  
      
    'Bind the DataTable to the DataGrid
    dgPeople.DataSource = dt
    ...
[View a Live Demo!]

For more information on the Expression property be sure to check out the technical documentation and the article Express Yourself with Expression-Based Columns.

Computing Values with the Compute() Method


The DataTable contains a Compute() method that computes a specified function on rows that pass a specified filter. The Compute() method returns a scalar value - the value of the computation performed - so this method is useful for generating summary statistics or statistics based on user input.

The Compute() method has the following definition:

Function Compute(expression as String, filter as String) as Object

For example, given the DataTable from the previous live demos we examined in this article, we could compute the total number of views for all FAQs that were added after a specified date using the following syntax:

Dim count as Integer
count = Convert.ToInt32(dt.Compute("SUM(ViewCount)", "DateEntered > someDate"))

This Compute() function is especially nice because it performs a computation on a filtered set of rows. Therefore, you can allow for interesting aggregated statistics based on user-entered queries. In Darren's blog entry he showed how to use Compute() to let the user enter a search string into a TextBox and the number of matches would be displayed.

To get some practice with the Compute() method, let's create a Web page that displays the list of the most popular FAQs and allows the user to view the average number of views for all popular FAQs that were created after a certain date. To accomplish this we need a TextBox Web control for the user to enter a date, and then we need an event handler for the Button's Click event.

Also, upon postback we'll need to repopulate the DataTable (unless you decide to cache it). To facilitate this, we'll create a simple function called GetData() that populates the DataTable with the sp_Popularity stored procedure. The germane code for the page can be seen below; check out the live demo for complete source code. 

<script language="VB" runat="server">
  ...
  
  Sub DisplayStats(sender as Object, e as EventArgs)
    'Compute the avg.
    Dim dt as DataTable = GetData()
    
    Dim result as Object = dt.Compute("AVG(ViewCount)", "DateEntered > '" & FAQDate.Text & "'")
    Dim avg as Single = 0.0
    
    If Not result.Equals(DBNull.Value) then
      avg = Convert.ToSingle(result)
    End If
    
    Results.Text = "Average Views: " & avg
  End Sub

</script>

<form runat="server">
  Compute the average number of views for FAQs after a specified date:
  <asp:TextBox runat="server" id="FAQDate" />
  <asp:Button Text="Compute" runat="server" OnClick="DisplayStats" />
  <br />
  <asp:Label id="Results" runat="server" />
</form>

<p>
<asp:DataGrid runat="server" id="dgPeople"
	HeaderStyle-BackColor="LightGray"
	HeaderStyle-Font-Bold="True" />
[View the Live Demo!]

For more information on Compute(), check out the technical documentation. For an example of Compute() in C#, check out this code download.

Conclusion


In this article we examined two neat features of the DataGrid: the Compute() method and the Expression property of the DataColumn class. Additionally, we saw how these two features could be used in real-world settings.

Happy Programming!

  • By Scott Mitchell


    • ASP.NET [1.x] [2.0] | ASPMessageboard.com | ASPFAQs.com | Advertise | Feedback | Author an Article