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.
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:
Create the DataTable object,
Create the DataColumn objects and add them to the DataTable's Columns
collection, and
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" />
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" />
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
...
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" />
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.
