Before continuing this article, I highly recommend reading these previous parts:

CRUD using DataGrid

So far, we have seen the basic operations in the DataGrid working with a collection of objects. Now, let's work with a database. For our sample, we will use an existing Northwind database provided by Microsoft.

Northwind Database

In Figure 15 we can see some core tables of the Northwind database.


Figure 15

  1. Suppliers table contains the information about those who supply to the company
  2. Customers table contains the Northwind customers details who buy from Northwind
  3. Employees contains details of Northwind traders that work for Northwind
  4. Products contain the products that Northwind trades
  5. Shippers contains details of the shippers who ship the products from the traders to the end-customers
  6. Orders table contains all the information related to Northwind sales and purchases
  7. Order Details contains the information for all the orders of which products are associated with a specific order

LINQ to Data

Language-Integrated Query (LINQ) was introduced in Visual Studio 2008 and .NET Frmework 3.5. LINQ is used for quering data and it also support the Add, Update and Delete. LINQ can also be used to access various types of data, including XML, SQL Data source, web services and so on.

Let's say, we have an array of integers as in:

int[] numbers = new int[7] { 0, 1, 2, 3, 4, 5, 6 };
Listing 25

The Listing 26 is the LINQ query that selects the numbers based on the where clause.
  1. var numResults = from num in numbers where (num % 2) == 0 select num;
Listing 26

Now, let's review a typical SQL query as listed in Listing 27, that expects all the columns from the Customers table where the City is Redmond.
  1. SELECT * FROM Customers WHERE City='Redmond'
Listing 27

Listing 27 can be converted to a LINQ query that looks as in Listing 28.
  1. var CustomersResult =
  2. from cust in db.Customers
  3. where cust.City == "Redmond"
  4. select cust;

Listing 27

Unlike a SQL query that starts with a SELECT statement, the LINQ query starts with "from" followed by the "where" and "select" statements.

You can learn more about LINQ here >

Access Data

Let's set up the database connection using LINQ in our application to access the SQL Server database.

Right-click on the project then Add New Item and choose “LINQ to SQL Classes” that the file has the (.dbml) extension. See Figure 16.


Figure 16

Select the LINQ to SQL Classes file. See Figure 17.


Figure 17

A dbml file will added to the project. See Figure 18.


Figure 18

Now open the Server Explorer to configure the database connection. See Figure 19.


Figure 19

Click on “Connect to Database” as shown in Figure 20.


Figure 20
Follow the next few steps.


Figure 21


Figure 22

Drag and drop all required database tables to the dbml designer surface to generate database classes that can be used in your code.

When you drag and drop the tables onto the designer surface a warning message will be shown that is given below, click Yes to add database connection string information to the web.config file.


Figure 23


Figure 24

Now open the WPF XAML page and add a “DataGrid” onto the design surface.

Listing 28
  1. <DataGrid x:Name="EmployeeDataGrid" HorizontalAlignment="Left" Height="491" Margin="10,10,0,0" VerticalAlignment="Top" Width="741" AutoGe nerateColumns="False" ItemsSource="{Binding}">
  2. <DataGrid.Columns>
  3. <DataGridTextColumn Binding="{Binding EmployeeID}" Width="100" Header="Employee ID" IsReadOnly="True" />
  4. <DataGridTextColumn Binding="{Binding LastName}" Width="100" Header="Last Name"/>
  5. <DataGridTextColumn Binding="{Binding FirstName}" Width="100" Header="First Name"/>
  6. <DataGridTextColumn Binding="{Binding Title}" Width="150" Header="Title"/>
  7. <DataGridTemplateColumn Header="Birth Date">
  8. <DataGridTemplateColumn.CellTemplate>
  9. <DataTemplate>
  10. <TextBlock Text="{Binding BirthDate, StringFormat=\{0:d\}}" />
  11. </DataTemplate>
  12. </DataGridTemplateColumn.CellTemplate>
  13. <DataGridTemplateColumn.CellEditingTemplate>
  14. <DataTemplate>
  15. <DatePicker SelectedDate ="{Binding BirthDate}" />
  16. </DataTemplate>
  17. </DataGridTemplateColumn.CellEditingTemplate>
  18. </DataGridTemplateColumn>
  19. </DataGrid.Columns>
  20. </DataGrid>
