Working with Data Tables in C#

Most of my work I have done, when it comes to do data look up or creating collection, I always used some sort of ICollection. I was able to solve them using Dictionary or List etc., Recently I came across a situation where none of these were able to solve it directly. I was going to create a custom Collection derived off of IDictionary then I came across DataTable (thanks to Bernardo @ Component One). Once I started using in memory DataTable then there is no going back, you get hooked. It is very powerful. With Linq on top of it, you will write code as if you have local SQL table. The funny thing is, it was there all along except since Silverlight did not support DataTable out of the box, you never use it and forgot about it. By the way, even though Silverlight by itself does not support DataTable, Component One does have their implementation of DataTable for Silverlight. So if you need to use Data Table for Silverlight you can look them up.

There are lot of excellent references out there if you haven’t used DataTable at all. Here are some of the references that I used for get me going

http://www.codeproject.com/KB/grid/practicalguidedatagrids2.aspx

http://msdn.microsoft.com/en-us/library/system.data.datatable.aspx

you got the idea, it is there and use it. I am not going to explain how and what since it has been documented multiple times by talented people. I want to add couple things, I did when I start using it.

1. Please make sure you have primary key defined in the data table otherwise when you get to large data table, your search will slow down drastically. I had one table, where my search took more than a minute without primary key, by adding primary key, my select dropped to seconds.

2. When you do query against the table, it returns collection of DataRow, which by default does not support ForEach in linq, so I added a extension method like the following

public static void ForEach<T>(this IEnumerable<T> sequence, Action<T> action)
{
   foreach (T item in sequence) action(item);
}
 

3. You could avoid the extension by converting the DataRow collection to List and then you can use ForEach in the List type, but I would not recommend that approach since, it is creating additional memory.  Most of the times when you use any of the linq comment, you normally use single statement. In case if you want to use more than one statement, you can do something like this

collection.ForEach( item => { method1(); method2(); });

I update this blog post I learn more about DataTables.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s