Populate treeview with user created tables, and their columns

Question

I am learning C# and .NET on my own. I am trying to learn how to use GetSchema.

What I am trying to do is this:

Open MS Access database and populate treeview control with database schema. I want to populate parent nodes with user created table names, and their child nodes will contain column names.

I have tried to adapt the code example I linked to above but have failed.

Here is the code:

using (OleDbConnection OleDBConnection = new OleDbConnection())
{
    OleDBConnection.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" + 
        "Data Source=" + databaseLocation + ";Persist Security Info=False;";

    try
    {
        OleDBConnection.Open();
        // get database schema
        DataTable dataTable = OleDBConnection.GetSchema("Tables");
        // clear the treeview
        TreeView.Nodes.Clear();
        // here I tried to populate the treeview but have failed
        foreach (System.Data.DataRow row in dataTable.Rows)
        {
            // add parent node -> table name
            TreeView.Nodes.Add(row["TABLE_NAME"].ToString());
            // now add children -> all table columns
            foreach(DataColumn column in dataTable.Columns)
            {
                TreeView.Nodes.Add(column.ColumnName);
            }
        }
        // all done, close the connection
        OleDBConnection.Close();
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message);
        Application.Exit();
    }
}

The result I get, when testing on Northwind database, is the following:

- Customers
    - TABLE_CATALOG
    - TABLE_SCHEMA
    - TABLE_NAME
    - TABLE_TYPE
    - TABLE_GUID
    - DESCRIPTION
    - TABLE_PROPID
    - DATE_CREATED
    - DATE_MODIFIED
- Employees
    - TABLE_CATALOG
    - TABLE_SCHEMA
    - TABLE_NAME
    - TABLE_TYPE
    - TABLE_GUID
    - DESCRIPTION
    - TABLE_PROPID
    - DATE_CREATED
    - DATE_MODIFIED 
...

The problem with the above result is that it also includes non-user created tables as parent nodes, and I do not get column names from those tables ( instead, I get TABLE_CATALOG and so on for every table).

QUESTION:

How can I load user created tables as parent nodes, and add child nodes that hold those tables column names?

Again, I apologize if the solution is trivial, but bare in mind that this was my first attempt since I am just beginning with C# and .NET

Answer 1

This ought to do what you want. You might want to take the time to examine the data in the table and column DataTables. They aren't exactly as you might expect to see.

    public static TreeView PopulateTreeViewWithSchema(System.Data.OleDb.OleDbConnection conn, TreeView tree)
    {
        if (tree == null)
            tree = new TreeView();

        // get database schema
        DataTable tableTable = conn.GetSchema("Tables");
        DataTable columnsTable = conn.GetSchema("Columns");

        // clear the treeview
        tree.Nodes.Clear();
        // here I tried to populate the treeview but have failed
        foreach (System.Data.DataRow row in tableTable.Rows)
        {
            // add parent node -> table name
            string tableName = row["TABLE_NAME"].ToString();
            if (!tableName.StartsWith("MSys"))
            {
                TreeNode node = new TreeNode(tableName);
                tree.Nodes.Add(node);
                // now add children -> all table columns
                foreach (System.Data.DataRow columnRow in columnsTable.Rows)
                {
                    if (columnRow["TABLE_NAME"].ToString().Equals(tableName))
                    {
                        node.Nodes.Add(columnRow["COLUMN_NAME"].ToString());
                    }
                }
            }
        }
        return tree;
    }

Answer 2

There are two problems in your code.

1. The GetSchema returns only the table details of the database. That
   information does not contain column details. To filter out system
   tables you have to use the other overload of GetSchema(). You can
   specify the table type filter criteria. "TABLE" in the restrictions refers user created tables. Other values are "SYSTEM TABLE, ACCESS
   TABLE, TABLE".
   

2. The columns should be added as a child node of the parent tree node.

suggestion: Use the debug visualizers to identify what data is available in a table.

Sample Code,

using (OleDbConnection OleDBConnection = new OleDbConnection())
{
    OleDBConnection.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" +
        "Data Source=" + databaseLocation + ";Persist Security Info=False;";

    try
    {
        OleDBConnection.Open();

        string[] restrictions2 = new string[] { null, null, null, "TABLE" };
        System.Data.DataTable DataTable2 = OleDBConnection.GetSchema("Tables", restrictions2);

        // clear the treeview
        treeView1.Nodes.Clear();
        // here I tried to populate the treeview but have failed
        foreach (System.Data.DataRow row in DataTable2.Rows)
        {
            // add parent node -> table name
            var parent = treeView1.Nodes.Add(row["TABLE_NAME"].ToString());
            // now add children -> all table columns

            string[] restrictions1 = new string[] { null, null, (string)row["TABLE_NAME"], null };
            System.Data.DataTable DataTable1 = OleDBConnection.GetSchema("Columns", restrictions1);

            foreach (DataRow row1 in DataTable1.Rows)
            {
                parent.Nodes.Add((string)row1["COLUMN_NAME"]);
            }
        }
        // all done, close the connection
        OleDBConnection.Close();
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message);
        Application.Exit();
    }
}