Populate TreeView Control with Tables, Views and Columns from a SQL Server Database

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