Creating Application using Windows Presentation Foundation (WPF) Commanding and ADO.NET Entity Framework - (Part I) Fetching Data

Posted by: Mahesh Sabnis , on 8/17/2010, in Category WPF
Views: 71574
Abstract: ADO.NET EF and WPF Commanding helps to build business applications using effective database model based programming and code less development with commands. In this article, we will explore these features.
Windows Presentation Foundation (WPF) is an awesome technology for providing better user experiences (UX) to end users. Line-of-Business (LOB) applications can be developed using WPF by using its various features like Databinding, Commanding etc. In .NET Framework 3.5 and 4.0, the developer community has been provided with a new data access feature known as ADO.NET Entity Framework (ADONET-EF). This framework provides mapping between Database objects (Tables, Views etc) and user defined classes (Entities).
In this article, I will create a class library with ADO.NET EF configured in it. This will map with my ‘Company’ database on SQL Server. From this database, ‘Employee’ table will be used for mapping. This library will also contain classes for calling methods from the entity framework mapped.
The script for creating the database has been included in the source code.
Establishing mapping between ADO.NET EF and Database.
 
Step 1: Open VS 2010 and create a blank solution, name it as ‘WPF_Commanding_ADONET_EF’.
Step 2: To this solution, add a new class library project, name it as ‘DataAccessLibrary’. In this project, right click and add a new ADO.NET Entity Data Model as shown below:
AddNewItem
The name I have given for this Data Model is ‘CompanyEDMX’. This will add a .edmx file in the project which help in design mapping. Once you click on ‘Add’ button, it will start a Wizard where the Tables for mapping from the database are selected. For this article, I have established mapping between ‘Department’ and ‘Employee’ table. The designer will be as shown below:
Database
The above step will create ‘CompanyEDMX.Designer.cs’ file which will contain classes derived from ‘ObjectContext’ class. This class is responsible for managing database connection and also defines public entity set properties for all tables mapped from the database. This file contains classes for every table selected form database, based upon table relationship. 
If you open the above .edmx in the xml, you will find the mapping between the Table and the Department/Employee classes. After the completion of this step, the App.Config file gets added to the project. This file will contain the connection string to the database.
Step 3: From the class library, rename ‘Class1.cs’ to ‘DataAccess.cs’. Write the following code in this class. This class contains methods which makes a call to ‘CompanyEntities’ class generated in the above steps. The code is as shown below:
C#
using System.Collections.Generic;
using System.Linq;
 
namespace DataAccessLibrary
{
    public class DataAccess
    {
        CompanyEntities objDataContext;
        public DataAccess()
        {
            objDataContext = new CompanyEntities(); 
        }
 
        public void CreateEmployee(Employee objEmp)
        {
            objDataContext.AddToEmployee(objEmp);
            objDataContext.SaveChanges();
        }
 
        public List<Employee> GetAllEmployees()
        {
            return objDataContext.Employee.ToList<Employee>();   
        }
 
        public List<Department> GetAllDepartments()
        {
            return objDataContext.Department.ToList<Department>();  
        }
 
        public List<Employee> GetAllEmployeeBeDeptName(string DeptName)
        {
            var Res = (from Emp in objDataContext.Employee
                       from Dept in objDataContext.Department
                       where Emp.DeptNo == Dept.DeptNo && Dept.Dname == DeptName
                       select Emp).ToList<Employee>();
            return Res;
        }
    }
}
 
VB.NET
Imports System.Collections.Generic
Imports System.Linq
 
Namespace DataAccessLibrary
      Public Class DataAccess
            Private objDataContext As CompanyEntities
            Public Sub New()
                  objDataContext = New CompanyEntities()
            End Sub
 
            Public Sub CreateEmployee(ByVal objEmp As Employee)
                  objDataContext.AddToEmployee(objEmp)
                  objDataContext.SaveChanges()
            End Sub
 
            Public Function GetAllEmployees() As List(Of Employee)
                  Return objDataContext.Employee.ToList(Of Employee)()
            End Function
 
            Public Function GetAllDepartments() As List(Of Department)
                  Return objDataContext.Department.ToList(Of Department)()
            End Function
 
            Public Function GetAllEmployeeBeDeptName(ByVal DeptName As String) As List(Of Employee)
                  Dim Res = (
                      From Emp In objDataContext.Employee , Dept In objDataContext.Department
                      Where Emp.DeptNo = Dept.DeptNo AndAlso Dept.Dname = DeptName
                      Select Emp).ToList(Of Employee)()
                  Return Res
            End Function
      End Class
End Namespace
Step 4: Build the project and make sure that it is error free.
 
Windows Presentation Foundation (WPF) client application for displaying Employee records.
 
