如何从 SqlLite 数据库加载 TreeView 节点 [英] How to load TreeView nodes from SqlLite database

问题描述

我正在尝试使用 System.Data.SQLite 将节点加载到 c# winform 树视图中.

I am trying to load nodes into a c# winform treeview using System.Data.SQLite.

目前我的数据库表是这样的:

Currently my database table looks like this:

ID  Parent_ID  Name 
1   0          Apple
2   0          Pear 
3   2          Grapes 
4   3          Banana

我需要我的树视图看起来像这样:

I need my treeview to look like this:

Apple
Pear
-> Grapes
-> -> Banana

'Grapes'的Parent_ID为'2'，使其成为'Pear'的子节点，'Banana'的Parent_ID为'3'，使其成为'Grapes'的子节点.

'Grapes' has a Parent_ID of '2', making it a child node of 'Pear', and 'Banana' has a Parent_ID of '3', making it a child node of 'Grapes'.

我对 SQL 不是很有经验，不知道如何从包含表MyTable"的 SQLite 文件Database.db"中获取数据.

I'm not very experienced with SQL and am not sure how to go about getting the data out of my SQLite file 'Database.db', containing a table 'MyTable'.

非常感谢任何帮助.

推荐答案

非常感谢 Jdweng 提供的解决方案.

Many thanks to Jdweng for providing a solution.

我稍微修改了他们的代码，以便它不依赖于手动制作数据表，而是从我的 SQLite 数据库文件中获取它.

I modified their code slightly so that it didn't rely on making the datatable manually and instead took it from my SQLite database file.

Jdweng 的代码还创建了一个根"节点，这对我的应用程序来说不是必需的，因此我将其更改为仅包含包含在我的 SQLite 数据库表中的节点.

Jdweng's code also made a 'Root' node which was not necessary for my application so I changed it to only include the nodes included in my SQLite DB table.

我还需要更改MakeTree"方法以查找 Int64，因为我在查询 ID 和 Parent_ID 时收到错误指定的强制转换无效".

I also needed to change the 'MakeTree' method to look for Int64 as I was receiving an error, 'Specified cast is not valid' when querying ID and Parent_ID.

using System;
using System.Data;
using System.Data.SQLite;
using System.Linq;
using System.Windows.Forms;

namespace TreeView_SQL_Loader
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            DataTable DT = new DataTable();

            SQLiteConnection sql_con = new SQLiteConnection("data source=\"mydatabase.db\"");
            sql_con.Open();
            SQLiteCommand sql_cmd = sql_con.CreateCommand();
            sql_cmd.CommandText = "SELECT * FROM mytable";
            SQLiteDataAdapter DA = new SQLiteDataAdapter(sql_cmd.CommandText, sql_con);

            DA.Fill(DT);

            sql_con.Close();


            int parentID = 0;
            MakeTree(parentID, treeView1.Nodes, DT);

            treeView1.ExpandAll();
        }

        public void MakeTree(Int64 parentID, TreeNodeCollection parentNode, DataTable DT)
        {
            foreach (DataRow row in DT.AsEnumerable().Where(x => x.Field<Int64>("Field2") == parentID))
            {
                string name = row.Field<string>("Field3");
                Int64 id = row.Field<Int64>("Field1");
                TreeNode node = new TreeNode(name);
                parentNode.Add(node);
                MakeTree(id, node.Nodes, DT);

            }
        }
    }
}

这篇关于如何从 SqlLite 数据库加载 TreeView 节点的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！