Express Yourself - 30 Oct 2009

Manipulate Data Using DataColumn Expressions

asp:Feature

LANGUAGES: C#

ASP.NET VERSIONS: 2.0

 

Express Yourself

Manipulate Data Using DataColumn Expressions

 

By Gregory Corbin

 

DataSets in ASP.NET can be very useful when a user needs to work with blocks of information from a DataSource. Many controls in ASP.NET can bind to a DataSet. This makes it easy for a developer to present the user with a piece of information from a database. However, a problem arises when the DataSource 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 new columns to it that are evaluated expressions based on existing columns. This can be very helpful when we need to show a calculated total or aggregated data from many different columns.

 

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

 

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

 

Function

Description

Syntax

CONVERT

Used to convert one type to another.

Convert(expression,type)

LEN

Used to get the length of the string.

Len(expression)

ISNULL

Used to check if an expression is null. If null, then it returns secondaryVal.

IsNull(expression,secondaryVal)

IIF

Evaluates a logical expression and returns the first if it evaluates to true; otherwise, it returns the second value.

IIF(expression, firstval, secondval)

TRIM

Used to remove all leading and trailing blank characters.

Trim(expression)

SUBSTRING

Used to get a part of an expression.

Substring(expression, start, length)

Figure 1: Expression functions.

 

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

 

Type of Expression

Example

Multiplication

myDataColumn.Expression= COST * QUANTITY

Pattern Matching

myDataColumn.Expression= PRODUCT like *fruit

Data Relationships

myDataColumn.Expression= Parent.BRANDNAME

Aggregates

myDataColumn.Expression= Avg(COST)

Function

myDataColumn.Expression= IIF(COST>0.99, OnSale , NoSale )

Figure 2: We can write an expression as illustrated here.

 

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

 

The DataSource for this example will be the XML shown in Figure 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.NET page. The DataColumn expressions will be used to add columns to the DataSet before we bind it to our DataGrid. The columns we add will get their data by manipulating the original data to produce a new column with the data we want (see Figure 4).

 

 

   Apples

   0.59

   5

 

 

   Bananas

   0.99

   2

 

 

   Cantalopes

   1.29

   1

  

Figure 3: The DataSource for our example.

 


Figure 4: Manipulate the original data to produce a new column.

 

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

 

The first column we add is named Total. In this column we multiply 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 to numeric values. All the data loaded from this XML is a string. We could use an XSD schema to define types within our XML, but that topic is beyond the scope of this article. So, we ll use the Convert function to change the type for both items from string to decimal. The results for this column will be a value that is the product of the COST and QUANTITY. The next column will be named Sale Description. It will be a string concatenation of the PRODUCT and COST and a hard-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 reorder a product. It does this by using the IIF function to determine if the QUANTITY is below a defined level. The IIF function always determines what data to output based on the expression passed into it. If the expression evaluates to True, then the second argument of the IIF function is returned (otherwise, we get the third argument of the IIF function returned). The result for this column is a string that informs the user of the inventory status. The final lines 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 have followed. We could have produced these same results by using SQL functions in the database to create a stored procedure, and then simply bound that DataSource to our DataGrid. Also, we could have used XSLT to manipulate the XML into the desired format. Both of these approaches are just as acceptable and may be preferred, based on a project s needs.

 

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

 

The sample code accompanying this article is available for download.

 

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

 

 

 

Hide comments

Comments

  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.
Publish