Building A Data Driven App Using WPF XamDataGrid

A typical rich data-driven Windows client app includes a backend database and a front-end client UI. The app connects to a database, reads the database, displays records in data controls, and implements CRUD operations. The app may also include some advanced UI operations such as sorting, searching, filtering, multiple views, and data export.

WPF provides several built-in data controls including the DataGrid control. Unfortunately, the built-in WPF controls aren’t rich enough to build a fully functional rich data-driven application in a limited time. However, there are several third-party component vendors that provide rich WPF data controls and one of those component vendors is Infragistics.

In this tutorial. I will show you how to build a complete data-driven WPF app using Visual Studio 2017, C#, and Infragistics DataGrid control, XamDataGrid.

The tutorial includes the following topics:

Introduction to the XamDataGrid control
Databinding - Connect and load data from a database
Show selected columns
Change themes
Filter data on various column values
Advanced data filtering
Grouping
Sorting
CRUD (Create, Read, Update, and Delete) data operations
Make data read-only
Export data to Excel

Why Third-Party Controls

Building great looking fully-functional complex data-driven applications is a time-consuming affair. Using third-party controls to build the complex functionality your app needs can save you a ton of time.

Most of the third-party component vendors are experts in their areas. Their code is optimal and follows industry standard best practices and guidelines.

Introduction to XamDataGrid

The Infragistics product, Ultimate UI for WPF provides rich, fast, modern controls for WPF. The Ultimate UI for WPF product consists 80+ WPF controls and starts at the US $899 with standard support. Some of the popular controls include Data Grid, Data Chart, Dock Manager, Ribbon, Schedule, Pivot Grid, Tree List, Gauges, Menus, Data Entry controls, Data Visualization controls, Schedulers, and Styles and Themes.

Once downloaded and installed, the controls are accessible via Visual Studio Toolbox, like any other WPF control. You can download a free 30-day trial version of the product. See Figure 1.

Figure 1.

Now, you can use all of these controls like any other WPF control by dragging and dropping them to a window or simply typing XAML manually.

As you can see from Figure 1, Infragistics WPF controls starts with XamControlName and the XamDataGrid is the DataGrid control. XamDataGrid provides built-in data-binding and data operations such as filtering, search, sorting, and others.

Setting Initial Layout

One of the first operations we do on a data-bound control is, to bind the control to a data source and load data into the control.

Open Visual Studio and create a new “WPF App” project by selecting File > New > Project >Visual C# > Windows Classic Desktop > WPF App (.NET Framework).

Give your project a name and click OK. My project name is xamDataGridDemo. See Figure 2.

WPF

Figure 2.

Once the project is created and loaded in Visual Studio, open Visual Studio Toolbox. Go View-> Toolbox or use shortcut key (Ctrl+Alt+x).

In Toolbox, you should see Infragistics 17.2 WPF category and all controls loaded below. See Figure 3.

Note: The version may be different depending on the version you’ve installed on your machine.

Figure 3.

Now, drag and drop a xamDataGrid control to the WPF designer. See Figure 4.

Figure 4.

Now right click on the xamDataGrid control and under Layout, click Reset All, so the xamDataGrid gets the entire view. See Figure 5.

Figure 5.

Once the layout is reset, you will see the xamDataGrid takes the entire design surface. You may also notice the Height and the Width properties of the xamDataGrid are set to a specific height and width based on the designer. See Figure 6.

Figure 6.

Now, we need to name our control. I set the Name property of the xamDataGrid to “xamDataGridControl”. See Figure 7.

Figure 7.

Data Binding

Now, our goal is to bind the xamDataGrid control to a data source so we can load data in the grid control. We’re going to use C# and ADO.NET to connect to a SQL “Northwind” database.

Note: The Northwind database is a relational SQL Server database provided by Microsoft that contains the sales data for Northwind Traders, a fictitious specialty foods export-import company. You may download the Northwind database from this link.

https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/linq/downloading-sample-databases