In this task, I will create a WPF client application. This app will make a call to the Data Access Library created in the previous task. In this application, I have used Commanding feature of WPF for code less UI development. I have defined WPF User Controls which are used to display data fetched and inserted from and to the data access library. None of the User control contains any code behind. The complete parameter passing for insert operation and for data display is performed using Commanding feature of the Xaml button.
WPF provides ICommand interface, which helps to define operations to perform when any event is fired by a WPF element e.g. Button. This interface provides 2 methods named ‘CanExecute()’ and ‘Execute()’ and an event ‘CanExecuteChanged’. The method ‘CanExecute()’ returns Boolean result, based upon which the View (UI) is accessible for user interaction. ‘Execute()’ method gets executed when the ‘CanExecute()’ return ‘True’. The ‘Execute()’ methods makes call to data access. 
Step 1: To the current solution, add a new WPF application, name it as ‘WPF_ADONET_EF_Commanding’. Add the reference of the ‘DataAccessLibrary.dll’ in this project. Also copy the App.Config file generated in the class library in this application.
Step 2: In this project, add two new folders named as ‘ViewModel’ and ‘Views’.
Step 3: In the ViewModel folder, add the following class file:
-      AllEmployees_Command_ViewModel.cs: used to define the command class and the data fetch class from the data access for displaying all Employees.
Step 4: In the ‘Views’ folder add following two user control:
-      AllEmployeesView.xaml: User Control view for displaying all employees.
Step 5: Open ‘AllEmployees_Command_ViewModel.cs’ class file and create the following two classes:
-      AllEmployeeViewModel: used for making call to data access library.
-      AllEmployeesCommand: acts as a bridge between UI and the AllEmployeeViewModel class.
C#
using System;
using System.Collections.Generic;
using System.Windows.Input;
using DataAccessLibrary;
using System.Collections.ObjectModel;
 
namespace WPF_ADONET_EF_Commanding
{
    public class AllEmployeesCommand : ICommand
    {
 
        AllEmployeeViewModel EmpViewModel;
 
        public AllEmployeesCommand(AllEmployeeViewModel empViewModel)
        {
            EmpViewModel = empViewModel;
        }
        public bool CanExecute(object parameter)
        {
            bool action = false;
 
            if (EmpViewModel.Employees.Count >= 0)
            {
                action = true;
            }
 
            return action;
        }
        public event EventHandler CanExecuteChanged;
 
        public void Execute(object parameter)
        {
            EmpViewModel.ViweAllEmployees();
        }
    }
 
    public class AllEmployeeViewModel
    {
        public ObservableCollection<Employee> Employees { get; set; }
 
        DataAccess objDs;
 
        public AllEmployeeViewModel()
        {
            Employees = new ObservableCollection<Employee>();
            objDs = new DataAccess();
        }
 
        public ICommand AllEmployees
        {
            get
            {
                return new AllEmployeesCommand(this); 
            }
        }
        public void ViweAllEmployees()
        {
            List<Employee> lstEmp = objDs.GetAllEmployees();
 
            foreach (Employee Emp in lstEmp)
            {
                Employees.Add(Emp); 
            }
        }
    }
}
 
 
VB.NET
Imports System
Imports System.Collections.Generic
Imports System.Windows.Input
Imports DataAccessLibrary
Imports System.Collections.ObjectModel
 
Namespace WPF_ADONET_EF_Commanding
      Public Class AllEmployeesCommand
            Implements ICommand
 
            Private EmpViewModel As AllEmployeeViewModel
 
            Public Sub New(ByVal empViewModel As AllEmployeeViewModel)
                  Me.EmpViewModel = empViewModel
            End Sub
            Public Function CanExecute(ByVal parameter As Object) As Boolean Implements ICommand.CanExecute
                  Dim action As Boolean = False
 
                  If EmpViewModel.Employees.Count >= 0 Then
                        action = True
                  End If
 
                  Return action
            End Function
            Public Event CanExecuteChanged As EventHandler Implements ICommand.CanExecuteChanged
 
            Public Sub Execute(ByVal parameter As Object) Implements ICommand.Execute
                  EmpViewModel.ViweAllEmployees()
            End Sub
      End Class
 
      Public Class AllEmployeeViewModel
            Public Property Employees() As ObservableCollection(Of Employee)
 
            Private objDs As DataAccess
 
            Public Sub New()
                  Employees = New ObservableCollection(Of Employee)()
                  objDs = New DataAccess()
            End Sub
 
            Public ReadOnly Property AllEmployees() As ICommand
                  Get
                        Return New AllEmployeesCommand(Me)
                  End Get
            End Property
            Public Sub ViweAllEmployees()
                  Dim lstEmp As List(Of Employee) = objDs.GetAllEmployees()
 
                  For Each Emp As Employee In lstEmp
                        Employees.Add(Emp)
                  Next Emp
            End Sub
      End Class
End Namespace
 
