There are some features in the System.Data.DataTable the class that a lot of developers don’t utilize. I base that statement on different code samples I’ve seen on blogs and article bases during the last couple of years. Some of these features can improve performance. Calculated columns
First of all, I’ll create a DataTable manually, even though it is more likely to be
created from querying a database.
DataTable dt = new DataTable();
dt.Columns.Add("Name", typeof(string));
dt.Columns.Add("Price", typeof(double));
dt.Columns.Add("ItemsInStock", typeof(double));
Imagine that there are 100 rows in that DataTable and you now want to calculate the total price of all item currently in stock. The calculation is Price*ItemsInStock.
What I see in a lot of code samples is that this column is calculated in the database by a SQL statement like this:
“SELECT name, price, itemsinstock, (price*itemsinstock) AS stockprice FROM products”
The overhead in letting the database do the calculation is not that much in this particular example, because it is a simple multiplication of two rows. It could
easily be more complicated than this example. The thing is, that .NET performs these kinds of calculation much more efficient than a database and that’s why we would like .NET to do them.
The DataTable class supports on-the-fly calculated columns and they are perfect to use in the example. Just add another column to the DataTable and give it
a calculation formula.
dt.Columns.Add("StockPrice", typeof(double), "Price*ItemsInStock");
The calculation expression (“Price*ItemsInStock”) can also use predefined functions
like an if-statement.
"IIF(ItemsInStock = 0, 100, PricePrice*ItemsInStock)"
There a many different functions to use in the calculation expression.Autoincrement
Let’s say you want to bind the DataTable to a DataGrid in an ASP.NET page and that you want a column to display the row number. This can be done by adding a column to the DataTable that has enabled the AutoIncrement property.
DataColumn col = new DataColumn("#", typeof(int));
col.AutoIncrement = true;
col.AutoIncrementSeed = 1;
dt.Columns.Add(col);
Now you have a column named “#” that contains the row number. Querying the DataTable You can query a DataTable in different ways in order to find the row you need.
If you want all the rows in the DataTable that matches a search expression then you
would use the Select method.
DataRow[] rows = dt.Select("Price > 159");
The Select method returns a DataRow array you can loop through like you normally
would loop through all the rows in the DataTable.
foreach (DataRow row in rows)
{
DoSomeThing();
}
If you just want a single row based on the DataTable’s primary key, then you have
to let the DataTable know which of the columns is the primary key.
dt.PrimaryKey = new DataColumn[] { dt.Columns["#"]};
When you have defined the DataTable’s primary key, you can now query directly for
that key and get the whole row returned by using the Find method.
DataRow oneRow = dt.Rows.Find("19");
This method is faster than the Select method. If there is no row with the primary
key value of “19”, the Find method returns null. So, before you use the returned
DataRow, you probably want to check if the row exists first.
if (oneRow != null)
{
DoSomething();
}
Column totals
You decide to add totals to the footer row of the DataGrid and therefore needs to
sum the integer type columns. You can do that very easily with the Compute method.
dt.Compute("sum(price)", null)
Or, put a filter on
dt.Compute("sum(price)", "price > 40")
The DataTable class is very powerful and can improve the performance by removing calculations to .NET instead of doing them on the database. The different ways to
query the rows are also very impressive and flexible and that makes the DataTable a serious in-memory database.