Each database connection in ADO.NET requires a connection string that consists of a database name, user id, password, and access information. A connection string is usually written in the configuration file. Our connection string looks like the following:

Data Source=.;Initial Catalog=NORTHWND;User ID=sa;Password=Password$2

Now, let’s bind the xamDataGrid control to a data source so we can load data in the grid control. We’re going to use C# and ADO.NET to connect to a SQL “Northwind” database. The code snippet listed in Listing 1 is a method that connects to a database and loads data in a DataSet and binds the default view of the first table to the control.

private void PopulateDataGrid() {
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["DatabaseConnectionString"].ToString());
SqlDataAdapter adap = new SqlDataAdapter("select * from Orders", con);
DataSet ds = new DataSet();
adap.Fill(ds);
if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0) {
xamDataGridControl.DataSource = ds.Tables[0].DefaultView;
}
}

Now, let’s build and run the app.

You will see a fully functional data bound xamDataGrid control that displays data from the database. See Figure 8.

Figure 8.

Now, let’s customize our grid according to our need.

Show Selected Columns

By default, the grid displays all columns available in a DataTable but we can show and hide columns according to our requirement.

Click on the icon at the top right of the xamDataGrid to view its settings. See Figure 9.

Figure 9.

Then click Settings > FieldLayoutSettigns. See Figure 10.

Figure 10.

Next, click Appearance and uncheck AutoGeneratedFields checkbox. See Figure 11.

Figure 11.

Now, if you review your XAML code, you will see AutoGenerateFields="False" for FieldLayoutSettings.

<Custom:XamDataGrid.FieldLayoutSettings>
<Custom:FieldLayoutSettings AutoGenerateFields="False" />
</Custom:XamDataGrid.FieldLayoutSettings>

See Listing 2.

Listing 2.

Now, to select the columns, select xamDataGrid and press “F4” to open the Properties window. Find the Miscellaneous section, click on the FieldLayouts and click on “…” in Collections dropdown. See Figure 12.

Figure 12.

That launches the Collections windows where we can set FieldLayouts of various fields. You can see in the bottom of the window, there is an option to add a FieldLayout, move up and down to change a field position, and the Add button to add. The initial layout will be added to the 0^th position. Select the layout and click the OK button to confirm. See Figure 13.

Figure 13.

The above action adds a Custom:FieldLayout to the FieldLayouts collection in XAML. You can also add this code manually by typing in the XAML file direct.

<Custom:XamDataGrid.FieldLayouts>
<Custom:FieldLayout/>
</Custom:XamDataGrid.FieldLayouts>

See Listing 3.

Listing 3.

Now, we need to add the fields (columns) that we want to show in our xamDataGrid. Select a FieldLayout and press F4 to open the Properties window. Under the “Miscellaneous” section click on the “FieldItems” “…” item. See Figure 14.

Figure 14.

Select the field type “Field” in the dropdown and click Add button to add the field. See Figure 15.

Figure 15.

Now, in the right side of properties, set the “Name” (this name should be same as name of the column in database table) in the Properties window and click “Add” to add a field type. For example, I change Name to “ShipName”. See Figure 16.

Figure 16.

Similarly, I add Freight OrderDate, ShipAddress, and ShipCity fields, that are the names of the columns in our database table.

<Custom:FieldLayout>
<Custom:Field Name="ShipName" />
<Custom:Field Name="Freight" />
<Custom:Field Name="OrderDate" />
<Custom:Field Name="ShipAddress" />
<Custom:Field Name="ShipCity" />
</Custom:FieldLayout>

See Listing 4.

Listing 4.

Now, build and run the app. You will see only five columns are loaded in the DataGrid control. See Figure 17.

Figure 17.

Changing theme

The xamDataGrid comes with several themes to change the appearance of the grid.

Click on the icon at the top right of xamDataGrid to view settings.

Under the “Appearance” section, click “Theme” and here you can select one of the themes. In this example, I am going to use “LunaOlive”. See Figure 18.

Figure 18.

