Treeview Control - Using SQL datasource and closure table method

Question

5.00/5 (1 vote)

See more:

Hi to all at Code Project this is my first post!

My query is probably very simple to all you experienced vb developers out there but I am from a Excel VBA developer background and trying to learn to develop using Winforms, Visual Studio 2012 and SQL Server in a very short time frame and am looking for any advice / pointers on how to achieve this avoiding any known pitfalls along the way.

The Project
I have been asked to create a hierarchical Treeview from a SQL table with the ability to drag and drop nodes and to also have the underlying data in the SQL table modifed as per the nodes dragged and dropped.

The dataset is one SQL table. (>30000 records) The main relevant fields are 'TagNo' which is a unique record ID field, and 'ParentTag' which hold the parent 'TagNo' (if any) for the record. There are roughly 5600 unique ParentTags (i.e. Parent level nodes)

I have been tasked to achieve this using the 'closure table' method (or Ancestor Tables method as I've seen it referred to) as my boss likes this method of implementation.

Steps Taken so Far

1: Created a new Winforms application containing a SQL dataset (one table showing all equipment to be displayed in treeview), a form to display the treeview and a treeview control on this form.

2: I have read up on: drag and drop methods, how to build a treeview from SQL dataset, setting context menus etc. but have had difficulty tracking down any examples where the changes are also written back to the dataset.

What I would like to know / find

1: Is the Closure Table method suitable for this task? If so, a simple method to build a closure table from the SQL table? If not, what method would you recommend instead?

2: Any known examples of writing data changes back to the underlying dataset after drag and drop of nodes (i.e. not just altering how the data is displayed in treeview but updating the SQL data table.)

Any pointers on methods, resources or examples of this would be greatly appreciated as I am a bit overwhelmed by the amount of 'Treeview' examples out there, none of which seem to show how to write the changes back to the underlying SQL dataset?

I presume it's just a matter of running UPDATE queries after the drag and drop event is triggered but would like to see how to do this "properly" and efficiently and would also like to see an exmaple of the closure table method if anyone has implemented this?

Thanks in advance.

Posted 31-Jul-13 1:41am

Steve Muirche7

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Steve Muirche7 · Answer 1 · 2013-08-08T04:56:00

OK, doesn't look like I'm getting any response to my original question. Probably way too vague as I'm struggling with this and am probably not asking the right questions.

I now realy only need an VB code example of how to populate a treeview control using my Closure Table dataset.

The Closure Table holds a 'Level' field, indicating at which level each node should be displayed at.

What I need is an example of a VB sub which loops through all the rows on the dataset and populates to the treeview.

One thing to note is that the Closure Table holds approx. 180,000 rows mapping all possible connections between roughly 30,000 items from the original data table. It is basically a long list of systems / sub systems / components / sub componants etc etc. This can go down to 10+ levels.

Closure Table Structure

ID PartID ParentID Level

(Where 'Level' is the level this node should be in the Treeview Hierarchy)

Hope this makes sense? Surely someone out there has had to do something like this in a Visual Studio Winforms application?

Any advice or pointers would be greatly appreciated.