Computing aggregated data in a DataTable
The other day I was struggling to come to terms with how to perform aggregate queries on a DataTable. I was able to create calculated columns to display calculated data but, I couldn’t work out how to return aggregated data from a query over a DataTable.
Well that all changed today while reading Greg Low’s notes from the recent Australian Tech-Ed. Basically you just use the Compute method of the DataTable – here’s a little demo that will run a LIKE query over a list of names (using the characters that you enter in the textbox followed by a wildcard):
// Compute documentation
http://msdn.microsoft.com/library/en-us/cpref/html/frlrfSystemDataDataTableClassComputeTopic.asp?frame=true
// Expression Syntax
http://msdn.microsoft.com/library/en-us/cpref/html/frlrfsystemdatadatacolumnclassexpressiontopic.asp?frame=true
// WebForm
<form id="Form1" method="post" runat="server">
<asp:DataGrid id="namesGrid" runat="server" />
<P>
<HR width="100%" SIZE="1">
<asp:TextBox id="entryTextBox" runat="server"></asp:TextBox>
<asp:Button id="getResultsButton" runat="server" Text="Get Count" />
</P>
<P>
<asp:Label id="resultLabel" runat="server"></asp:Label>
</P>
</form>
// Codebehind
private void Page_Load(object sender, System.EventArgs e)
{
if(!IsPostBack)
{
DataTable dt = GetData() ;
namesGrid.DataSource = dt.DefaultView ;
namesGrid.DataBind() ;
}
}
private DataTable GetData()
{
DataTable dt = new DataTable("test") ;
DataColumn dc = new DataColumn("Name", typeof(string)) ;
dt.Columns.Add(dc) ;
string[] names = new string[] {"darren", "peter", "robin", "phil", "wayne", "rob", "doug"} ;
for(int i = 0; i
{
DataRow dr = dt.NewRow() ;
dr["Name"] = names[i] ;
dt.Rows.Add(dr) ;
}
return dt ;
}
private void getResultsButton_Click(object sender, System.EventArgs e)
{
DataTable dt = GetData() ;
namesGrid.DataSource = dt.DefaultView ;
namesGrid.DataBind() ;
int countOfName = (int) dt.Compute("Count(Name)", "Name Like '" + entryTextBox.Text + "*'") ;
resultLabel.Text = countOfName.ToString() + " results match that query." ;
}