Using Datatables in Asp.Net Application

Question

What is considering good practice using datatables in an asp.net applications?

I need to make multiple queries everytime the user clicks a control. Is it better to go directly to the sql server table or load that data in a datatable and use LINQ to get the data. In this case the table has 10 columns and a 3000+ rows.

Answer 1

Use data cache, depending on your application size and complexity you will decide on a distributed system or not:

http://www.25hoursaday.com/weblog/CommentView.aspx?guid=3109dc37-49f8-4249-baf1-56d4c6158321

http://www.infoq.com/news/2007/07/memcached

Answer 2

DataTable is okay, using SqlDataAdapter is slow compared to SqlDataReader. I like to read data into my own custom structures for easy retrieval.

10 columns * 3000 rows is very small and you'd be fine keeping that in memory if it was important data. if you assume 1k per cell, that is only 30k, tiny, and if you have a lot of traffic to the page it will be faster to much faster depending on the speed of the query that retrieves the data from the database.

One thing to keep in mind is you will probably need to think about refreshing your data from time to time, or managing changes to the data. ASP has a Cache object that you can use for this purpose, it allows you set expiration times in various ways.

If the data is subject to change very often and from many different sources, it can be complicated to manage concurrency of changes. When I use a caching strategy I try to use it on non critical data that isn't subject to constant change. This isn't to say it's impossible to cache data that changes a lot, it's just more complicated.

Answer 3

That's really a fairly complex question (without a whole lot of detail here). At the highest level, you're trying to balance the optimization of holding data in memory vs. factors like concurrency and memory utilization. I'd bet if you did a little reading on caching strategies, you'd start to get a sense for how you can weigh these tradeoffs.