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:

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