How to populate a TreeViewControl with hierarchy level?

Question

I have a TreeView Control (that should look like this)

But I don't know how to populate it with my query:

SELECT T.TableName, C.Column_Name FROM Information_Schema.Tables T
INNER JOIN Information_Schema.Columns C
 ON T.TableName= C.TableName
WHERE T.TableName IN('BASE_TABLE', 'BASE TABLE')
ORDER BY 1, C.Ordinal_Position

Can anyone help me please...

Thanks.

Edit This is what I tried, but just the table names...

private void PopulateTreeView()
{
    SqlCeCommand cmd = new SqlCeCommand();
    try
    {
        using (SqlCeConnection conn = new SqlCeConnection("Data Source=" + connString))
        {            
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES";
            conn.Open();
            cmd.Connection = conn;
            cmd.ExecuteNonQuery();
            // Don't know what's next...
        }
    }
    catch (Exception x)
    {
        MessageBox.Show(x.GetBaseException().ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
    }
    finally
    {
        cmd.Dispose();
    }
}

Answer 1

This is the working answer. I just edited some lines from Sir Gravell's code

private void PopulateTreeView()
{
    treeView1.Nodes.Clear();

    using (var conn = new SqlCeConnection("Data Source=" + connString))
    using (var cmd = conn.CreateCommand())
    {
        cmd.CommandType = CommandType.Text;
        cmd.CommandText = @"SELECT T.TABLE_NAME, C.COLUMN_NAME
                            FROM INFORMATION_SCHEMA.TABLES AS T INNER JOIN
                            Information_Schema.Columns AS C ON T.TABLE_NAME = C.TABLE_NAME
                            WHERE T.TABLE_TYPE IN('BASE_TABLE', 'BASE TABLE', 'TABLE')
                            ORDER BY T.TABLE_NAME, C.ORDINAL_POSITION";
        conn.Open();
        cmd.Connection = conn;

        using (var reader = cmd.ExecuteReader())
        {
            string lastTable = null;
            TreeNode tableNode = null;
            while (reader.Read())
            {
                if (lastTable != reader.GetString(0))
                {
                    lastTable = reader.GetString(0);
                    tableNode = new TreeNode(lastTable);
                    treeView1.Nodes.Add(tableNode);
                }
                tableNode.Nodes.Add(new TreeNode(reader.GetString(1)));
            }
        }
    }
}

Answer 2

Here's a non-LINQ answer that does something simmilar:

using (var conn = new SqlCeConnection(connectionString))
using (var cmd = conn.CreateCommand())
{
    cmd.CommandType = CommandType.Text;
    cmd.CommandText = @"
SELECT T.TABLE_NAME, C.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLES T
INNER JOIN INFORMATION_SCHEMA.COLUMNS C
ON T.TABLE_NAME= C.TABLE_NAME
WHERE T.TABLE_NAME IN('BASE_TABLE', 'BASE TABLE')
ORDER BY 1, C.ORDINAL_POSITION";
    conn.Open();
    cmd.Connection = conn;
    using (var reader = cmd.ExecuteReader())
    {
        string lastTable = null;
        TreeNode tableNode = null;
        while (reader.Read()) { 
            if (lastTable != reader.GetString(0)) {
                lastTable = reader.GetString(0);
                tableNode = new TreeNode(lastTable);
                myTree.Nodes.Add(tableNode);
            }
            tableNode.ChildNodes.Add(new TreeNode(reader.GetString(1)));
        }
    }
}

Answer 3

btw, never never use MessageBox on a server ;p

The core query using LINQ-to-SQL is something like:

using (var ctx = new DataClasses1DataContext())
{
    string[] names = {"BASE_TABLE", "BASE TABLE"};
    var qry = (from table in ctx.Tables
               where names.Contains(table.TableName)
               join column in ctx.Columns on table.TableName equals column.TableName
               orderby table.TableName, column.ColumnName
               select new { table.TableName, column.ColumnName }).ToList();
    foreach (var pair in qry.GroupBy(pair => pair.TableName))
    {
        TreeNode tableNode = new TreeNode(pair.Key);
        myTree.Nodes.Add(tableNode);
        foreach (var col in pair)
        {
            tableNode.ChildNodes.Add(new TreeNode(col.ColumnName));
        }
    }
}

assuming a hand-crafted DBML (since the designer filters these tables), along the lines of:

<?xml version="1.0" encoding="utf-8"?><Database Name="YourDatabase" Class="DataClasses1DataContext" xmlns="http://schemas.microsoft.com/linqtosql/dbml/2007">
  <Connection Mode="WebSettings" ConnectionString="YourConnectionString" SettingsObjectName="System.Configuration.ConfigurationManager.ConnectionStrings" SettingsPropertyName="YourConnectionStringPropertyName" Provider="System.Data.SqlClient" />
  <Table Name="INFORMATION_SCHEMA.TABLES" Member="Tables">
    <Type Name="Table">
      <Column Name="TABLE_NAME" Member="TableName" Type="System.String" DbType="nvarchar(128) NOT NULL" CanBeNull="false" />
    </Type>
  </Table>
  <Table Name="INFORMATION_SCHEMA.COLUMNS" Member="Columns">
    <Type Name="Column">
      <Column Name="TABLE_NAME" Member="TableName" Type="System.String" DbType="nvarchar(128) NOT NULL" CanBeNull="false" />
      <Column Name="COLUMN_NAME" Member="ColumnName" Type="System.String" DbType="nvarchar(128) NOT NULL" CanBeNull="false" />
      <Column Name="ORDINAL_POSITION"  Member="OrdinalPosition" Type="System.Int32" DbType="int NOT NULL" CanBeNull="false" />
    </Type>
  </Table>
</Database>