On the window load event “Window_Loaded” write the LINQ select query to get a result set and bind it with the WPF DataGrid.

Listing 29
  1. private void Window_Loaded(object sender, RoutedEventArgs e)
  2. {
  3. EmployeeDataGrid.ItemsSource = GetAllEmployees();
  4. }
Listing 30
  1. private ObservableCollection<Employee> GetAllEmployees()
  2. {
  3. var empResult = from emp in context.Employees select emp;
  4. return new ObservableCollection<Employee>(empResult);
  5. }
Result:


Figure 25

Add, Update, Delete Data

LINQ also supports inserting, updating and deleting in “LINQ to SQL”.

Listing 30 shows inserting a new record using LINQ to SQL.

Listing 30
  1. EmployeeNorthwindDataContext context = new EmployeeNorthwindDataContext();
  2. employee.FirstName = emp.FirstName;
  3. employee.LastName = emp.LastName;
  4. employee.Title = emp.Title;
  5. employee.BirthDate = emp.BirthDate;
  6. employee.Photo = emp.Photo;
  7. context.Employees.InsertOnSubmit(employee);
  8. context.SubmitChanges();
Listing 31 shows updating a new record using LINQ to SQL.

Listing 31

EmployeeNorthwindDataContext context = new EmployeeNorthwindDataContext();
  1. employee.EmployeeID = emp.EmployeeID;
  2. employee.FirstName = emp.FirstName;
  3. employee.LastName = emp.LastName;
  4. employee.Title = emp.Title;
  5. employee.BirthDate = emp.BirthDate;
  6. employee.Photo = emp.Photo;
  7. context.SubmitChanges();
Listing 32 shows deleting a new record using LINQ to SQL.

Listing 32

context.Employees.DeleteOnSubmit(employee);

Listing 33 shows adding binding properties with columns.

Gets or sets a value that indicates whether to raise the TargetUpdated event when a value is transferred from the binding source to the binding target and update them.

Listing 33
  1. Binding="{Binding EmployeeID, NotifyOnTargetUpdated=True,Mode=TwoWay, UpdateSourceTrigger=PropertyChanged}"
DataGrid

Listing 34
  1. <DataGrid x:Name="EmployeeDataGrid" HorizontalAlignment="Left" Height="491" Margin="10,10,0,0" VerticalAlignment="Top" Width="760"
  2. AutoGenerateColumns="False"
  3. ItemsSource=" {Binding}" RowEditEnding="EmployeeDataGrid_RowEditEnding" AddingNewItem="EmployeeDataGrid_AddingNewItem" PreviewKeyDow n="EmployeeDataGrid _PreviewKeyDown" BeginningEdit="EmployeeDataGrid_BeginningEdit">
  4. <DataGrid.Columns>
  5. <DataGridTextColumn Binding=" {Binding EmployeeID, NotifyOnTargetUpdated=True,Mode=TwoWay, UpdateSourceTrigger=PropertyChanged}" Width="100" Header="Employee ID" IsReadOnly="True" />
  6. <DataGridTextColumn Binding=" {Binding LastName, NotifyOnTargetUpdated=True, Mode=TwoWay, UpdateSourceTrigger=PropertyChanged}" W idth="100" Header="Last Name"/>
  7. <DataGridTextColumn Binding=" {Binding FirstName, NotifyOnSourceUpdated=True, Mode=TwoWay,UpdateSourceTrigger=PropertyChanged}" W idth="100" Header="First Name"/>
  8. <DataGridTextColumn Binding=" {Binding Title, NotifyOnTargetUpdated=True,Mode=TwoWay, UpdateSourceTrigger=PropertyChanged}" Width ="150" Header="Title"/>
  9. <DataGridTemplateColumn Header="Birth Date">
  10. <DataGridTemplateColumn.CellTemplate>
  11. <DataTemplate>
  12. <TextBlock Text="{Binding BirthDate, StringFormat=\{0:d\}}" />
  13. </DataTemplate>
  14. </DataGridTemplateColumn.CellTemplate>
  15. <DataGridTemplateColumn.CellEditingTemplate>
  16. <DataTemplate>
  17. <DatePicker SelectedDate =" {Binding BirthDate, NotifyOnSourceUpdated=True,Mode=TwoWay, UpdateSourceTrigger=PropertyChanged}" />
  18. </DataTemplate>
  19. </DataGridTemplateColumn.CellEditingTemplate>
  20. </DataGridTemplateColumn>
  21. <DataGridTemplateColumn Header="Image" Width="120" IsReadOnly="True">
  22. <DataGridTemplateColumn.CellTemplate>
  23. <DataTemplate>
  24. <Image Stretch="UniformToFill" Name="Photo" Source=" {Binding Photo, Converter= {StaticResource imageConverter}, Mode=Two Way, ValidatesOnExceptions=true, NotifyOnValidationError=true, UpdateSourceTrigger=PropertyChanged}"/>
  25. </DataTemplate>
  26. </DataGridTemplateColumn.CellTemplate>
  27. </DataGridTemplateColumn>
  29. <DataGridTemplateColumn Header="Upload">
  30. <DataGridTemplateColumn.CellTemplate>
  31. <DataTemplate>
  32. <StackPanel>
  33. <Button Content="Browse" Height="20" Margin="5"
  34. Name="buttonBrowse" VerticalAlignment="Top" Width="58" Click="buttonBrowse_Click" />
  35. </StackPanel>
  36. </DataTemplate>
  37. </DataGridTemplateColumn.CellTemplate>
  38. </DataGridTemplateColumn>
  39. </DataGrid.Columns>
  40. </DataGrid>
