Accessing Business Data in WPF Applications

As more and more developers are looking to use Windows Presentation Foundation (WPF) as their technology for future user interfaces, there is also a growing need to know how to access business database data from WPF application.

That said, business applications often have windows or forms that allow users to add, edit, and delete data, corresponding to the four basic functions of SQL databases: create, read, update, and delete (CRUD). WPF includes very versatile support for data binding, and thus accessing database data using ADO.NET or LINQ is possible.

However, in Visual Studio 2008 the database data support for WPF applications is still lacking behind the database support in WinForms (Windows Forms) applications. For example, you cannot simply drag and drop a data source onto the form, and be able to see how Visual Studio automatically creates data access components and manipulation controls for you.

As you might imagine, WPF is still perfectly capable of accessing data from ADO.NET data sources; it just requires a little bit of more work (presently). You can start with the simplest possible WPF example: an application that is able to display data from an Access 2003 .mdb database file.

Introducing the Sample Application

The sample application to accompany this article can be seen in Figure 1. The application has a single window that mimics those seen in many organizations: It has the ability to browse data, make changes, and add and delete records.

Figure 1: The sample application as it looks like on Windows Vista.

In the sample application's case, the data being edited is customer data. Editing is done through a simple list box control on the left, editing fields on the right, and Next and Previous buttons to help with browsing. Below those, you can find three more buttons: one to save changes, one to add a new customer record, and a third to delete one.

The database behind the application is a simple, one-table database. It contains a table called Customers, which in turn contains fields to store the contact details such as address, phone number, and so on. To allow an index number to be generated automatically, the first field of the table, named ID, is set to be an Access autonumber field. The table definition can be seen in Figure 2.

Figure 2: The structure of the sample table Customers.

Next, you will learn how to get started in displaying data in WPF programs.

Viewing Data on a Form

WPF applications support multiple ways to bind data into generic user interface controls. Data can be bound by using code, XAML, or in a combination of both. An example of a so-called XAML binding expression would be:

<TextBox Text="{Binding CustomerName}" />

Although there are multiple ways to get data on the screen, using a combination of binding expressions along with the form's DataContext property is one of the simplest ways to achieve the goal. To display data on the screen, you would first add one or more basic controls (such text boxes as above), set the appropriate content property with a binding expression, and finally set the DataContext property for the entire window:

CustomersTableAdapter customersTableAdapter =
   new CustomersTableAdapter();
CustomersDataSet.CustomersDataTable customers =
   customersTableAdapter.GetData();
this.DataContext = customers;

After this, the application is able to display data from the database directly on the controls. However, the problem is that there is no way to browse the data, especially if all controls are just regular text boxes. To browse the data, you would need a couple of buttons on the form, and then write some C# code. For instance, if you want to add a "Next" button on the form, you could enable navigation to the next record (row) in the database with the following code:

private void nextButton_Click(
   object sender, RoutedEventArgs e)
{
   CollectionView view = (CollectionView)
      CollectionViewSource.
      GetDefaultView(this.DataContext);
   view.MoveCurrentToNext();
}

In WPF applications, you can use the CollectionView class to move back and forward in the database data records. For example, the MoveCurrentToNext and MoveCurrentToPrevious move the current record position forward and backward, respectively.

Of course, you need to be able to get to the CollectionView class first. To do so, you can use a class called CollectionViewSource from the System.Windows.Data namespace and its static GetDefaultView method. This method returns the currently used default view object as an ICollectionView interface. This is the reason you also need an explicit cast to get the CollectionView data type.

Oftentimes, business applications need lists of data. For example, you might want to allow the user to see a list of all customers, pick one, and then see more details. WPF applications do not (yet) have a built-in data grid control, but you could use a regular ListBox control to simulate one.

The ListBox control is a template control; this means that you can define a template for each row, and then have the control repeat that template for each data item associated with it. In this case, that data would of course be customer records.

Here is an example of a list box template used to display customer company name and the country on the same row:

<DataTemplate x:Key="CustomerListTemplate">
   <Grid ShowGridLines="False">
      <Grid.ColumnDefinitions>
         <ColumnDefinition Width="120" />
         <ColumnDefinition Width="50" />
      </Grid.ColumnDefinitions>
      <Grid.RowDefinitions>
         <RowDefinition Height="16" />
      </Grid.RowDefinitions>
      <TextBlock Text="{Binding CompanyName}"
         Grid.Column="0" Grid.Row="0" />
      <TextBlock Text="{Binding Country}"
         Grid.Column="1" Grid.Row="0" />
   </Grid>
</DataTemplate>

