Dec 6, 2008

Creating Master Detail representation data display forms using LINQ (C#)

Creating Master Detail representation data display forms using LINQ (C#)
We need to develop a master and detail transaction display or entry data forms while building enterprise applications.
I am trying to use the power and strength of LINQ in my windows application. In this article LINQ is used to extract data from object sources
In this article by giving a simple example, the master and detail representation of data display is explained using C# win forms and LINQ.
In my earlier articles I used Northwind database to work on LINQ, here the custom objects are used to hold data. Here LINQ is used to extract data from object source using joins and grouping.
In this example 2 classes Department and Employee with required public properties in each class are used. Each class is having constructor to save the data.
Also 2 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 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 Departments = new List();
private List Employees = new List();

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.
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

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];

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 Departments = new List();
private List Employees = new List();

public Form1()

dgvEmployees.AutoGenerateColumns = true;
dgvDepartments.AutoGenerateColumns = true;


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];

Hope this helps you


Extracting data from multiple database tables using LINQ

Extracting data from multiple database tables using LINQ


I have been working on C#, ASP.Net and SQL Server long time. Now the time has come for the need to explore the latest .Net framework 3.0 and 3.5 features. This is because many companies are looking forward to call their windows based applications through web as Smart client. Some applications need animated (2D/ #D) graphics in their sites.

I hope all the latest applications (.Net based) use the goodness of the latest .Net Technologies to build web based data centric enterprise applications.

As may of us developing enterprise data oriented applications, we have some difficulties in debugging the database objects like stored procedures, functions etc. We need to write a bit more code to get data from database, update and save it back to database, searching for the data and for providing pagination and sorting for the data.

LINQ solves all the above discussed difficulties

.NET Language-Integrated Query

After two decades, the industry has reached a stable point in the evolution of object-oriented (OO) programming technologies. Programmers now take for granted features like classes, objects, and methods. In looking at the current and next generation of technologies, it has become apparent that the next big challenge in programming technology is to reduce the complexity of accessing and integrating information that is not natively defined using OO technology. The two most common sources of non-OO information are relational databases and XML.

Rather than add relational or XML-specific features to our programming languages and runtime, with the LINQ project we have taken a more general approach and are adding general-purpose query facilities to the .NET Framework that apply to all sources of information, not just relational or XML data. This facility is called .NET Language-Integrated Query (LINQ).

LINQ has a great power of querying on any source of data, data source could be the collections of objects, database or XML files. We can easily retrieve data from any object that implements the IEnumerable interface. Microsoft basically divides LINQ into three areas and that are give below.

• LINQ to Object {Queries performed against the in-memory data}
• LINQ to ADO.Net
• LINQ to SQL (formerly DLinq) {Queries performed against the relation database only Microsoft SQL Server Supported}
• LINQ to DataSet {Supports queries by using ADO.NET data sets and data tables}
• LINQ to Entities {Microsoft ORM solution}
• LINQ to XML (formerly XLinq) { Queries performed against the XML source}

In this article I am trying to extract data from multiple database tables using LINQ. We need to have some knowledge in simple SQL query writing. LINQ looks like a simple SQL query for extracting the data. We can use LAMDA expressions and Extension methods in this process. We can easily execute stored procedures and functions

The DataContext class provides the main entry point for the LINQ to SQL framework. The DataContext is the source of all entities mapped over a database connection. It tracks changes that you made to all retrieved entities and maintains an "identity cache" that guarantees that entities retrieved more than one time are represented by using the same object instance.

In general, a DataContext instance is designed to last for one "unit of work" however your application defines that term. A DataContext is lightweight and is not expensive to create. A typical LINQ to SQL application creates DataContext instances at method scope or as a member of short-lived classes that represent a logical set of related database operations.

In my Example I am extracting data from Northwind Database (in SQL 2005). You can download sample databases from Microsoft site ( and it is very easy to install the databases in SQL server 2005

Step #1: Create a new website (C#) in Visual Studio 2008 / Visual Studio 2005.

Step #2: Right click on the project and click on “Add + New Item”. Select “Visual C#” in categories and “LINQ to SQL classes” from templates, rename the file (.dbml) name as “Northwind.dbml”

Step #3: Click on View menu in Visual Studio IDE and select Server Explorer. This shows the available Database connections. Right click on “Data Connections” in Server explorer and then click on “Add New Connection”. Provide the required information to get connected with “Northwind” database in SQL Server

Step #4: Explore the new data base connection for tables, select “Categories”, “Order Details”, “Orders” and “Products” tables from the lists. Drag and drop the selected tables on the “LINQ to SQL Classes” (Northwind.dbml) designer. As specified below

Step #5: Add a GridView control to Default.aspx page. Open the Default.aspx.cs file and add the below code

protected void Page_Load(object sender, EventArgs e)
int startRow = Convert.ToInt32(Request.QueryString["Row"]);


private void BindProducts(int startRow)
NorthwindDataContext db = new NorthwindDataContext();

var products = from p in db.Products
//where p.Category.CategoryName == "Beverages"
//select p;
//where p.OrderDetails.Count > 2 && p.Category.CategoryName == "Beverages"
where p.Order_Details.Count > 2
select new
ID = p.ProductID,
Name = p.ProductName,
NumOrders = p.Order_Details.Count,
Revenue = p.Order_Details.Sum(o => o.UnitPrice * o.Quantity)

GridView1.DataSource = products.Skip(startRow).Take(10);

Run the application, we can see the data in GridView control

As we can debug the query (in code), put some break points at “var products” and watch the below query

“{SELECT [t0].[ProductID] AS [ID], [t0].[ProductName] AS [Name], (
FROM [dbo].[Order Details] AS [t2]
WHERE [t2].[ProductID] = [t0].[ProductID]
) AS [NumOrders], (
SELECT SUM([t4].[value])
SELECT [t3].[UnitPrice] * (CONVERT(Decimal(29,4),[t3].[Quantity])) AS [value], [t3].[ProductID]
FROM [dbo].[Order Details] AS [t3]
) AS [t4]
WHERE [t4].[ProductID] = [t0].[ProductID]
) AS [Revenue]
FROM [dbo].[Products] AS [t0]
FROM [dbo].[Order Details] AS [t1]
WHERE [t1].[ProductID] = [t0].[ProductID]
)) > @p0 }

