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.
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'sColumns
collection, and - Add a number of
DataRow
s
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.
|
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).
|
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.
|
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
|
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.
|
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!