Listing 35 shows adding a new record/update on “RowEditEnding” event.

Listing 35
  1. private void EmployeeDataGrid_RowEditEnding(object sender, DataGridRowEditEndingEventArgs e)
  2. {
  3. Employee employee = new Employee();
  4. Employee emp = e.Row.DataContext as Employee;
  6. if (emp != null)
  7. {
  8. if (emp.EmployeeID > 0)
  9. {
  10. isInsert = false;
  11. }
  12. else
  13. {
  14. isInsert = true;
  15. }
  16. }
  18. if (isInsert)
  19. {
  20. var InsertRecord = MessageBox.Show("Do you want to add " + emp.FirstName + "?", "Confirm", MessageBoxButton.YesNo, MessageBoxImage. Question);
  21. if (InsertRecord == MessageBoxResult.Yes)
  22. {
  23. employee.FirstName = emp.FirstName;
  24. employee.LastName = emp.LastName;
  25. employee.Title = emp.Title;
  26. employee.BirthDate = emp.BirthDate;
  27. employee.Photo = emp.Photo;
  28. context.Employees.InsertOnSubmit(employee);
  29. context.SubmitChanges();
  30. EmployeeDataGrid.ItemsSource = GetAllEmployees();
  31. MessageBox.Show(employee.FirstName + " " + employee.LastName + " has added sucessfully.", "Add New Employee", MessageBoxButton.O K, MessageBoxImage.Information);
  32. }
  33. else
  34. EmployeeDataGrid.ItemsSource = GetAllEmployees();
  35. }
  36. context.SubmitChanges();
  37. }
Listing 36 shows addition of a delete record on “PreviewKeyDown” event.

Listing 36
  1. private void EmployeeDataGrid_PreviewKeyDown(object sender, KeyEventArgs e)
  2. {
  3. if (e.Key == Key.Delete && !isEdit)
  4. {
  5. var grid = (DataGrid)sender;
  6. if (grid.SelectedItems.Count > 0)
  7. {
  8. var result = MessageBox.Show("Are you sure you want to delete this employee?", "Deleting Records", MessageBoxButton.YesNo, Messa geBoxImage.Exclamation);
  9. if (result == MessageBoxResult.Yes)
  10. {
  11. foreach (var row in grid.SelectedItems)
  12. {
  13. Employee employee = row as Employee;
  14. context.Employees.DeleteOnSubmit(employee);
  15. }
  16. context.SubmitChanges();
  17. MessageBox.Show("Employee deleted sucessfully.", "Delete Employee", MessageBoxButton.OK, MessageBoxImage.Information);
  18. }
  19. else
  20. EmployeeDataGrid.ItemsSource = GetAllEmployees();
  21. }
  22. }
  23. }
