ADO.NET 3.5 Cookbook, 2nd Edition

2.5. Adding a Calculated Column to a DataTable

Problem

You need to display a calculated value for each row in a DataTable.

Solution

Add an expression column to the table and display it.

The solution creates a DataTable containing the top five rows from the Sales.SalesOrderDetail table from AdventureWorks. An expression column calculating the extended price for each record is added to the table. The results are output to the console.

The C# code in Program.cs in the project AddCalculatedColumnDataTable is shown in Example 2-5.

Example 2-5. File: Program.cs for AddCalculatedColumnDataTable solution

using System; using System.Data; using System.Data.SqlClient; namespace AddCalculatedColumnDataTable { class Program { static void Main(string[] args) { string sqlConnectString = "Data Source=(local);" + "Integrated security=SSPI;Initial Catalog=AdventureWorks;"; string sqlSelect = "SELECT TOP 5 * FROM Sales.SalesOrderDetail"; // Use a DataAdapter to fill a DataTable SqlDataAdapter da = new SqlDataAdapter(sqlSelect, sqlConnectString); DataTable dt = new DataTable(); da.Fill(dt); // Add an expression column to the table. dt.Columns.Add(new DataColumn("ExtendedPrice", typeof(Decimal), "UnitPrice * (1 - UnitPriceDiscount) * OrderQty")); foreach (DataRow row in dt.Rows) Console.WriteLine("ID = {0}, UnitPrice = {1}, " + "Disc = {2}, Qty = {3}, Total = {4}", row["SalesOrderDetailID"], row["UnitPrice"], row["UnitPriceDiscount"], row["OrderQty"], row["ExtendedPrice"]); Console.WriteLine("\nPress any key ...