July 28, 2014
Hot Topics:
RSS RSS feed Download our iPhone app

Accessing Business Data in WPF Applications

  • August 19, 2008
  • By Jani Järvinen
  • Send Email »
  • More Articles »

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.

Download the Code

Click here to download the code for the sample application.

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.





Page 3 of 3



Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 


Sitemap | Contact Us

Rocket Fuel