Listing 37 shows uploading a photo using the browse button in a DataGrid.

Listing 37
  1. private void buttonBrowse_Click(object sender, RoutedEventArgs e)
  2. {
  3. Employee employee = new Employee();
  4. FileDialog OpenFileDialog1 = new OpenFileDialog();
  5. OpenFileDialog1.Title = "Insert Image";
  6. OpenFileDialog1.InitialDirectory = "c:\\";
  7. // Below code will filter all file in open folder as per given file extension
  8. OpenFileDialog1.Filter = "JPEG (*.jpg;*.jpeg;*.jpe)|*.jpg;*.jpeg;*.jpe|PNG (*.png)|*.png|TIFF (*.tiff)|*.tiff|GIF (*.gif)|*.gif|All Fi les (*.*)|*.*";
  9. if (OpenFileDialog1.ShowDialog() == true)
  10. {
  11. var clipBoardData = Clipboard.GetDataObject();
  12. var emp = ((FrameworkElement)sender).DataContext as Employee;
  14. if (emp != null)
  15. {
  16. emp.Photo = GetPhoto(OpenFileDialog1.FileName);
  18. if (emp.EmployeeID > 0)
  19. {
  20. var Result = MessageBox.Show("Are you sure you want to upload this photo.", "Photo", MessageBoxButton.YesNo, MessageBoxImage. Exclamation);
  21. if (Result == MessageBoxResult.Yes)
  22. {
  23. employee.EmployeeID = emp.EmployeeID;
  24. employee.FirstName = emp.FirstName;
  25. employee.LastName = emp.LastName;
  26. employee.Title = emp.Title;
  27. employee.BirthDate = emp.BirthDate;
  28. employee.Photo = emp.Photo;
  29. context.SubmitChanges();
  30. EmployeeDataGrid.ItemsSource = GetAllEmployees();
  31. MessageBox.Show("Photo uploaded sucessfully!", "Photo Upload", MessageBoxButton.OK, MessageBoxImage.Information);
  32. }
  33. }
  35. }
  36. else
  37. {
  38. MessageBox.Show("Can't uploaded photo, Add the employee first!", "Add Employee", MessageBoxButton.OK, MessageBoxImage.Informatio n);
  39. }
  41. }
  42. }
Listing 38 shows converting an image into a byte [].

Listing 38
  1. public static byte[] GetPhoto(string filePath)
  2. {
  3. FileStream stream = new FileStream(
  4. filePath, FileMode.Open, FileAccess.Read);
  5. BinaryReader reader = new BinaryReader(stream);
  7. byte[] photo = reader.ReadBytes((int)stream.Length);
  9. reader.Close();
  10. stream.Close();
  12. return photo;
  13. }
Binary Image Converter

It will convert an image data from database into an image to display in a DataGrid. See Listing 39.

Listing 39
  1. public object Convert(object value, Type targetType, object parameter, CultureInfo culture)
  2. {
  3. if (value != null)
  4. {
  5. byte[] byteArray = (byte[])value;
  7. if (byteArray == null)
  8. return null;
  9. BitmapImage image = new BitmapImage();
  10. using (MemoryStream imageStream = new MemoryStream())
  11. {
  12. imageStream.Write(byteArray, 0, byteArray.Length);
  13. imageStream.Seek(0, System.IO.SeekOrigin.Begin);
  14. image.BeginInit();
  15. image.CacheOption = BitmapCacheOption.OnLoad;
  16. image.StreamSource = imageStream;
  17. image.EndInit();
  18. image.Freeze();
  19. }
  20. return image;
  21. }
  22. return null;
  23. }
To set the DataGrid in insertion and update mode.

Listing 40
  1. EmployeeNorthwindDataContext context = new EmployeeNorthwindDataContext();
  2. bool isInsert = false;
  3. bool isEdit = false;
