0

I have a very simple question to ask:

I have a C# webpage which has a treeview on the left hand side and nine text boxes on the right hand side.

Tree view is populated from thousands of rows of a sql table that has 9 columns.

The aim: On click of a tree view item, update the nine text boxes and show each of the column's value.

What is the best way to do it? Should I fetch all of the data on page load in a temporary data table and query the C# data table on click of a tree view? Or should I send a select statement to the sql table on click of the tree view item? Or is there a better way?

This solution will be used by over 15 users and performance is a key factor.

Thanks,

FM

Jon Seigel
  • 12,251
  • 8
  • 58
  • 92
InfoLearner
  • 14,952
  • 20
  • 76
  • 124

2 Answers2

1

I would use the Server Cache to store the DataTable.

The Treeview is the same for every user and the cache holds application-wide references on objects like a DataTable.

add:

Cache["MyTreeviewDataTable"] = MyTreeviewDataTable;

retrieve:

DataTable MyTreeviewDataTable = (DataTable)Cache["MyTreeviewDataTable"];

Update: You should also have a look at the SqlDependency and SqlCachDependency Classes to notify when data changes.

Query Notifications in SQL Server (ADO.NET)

Tim Schmelter
  • 450,073
  • 74
  • 686
  • 939
  • Ok, thanks. It means its better to create a memory object rather than executing sql command each time a user clicks on an item. – InfoLearner Nov 25 '10 at 23:35
  • At any rate! The cache fits perfectly your requirement. It is created only once at application start and lasts till all sessions are destroyed (or iis is restarted). You only have to ensure that you modify also the Datatable in memory when you make changes to the TreeView. You can put it f.e. into a property of your MasterPage or wherever you have access from all pages you need to use the TreeView. You can precheck if it is NULL and then load it from Database. – Tim Schmelter Nov 26 '10 at 07:54
  • If the user updates a row and submits the change to the database then all of the other users will be updating stale data, which really wouldn't be useful. In that case, is executing sql statement on each tree view item select a better choice? – InfoLearner Nov 26 '10 at 10:37
  • They won't have stale data, because they all use the same Datatable. So, if anybody modifies the Treeview, you must change the DataTable in memory first(update,delete,insert) and then update the database via Dataadapter. – Tim Schmelter Nov 26 '10 at 11:21
  • ok thanks. that cleared my understanding. Great. If it was a windows application and the requirements were same, what would you recommend in that case? – InfoLearner Nov 26 '10 at 11:27
  • Have a look at this SqlDependency Video: http://windowsclient.net/learn/video.aspx?v=27881 Btw,its also useful for ASP.Net-caching scenarios. Here is the MSDN-Link: http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldependency.aspx – Tim Schmelter Nov 26 '10 at 11:34
  • That's fine. I understood that. SqlDependency will just trigger an event that I can catch to update the rows but how will I create a shared DataTable in a windows application because for me to use SqlDependency, i will have to have an open sql connection? – InfoLearner Nov 26 '10 at 11:47
  • You can also use Caching in a Winform Application: http://stackoverflow.com/questions/392043/what-are-some-database-caching-options-for-net – Tim Schmelter Nov 26 '10 at 12:05
1

If the tree view is same for all the users of your application than

1- On your Application Start event , fill the data in a dataable and store in the Application Variable.

2- Use above created Application varaible to query the datatble stored into it.

3- When you click on a node in the treeview, show the details by querying stored datatable.

this appraoch would work well , if the treeview is not user-specific by doing so you will get the performance benefit as you minimized the database trips.

Tim Schmelter
  • 450,073
  • 74
  • 686
  • 939
TalentTuner
  • 17,262
  • 5
  • 38
  • 63