Computing Values from a DataTableBy Scott Mitchell
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
Expressionproperty of the
DataColumnclass and the DataTable's own
In this short article we'll briefly examine the DataTable,
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.Datanamespace, 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
property that is a set of
DataColumn objects. These
objects specify the name and type of the columns of the DataTable. Additionally, the DataTable
Rows property, which is set of
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
DataColumnobjects and add them to the DataTable's
- Add a number of
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 having string values, and
Age having integer values.
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).
Expression Property to Create Computed Columns
In the previous example we saw how to display the contents of the
sp_Popularitystored 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
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
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
Fill() method has been called, but
before the actual data binding.
Computing Values with the
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.
Compute() method has the following definition:
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:
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
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
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.
In this article we examined two neat features of the DataGrid: the
Compute()method and the
Expressionproperty of the
DataColumnclass. Additionally, we saw how these two features could be used in real-world settings.