WPF DataGridView实现父行内嵌子表数据的层级展示及筛选、新增功能需求咨询

一、数据库结构说明 #

我有一个名为EmployeesDB的SQL Server数据库，包含4张表，表间为父子关系：

• Employees表：作为父表，存储员工基础信息
• EmployeesContacts表：Employees的子表，存储员工的电话号码（多数员工有多个号码）
• EmployeesLanguages表：Employees的子表，存储员工掌握的语言及听说读写能力
• EmployeesDuties表：Employees的子表，存储员工的工作职责

数据库建表脚本 #

USE [EmployeesDB]
GO

/****** Object:  Table [dbo].[Employees] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Employees](
[EmployeeID] [int] IDENTITY(1,1) NOT NULL,
[FirstName] [nvarchar](50) NULL,
[LastName] [nvarchar](50) NULL,
[Age] [int] NULL,
[Gender] [nvarchar](10) NULL,
[Email] [nvarchar](200) NULL,
CONSTRAINT [PK_Employees] PRIMARY KEY CLUSTERED
(
[EmployeeID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

/****** Object:  Table [dbo].[EmployeesContacts]  ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[EmployeesContacts](
[EmployeeID] [int] NOT NULL,
[Telephone] [nvarchar](12) NOT NULL,
CONSTRAINT [PK_EmployeesContacts] PRIMARY KEY CLUSTERED
(
[Telephone] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

/****** Object:  Table [dbo].[EmployeesDuties] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[EmployeesDuties](
[EmployeeID] [int] NOT NULL,
[EmployeeDutyID] [int] IDENTITY(1,1) NOT NULL,
[Duty] [nvarchar](20) NOT NULL,
CONSTRAINT [PK_EmployeesDuties] PRIMARY KEY CLUSTERED
(
[EmployeeDutyID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

/****** Object:  Table [dbo].[EmployeesLanguages] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[EmployeesLanguages](
[EmployeeID] [int] NOT NULL,
[EmployeeLanguageID] [int] IDENTITY(1,1) NOT NULL,
[LanguageName] [nvarchar](50) NULL,
[TalkingAbility] [nvarchar](20) NULL,
[ReadingAbility] [nvarchar](20) NULL,
[WritingAbility] [nvarchar](20) NULL,
CONSTRAINT [PK_EmployeesLanguages] PRIMARY KEY CLUSTERED
(
[EmployeeLanguageID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[EmployeesContacts]  WITH CHECK ADD  CONSTRAINT [FK_EmployeesContacts_Employees] FOREIGN KEY([EmployeeID])
REFERENCES [dbo].[Employees] ([EmployeeID])
GO
ALTER TABLE [dbo].[EmployeesContacts] CHECK CONSTRAINT [FK_EmployeesContacts_Employees]
GO

ALTER TABLE [dbo].[EmployeesDuties]  WITH CHECK ADD  CONSTRAINT [FK_EmployeesDuties_Employees] FOREIGN KEY([EmployeeID])
REFERENCES [dbo].[Employees] ([EmployeeID])
GO
ALTER TABLE [dbo].[EmployeesDuties] CHECK CONSTRAINT [FK_EmployeesDuties_Employees]
GO

ALTER TABLE [dbo].[EmployeesLanguages]  WITH CHECK ADD  CONSTRAINT [FK_EmployeesLanguages_Employees] FOREIGN KEY([EmployeeID])
REFERENCES [dbo].[Employees] ([EmployeeID])
GO
ALTER TABLE [dbo].[EmployeesLanguages] CHECK CONSTRAINT [FK_EmployeesLanguages_Employees]
GO

二、UI需求背景 #

我现在用WPF做了一个带DataGridView的窗口，但遇到了棘手的问题：我希望把所有父子表数据整合到同一个DataGridView中展示，这样就能复用DataGridView自带的筛选、排序、搜索等功能。

期望的展示效果是层级式的（所有行默认展开），父行对应Employees表数据，下方内嵌对应的Contacts、Languages、Duties子表数据行。

三、具体功能需求 #

• 新增行提示：在每个父行的子行末尾，以及所有父行的末尾，都要显示“Click To Add New Row”的提示（类似示例图效果）
• 筛选联动：比如筛选工作职责为“Advertiser”的员工时，要完整展示符合条件的员工的父行及所有关联子行（类似示例图的筛选后效果）

四、尝试过的方案及困境 #

我已经在谷歌上搜索了各种类似案例，但毫无收获；也尝试过用SQL Server视图来整合数据，但没能达到预期效果。真心希望能得到大家的帮助！

备注：内容来源于stack exchange，提问作者Meshka