(Note: In the above code, the namespace is the same as the application name. This helps to refer the class in the Xaml directly by registering this namespace in the xaml.)
Step 5: Open ‘AllEmployeeView.xaml’ and write define the following Xaml.
<UserControl x:Class="WPF_ADONET_EF_Commanding.AllEmployeesView"
             xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
             xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
             xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"
             xmlns:d="http://schemas.microsoft.com/expression/blend/2008"
             xmlns:DAL="clr-namespace:DataAccessLibrary;assembly=DataAccessLibrary"
             xmlns:viewmodel="clr-namespace:WPF_ADONET_EF_Commanding"
             mc:Ignorable="d"
             d:DesignHeight="400" d:DesignWidth="330">
    <UserControl.Resources>
        <!--Instance of the AllEmployees_Command_ViewModel class-->
        <viewmodel:AllEmployeeViewModel x:Key="EmpViewModel"></viewmodel:AllEmployeeViewModel>
    </UserControl.Resources>
    <Grid x:Name="grdMain" Width="320"
           DataContext="{Binding Source={StaticResource EmpViewModel}}">
        <Grid x:Name="grdChild" DataContext="{Binding Path=Employees}">
            <Button Content="Get All Employee" Height="39" HorizontalAlignment="Left"
                    Margin="30,0,0,0" Name="btnGetAllEmployee" VerticalAlignment="Top"
                    Width="130"
                     Command="{Binding Path=DataContext.AllEmployees,ElementName=grdMain}"/>
            <DataGrid AutoGenerateColumns="False" Height="239" HorizontalAlignment="Left"
                      Margin="10,68,0,0" Name="dgEmployee" VerticalAlignment="Top" Width="300"
                       DataContext="{Binding}"
                       ItemsSource="{Binding}" ColumnWidth="*">
                <DataGrid.Columns>
                    <DataGridTextColumn Binding="{Binding EmpNo}" Header="EmpNo" Width="40"/>
                    <DataGridTextColumn Binding="{Binding EmpName}" Header="EmpName" Width="100"/>
                    <DataGridTextColumn Binding="{Binding DeptNo}" Header="DeptNo" Width="40"/>
                    <DataGridTextColumn Binding="{Binding Salary}" Header="Salary" Width="*"/>
                </DataGrid.Columns>
            </DataGrid>
        </Grid>
    </Grid>
</UserControl>
 
 
Note: The class of the user control is directly present under the application namespace. The width * for the last column will fill the remaining width of the DataGrid.
In the above xaml code, concentrate on the Gray marked code. This explains the registration of the namespace ‘WPF_ADONET_EF_Commanding’ in the xaml so that classes under this namespace can be instantiated in the current xaml. The instance of the ‘AllEmployeeViewModel’ is defined under the user control resource. This instance is bound with the Grid named ‘grdMain’ so that all the public declaration under this class will be accessible to the xaml.
The Grid, ‘grdChild’ is bound with ‘Employee’, which is an ObservableCollection declared in ‘AllEmployeeViewModel’. In the button, the command property is bound with ‘AllEmployees’ property of the class, which returns ‘ICommand’ and executes ‘Execute()’ method of the ‘AllEmployeesCommand’ class. This methods then executes ‘ViewAllEmployees()’ method of the ‘AllEmployeeViewModel’ class.
This method makes call to the method from my data access library and retrieve data. This data is stored in ObservableCollection ‘Employees’ which is bound with Grid ‘grdChild’ and hence it will be available for the DataGrid ‘dgEmployee’ and the data will be displayed.
The user control does not contain any code behind. Thus the name Code-less development. This is the advantage of using the ‘Commanding’ of WPF in LOB applications.
Step 6: Open MainWindow.xaml and add the following Xaml inside it.
<Window x:Class="WPF_ADONET_EF_Commanding.MainWindow"
        xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
        xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
        Title="MainWindow" Height="350" Width="1036" Loaded="Window_Loaded">
    <Grid Width="850" Margin="1,1,1,1" x:Name="grdMain">
        <Grid.ColumnDefinitions>
            <ColumnDefinition Width="409*" />
            <ColumnDefinition Width="441*" />
        </Grid.ColumnDefinitions>
        <Grid x:Name="grdAllEmp" Grid.Column="0"></Grid>
        <Grid x:Name="grdNewEmp" Grid.Column="1"></Grid>
    </Grid>
</Window>
 
Step 7: Write the following code in the loaded event of the window:
C#
private void Window_Loaded(object sender, RoutedEventArgs e)
{
            AllEmployeesView objAllEmpView = new AllEmployeesView();
            grdAllEmp.Children.Add(objAllEmpView);
}
 
VB.NET
Private Sub Window_Loaded(ByVal sender As Object, ByVal e As RoutedEventArgs)
                  Dim objAllEmpView As New AllEmployeesView()
                  grdAllEmp.Children.Add(objAllEmpView)
