Click here to Skip to main content
15,885,757 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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:
C#
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
Posted

1 solution

Firstly, you do not have to apologise.
Secondly, you could have easily solved your problem if you have searched on GOOGLE.

Anyways, here you go;

In order to get user created tables and their respective columns for a specified database, do the following;

STEP 1: Select the database <new query="">
STEP 2: Query
SQL
select				o.name as [Table Name],
					c.name as [Column Name],
					o.object_id as [Table ID],
					c.column_id as [Column ID]
from				sys.objects o
left outer join		sys.columns c on o.object_id = c.object_id
where				o.type = 'U'
order by			o.name


I have used IDs in the query which will help you build the treeview.

Let me know if this helps you.

Cheers..
 
Share this answer
 

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900