How To Use DataTable and improve performance

There are some features in the System.Data.DataTable 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 the 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));

Imaging that there is 100 rows in that DataTable and you now want to calculate 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.
Auto increment

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 exist 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 easy 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.
Reactions

Post a Comment

1 Comments

  1. Thanks for this code to create a data table in asp.net.. Now finally i complete my assignment...

    ReplyDelete

Please do not post any spam link in the comment box😊

Emoji
(y)
:)
:(
hihi
:-)
:D
=D
:-d
;(
;-(
@-)
:P
:o
:>)
(o)
:p
(p)
:-s
(m)
8-)
:-t
:-b
b-(
:-#
=p~
x-)
(k)

Close Menu