如何在WPF TreeView中基于给定数据库表展示指定层级结构

嘿，我来帮你搞定在WPF TreeView里展示用户-窗口-操作层级结构的问题！咱们一步步来实现：

首先得把分散在四个表中的数据关联起来，筛选出用户有权限访问的窗口和操作。你可以用下面的SQL查询语句，它会帮你把用户、对应的窗口、窗口下的操作关联好：

SELECT 
    u.LoginName,
    w.WinName,
    a.ActionName
FROM 
    iUser u
JOIN 
    iAccess acc ON u.Id = acc.UserRef
JOIN 
    iWindow w ON acc.GroupRef = w.OwnerRef -- 这里假设GroupRef对应窗口的OwnerRef，根据你的表结构调整关联逻辑
JOIN 
    iAction a ON acc.ActionRef = a.Id
WHERE 
    acc.HasAccess = 1 -- 只取有访问权限的记录
ORDER BY 
    u.LoginName, w.DisplayIndex, a.DisplayIndex;

注意：如果你的表关联逻辑和我假设的不一样（比如GroupRef的对应关系），记得调整JOIN条件，确保数据关联正确。

接下来要创建几个简单的模型类，用来存储查询到的层级数据，方便TreeView绑定：

using System.Collections.ObjectModel;
using System.ComponentModel;

// 用户模型
public class UserModel : INotifyPropertyChanged
{
    private string _loginName;
    public string LoginName
    {
        get => _loginName;
        set { _loginName = value; OnPropertyChanged(nameof(LoginName)); }
    }

    public ObservableCollection<WindowModel> Windows { get; set; } = new ObservableCollection<WindowModel>();

    public event PropertyChangedEventHandler PropertyChanged;
    protected void OnPropertyChanged(string propertyName)
    {
        PropertyChanged?.Invoke(this, new PropertyChangedEventArgs(propertyName));
    }
}

// 窗口模型
public class WindowModel : INotifyPropertyChanged
{
    private string _winName;
    public string WinName
    {
        get => _winName;
        set { _winName = value; OnPropertyChanged(nameof(WinName)); }
    }

    public ObservableCollection<ActionModel> Actions { get; set; } = new ObservableCollection<ActionModel>();

    public event PropertyChangedEventHandler PropertyChanged;
    protected void OnPropertyChanged(string propertyName)
    {
        PropertyChanged?.Invoke(this, new PropertyChangedEventArgs(propertyName));
    }
}

// 操作模型
public class ActionModel : INotifyPropertyChanged
{
    private string _actionName;
    public string ActionName
    {
        get => _actionName;
        set { _actionName = value; OnPropertyChanged(nameof(ActionName)); }
    }

    public event PropertyChangedEventHandler PropertyChanged;
    protected void OnPropertyChanged(string propertyName)
    {
        PropertyChanged?.Invoke(this, new PropertyChangedEventArgs(propertyName));
    }
}

实现INotifyPropertyChanged是为了让数据变化时TreeView能自动更新，如果你不需要动态更新数据，也可以简化成普通的属性类。

创建一个ViewModel类，用来加载数据库数据并整理成层级结构：

using System.Collections.ObjectModel;
using System.Data.SqlClient;

public class MainViewModel
{
    public ObservableCollection<UserModel> Users { get; set; } = new ObservableCollection<UserModel>();

    public MainViewModel()
    {
        LoadPermissionData();
    }

    private void LoadPermissionData()
    {
        // 替换成你的数据库连接字符串
        string connectionString = "Your Database Connection String";
        string sql = "SELECT u.LoginName, w.WinName, a.ActionName FROM iUser u JOIN iAccess acc ON u.Id = acc.UserRef JOIN iWindow w ON acc.GroupRef = w.OwnerRef JOIN iAction a ON acc.ActionRef = a.Id WHERE acc.HasAccess = 1 ORDER BY u.LoginName, w.DisplayIndex, a.DisplayIndex;";

        using (SqlConnection conn = new SqlConnection(connectionString))
        {
            conn.Open();
            SqlCommand cmd = new SqlCommand(sql, conn);
            SqlDataReader reader = cmd.ExecuteReader();

            UserModel currentUser = null;
            WindowModel currentWindow = null;

            while (reader.Read())
            {
                string loginName = reader["LoginName"].ToString();
                string winName = reader["WinName"].ToString();
                string actionName = reader["ActionName"].ToString();

                // 处理用户层级
                if (currentUser == null || currentUser.LoginName != loginName)
                {
                    currentUser = new UserModel { LoginName = loginName };
                    Users.Add(currentUser);
                    currentWindow = null;
                }

                // 处理窗口层级
                if (currentWindow == null || currentWindow.WinName != winName)
                {
                    currentWindow = new WindowModel { WinName = winName };
                    currentUser.Windows.Add(currentWindow);
                }

                // 添加操作
                currentWindow.Actions.Add(new ActionModel { ActionName = actionName });
            }
        }
    }
}

最后，在你的WPF窗口XAML里添加TreeView，并设置层级数据模板：

<Window x:Class="YourNamespace.MainWindow"
        xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
        xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
        xmlns:local="clr-namespace:YourNamespace"
        Title="Permission Tree" Height="450" Width="800">
    <Window.DataContext>
        <local:MainViewModel />
    </Window.DataContext>
    <Grid>
        <TreeView ItemsSource="{Binding Users}">
            <!-- 用户层级模板 -->
            <TreeView.ItemTemplate>
                <HierarchicalDataTemplate DataType="{x:Type local:UserModel}" ItemsSource="{Binding Windows}">
                    <TextBlock Text="{Binding LoginName}" />
                    <!-- 窗口层级模板 -->
                    <HierarchicalDataTemplate.ItemTemplate>
                        <HierarchicalDataTemplate DataType="{x:Type local:WindowModel}" ItemsSource="{Binding Actions}">
                            <TextBlock Text="{Binding WinName}" />
                            <!-- 操作层级模板 -->
                            <HierarchicalDataTemplate.ItemTemplate>
                                <DataTemplate DataType="{x:Type local:ActionModel}">
                                    <TextBlock Text="{Binding ActionName}" />
                                </DataTemplate>
                            </HierarchicalDataTemplate.ItemTemplate>
                        </HierarchicalDataTemplate>
                    </HierarchicalDataTemplate.ItemTemplate>
                </HierarchicalDataTemplate>
            </TreeView.ItemTemplate>
        </TreeView>
    </Grid>
</Window>

这样一来，运行程序后TreeView就会按照你想要的层级展示：用户 -> 窗口 -> 操作，而且只会显示用户有权限访问的内容。

内容的提问来源于stack exchange，提问作者Elham Azadfar