The DataTemplate XAML element starts the definition of a template named CustomerListTemplate. Inside each row, two TextBlocks are used to display the company name and the country. To display the information nicely formatted, a grid control can be used inside the listbox row. In addition to declaring the data template, the listbox control must be made aware of the template. This can be done with the following XAML code:

<ListBox ItemsSource="{Binding}"
   ItemTemplate="{StaticResource CustomerListTemplate}"
   Name="customerListBox" ... />

The next step is to connect the listbox to the current record. By default, the listbox only displays information from the database. Although merely displaying data can be useful in some cases, you would most often require more functionality.

The listbox control has a property named IsSynchronizedWithCurrentItem, which, when set to True, automatically makes the list follow the current record and also sets the current record if an item is clicked on the list. This is often exactly what is needed, so this property if indeed very useful.

Accessing Business Data in WPF Applications

Updating Data and Adding New Records

As you learned previously, getting data from a database and displaying it on a WPF window is straightforward. But, you also need to figure how to update database data. Luckily, if reading and displaying data was quite easy, updating data is even easier!

In fact, to update data in the database, you only need a single line of code. When you connect display controls to a data source through the DataContext property and the user modifies data on the window (for example, by typing new text to the text box), the underlying data source is also updated.

The example application uses a typed DataSet class and a DataTable to hold the database data. Therefore, when the WPF data binding framework generates updates, the changed data in saved back to the DataTable object. Because you are already familiar with the DataTable object, you know that it stores the database data in memory. Thus, all updates to the table are not committed to the database until you instruct the object to do so.

Committing the changes back to the database is a matter of calling the Update method of the table adapter. In code, this looks similar to this:

CustomersTableAdapter customersTableAdapter =
   new CustomersTableAdapter();
int rows = customersTableAdapter.Update(customers);
System.Windows.MessageBox.Show(
   "Changes saved back to the database, " +
   rows + " row(s) updated.");

Here, the code constructs an instance of the typed CustomersTableAdapter class, and then calls its Update method and passes the customers DataTable object as a parameter. The result is an integer value indicating how many rows were updated, inserted, or deleted. It could be argued that, because this method works mostly with SQL databases, "Commit" might be a more appropriate name for it.

Adding new records can be done in more than one way, but maybe the simplest option is to call the typed DataTable object's method called AddNNNRow, where NNN is the name of the table in question. In the case of the example application, the method is named AddCustomersRow.

This method requires as a parameter either a row object, or values for all the fields in the table. For example, to add a new customer row with minimal data, you could use the following code:

customers.AddCustomersRow("New Company", "", "", "",
   "", "", "", "", "", "");
customerListBox.SelectedIndex = customers.Rows.Count - 1;

In this code, the AddCustomersRow method is called to add the new row to the DataTable. At this point, the WPF user interface components are automatically updated, but the currently selected item stays. That is, the list box on the user interface immediately displays "New Company" at the bottom of the list. To shift focus to the newly added row, the SelectedIndex property is modified to select the last item.

The fourth basic database operation remaining is the deletion of data. Deleting from the DataTable object is as simple as calling the Delete method. In addition to that, you might want to confirm from the user that he or she actually wants to delete the data record. Here is how this can be done:

CustomersDataSet.CustomersRow selectedRow =
   (CustomersDataSet.CustomersRow)
   ((DataRowView)customerListBox.SelectedItem).Row;
string company = selectedRow.CompanyName;
string message = "Are you sure you want to delete the " +
   "customer \"" + company + "\"?";
if (MessageBox.Show(message, "Delete Customer",
   MessageBoxButton.YesNoCancel,
   MessageBoxImage.Exclamation) == MessageBoxResult.Yes)
{
   selectedRow.Delete();
}

The objective is to get the currently selected company name, and then display a message repeating the company name. The list box control's SelectedItem returns the currently selected item. Because a DataTable is associated with the list box, the SelectedItem object type is always a DataRowView. This class in turn has a property called Row, which can be cast directly as a CustomersDataSet.CustomersRow object.

Although multiple casts are needed, making the code a bit difficult to read, the concept is simple enough. Next, the code asks for a simple confirmation with the MessageBox class. If the user chooses Yes, the selected row will be deleted.

Reading Access Autonumber Fields

If you would run the sample application with the C# implementation shown above, you would notice that when adding records to the database, the ID field values would be negative. As you will recall, the Access database is defined to include a field named ID, which is of type "autonumber". In Access terminology, this means a field whose value increases automatically and uniquely as rows are added to the table.

