Introduction
This is the second blog post about populating a TreeView control. In the first blog post, Populate TreeView Control with Tables and Columns from MS Access Database, I used a MS Access database to populate a TreeView with its tables and related columns. In this blog post I will instead be using a SQL Server Database.
The first example is based on a SQL query and where the retrieved data is than used to populate a TreeView control.
Since it very common that users don’t have access to system data like system tables I also show a scenario where the required information is made available in a XML file. In the second example I use the XMLDataDocument Class to read the XML file in order to populate the TreeView control with required data.
The SQL Query Case
To retrieve the tables, views and their related columns from a SQL Server database we use its schema table information_schema.columns. In other words, we can use a Dataset and to fill it with data we use a SQL query. In the next step we can populate the TreeView control with data. I use the same case as in the first blog post so what is showed below is only the updated code:
Imports System.Data.SqlClient
Imports System.Windows.Forms
Public Class Form1
Const Con As String = _
"Data Source=DEVELOP;Initial Catalog=AdventureWorks;" & _
"Integrated Security=True"
Const SQLExpression As String = _
"SELECT TABLE_NAME, COLUMN_NAME " & _
"FROM information_schema.columns " & _
"ORDER BY TABLE_NAME,COLUMN_NAME"
Const Database As String = "AdventureWorks"
Private Sub Button3_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles Button3.Click
Dim Rootnode As TreeNode = Nothing
Dim Mainnode As TreeNode = Nothing
Dim Childnode As TreeNode = Nothing
Dim MainName As String = String.Empty
Dim cn As New SqlConnection(Con)
Dim adp As New SqlDataAdapter(SQLExpression, cn)
Dim ds As New DataSet
adp.Fill(ds, "SystemData")
TVDatabase.Nodes.Clear()
Rootnode = TVDatabase.Nodes.Add(key:="Root", text:=Database, _
imageIndex:=0, selectedImageIndex:=0)
For Each row As DataRow In ds.Tables("SystemData").Rows
If MainName <> row(0).ToString Then
Mainnode = Rootnode.Nodes.Add(key:="Table", text:=row(0).ToString, _
imageIndex:=1, selectedImageIndex:=1)
MainName = row(0).ToString
End If
Childnode = Mainnode.Nodes.Add(key:="Column", text:=row(1).ToString, _
imageIndex:=2, selectedImageIndex:=2)
Next
TVDatabase.Nodes(0).EnsureVisible()
ds.Dispose()
ds = Nothing
adp.Dispose()
adp = Nothing
cn.Dispose()
cn = Nothing
End Sub
End Class
When we run the code to populate the TreeView control we get the following output:
The XML File Case
In order to set up this case it required a XML file to work with. I created a relevant XML file by using the Dataset in the above case where I used the following snippet code:
ds.WriteXml("c:\SystemData.xml")
The following XML code snippet shows the data in the generated file:
<?xml version="1.0" standalone="yes"?>
<NewDataSet>
<SystemData>
<TABLE_NAME>Address</TABLE_NAME>
<COLUMN_NAME>AddressID</COLUMN_NAME>
</SystemData>
<SystemData>
<TABLE_NAME>Address</TABLE_NAME>
<COLUMN_NAME>AddressLine1</COLUMN_NAME>
</SystemData>
<SystemData>
<TABLE_NAME>Address</TABLE_NAME>
<COLUMN_NAME>AddressLine2</COLUMN_NAME>
</SystemData>
For reading XML files as well as iterating through their nodes I prefer to use the XMLDataDocument Class which also the below code shows. Initially it may look complex but it’s a rather straightfull way. I use the same case as in the first blog post so what is showed below is only the updated code:
Imports System.Xml
Public Class Form1
Const Database As String = "AdventureWorks - XML"
Private Sub Button3_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles Button3.Click
Dim Rootnode As TreeNode = Nothing
Dim Mainnode As TreeNode = Nothing
Dim Childnode As TreeNode = Nothing
Dim MainName As String = String.Empty
Dim NodeName As String = String.Empty
Dim ChildName As String = String.Empty
Dim xmldoc As New XmlDataDocument()
Dim xmlnode As XmlNodeList
TVDatabase.Nodes.Clear()
Rootnode = TVDatabase.Nodes.Add(key:="Root", text:=Database, _
imageIndex:=0, selectedImageIndex:=0)
'Open and read the document.
xmldoc.Load(filename:="c:\SystemData.xml")
'Node that includes the wanted childnodes.
xmlnode = xmldoc.GetElementsByTagName(name:="SystemData")
For CountNodes As Integer = 0 To xmlnode.Count - 1
'Get the table name.
NodeName = xmlnode(CountNodes).ChildNodes.Item(0).InnerText.Trim()
If MainName <> NodeName Then
Mainnode = Rootnode.Nodes.Add(key:="Table", text:=NodeName, _
imageIndex:=1, selectedImageIndex:=1)
MainName = NodeName
End If
'Get the column name.
ChildName = xmlnode(CountNodes).ChildNodes.Item(1).InnerText.Trim()
Childnode = Mainnode.Nodes.Add(key:="Column", text:=ChildName, _
imageIndex:=2, selectedImageIndex:=2)
Next
TVDatabase.Nodes(0).EnsureVisible()
End Sub
'...
End Class
When executing the code the TreeView control is populated with the wanted data as the following screen shot shows:
That’s all for now and the next blog post will show how we achieve the same tasks as above by using LINQ to SQL and LINQ to XML.
Kind regards,
Dennis
The third article on the subject can be found here:
Populate TreeView Controls with Tables from SQL Server & Access Database by using ADOX
VSTO & .NET & Excel 