SQL Server How can I create a WPF DataGridView that Displays child rows (from child tables) inside parent rows? [closed]

iyfjxgzm 于 2023-10-15 发布在其他

关注(0)|答案(1)|浏览(102)

Closed. This question needs to be more focused . It is not currently accepting answers.

Want to improve this question? Update the question so it focuses on one problem only by editing this post .

Closed 16 days ago.
Improve this question

I have SQL Server Database Named EmployeesDB that contains 4 Tables as following:

Employees Table which is the Parent Table in this Example.
EmployeesContacts Table Which is the Child Table of Employees holding employee Telephone Numbers because most of the Employees have more than One Telephone Numbers.
EmployeesLanguages Table Which is the Child Table of Employees holding The Languages of the Employees and their Ability to Talk, Read and Write in those languages.
EmployeesDuties Table Which is the Child Table of Employees holding the Duties of all the Employees in the Company. Here is the Database diagram:

And Here is the Database Script

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

And for XAML and C# it is just a Window with WPF DataGridView

The Impossible thing to me Came when I turned to the UI of which I wanted the whole Data to appear in One DataGridView so that I take Advantages of the Features of DataGridView like Filter, Sort, Search e.t.c.

Here is an Example of the View I Wanted which All The Rows are in Expanded Mode:

And Here is the Description of which table the Columns Belong to:

The Behaviours and Features I wanted are:

To Show “Click To Add New Row” at the End of each Sub-Rows and at The End of Parent Rows like in the pictures above.
When I Filter for Example Employees by Duty “Advertiser”, the Filter to Show full Data with All Sub-Rows like in the Picture below (Filtered by Duties Column).

Here is an Image of How The DatagridView looks when Filtered

With all my efforts I searched google for anything similar and found Nothing, Tried SQL Server View but with no success … I will really appreciate any help in this question.

sql-server

来源：https://stackoverflow.com/questions/77144100/how-can-i-create-a-wpf-datagridview-that-displays-child-rows-from-child-tables

1条答案

按热度按时间

o2g1uqev1#

You can try this one XAML:

<Window.Resources>
        <CollectionViewSource x:Key="CvsKey">
            <CollectionViewSource.GroupDescriptions>
                <PropertyGroupDescription PropertyName="Employee"/>
            </CollectionViewSource.GroupDescriptions>
        </CollectionViewSource>
        
    </Window.Resources>

    <Grid>
        <DataGrid AutoGenerateColumns="False" x:Name="dataGrid1" ItemsSource="{Binding Employees}" IsReadOnly="True" RowDetailsVisibilityMode="Collapsed"
                          
                  CanUserAddRows="True">

            <DataGrid.Columns>
                <DataGridTextColumn Binding="{Binding EmployeeID}" Header="EmployeeID"/>
                <DataGridTextColumn Binding="{Binding FirstName}" Header="FirstName"/>
                <DataGridTextColumn Binding="{Binding LastName}" Header="LastName"/>
                <DataGridTextColumn Binding="{Binding Age}" Header="Age"/>
                <DataGridTextColumn Binding="{Binding Gender}" Header="Gender"/>
                <DataGridTextColumn Binding="{Binding Email}" Header="Email"/>
                <DataGridTemplateColumn Header="Contects"  Width="75">
                    <DataGridTemplateColumn.CellTemplate>
                        <DataTemplate>
                            <DataGrid ItemsSource="{Binding Contacts}" IsReadOnly="True" AutoGenerateColumns="False" HeadersVisibility="None">
                                <DataGrid.Columns>
                                    <DataGridTemplateColumn Width="*">
                                        <DataGridTemplateColumn.CellTemplate>
                                            <DataTemplate>
                                                <TextBlock Text="{Binding Telephone}"/>
                                            </DataTemplate>
                                        </DataGridTemplateColumn.CellTemplate>
                                    </DataGridTemplateColumn>
                                </DataGrid.Columns>
                            </DataGrid>
                        </DataTemplate>
                    </DataGridTemplateColumn.CellTemplate>
                </DataGridTemplateColumn>
                <DataGridTemplateColumn Header="Duties"  Width="75">
                    <DataGridTemplateColumn.CellTemplate>
                        <DataTemplate>
                            <DataGrid ItemsSource="{Binding Duties}" IsReadOnly="True" AutoGenerateColumns="False" HeadersVisibility="None">
                                <DataGrid.Columns>
                                    <DataGridTemplateColumn Width="*">
                                        <DataGridTemplateColumn.CellTemplate>
                                            <DataTemplate>
                                                <TextBlock Text="{Binding Duty}"/>
                                            </DataTemplate>
                                        </DataGridTemplateColumn.CellTemplate>
                                    </DataGridTemplateColumn>
                                </DataGrid.Columns>
                            </DataGrid>
                        </DataTemplate>
                    </DataGridTemplateColumn.CellTemplate>
                </DataGridTemplateColumn>
                <DataGridTemplateColumn Header="Languages"  Width="75">
                    <DataGridTemplateColumn.CellTemplate>
                        <DataTemplate>
                            <DataGrid ItemsSource="{Binding Languages}" IsReadOnly="True" AutoGenerateColumns="False" HeadersVisibility="None">
                                <DataGrid.Columns>
                                    <DataGridTemplateColumn Width="*">
                                        <DataGridTemplateColumn.CellTemplate>
                                            <DataTemplate>
                                                <TextBlock Text="{Binding LanguageName}"/>
                                                
                                            </DataTemplate>
                                        </DataGridTemplateColumn.CellTemplate>
                                    </DataGridTemplateColumn>
                                </DataGrid.Columns>
                            </DataGrid>
                        </DataTemplate>
                    </DataGridTemplateColumn.CellTemplate>
                </DataGridTemplateColumn>
            </DataGrid.Columns>

        </DataGrid>
    </Grid>

