Tag Archives: ComboBox

Using MySQL with WPF

MySQL is a popular free database which many (including me) prefer for writing database applications. Being relatively new to WPF, I wanted to see how MySQL could be used in a WPF application. This proved to be a fairly long journey, so we’ll probably need several posts to get through it all.

First, if you’re new to MySQL, you’ll need to download and install it. I won’t go into great detail here, apart from pointing you in the direction of the main download page for the free version (there are commercial versions that cost real money as well). After installing MySQL itself, you’ll need to install the MySQL ADO.NET connector in order to use it in WPF programming. This is currently located here, but if in the future this link is dead, just do a search for MySQL and ADO.NET.

With these two packages installed you should be ready to start writing code in Visual Studio (VS). The program I’ll discuss is a front end to an existing database, which allows you to insert, modify and delete records in the database. In homage to Sheldon Cooper, the database will contain details of my comic book collection. I’ll assume that you understand the basics of database construction, including the creation of tables and insertion of records into these tables. I’ll also assume you know the rudiments of SQL, since I’ll be using it to construct a few commands to be sent to the database. If you don’t know SQL very well (or at all) it shouldn’t hamper you too much since you can probably figure out what the commands are doing (SQL is fairly transparent at this level).

First, we’ll need to consider the structure of the database itself. It contains three tables, which contain the following fields:

Publishers (publishers of comic books)

  • Key_Publishers (the primary key)
  • Name (the name of the publisher)

Titles (titles of comic book series, not of individual issues)

  • Key_Titles (the primary key)
  • Title (the title’s name, such as Action Comics)
  • Publisher (an int giving the key of the publisher of this title)

