使用用户创建的表及其列填充树视图 [英] Populate treeview with user created tables, and their columns

问题描述

我自己学习C＃和.NET。我正在尝试学习如何使用 GetSchema [ ^ ]。



我想要做的是：



打开MS Access数据库并使用以下命令填充treeview控件数据库架构。我想用用户创建的表名填充父节点，他们的子节点将包含列名。



我试图调整我上面链接的代码示例但是失败了。



这是代码：

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();
    }
}

在Northwind数据库上测试时得到的结果如下：

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 
...

以上结果的问题是它还包括非用户创建的表作为父节点，我没有从这些表中获取列名（相反，我得到每个表的TABLE_CATALOG等等。）



问题：



如何将用户创建的表作为父节点加载，并添加包含这些表列名的子节点？


再次，如果解决方案很简单，我道歉，但请记住，这是我第一次尝试，因为我刚刚开始使用C＃和.NET

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

推荐答案

首先，您不必道歉。

其次，如果你在GOOGLE上搜索过，你可以轻松解决问题。



无论如何，你走了;



为了获取用户为指定数据库创建的表及其各自的列，请执行以下操作：



步骤1 ：选择数据库< new query =>

第2步：查询

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

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

我在查询中使用了ID来帮助你构建树视图。



让我来知道这是否对你有所帮助。



干杯..

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

Let me know if this helps you.

Cheers..

这篇关于使用用户创建的表及其列填充树视图的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！