We can also change the theme of the control by directly setting Theme in XAML file.

<Custom:XamDataGrid x:Name="xamDataGridControl" Theme="LunaOlive"> </Custom:XamDataGrid>

Now, run the application to view the theme, you can see the “LunaOlive” theme for the xamDataGrid. See Figure 19.

Figure 19.

XamDataGrid supports various styles and themes to change the look and feel of the control. Check out XamDataGrid Designing The Look and Feel help documentation for more details.

Data Filtering

The xamDataGrid control provides built-in data filtering. Data can be filtered for various column values.

Click on icon at the top right of the xamDataGrid to view settings, then go to the FieldSettings tab under the Settings tab. See Figure 20.

Figure 20.

Then check the “AllowRecordFiltering” checkbox under “Record Filtering” tab. See Figure 21.

Figure 21.

Now, build and run the app and you will see the DataGrid view is changed. See Figure 22.

Figure 22.

As you can see in Figure 22, the xamDataGrid now has text boxes where we can enter the value of a column to filter the DataGrid results.

Let’s enter the value “Bon” in filter textbox for ShipName. Now, you will see all matching records are loaded where ShipName now contains “Bon” in them. See Figure 23.

Figure 23.

The best part about the xamDataGrid filter options is, the operation supports various operators for strings including the following:

Contain
Does not contain
Like
Not Like
End with
Does not end with
Etc.

The numeric fields have different filter options as listed in Figure 24.

Figure 24.

For example, if we set Freight column value to < Less than 10, all records that are loaded in the grid have fewer than $10. See Figure 25.

Figure 25.

Advanced Filtering

The xamDataGrid control provides some advanced filtering options. Click on the Custom to use advanced filtering options. See Figure 26.

Figure 26.

You will see the advanced filtering options where you can add multiple filtering conditions. See Figure 27. We can add multiple conditions by using “AND” and “OR” operators. The Add Condition button is used to add a new condition. See Figure 27.

Figure 27.

In Figure 27, I add two conditions where field ShipName starts with “an” or “al”. New DataGrid with filtered results looks like Figure 28.

Figure 28.

Grouping

The xamDataGrid control provides a built-in grouping feature that you can use to group the grid data using one or more than one column header.

For enabling grouping, click on the group by area on the top of the column header. See Figure 29.

Figure 29.

Here you can drag and drop the column header to group data by the xamDataGrid field name (or column name). See Figure 30.

Figure 30.

Let’s drag and drop the ShipName column to the group by area. The result will group by data on the ShipName column. See Figure 31.

Figure 31.

Now, let’s drag and drop the ShipCity column also. Now, the records are grouped based on two columns. See Figure 32.

Figure 32.

Sorting

Column sorting can be easily accessed by a single click by mouse on the column header. You can even sort records on multiple columns by holding the control key (Ctrl). Sorting is enabled on the xamDataGrid by default.

To sort a column value, click on the column header and it will sort the values in ascending order and clicking again will sort the column values in descending order.

We can override the default behavior of column sorting. To do that, click on the <Custom:FieldLayout> property in XAML and press F4 to open the Properties window. Under the “Behavior” section, click “SortedFields” collection. See Figure 33.

Figure 33.

On the SortedFields collection, we can add a FieldSortDescription and select it and set the FieldName that we want to sort by default, also set the Direction as “Ascending” or “Descending” and click OK. In this example, I am going to set the Direction value to Descending. See Figure 34.

Figure 34.

Similarly, you can add as many as columns you would like to participate in the sort operation.

The following XAML code is added to the XAML file for Custom:FieldLayouts:SortedFields.

<Custom:FieldLayout.SortedFields>
<Custom:FieldSortDescription Direction="Descending" FieldName="ShipName"/>
</Custom:FieldLayout.SortedFields>

The default grid view records are sorted on the ShipName column. See Figure 35.

Figure 35.

Learn more about xamDataGrid and its advanced features such as advanced sorting, filtering, grouping and aggregation and download sample project here >