CODE BEHIND:

using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.ComponentModel.DataAnnotations;
using System.Linq;
using System.Windows;
using System.Windows.Input;

namespace WpfApp1
{
    public partial class MainWindow : Window
    {
        private EmployeeViewModel _viewModel;

        public MainWindow()
        {
            InitializeComponent();
            _viewModel = new EmployeeViewModel();
            DataContext = _viewModel;
        }
    }

    public class EmployeeViewModel
    {
        private readonly EmployeeDbContext _context;

        public EmployeeViewModel()
        {

            using (var context = new EmployeeDbContext())
            {
                Employees = context.Employees
                       .Include(e => e.Duties)
                    .Include(e => e.Contacts)

                    .Include(e => e.Languages)
                    .ToList();

            }

        }

        public List<Employee> Employees { get; set; } = new List<Employee>();

        public void SaveChanges()
        {
            _context.SaveChanges();
        }

        // Add methods for CRUD operations as needed
    }
  
    public class EmployeeDbContext : DbContext
    {
        public DbSet<Employee> Employees { get; set; }
        public DbSet<EmployeesContact> EmployeesContacts { get; set; }
        public DbSet<EmployeesDuty> EmployeesDuties { get; set; }
        public DbSet<EmployeesLanguage> EmployeesLanguages { get; set; }



        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            // Configure your database connection here
            optionsBuilder.UseSqlServer("Data Source=(localdb)\\ProjectModels;Initial Catalog=EmployeesDB;Integrated Security=True;Connect Timeout=30;Encrypt=False;Trust Server Certificate=False;Application Intent=ReadWrite;Multi Subnet Failover=False");

        }
    }
    public class Employee
    {
        public int EmployeeID { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public int Age { get; set; }
        public string Gender { get; set; }
        public string Email { get; set; }

        public ObservableCollection<EmployeesContact> Contacts { get; set; }
        public ObservableCollection<EmployeesDuty> Duties { get; set; }
        public ObservableCollection<EmployeesLanguage> Languages { get; set; }
    }

    public class EmployeesContact
    {
        [Key] // Define TelephoneID as the primary key
        public int TelephoneID { get; set; }

        public int EmployeeID { get; set; }
        public string Telephone { get; set; }

        // Other properties and relationships
    }

    public class EmployeesDuty
    {
        public int EmployeeID { get; set; }
        [Key]
        public int EmployeeDutyID { get; set; }
        public string Duty { get; set; }
    }

    public class EmployeesLanguage
    {
        public int EmployeeID { get; set; }
        [Key]
        public int EmployeeLanguageID { get; set; }
        public string LanguageName { get; set; }
        public string TalkingAbility { get; set; }
        public string ReadingAbility { get; set; }
        public string WritingAbility { get; set; }
    }
}

RESULT:

赞(0）回复(0）举报 2023-10-15

我来回答

SQL Server How can I create a WPF DataGridView that Displays child rows (from child tables) inside parent rows? [closed]

1条答案

相关问题

热门标签

最新问答