This is interesting thing, actually LINQ expression are converted into SQL when they get executed at database server end.

One more interesting thing is LINQ supports for paging also, look at the below line of code

GridView1.DataSource = products.Skip(startRow).Take(10);

We can start at a specific position of the row and take the no of other required rows. This can be done in LINQ using method extensions. Wow! How simple it is now for paging the data in the grid view

Hope this helps you


Dynamic WPF Page (UI) generation using C#

I have gone through many blogs and support sites and read some of the articles, many of them gave the idea about generating the Dynamic WPF page using VB.Net. But as a developer I always prefer writing the code in C#.

Generating the Page in WPF is very simple. Before going to do this we should have some basic idea about object sources and XML.

WPF supports vector based UI design and it is fully XAML (Extensible Application Markup Language) style, because of this, the code behind totally separated from its UI design.
In this article, I am going to explain about creating dynamic WPF page at runtime. This was really easy using XML literals and XML namespace imports. We can load and save XAML at runtime using the System.Windows.Markup.XamlReader and System.Windows.Markup.XamlWriter classes .

Basically I generate required markup elements at runtime and store it in a StringBuilder, parse the string builder to an XElement and submit that to a ContentControl as its content through XamlReader class.

I am using LINQ to SQL Classes item to get the required table and columns information through a stored procedure.

Step 1#: create a stored procedure in SQL to get required table and column information; this is very simple if we have some idea about using the information_schema schema
CREATE PROCEDURE dbo.spGetTableSchema
@table varchar(50)
c.table_name As TableName,
c.column_name As ColumnName,
c.data_type As DataType,
c.character_maximum_length As MaxLength,
CASE cu.column_name
WHEN null THEN 0
FROM information_schema.constraint_column_usage cu
INNER join information_schema.table_constraints ct
ON ct.constraint_name = cu.constraint_name
ct.constraint_type = 'PRIMARY KEY'
AND ct.table_name = c.table_name
AND cu.column_name = c.column_name
),0) AS IsPrimaryKey
FROM information_schema.columns c
INNER JOIN information_schema.tables t
ON c.table_name = t.table_name
WHERE @table = t.table_name and
(t.table_type = 'BASE TABLE' and not
(t.table_name = 'dtproperties') and not
(t.table_name = 'sysdiagrams'))
ORDER BY c.table_name, c.ordinal_position

Step #2: Open Visual Studio 2008 IDE and create a new WPF browser application and name it as DynamiWPFPage

Explore project view and it looks as follows, open the Page1.xaml in design mode and view the XML code

Step #3: Update the xaml (Page1.XAML) as specified below

Step #4: now we need to add the LINQ to SQL classes item to project. For this, right click on the project and select “Add new item”, from the list select LINQ to SQL classes. Name it as DynamicDataClasses.dbml

The LINQ to SQL Classes item designer will be opened automatically for DynamicDataClasses.dbml

Step #5: Click on View menu in VS2008 IDE and select Server Explorer, this shows the SQL connections list. In the Server Explorer, right click on Data Connections and click on Add new Connection. Then we need to provide the SQL server information to get connected to our SQL Server Northwind database (provide details as shown in the below screen shot).

Explore the newly created connection, select the spGetTableSchema stored procedure from SQL Server, drag and drop the procedure on LINQ to SQL Classes designer, it will be added to methods pane of the designer. Drag and drop the table “Customer” in the LINQ designer

Step #6: Add a new class with required properties (TableSchema) to designer as specified below. Change MaxLength property properties of TableSchema class as specified below

Nullable: make it True
Type : int(system.Int32)

Similarly IsPrimaryKey property Type property also change to int (system.Int32)

Right click on the spGetTableSchema procedure in Methods pane of LINW to SQL designer and selecte properties. Change the ReturnType to TableSchema

Step #7: Now open the Page1.xaml.cs file and add the below code to it. This example creates a Dynamic WPF for Customers table in Northwind database

I could not upload the code due to some technical problem, due to this i posted the code as image here. you can try with it, still you need any support, feel free to contact me

Hope this helps you