CRUD operations

One of the best parts of using the xamDataGrid is, it supports default CRUD (Create, Read, Update, and Delete) data operations. You only have to write a few lines of code to add a new record or update and delete existing records in a database via the grid.

Let’s look at how we can implement the add, update, and delete records via a xamDataGrid.

The xamDataGrid has a property, AllowEdit on FieldSettings, that makes xamDataGrid rows editable. The other two properties that we will use in this section are AllowAddNew and AddNewRecordLocation.

Adding a new record

To add a new record, we need to add the below properties in xamDataGrid

AllowEdit="true" property will make the xamDataGrid rows editable.
AllowAddNew="True" property will add the section just under the column header inside the xamDataGrid for adding new rows.
AddNewRecordLocation="OnTopFixed" property will set the add new record location on the top of the xamDataGrid

Let’s create a new Window and use a xamDataTable and bind it to the Category table of the database. On this screen, we will add, update, and delete categories. See XAML in Listing

<Custom:XamDataGrid x:Name="xamDataGridCategories">
<Custom:XamDataGrid.FieldSettings>
<Custom:FieldSettings AllowEdit="true"/>
</Custom:XamDataGrid.FieldSettings>
<Custom:XamDataGrid.FieldLayouts>
<Custom:FieldLayout>
<Custom:Field Name="CategoryID"/>
<Custom:Field Name="CategoryName"/>
<Custom:Field Name="Description"/>
</Custom:FieldLayout>
</Custom:XamDataGrid.FieldLayouts>
<Custom:XamDataGrid.FieldLayoutSettings>
<Custom:FieldLayoutSettings AutoGenerateFields="False" AllowAddNew="True" AddNewRecordLocation="OnTopFixed"/>
</Custom:XamDataGrid.FieldLayoutSettings>
</Custom:XamDataGrid>

The above code creates the following UI in Figure 36. As you can see in Figure 36, there is a “+” button that by clicking it will add a new row to the data grid. The data grid is also editable and when you click on a cell, the cell will become editable.

Figure 36.

When adding a new record, the RecordAdding and RecordAdded events are important.

There are two events that can be used

RecordAdding occurs when the first edit is done and the record is being added to the DataSource.
RecordAdded occurs when the first edit of a template add record is made and after an item has been added to the DataSource.

These events can be added in XAML using the following code snippet:

<Custom:XamDataGrid x:Name="xamDataGridCategories"
RecordAdding="xamDataGridCategories_RecordAdding"
RecordAdded="xamDataGridCategories_RecordAdded">
</Custom:XamDataGrid>

The following code in Listing is the code behind event handlers where we can write code that will be executed when these events are fired.

public partial class CRUDInXamDataGrid : Window
{
DataRecord record = null;
public CRUDInXamDataGrid()
{
InitializeComponent();
xamDataGridCategories.RecordAdding += XamDataGridCategories_RecordAdding;
xamDataGridCategories.RecordAdded += XamDataGridCategories_RecordAdded;
}
private void XamDataGridCategories_RecordAdded(object sender, Infragistics.Windows.DataPresenter.Events.RecordAddedEventArgs e)
{
}
private void XamDataGridCategories_RecordAdding(object sender, Infragistics.Windows.DataPresenter.Events.RecordAddingEventArgs e)
{
}
}

Updating an existing record

You use code in the above event handlers when a new record is being added or updated in the UI. For example, if a new record is updated and you want to notify a customer, this will be the event where you will write the notification functionality.

The RecordUpdating and the RecordUpdated events are fired when a record is being updated and when a record has been updated in the xamDataGrid respectively. This is where you also want to save your changes to the backend database.

RecordUpdating occurs when a DataRecord is about to be updated.
RecordUpdated occurs after a DataRecord has been updated.

The code snippet in Listing implements these two event handlers and the code for adding and updating backend is written there.

