Express Yourself - 30 Oct 2009

asp:Feature

LANGUAGES:C#

ASP.NETVERSIONS: 2.0

Express Yourself

Manipulate Data Using DataColumn Expressions

By Gregory Corbin

DataSets in ASP.NET can be very useful when a user needsto work with blocks of information from a DataSource. Many controls in ASP.NETcan bind to a DataSet. This makes it easy for a developer to present the userwith a piece of information from a database. However, a problem arises when theDataSource does not return all the data in a format that is useful to you.Luckily, ASP.NET allows the developer to manipulate a DataSet by adding newcolumns to it that are evaluated expressions based on existing columns. Thiscan be very helpful when we need to show a calculated total or aggregated datafrom many different columns.

There are several different approaches that can be takenwhen a calculated value is needed in a DataColumn. A developer can choose tomove this task to the database and expect that the data be returned in a mannerthat is required. There are a few problems with this approach. The most obviousproblem is, what if the data is coming from a source to which you do not haveaccess? If the data source is a Web service or an RSS news feed, then thisoption is not possible. Also, if the data source were an XML file, there wouldbe no way to have this file generate a calculated value.

Another approach would be to create a method that woulditerate over all the rows in the DataSet, extract the data needed for thecalculation, and add the results to each row. The problem with doing it thisway is that it requires a lot more coding and if you wanted to change thecalculation, it would require a change in the logical flow of the code.Fortunately, there is another approach that will solve all of these problemsfor us. The DataColumn object has a property named Expression. This property isused to define how a column should display its data. When using this property,the developer has the ability to format, calculate, aggregate, and conditionallydisplay the data by using a built-in set of functions. See Figure 1 for alisting of these functions.

Figure 1: Expressionfunctions.

A limitation of the expression syntax is that it can onlybe used to create new columns. DataColumn expressions cannot be used to modifythe content of an existing column. DataColumn expressions support a full set ofmathematical operators. In addition to these operators, DataColumn expressionssupport wild-card characters, string operators, aggregates, and parent/childrelationships (see Figure 2).

Figure 2: We canwrite an expression as illustrated here.

We are now going to look at an ASP.NET page where we useDataColumn expressions. In this example, we ll explore some of the syntax shownin Figure 2.

The DataSource for this example will be the XML shown inFigure 3. As you can see, this XML defines three DataColumns named PRODUCT, COST,and QUANTITY. We ll use a DataGrid to display this information on our ASP.NETpage. The DataColumn expressions will be used to add columns to the DataSetbefore we bind it to our DataGrid. The columns we add will get their data bymanipulating the original data to produce a new column with the data we want (seeFigure 4).

   Apples

   0.59

   5

   Bananas

   0.99

   2

   Cantalopes

   1.29

   1

Figure 3: TheDataSource for our example.

Figure 4: Manipulate the originaldata to produce a new column.

The code in Figure 4 first shows how to create a DataSetand load it with XML. The DataSet could use any DataSource you want, but forsimplicity we are using an XML file. Next, we get a reference to the DataTable onwhich we want to work. In this case, the DataTable s name is DATA. Now that wehave a reference to our DataTable, we can add columns to it.

The first column we add is named Total. In this column wemultiply the QUANTITY by the COST. This gives us the total cost for each item.However, before we can multiply these two values, we need to convert them tonumeric values. All the data loaded from this XML is a string. We could use anXSD schema to define types within our XML, but that topic is beyond the scope ofthis article. So, we ll use the Convert function to change the type for bothitems from string to decimal. The results for this column will be a value thatis the product of the COST and QUANTITY. The next column will be named SaleDescription. It will be a string concatenation of the PRODUCT and COST and ahard-coded string.

The last column will be a column named Inventory Status.This column will be used to display an indicator of whether we need to reordera product. It does this by using the IIF function to determine if the QUANTITYis below a defined level. The IIF function always determines what data tooutput based on the expression passed into it. If the expression evaluates toTrue, then the second argument of the IIF function is returned (otherwise, weget the third argument of the IIF function returned). The result for thiscolumn is a string that informs the user of the inventory status. The finallines of code define the DataSource and DataMember, and bind them to the DataGrid.When we load this ASP.NET page, we ll see the output shown in Figure 5.

Figure 5: The new columns.

There are several alternate solutions we could havefollowed. We could have produced these same results by using SQL functions inthe database to create a stored procedure, and then simply bound that DataSourceto our DataGrid. Also, we could have used XSLT to manipulate the XML into thedesired format. Both of these approaches are just as acceptable and may bepreferred, based on a project s needs.

DataColumn expressions have been very useful in myexperience. I have used them on many projects. The most common use I find forthem is to calculate totals and concatenate strings together for a nicerdisplay. The sample code accompanying this article demonstrates the simplicityand power of the DataColumn Expression syntax. It shows that manipulating datacan be done in a clean and simple fashion in ASP.NET, and that it doesn t haveto be done by the database. Also, this solution presents a way to get more fromyour XML without the need to modify its contents. To get more details aboutthis syntax, see the Microsoft MSDN documentation.

The sample code accompanying this article is available fordownload.

Gregory Corbin is aSr. Software Engineer for a top healthcare company based in Boston, MA. He has developed software forcorporations such as Fidelity Investments, NASA, Quantum Computer Corporation,and Lycos.com. In his free time, he enjoys digital photo editing, making shortfilms, and woodcrafting. Contact Gregory at mailto:[email protected] withquestions or comments.