Introduction
We may need to develop a master and detail transaction display or a entry data form while building enterprise applications.
I would like to use the power and strength of LINQ in my windows application. In this article LINQ is used to extract data from object sources, by giving a simple example, the master and detail representation of data display is explained using C# winforms and LINQ.
Background
In my earlier articles, I used Northwind database to work on LINQ, here I used the custom objects to hold data. LINQ is used to extract data from object source using joins and grouping.
In this example two classes Department and Employee are used and required public properties are declared in each class. Each of the class is having constructor to save the data.
Also t2 GridViews are used to show Department and Employee records. Data will be bind to GridViews through Binding source
BindingSource Class (System.Windows.Forms):
The BindingSource component serves many purposes. First, it simplifies binding controls on a form to data by providing currency management, change notification, and other services between Windows Forms controls and data sources. This is accomplished by attaching the BindingSource component to your data source using the DataSource property
Initially data will be loaded to BindingSource and the BindingSource is used to get the data into GridView controls
In this case the BindingSource and the GridView both controls DataSource property needs to be set
LINQ data will be bind to Binding Source to its DataSource property and the BindingSource is used to set GridView DataSource property
Create list of objects for both sources and add data
private List<Department> Departments = new List<Department>();
private List<Employee> Employees = new List<Employee>();
Adding data to Department through list
Departments.Add(new Department(10, "Sales", "Hyderabad"));
Adding data to Employee through list
Employees.Add(new Employee(1, "Krishna Prasad", 10, "Manager", 30000));
Set GridViews AutoGeneratedColumns property to true to show the BindingSource data from Object sources
dgvEmployees.AutoGenerateColumns = true;
dgvDepartments.AutoGenerateColumns = true;
Data can be grouped and retrieved from object sources as specified below
var matchingEmployees = from dept in Departments
join emp in Employees on dept.DeptNo equals emp.DeptNo
into AvailableEmployees
select new { department = dept, employees = AvailableEmployees };
The Dictionary object is used to hold selected group data.
Dictionary
Represents a collection of keys and values
The Dictionary<(Of <(TKey, TValue>)>) generic class provides a mapping from a set of keys to a set of values. Each addition to the dictionary consists of a value and its associated key. Retrieving a value by using its key is very fast, close to O(1), because the Dictionary<(Of <(TKey, TValue>)>) class is implemented as a hash table
The Dictionary object requires two parameters, first one is the key and the second one is the value.
In this example the Key is Department (record) and value is related Employee records for the selected Department
private Dictionary<Department, IEnumerable<Employee>> GroupEmployee;
this.GroupEmployee = matchingEmployees.ToDictionary(x => x.department, y => y.employees);
Now the data is saved into the Dictionary object. When ever the Master record (Ex: Department in this example)selection is changed in maser GridView, the records need to be shown in the detail GridView (Ex: Employee records in this example)
This process can be implemented through BindingSources through its CurrentChanged event, as they are bound to specifc data and GridView as bound to BindingSources
private void bsDepartments_CurrentChanged(object sender, EventArgs e)
{
bsEmployees.DataSource = this.GroupEmployee [(Department) bsDepartments.Current];
}
Using the code
Download the attached Zip file, rebuild the solution and run the SecondLINQ sample it will work
Complete Code
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
namespace SecondLINQ
{
public partial class Form1 : Form
{
private List<Department> Departments = new List<Department>();
private List<Employee> Employees = new List<Employee>();
private Dictionary<Department, IEnumerable<Employee>> GroupEmployee;
public Form1()
{
InitializeComponent();
dgvEmployees.AutoGenerateColumns = true;
dgvDepartments.AutoGenerateColumns = true;
loadDepartments();
loadEmployees();
var matchingEmployees = from dept in Departments
join emp in Employees on dept.DeptNo equals emp.DeptNo
into AvailableEmployees select new { department = dept, employees = AvailableEmployees };
this.GroupEmployee = matchingEmployees.ToDictionary(x => x.department, y => y.employees);
bsDepartments.DataSource = GroupEmployee.Keys;
}
public class Department
{
private int deptNo = 0;
private String deptName = String.Empty;
private String deptLoc = String.Empty;
public Department() { }
public Department(int DeptNo, String DeptName, String DeptLoc)
{
deptNo = DeptNo;
deptName = DeptName;
deptLoc = DeptLoc;
}
public int DeptNo
{
set { deptNo = value; }
get {return deptNo;}
}
public String DeptName
{
set { deptName = value; }
get { return deptName; }
}
public String DeptLoc
{
set { deptLoc = value; }
get { return deptLoc; }
}
}
private class Employee
{
private int employeeNo;
private String employeeName;
private int deptNo;
private String job;
private Double salary;
public Employee() { }
public Employee(int EmployeeNo, String EmployeeName, int DeptNo, String Job, Double Salary)
{
employeeNo = EmployeeNo;
employeeName = EmployeeName;
deptNo = DeptNo;
job = Job;
salary = Salary;
}
public int EmployeeNo
{
set { employeeNo = value; }
get { return employeeNo; }
}
public String EmployeeName
{
set { employeeName = value; }
get { return employeeName; }
}
public int DeptNo
{
set { deptNo = value; }
get { return deptNo; }
}
public String Job
{
set { job = value; }
get { return job; }
}
public Double Salary
{
set { salary = value; }
get { return salary; }
}
}
private void loadDepartments()
{
Departments.Add(new Department(10, "Sales", "Hyderabad"));
Departments.Add(new Department(20, "Purchases", "Mumbai"));
Departments.Add(new Department(30, "Admin", "Mumbai"));
Departments.Add(new Department(40, "Accounts", "Mumbai"));
Departments.Add(new Department(50, "Training", "Hyderabad"));
Departments.Add(new Department(60, "Stores", "Hyderabad"));
}
private void loadEmployees()
{
Employees.Add(new Employee(1, "Krishna Prasad", 10, "Manager", 30000));
Employees.Add(new Employee(2, "Rajesh", 10, "Clerk", 10000));
Employees.Add(new Employee(3, "Ramesh", 10, "Assistant", 5000));
Employees.Add(new Employee(4, "Ragesh", 10, "Computer Operator", 4000));
Employees.Add(new Employee(5, "Murali", 20, "Manager", 30000));
Employees.Add(new Employee(6, "Anil", 20, "Clerk", 10000));
Employees.Add(new Employee(7, "Karthik", 30, "Manager", 30000));
Employees.Add(new Employee(8, "Anirudh", 30, "Assistant", 7000));
Employees.Add(new Employee(9, "Sarma Chada", 40, "Manager", 15000));
Employees.Add(new Employee(10, "Anupama", 40, "Assistant", 3000));
Employees.Add(new Employee(10, "Anirudh", 40, "Accountant", 7000));
Employees.Add(new Employee(11, "Sailesh", 60, "Store Keeper", 4000));
}
private void bsDepartments_CurrentChanged(object sender, EventArgs e)
{
bsEmployees.DataSource = this.GroupEmployee [(Department) bsDepartments.Current];
}
}
}
Points of Interest
Join and Grouping the data through LINQ is very easy thing. we can write a simple LINQ expression using Lamda to get complex data from multiple tables
History
Posted and updated on Nov 7, 2008