public partial class CRUDInXamDataGrid : Window
{
DataRecord record = null;
public CRUDInXamDataGrid()
{
InitializeComponent();
xamDataGridCategories.RecordAdding += XamDataGridCategories_RecordAdding;
xamDataGridCategories.RecordAdded += XamDataGridCategories_RecordAdded;
xamDataGridCategories.RecordUpdating += XamDataGridCategories_RecordUpdating;
xamDataGridCategories.RecordUpdated += XamDataGridCategories_RecordUpdated;
}
private void XamDataGridCategories_RecordAdded(object sender, Infragistics.Windows.DataPresenter.Events.RecordAddedEventArgs e)
{
}
private void XamDataGridCategories_RecordAdding(object sender, Infragistics.Windows.DataPresenter.Events.RecordAddingEventArgs e)
{
}
private void XamDataGridCategories_RecordUpdating(object sender, Infragistics.Windows.DataPresenter.Events.RecordUpdatingEventArgs e)
{
int index = ((Infragistics.Windows.DataPresenter.DataPresenterBase)e.Source).ActiveRecord.Index;
record = (DataRecord)xamDataGridCategories.Records[index];
}
private void XamDataGridCategories_RecordUpdated(object sender, Infragistics.Windows.DataPresenter.Events.RecordUpdatedEventArgs e)
{
string categoryID = record.Cells["CategoryID"].Value.ToString();
string categoryName = record.Cells["CategoryName"].Value.ToString();
string description = record.Cells["Description"].Value.ToString();
if (!string.IsNullOrEmpty(categoryID))
{
// Update the record
SqlHelper.UpdateRecord(categoryID, categoryName, description);
}
else
{
// Add New Record
SqlHelper.AddRecord(categoryName, description);
xamDataGridCategories.DataSource = SqlHelper.PopulateDataGridCategories().Tables[0].DefaultView;
}
}
private void Window_Loaded(object sender, RoutedEventArgs e)
{
xamDataGridCategories.DataSource = SqlHelper.PopulateDataGridCategories().Tables[0].DefaultView;
}
}

Now when you build and run the app and click on the “+” sign, type new values and hit ENTER, and the new record will be added to data grid control. The event handler code written in Listing will also save the record in the database. Once a record is added successfully, you will see a popup message confirming the action. See Figure 37.

Figure 37.

Now, if you double-click on a row and update the value of a cell or multiple cells, and hit ENTER, the xamDataGrid update events will be fired, the code executes, and the data is updated in the data grid as well as in the backend database. See Figure 38.

Figure 38.

Deleting a record

The xamDataGrid also provides a delete functionality by first setting the FieldLayoutSettings property AllowDelete="False".

<Custom:XamDataGrid>
<Custom:XamDataGrid.FieldLayoutSettings>
<Custom:FieldLayoutSettings AllowDelete="True"/>
</Custom:XamDataGrid.FieldLayoutSettings>
</Custom:XamDataGrid>

Similar to add and update events, there are two delete events that are fired when a record is being deleted in a xamDataGrid.

RecordDeleting occurs when one or more DataRecords is about to be deleted.
RecordDeleted occurs after one or more DataRecords has been deleted.

The code snippet in Listing,

public partial class CRUDInXamDataGrid : Window
{
DataRecord record = null;
public CRUDInXamDataGrid()
{
InitializeComponent();
xamDataGridCategories.RecordsDeleting += XamDataGridCategories_RecordsDeleting;
xamDataGridCategories.RecordsDeleted += XamDataGridCategories_RecordsDeleted;
}
private void XamDataGridCategories_RecordsDeleting(object sender, Infragistics.Windows.DataPresenter.Events.RecordsDeletingEventArgs e)
{
int index = ((Infragistics.Windows.DataPresenter.DataPresenterBase)e.Source).ActiveRecord.Index;
record = (DataRecord)xamDataGridCategories.Records[index];
}
private void XamDataGridCategories_RecordsDeleted(object sender, Infragistics.Windows.DataPresenter.Events.RecordsDeletedEventArgs e)
{
string value = record.Cells["CategoryID"].Value.ToString();
SqlHelper.DeleteRecord(value);
}
}