End Sub
Step 8: Run the application, and the following result will be displayed:
MainWindow

In the next article, I will explain inserting data using WPF Commanding and ADO.NET EF. Update: The 2nd part of this article can be viewed over here Creating Application using WPF Commanding and ADO.NET Entity Framework – (Part 2) Inserting Data
 
Conclusion: ADO.NET EF and WPF Commanding  helps to build business applications using effective database model based programming and code less development with commands.
The entire source code of this article can be downloaded over here
What Others Are Reading!
Was this article worth reading? Share it with fellow developers too. Thanks!
Share on LinkedIn
Share on Google+

Author
Mahesh Sabnis is a DotNetCurry author and Microsoft MVP having over 17 years of experience in IT education and development. He is a Microsoft Certified Trainer (MCT) since 2005 and has conducted various Corporate Training programs for .NET Technologies (all versions). Follow him on twitter @maheshdotnet


Page copy protected against web site content infringement 	by Copyscape




Feedback - Leave us some adulation, criticism and everything in between!
Comment posted by A problem on Tuesday, February 8, 2011 12:49 PM
I am getting an exception in AllEmployeeView.xaml related to ef connection string blocking my design view can you please help me in this
Comment posted by Brett Ryan on Thursday, April 28, 2011 11:17 PM
You should be closing your context as soon as possible and not holding a reference to it, this could cause a nasty memory leak if you do.

Surround your context in a using statement.

using (CompanyEntities ctx = new CompanyEntities()) {
    ctx.AddToEmployee(objEmp);
    ctx.SaveChanges();
}

A less serious observation is that you shouldn't return a concrete type in your service, i.e. return IEnumerable<Employee> or at most IList<Employee> in GetAllEmployees.
Comment posted by rajesh on Wednesday, May 9, 2012 7:24 AM
started learning .net now,,getting difficult to me lack of prog skills using oops concepts@
how can i learn
Comment posted by Vema Reddy on Tuesday, August 7, 2012 4:41 AM
Get All employees is not working
can you tell me where i had missed code
for that button i had given that

Command="{Binding Path=DataContext.AllEmployees,ElementName=grdMain}"/>

but still it is not working

And one more thing is it possible direct like geting data from database in the form of dataset or datatable and then convert that data into list of object and then binding to datagrid.
Comment posted by Vema Reddy on Tuesday, August 7, 2012 5:49 AM
Get All employees is not working
can you tell me where i had missed code
for that button i had given that

Command="{Binding Path=DataContext.AllEmployees,ElementName=grdMain}"/>

but still it is not working

And one more thing is it possible direct like geting data from database in the form of dataset or datatable and then convert that data into list of object and then binding to datagrid.
Comment posted by Vema Reddy on Tuesday, August 7, 2012 5:49 AM
Get All employees is not working
can you tell me where i had missed code
for that button i had given that

Command="{Binding Path=DataContext.AllEmployees,ElementName=grdMain}"/>

but still it is not working

And one more thing is it possible direct like geting data from database in the form of dataset or datatable and then convert that data into list of object and then binding to datagrid.
Comment posted by rajasekhar on Tuesday, October 16, 2012 5:20 AM
nice/................................
Comment posted by rajasekhar on Tuesday, October 16, 2012 5:20 AM
nice/................................
Comment posted by rajasekhar on Tuesday, October 16, 2012 5:20 AM
nice/................................
Comment posted by maryam on Monday, May 20, 2013 4:10 AM
Hi,thanks for your practical tutorial.I created all classes and everything and copy the app.config from classlibrary to main application but still returns this error :
The specified named connection is either not found in the configuration, not intended to be used with the EntityClient provider, or not valid.

please help me.
thank you very much.
Comment posted by radfaasdf on Thursday, June 6, 2013 1:28 AM
asdfasd fas dfasdfasdfa  asdfasdfasdf asdfasdfasdf asdfasdfasdf asdfasdfasdf asdfasdfasdfasdf asdfasdfasdfasd  asdfasdfasdf   asdfasdfasdf  asdfasdfasdfasdfaasdfasdf  asdfasdfasdf  asdfasdfasdfasdf  asdfasdfasdfasdfasdf sdfasdfasdf  asdfasdfsadf   asdfasdfasdfasdf
Comment posted by joe on Wednesday, February 19, 2014 6:38 AM
nice article, but I dont't download source file
would you please check out source file
Comment posted by Sajjad Aslam on Wednesday, June 25, 2014 7:44 AM
good well explained

Categories

JOIN OUR COMMUNITY

POPULAR ARTICLES

FREE .NET MAGAZINES

Free DNC .NET Magazine

Tags

JQUERY COOKBOOK

jQuery CookBook