Listing 41
  1. private void EmployeeDataGrid_AddingNewItem(object sender, AddingNewItemEventArgs e)
  2. {
  3. isInsert = true;
  4. }
  5. private void EmployeeDataGrid_BeginningEdit(object sender, DataGridBeginningEditEventArgs e)
  6. {
  7. isEdit = true;
  8. }
Sort, Filter, Group, Find

DataGrid also supports the sorting, grouping and filtering of data.

Sorting data in DataGrid

The CanUserSortColumns property allows the user to sort data in a datagrid by column.

CanUserSortColumns="True"

Listing 42 shows grouping in a DataGrid.
  1. private ObservableCollection<Employee> GetAllEmployees()
  2. {
  3. var empResult = from emp in context.Employees select emp;
  4. return new ObservableCollection<Employee>(empResult);
  5. }
  6. private void Window_Loaded(object sender, RoutedEventArgs e)
  7. {
  8. var EmployeeData = GetAllEmployees();
  9. ListCollectionView collection = new ListCollectionView(EmployeeData);
  10. collection.GroupDescriptions.Add(new PropertyGroupDescription("Title"));
  11. EmployeeDataGrid.ItemsSource = collection;
  12. }
Listing 42

Listing 43 shows a style for grouping.
  1. <Window.Resources>
  2. <Style x:Key="GroupHeaderStyle" TargetType="{x:Type GroupItem}">
  3. <Setter Property="Template">
  4. <Setter.Value>
  5. <ControlTemplate TargetType="{x:Type GroupItem}">
  6. <Expander x:Name="exp" IsExpanded="True"
  7. Background="White"
  8. Foreground="Black">
  9. <Expander.Header>
  10. <TextBlock Text="{Binding Title}"/>
  11. </Expander.Header>
  12. <ItemsPresenter />
  13. </Expander>
  14. </ControlTemplate>
  15. </Setter.Value>
  16. </Setter>
  17. </Style>
  18. </Window.Resources>
Listing 43

Listing 44 shows Apply style in a DataGrid for grouping.
  1. <DataGrid.GroupStyle>
  2. <GroupStyle ContainerStyle="{StaticResource GroupHeaderStyle}">
  3. <GroupStyle.Panel>
  4. <ItemsPanelTemplate>
  5. <DataGridRowsPresenter/>
  6. </ItemsPanelTemplate>
  7. </GroupStyle.Panel>
  8. </GroupStyle>
  9. </DataGrid.GroupStyle>
Listing 44

The following shows the DataGrid XAML code with a grouping style.
  1. <Grid>
  2. <DataGrid x:Name="EmployeeDataGrid" HorizontalAlignment="Left" Height="491" Margin="10,10,0,0" VerticalAlignment="Top" Width="760" Can UserSortColumns="True"
  3. AutoGenerateColumns="False"
  4. ItemsSource=" {Binding}" RowEditEnding="EmployeeDataGrid_RowEditEnding" AddingNewItem="EmployeeDataGrid_AddingNewItem" PreviewKey Down="EmployeeDataGrid_PreviewKeyDown" BeginningEdit="EmployeeDataGrid_BeginningEdit">
  5. <DataGrid.Columns>
  6. <DataGridTextColumn Binding=" {Binding EmployeeID, NotifyOnTargetUpdated=True,Mode=TwoWay, UpdateSourceTrigger=PropertyChanged }" Width="100" Header="Employee ID" IsReadOnly="True" />
  7. <DataGridTextColumn Binding=" {Binding LastName, NotifyOnTargetUpdated=True, Mode=TwoWay, UpdateSourceTrigger=PropertyChanged} " Width="100" Header="Last Name"/>
  8. <DataGridTextColumn Binding=" {Binding FirstName, NotifyOnSourceUpdated=True, Mode=TwoWay,UpdateSourceTrigger=PropertyChanged} " Width="100" Header="First Name"/>
  9. <DataGridTextColumn Binding=" {Binding Title, NotifyOnTargetUpdated=True,Mode=TwoWay, UpdateSourceTrigger=PropertyChanged}" Wi dth="150" Header="Title"/>
  10. <DataGridTemplateColumn Header="Birth Date">
  11. <DataGridTemplateColumn.CellTemplate>
  12. <DataTemplate>
  13. <TextBlock Text="{Binding BirthDate, StringFormat=\{0:d\}}" />
  14. </DataTemplate>
  15. </DataGridTemplateColumn.CellTemplate>
  16. <DataGridTemplateColumn.CellEditingTemplate>
  17. <DataTemplate>
  18. <DatePicker SelectedDate =" {Binding BirthDate, NotifyOnSourceUpdated=True,Mode=TwoWay, UpdateSourceTrigger=PropertyChanged }" />
  19. </DataTemplate>
  20. </DataGridTemplateColumn.CellEditingTemplate>
  21. </DataGridTemplateColumn>
  22. </DataGrid.Columns>
  23. <DataGrid.GroupStyle>
  24. <GroupStyle ContainerStyle="{StaticResource GroupHeaderStyle}">
  25. <GroupStyle.Panel>
  26. <ItemsPanelTemplate>
  27. <DataGridRowsPresenter/>
  28. </ItemsPanelTemplate>
  29. </GroupStyle.Panel>
  30. </GroupStyle>
  31. </DataGrid.GroupStyle>
  32. </DataGrid>