Listing

To delete a record, simply select a row in the data grid and press the DELETE button on the keyboard. See Figure 39.

Figure 39.

Read only DataGrid

To disable add, update, and delete operations in xamDataGrid, you can make it read-only by setting its AllowEdit property to false.

<Custom:XamDataGrid x:Name="xamDataGridControl" Theme="LunaOlive">
<Custom:XamDataGrid.FieldSettings>
<Custom:FieldSettings AllowEdit="false" AllowRecordFiltering="True"/>
</Custom:XamDataGrid.FieldSettings>
</Custom:XamDataGrid>

Export to Excel

Often, developers have a need to export DataGrid records into an Excel sheet for reporting purposes. The xamDataGrid, with the support of other classes, can easily be exported into an Excel sheet.

The Excel export functionality is defined in the DataPresenter and related namespaces. You need to import the following namespace in your code before you can export a xamDataGrid in to excel.

InfragisticsWPF4.DataPresenter.ExcelExporter.v17.2
InfragisticsWPF4.DataPresenter.v17.2
InfragisticsWPF4.Documents.Excel.v17.2

Now, let’s add a Button with a click event handler that will be responsible for exporting to Excel. The Button is placed on a StackPanel. The new UI looks like Figure 40.

Figure 40.

New XAMLcode is listed in Listing

<Grid>
<StackPanel Orientation="Vertical">
<StackPanel Orientation="Horizontal" Margin="10">
<Button x:Name="btnExport" Click="btnExport_Click" Height="30" Width="90">Export to Excel</Button>
</StackPanel>
<Custom:XamDataGrid x:Name="xamDataGridControl" Theme="LunaOlive">
<Custom:XamDataGrid.FieldSettings>|
<Custom:FieldSettings AllowRecordFiltering="True"/>
</Custom:XamDataGrid.FieldSettings>
<Custom:XamDataGrid.FieldLayouts>
<Custom:FieldLayout>
<Custom:FieldLayout.SortedFields>
<Custom:FieldSortDescription Direction="Descending" FieldName="ShipName"/>
</Custom:FieldLayout.SortedFields>
<Custom:Field Name="ShipName"/>
<Custom:Field Name="Freight"/>
<Custom:Field Name="OrderDate"/>
<Custom:Field Name="ShipAddress"/>
<Custom:Field Name="ShipCity"/>
</Custom:FieldLayout>
</Custom:XamDataGrid.FieldLayouts>
<Custom:XamDataGrid.FieldLayoutSettings>
<Custom:FieldLayoutSettings AutoGenerateFields="False"/>
</Custom:XamDataGrid.FieldLayoutSettings>
</Custom:XamDataGrid>
</StackPanel>
</Grid>

Listing

Now, on the Button click event handler, we use the DataPresenterExcelExporter object and its ExportAsync method that is responsible for creating a new Excel document with the xamDataGrid records in it.

private void btnExport_Click(object sender, RoutedEventArgs e)
{
try
{
string fileName = System.IO.Path.GetTempPath() + "TempFile.xlsx";
DataPresenterExcelExporter excelExporter = new DataPresenterExcelExporter();
excelExporter.ExportAsync(this.xamDataGridControl, fileName, WorkbookFormat.Excel2007);
System.Windows.MessageBox.Show("File save successfully. \n"+fileName);
}
catch (Exception ex)
{
System.Windows.MessageBox.Show(ex.Message);
}
}

Listing

The outlook looks like Figure 41

Figure 41.

Summary

The xamDataGrid is one of the richest data controls available for WPF. The control provides built-in functionality to sort, filter, group, and find records. In this tutorial, I demonstrated how to connect to a SQL Server database and load data in a xamDataGrid. You also saw how to filter, group, and sort data. In this tutorial, we also saw how to add, update, and delete data via the data grid control and export data to a Microsoft Excel document.

Learn more, download a free trial, and get started here: Get Started with Ultimate UI for WPF.