With the code shown previously, the user interface will not be updated with the actual ID field value when the updates are posted back to the database. Luckily, this can be solved easily. Access supports a special SQL statement, "SELECT @@IDENTITY", which, when executed immediately after an INSERT statement, returns the newly generated autonumber value.

Armed with this knowledge, you can hook up with an event in your DataAdapter class, manually execute the above-mentioned SELECT statement, and then update the DataTable in question with the returned value. This way, the user interface will show correct ID values instead of the default values of -1, -2, and so on. Remember, though, that the autonumber values are of course only available once the changes are saved back to the database with the table adapter's Update method.

The event you need to handle correctly is called RowUpdated. To associate an event handler with this event, you would use code similar to this:

CustomersTableAdapter customersTableAdapter =
   new CustomersTableAdapter();
customersTableAdapter.Adapter.RowUpdated +=
   new OleDbRowUpdatedEventHandler(OnRowUpdated);

Once the event handler has been hooked to the event, you also need to implement the handler. The OnRowUpdated could look for an example like this:

private void OnRowUpdated(object sender,
   OleDbRowUpdatedEventArgs e)
{
   if (e.StatementType == StatementType.Insert)
   {
      OleDbCommand cmdNewID = new OleDbCommand(
         "SELECT @@IDENTITY", e.Command.Connection);
      e.Row["ID"] = (int)cmdNewID.ExecuteScalar();
   }
}

Firstly, the event handler checks to see whether the previously executed SQL statement was an INSERT statement. Naturally, you only want to track the INSERT statements, because only then are new autonumber values generated.

If the previously executed SQL statement was indeed an INSERT, the code proceeds to create a new OleDbCommand object by using the same connection that was used to update the database. Then, the aforementioned SQL statement "SELECT @@IDENTITY" is executed; it returns a single integer value. The ExecuteScalar method is the most convenient way to get this value.

In the last code line, the resulting new autonumber value is simply stored back to the DataTable object. Once this updating has been completed, the user interface will automatically pick up the new ID field values, which then change from negative placeholder values to real values that match those in the database.

Conclusion

In this article, you learned how to implement common business database functions in Windows Presentation Foundation applications. You looked at a simple, yet representative WPF application that manipulates a simple Microsoft Access database holding customer information. The sample application is able to perform the four basic database CRUD operations: selects, inserts, updates, and deletes.

Although Visual Studio 2008's support for business databases in WPF applications still is behind the Windows Forms (WinForms) support, you saw that data access in WPF applications is by no means impossible. And, because the ADO.NET database access layer works the same in WinForms and WPF applications, you can leverage your existing database skills quickly.

Finally, you also learned how to read Access autonumber field values as they are generated, and you also saw how to browse database records with simple operations like Next and Previous. Armed with these essential database skills, you are ready to start building advanced business database applications with the slick WPF graphics and interactivity.

Let the journey begin!

About the Author

Jani Järvinen is a software development trainer and consultant in Finland. He is a Microsoft C# MVP and has written dozens of magazine articles and published three books about software development. He is a group leader of a Finnish software development expert group at ITpro.fi. His frequently updated blog can be found at http://www.saunalahti.fi/janij/. You can send him mail by clicking on his name at the top of the article.



About the Author

Jani Jarvinen

Jani Jarvinen is a software development trainer and consultant in Finland. He is a Microsoft C# MVP, a frequent author and has published three books about software development. He is the group leader of a Finnish software development expert group at ITpro.fi and a board member of the Finnish Visual Studio Team System User Group. His blog can be found at http://www.saunalahti.fi/janij/. You can send him mail by clicking on his name at the top of the article.

Downloads

Comments

  • There are no comments yet. Be the first to comment!

Leave a Comment
  • Your email address will not be published. All fields are required.

Top White Papers and Webcasts

  • Live Event Date: May 7, 2014 @ 1:00 p.m. ET / 10:00 a.m. PT This eSeminar will explore three popular games engines and how they empower developers to create exciting, graphically rich, and high-performance games for Android® on Intel® Architecture. Join us for a deep dive as experts describe the features, tools, and common challenges using Marmalade, App Game Kit, and Havok game engines, as well as a discussion of the pros and cons of each engine and how they fit into your development …

  • Instead of only managing projects organizations do need to manage value! "Doing the right things" and "doing things right" are the essential ingredients for successful software and systems delivery. Unfortunately, with distributed delivery spanning multiple disciplines, geographies and time zones, many organizations struggle with teams working in silos, broken lines of communication, lack of collaboration, inadequate traceability, and poor project visibility. This often results in organizations "doing the …

Most Popular Programming Stories

More for Developers

Latest Developer Headlines

RSS Feeds