c#sql-serverwpfdatagriddevexpress-wpf
How can I create a WPF DataGridView that Displays child rows (from child tables) inside parent rows?
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.
Solution
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:
- Why does 1.0/100.0 == 0.1/10.0 give True?
- How to organize the receive msg and user current input in C network such that it's clean
- Using inclusive scan syntax in OpenMP in the C language
- Unable to understand context in book "OOP in C" by Axel Schreiner
- How to dereference member from struct in which the definition is hid from user?
- variable-length array in struct with TI compiler in C (socket programming)
- A faster way to test 32bpp DDBs for a valid Alpha channel
- How can I compile the zephyr example-application as a freestanding application?
- Why does my forked process sometimes overwrite data in a file?
- Why in C can I initialize more values than the size of an array?
- _Generic in C needs typecasting?
- Declaring/defining an unused variable changes the output from an unrelated variable
- How to use gdb to explore the stack/heap?
- How can I read an input string of unknown length?
- Confused by difference between expression inside if and expression outside if
- Fast Arc Cos algorithm?
- Discrepancy of `unsigned long` size between llvm and gcc in riscv32
- GCC (C) - error: 'x' redeclared as different kind of symbol
- Why does GCC’s static analyser falsely warn that a pointer to an allocated memory block itself stored in an allocated memory block may leak?
- c language gcc compiler *.i file #3 "" 2 what is this?
- How to make clang compile to llvm IR
- Hashtable implementation in C
- EFI variables or config file on the EFI partition for EFI application?
- Pack high bit of every byte in ARM, for 64 bytes like AVX512 vpmovb2m?
- Calling an external C function (in a shared lib) from Perl with Inline::C does not work
- Can a C program detect if its own source code has been modified?
- Is this declaration UB?
- Eclipse C/C++ how to find variable belongs to which struct quickly
- Is this truly best way to delete last element in C?
- flint/arb.h: No such file or directory