Result

Result of all employees group by “Title”:


Filtering/Finding Data in DataGrid

Get the instance of CollectionView from DataGrid and apply a filter on this collection view.

Listing 45 shows getting the collection view from a DataGrid.
  1. ICollectionView cv = CollectionViewSource.GetDefaultView(EmployeeDataGrid.ItemsSource);
Listing 45

Listing 46 shows applying a filter on a CollectionView.
  1. cv.Filter = o =>
  2. {
  3. Employee a = o as Employee;
  4. if (t.Name == "txtId")
  5. return (a.EmployeeID == Convert.ToInt32(filter));
  6. return (a.LastName.ToUpper().StartsWith(filter.ToUpper()));
  7. };
Listing 46

Listing 47 shows adding a TextBox to the header of the LastName column to find the last name.
  1. <DataGridTextColumn
  2. Header="Last Name"
  3. Width="150"
  4. Binding="{Binding LastName, NotifyOnTargetUpdated=True, Mode=TwoWay, UpdateSourceTrigger=PropertyChanged}" >
  5. <DataGridTextColumn.ElementStyle>
  6. <Style TargetType="TextBlock">
  7. <Setter Property="TextWrapping" Value="Wrap"/>
  8. </Style>
  9. </DataGridTextColumn.ElementStyle>
  10. <DataGridTextColumn.EditingElementStyle>
  11. <Style TargetType="TextBox">
  12. <Setter Property="Foreground" Value="Blue"/>
  13. </Style>
  14. </DataGridTextColumn.EditingElementStyle>
  15. <DataGridTextColumn.HeaderTemplate>
  16. <DataTemplate>
  17. <StackPanel Orientation="Horizontal">
  18. <TextBlock Text="{Binding Content, RelativeSource=
  19. {RelativeSource Mode=TemplatedParent}}"/>
  20. <TextBox x:Name="txtLastName" Width="100" TextChanged="txtLastName_TextChanged" />
  21. </StackPanel>
  22. </DataTemplate>
  23. </DataGridTextColumn.HeaderTemplate>
  24. </DataGridTextColumn>
Listing 47
  1. private void txtLastName_TextChanged(object sender, TextChangedEventArgs e)
  2. {
  3. TextBox t = (TextBox)sender;
  4. string filter = t.Text;
  5. ICollectionView cv = CollectionViewSource.GetDefaultView(EmployeeDataGrid.ItemsSource);
  6. if (filter == "")
  7. cv.Filter = null;
  8. else
  9. {
  10. cv.Filter = o =>
  11. {
  12. Employee a = o as Employee;
  13. if (t.Name == "txtId")
  14. return (a.EmployeeID == Convert.ToInt32(filter));
  15. return (a.LastName.ToUpper().StartsWith(filter.ToUpper()));
  16. };
  17. }
  18. }

Summary

This is the last article of the Mastering WPF DataGrid series. In this article, we saw how to build CRUD operations for a DataGrid.

<<Previous article: Mastering WPF DataGrid in a Day: Hour 7 Data Template