Issues (individual issues of given titles)

  • Key_Issues (the primary key)
  • IssueTitle (the title of the individual issue)
  • Title (an int linking to the key in the Titles table)
  • Volume
  • Number
  • IssueDay
  • IssueMonth
  • IssueYear
  • ComicVine (the URL of the page on http://www.comicvine.com giving details about this issue)

The UI of the program consists of a tab control with three tabs: one for editing the issues, one for the titles and one for the publishers. We’ll consider the issues one first. It looks like this:

At the top is a ComboBox from which we can choose the title. Beneath this is a DataGrid in which we display the individual records from the data base for that title. At the bottom are a few boxes in which individual data for the selected issue are displayed. The user can edit the information either directly in the DataGrid, or by editing the boxes at the bottom.  The info in the boxes is identical to that displayed in the DataGrid except for the ComicVine link, which is shown as a URL in the text box, but as a hyperlink with the label ‘ComicVine’ in the DataGrid. This is done because the URL is usually far too long to be displayed conveniently in the grid, as you can see. The ‘Add’ button allows the user to add a new issue.

Clearly there’s a lot going on here, so we’ll need to take things step by step. To begin, let’s see how we can get the ComboBox to display the list of titles. (I’ll assume you can create the basic UI using Expression Blend (EB) or VS so I won’t go into that here.)

First, we need to let our VS project know we’ll be using MySQL. If you’ve installed the connecter mentioned above, you still need to include MySQL as a reference in your VS project. To do this, right-click on References in Solution Explorer and then click on the .NET tab. You may need to wait a few seconds while this list finishes loading, but eventually you can find MySQL.Data in the list, so you should add that. Now we can start writing some code.

Most of the linkage between controls and the MySQL database is done via data binding, which we’ve already looked at for simpler cases (see the index for a list of pages). Before we dive into the code it’s essential that you understand the structure of the program.

Ultimately the data are stored in the MySQL database, but the program itself doesn’t use this for the displays. First, the data must be loaded from the MySQL database into an internal data structure, which is then used for the main working of the program. Changes made by the user to the data using the UI affect only this internal representation of the data. If we want these changes to be made permanent, we need to write the code that will save these changes back to the original MySQL database.

WPF provides several internal data structures useful for storing information from a database. In fact, VS provides an automated way of creating a DataSet from database servers it has access to. Although you can create a DataSet from a MySQL server, the associated functionality provided by VS doesn’t seem to work with MySQL (at least I couldn’t find any way of making it work – there may be something that I haven’t discovered), so we need to write our own code to handle changes to the MySQL database. This isn’t that hard to do as we’ll see, but we’re getting ahead of ourselves.

Let’s see how we can access the data required to populate the ComboBox containing the list of Titles. From the above structure of the database, we see that the information is stored in the Titles table, so we need to load that data into the program. We’ve provided a separate class that handles this, and here’s the relevant code:

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;
//.....
class DatabaseTable
{
    MySqlConnection connection;
    MySqlDataAdapter adapter;
    public DataTable GetTable(String query, String sortBy)
    {
      String connString = "server=localhost;uid=root;pwd=xxxx;database=comics;";
      connection = new MySqlConnection(connString);
      adapter = new MySqlDataAdapter(query, connection);
      DataTable dataTable = new DataTable();
      adapter.Fill(dataTable);
      dataTable.DefaultView.Sort = sortBy;
      return dataTable;
    }
}

Note the ‘using’ statement that specifies the MySql library.

To get at data in a MySQL database we first need to connect to it, and that’s what the MySqlConnection is for. Its constructor requires a string giving the information needed to connect. In this case, we specify the server as ‘localhost’ which means it’s on the local machine. If you’re connecting to a remote server, you’d give the URL instead. The userID (uid) and password (pwd) are then given, followed by the name of the database.

Next, we need to create a MySqlDataAdapter, which is what does the actual work of retrieving the information from the database and storing it in the local object. In order for the adapter to get some data, it needs to know what to look for, and that’s what the ‘query’ string is for. This is an SQL command, which we’ll get to in a minute.

The adapter has to be given an object in which to store the data it retrieves. The standard data structure is the DataTable (in System.Data), which is essentially a single table of data. The Fill() method of MySqlDataAdapter executes the query on the MySQL database and loads the result in the DataTable. The DataTable will have columns with the same names as those in the original database, and it is these column names that can be used in data binding later on.

The last thing we do is define a sorting condition. Remember that whenever we bind a control to a data source such as a list, a collection view is automatically created and inserted between the data source and the control. This also applies to data binding between a control such as a ComboBox and a DataTable. The ComboBox displays the data as given by the collection view, so if we wish to sort the data as displayed in the UI, we add a sort command to the view. The data in the original DataTable is unchanged; all that changes is the way that data appears on screen.

In the case of a DataTable, we access its DefaultView as shown in the code, and then attach a Sort command to the view. The Sort is simply a string giving the sorting command we want. If we wish to sort the list of Titles, for example, we would specify ‘sortBy’ as “Title”, which is the name of the column we want to use as the sort key. (More complex sorting can be done too. We’ll see this when we consider the DataGrid showing the issues.)

There’s an important point here. Those of you familiar with SQL will know that we can include a sorting command as part of an SQL query, so you might be wondering why we didn’t just include the sorting command as part of the ‘query’ we sent to the database. The reason is that doing it this way would ask the MySQL database to do the sorting, so that the data used to populate the DataTable would be correctly sorted when it first arrives. However, if we then add extra rows to the DataTable by using our program, these rows would merely be tacked onto the end of the DataTable and would not be correctly sorted, since the DataTable hasn’t been told to sort its data. By putting the sort command into the DataTable rather that in the original SQL command, we ensure that the data as displayed in the program are always correctly sorted.

OK, we now have our internal DataTable, so how do we use this to populate the ComboBox? Fortunately, we’ve already considered data binding with a ComboBox, so we can model our code along that example. We’ll do things a little differently here, however, to illustrate a common technique for providing data sources in data binding.

We saw above that we provided the code for getting the DataTable in a class called DatabaseTable. We can access the DataTable by declaring an ObjectDataProvider in the XAML part of the code. In the Window.Resources section of the XAML, we can write:

<Window
  xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
  xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
  xmlns:d="http://schemas.microsoft.com/expression/blend/2008"
  xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"
  mc:Ignorable="d"
  x:Class="Comics.MainWindow"
  x:Name="Window"
  xmlns:local="clr-namespace:Comics"
  xmlns:s="clr-namespace:System;assembly=mscorlib"
  Title="Comics"
  Width="680" Height="814">

  <Window.Resources>
    <ObjectDataProvider x:Key="TitlesTable"
    ObjectType="{x:Type local:DatabaseTable}"
    MethodName="GetTable">
      <ObjectDataProvider.MethodParameters>
        <s:String>SELECT * FROM titles</s:String>
        <s:String>Title</s:String>
      </ObjectDataProvider.MethodParameters>
    </ObjectDataProvider>
  </Window.Resources>
  <!-- Code for UI -->
</Window>

This creates a resource with the name given as the Key (TitlesTable) by creating an object of the given type (DatabaseTable) and calling the indicated method (GetTable). The parameters passed to the method are given next, and in this case are two strings. The first string is the query to be sent to the MySQL database, and the second string is the Sort command, so that the DataTable sorts the records by Title.

Note that you’ll need to ensure that the various prefixes used in this XAML code are defined. Typically most of them are defined for you when you create a project in VS, but there are a couple that you’ll probably need to define yourself. Here, the ‘local’ namespace is the namespace used for the code in the project (Comics here). The ‘s’ namespace is required to define the String data type. (This is one reason why I hate XAML: although it is more compact in some cases, there is so much finicky stuff that it can be hard to get it right.)

We’re almost there. The last thing we’ll look at in this post is how to define the ComboBox so it gets its data from the ObjectDataProvider we just defined. The relevant line is:

            <ComboBox x:Name="booksTitlesComboBox" ItemsSource="{Binding Source={StaticResource TitlesTable}}" DisplayMemberPath="Title"
                    SelectedValuePath="Key_Titles" VerticalAlignment="Center" HorizontalContentAlignment="Stretch" Grid.Row="1" Grid.Column="1"
                    SelectionChanged="booksTitlesComboBox_SelectionChanged" FontWeight="Bold"/>

There’s a bit of stuff in here that we’ll get to later, but the relevant bits are:

  • ItemsSource is bound to the StaticResource TitlesTable that we just defined. This means that the items displayed in the ComboBox are bound to the items in that DataTable.
  • DisplayMemberPath is specified as ‘Title’. Since the DataTable contains more than one (two, actually) columns, we need to tell the ComboBox which column to display. The ‘Title’ is the textual title which the user can read.
  • SelectedValuePath: internally, we use the Key_Titles column for connecting a title with an issue (as we saw in the database tables above), so when the user selects an item from the ComboBox, we need to know the Key_Titles value in order to be able to retrieve the issues for that Title. More on this later when see how to populate the DataGrid.
  • SelectionChanged: when the user selects an item in the ComboBox, we need to update the display in the DataGrid to display all issues with that Title, so we provide an event handler to do this. Again, we’ll see how this works in a later post.

The program at this stage should display the list of titles in the ComboBox, although selecting an item won’t do anything yet. But that’s enough for one post, so we’ll continue the story in the next post.

Advertisements

Data binding with a ListBox and ComboBox in WPF

In the last post, we saw how to use data binding to a List to allow stepping through the elements of the List, displaying each item in separate TextBoxes. We also saw how a collection view provides an interface to the underlying List, and allows moving around the List as well as other operations like sorting and filtering.

However, when dealing with a list of objects, usually we would like a view that allows us to see several items in the list at the same time, and allows us to scroll through this display and select which item we want to deal with. To that end, we’ll have a look at how to bind a List to a ListBox and a ComboBox in WPF.

The program we use as illustration is an expanded version of the one from the previous post. The interface of the new program looks like this:

As before, we are looking at a list of books. Each book has an author, title, price and subject category. The available categories are shown in the ComboBox, while the titles of the books are displayed in a ListBox just above the buttons. Each category is represented by a unique integer code (so ‘physics’ has the code 2 in the picture). It is this numerical code that is stored in each Book object, so the textual representation of a given category is contained in a separate class called BookCategory. (Yes, I realize this example would be better done using a database, but for the purposes of illustrating data binding to lists, it serves its purpose.)

The buttons have the same functions as in the previous post (the First, Previous, Next and Last buttons move through the list of books, Add adds a new book to the list, Sort sorts the books by price and Cheap selects books with a price under 20.00. Nothing new here.

We’ve used data binding to keep the various parts of the interface synchronized. As we step through the list using one of the navigation buttons, the displays in the TextBoxes and ComboBox update to display the data in the current book, and the highlighted line in the ListBox also keeps in step with out current selection. If we select a book by clicking on a line in the ListBox, the rest of the display shows the data for that book. If we select a new category in the ComboBox, the Code box will update as well, and vice versa. (We haven’t put in any validation, so if you enter an invalid Code number, you won’t get an error message.)

The interface was built in Expression Blend as usual, so if you want to see the XAML for that, download the code (link at the end of the post) and have a look. We’ll look here at the code (all in C#) that does the binding between the lists and controls.

First, we’ll look at the classes that store the data that will be inserted into the lists. The Book class is slightly expanded from the previous post in order to accommodate the category code:

  class Book : INotifyPropertyChanged
  {
    public event PropertyChangedEventHandler PropertyChanged;
    protected void Notify(string propName)
    {
      if (this.PropertyChanged != null)
      {
        PropertyChanged(this, new PropertyChangedEventArgs(propName));
      }
    }

    string author;

    public string Author
    {
      get { return author; }
      set
      {
        if (author.Equals(value)) { return; }
        author = value;
        Notify("Author");
      }
    }

    string title;

    public string Title
    {
      get { return title; }
      set { if (title.Equals(value)) { return; }
        title = value;
        Notify("Title");
      }
    }

    decimal price;

    public decimal Price
    {
      get { return price; }
      set { if (price == value) { return; }
        price = value;
        Notify("Price");
      }
    }

    int categoryCode;

    public int CategoryCode
    {
      get { return categoryCode; }
      set { if (categoryCode == value) { return; }
        categoryCode = value;
        Notify("CategoryCode");
      }
    }

    public Book() { }
    public Book(string author, string title, decimal price, int code)
    {
      this.author = author;
      this.title = title;
      this.price = price;
      this.categoryCode = code;
    }
  }

Note, by the way, that the string returned in the Notify() call must match the name of the property in the code.

Next, we look at the BookCategory class:

  class BookCategory : INotifyPropertyChanged
  {
    public event PropertyChangedEventHandler PropertyChanged;
    protected void Notify(string propName)
    {
      if (this.PropertyChanged != null)
      {
        PropertyChanged(this, new PropertyChangedEventArgs(propName));
      }
    }

    int code;

    public int Code
    {
      get { return code; }
      set
      {
        code = value;
        Notify("Code");
      }
    }

    string category;

    public string Category
    {
      get { return category; }
      set
      {
        category = value;
        Notify("Category");
      }
    }

    public BookCategory(int code, string category)
    {
      Code = code;
      Category = category;
    }
  }

Again, no surprises here; it has the same form as the Book class.

Now we look at the binding code, contained in MainWindow.xaml.cs. The constructor is

    public MainWindow()
    {
      this.InitializeComponent();
      InitializeLibrary();
      InitializeCategoryList();
      UpdateButtonStates();
    }

After the standard InitializeComponent() call, we initialize the list of Books (the library) that is displayed in the ListBox and then the list of categories that is displayed in the ComboBox.

Here’s InitializeLibrary():

    ObservableCollection<Book> library;
    private void InitializeLibrary()
    {
      library = new ObservableCollection<Book>();
      library.Add(new Book("Feynman", "Feynman Lectures on Physics", BookPrice(), 2));
      library.Add(new Book("Asimov", "I, Robot", BookPrice(), 1));
      library.Add(new Book("Christie", "Death on the Nile", BookPrice(), 3));
      library.Add(new Book("Taylor", "From Sarajevo to Potsdam", BookPrice(), 4));
      LayoutRoot.DataContext = library;

      Binding listBoxBinding = new Binding();
      BindingOperations.SetBinding(booksListBox, ListBox.ItemsSourceProperty, listBoxBinding);
      booksListBox.DisplayMemberPath = "Title";
      booksListBox.SelectedValuePath = "CategoryCode";
      booksListBox.IsSynchronizedWithCurrentItem = true;
    }

We’re just hard-coding the first four books in the list. In more advanced code, you’d probably read them from a file and/or offer the user a way of entering the data in the interface (or, of course, read them from a database). Here we create a List of Books and set it as the data context.

Next we create a Binding and attach it to the list box (booksListBox is the ListBox that displays the books). The ItemsSourceProperty is set to this binding. Note that we don’t specify a Path for the binding itself. This causes the ListBox to bind to the entire data set in the data context. (Well, actually, it’s binding to the collection view that has been automatically wrapped around the List, as we saw in the previous post.) If we stopped at this point and ran the program, the ListBox would display a list of entires, each of which would say “ListBinding1.Book”. This is because, in the absence of any further instructions on how to display each element in the List, the ListBox calls the ToString() method of each object in the List. If no ToString() has been provided explicitly (we haven’t here), the default ToString() just prints out the data type of the object, which in this case is the Book class in the ListBinding1 namespace. In order to get something more informative to show up in the ListBox, we need to tell it which part of the Book object to display. That’s what the DisplayMemberPath is for. We set this to the name of the property of the Book that we want to show up; in this case, the Title. Note that DisplayMemberPath is a property of the ListBox and not of the binding. This can be a bit confusing, but if you think about it, it does make sense. The binding is concerned only with which element to display; the mechanics of what to display are handled by the UI control, in this case the ListBox.

The SelectedValuePath is a bit more subtle. List controls such as ListBoxes allow one property of the current item to be used for display, while reserving another property as the actual value of the item. The current item selected in a ListBox is the SelectedItem, and the value of this item is the SelectedValue. The SelectedValuePath tells the ListBox which property of the SelectedItem should be used for the SelectedValue. Thus SelectedValuePath is the name of a property and is not a value itself; the SelectedValue is the actual value of that property for the SelectedItem.

Finally we tell the ListBox to keep synchronized with the CurrentItem as specified in the collection view. If you don’t do this, the SelectedItem in the ListBox won’t change as you step through the Books in the collection view by using the navigation buttons.

If you ran the program at this point, you should see the titles displayed in the ListBox, and the highlighted item keep step with the CurrentItem as you use the buttons to move around the list. The ComboBox, however, will still be inert since we haven’t linked it into the program yet.

Now we look at the category list and how to bind it to the ComboBox. We use an ObservableCollection for this list as well, although in the current program there is no provision for adding or deleting categories, so we could have used an ordinary List with the same result. The code is:

    ObservableCollection<BookCategory> CategoryList;
    private void InitializeCategoryList()
    {
      CategoryList = new ObservableCollection<BookCategory>();
      CategoryList.Add(new BookCategory(1, "Science Fiction"));
      CategoryList.Add(new BookCategory(2, "Physics"));
      CategoryList.Add(new BookCategory(3, "Mystery"));
      CategoryList.Add(new BookCategory(4, "History"));
      Binding comboBinding = new Binding();
      comboBinding.Source = CategoryList;
      BindingOperations.SetBinding(categoryComboBox, ComboBox.ItemsSourceProperty, comboBinding);
      categoryComboBox.DisplayMemberPath = "Category";
      categoryComboBox.SelectedValuePath = "Code";

      Binding codeComboBinding = new Binding();
      codeComboBinding.Path = new PropertyPath("CategoryCode");
      BindingOperations.SetBinding(categoryComboBox, ComboBox.SelectedValueProperty, codeComboBinding);
    }

As with the book list, we are hard-coding a few categories for illustration; in real life you’d read these from a file or a database.

We begin by binding CategoryList to the combo box. Since CategoryList isn’t the data context, we need to specify it as the Source of the binding. As with the book list, we get the ComboBox to display the textual form of the category while using the numerical Code as its SelectedValue.

The semi-magical bit comes in the last 3 lines of code. Here we introduce a second binding for the ComboBox. This is a binding for the ComboBox’s SelectedValue, and we bind it to the CategoryCode property in the library (the list of Books). Note that we don’t specify a Source for this binding, so it will use the data context, which is the same library as the ListBox uses. By binding the ComboBox’s SelectedValue to the CategoryCode in the CurrentItem in the library, we synchronize the category displayed by the ComboBox to the category in the CurrentItem (the current Book being displayed). This means that if we change the category by typing a new number into the Code TextBox, then because that TextBox is bound to the CategoryCode in the Book, it will update that property. This in turn will cause the ComboBox’s SelectedValue to change. Then, due to the first binding between the CategoryList and the ComboBox, the new SelectedValue will be used to look up the corresponding DisplayMember, and the display of the ComboBox will be updated. The process also works in reverse: if you select a new category from the ComboBox, the same chain of events (in reverse) will cause the CategoryCode in the Book to change, which in turn will cause the Code TextBox to update.

There are only a couple of other minor changes that were made in this code compared to that in the previous post. The UpdateButtonStates() method has an extra line to ensure that the currently selected item in the ListBox is always visible, using the ScrollIntoView() method in ListBox:

    private void UpdateButtonStates()
    {
      CollectionView libraryView = GetLibraryView();
      previousButton.IsEnabled = libraryView.CurrentPosition > 0;
      nextButton.IsEnabled = libraryView.CurrentPosition < libraryView.Count - 1;
      booksListBox.ScrollIntoView(booksListBox.SelectedItem);
    }

Finally, the event handler for the Add button is modified to provide a category for a new book:

    private void addButton_Click(object sender, RoutedEventArgs e)
    {
      int newBookNum = library.Count + 1;
      library.Add(new Book("Author " + newBookNum, "Title " + newBookNum,
        BookPrice(), rand.Next(1, CategoryList.Count)));
      CollectionView libraryView = GetLibraryView();
      libraryView.MoveCurrentToLast();
      UpdateButtonStates();
    }

We generate the category randomly from those available in CategoryList.

Note that we didn’t need to change any of the logic for things like moving around the list, adding to the list, sorting or filtering. All of that is handled by the collection view, and the binding between the ListBox and ComboBox and the collection view works in exactly the same way as between the more cumbersome TextBox representation we used in the previous post.

Code available here.