Tag Archives: MySQL

LINQ with MySQL using DbLinq

Microsoft’s Language INtegrated Query, or LINQ extensions to C# allow SQL-like queries to be run on a variety of data sources, ranging from simple types such as arrays and lists to more complex data types such as XML files and databases. The built-in support for databases extends only as far as Microsoft’s own SQL Server, which is fair enough considering that’s their main database product. However, I’ve used MySQL for my own modest database requirements for many years, largely because it’s free and does everything I need.

We’ve already seen how to connect to MySQL from within a C# program, but in that example, we interacted with the database by constructing SQL commands as strings within C# and then using interface methods to pass these commands to the MySQL database, which took care of making the actual changes to the data. The main purpose of LINQ is to move the data processing commands into the C# language directly.

Doing this, however, does require that there is a lot of underlying code that handles the connection between C# and the database. LINQ as provided by Visual Studio contains all the tools you need to interact with regular data structures, XML and SQL Server, but if you want to talk to MySQL, you’ll need a third-party package to handle the interaction.

One such package that I’ve used only briefly is DbLinq, which provides interfaces between LINQ and not only MySQL, but a number of other popular databases as well. Since I’m interested only in MySQL, that’s all I’ll look at here.

Using DbLinq is fairly straightforward, although the lack of documentation can make it a bit of a trial to get running. I’ll run through the steps that I followed here, although if you’re reading this some time after a new version has come out, things may have changed.

First, go to the DbLinq site, follow the link to the zipped releases downloads page, and then get the zip file containing the source code (with an ‘src’ in its name). This is useful since this contains a ready-made Visual Studio project with several examples for the various databases supported. Unzip this file, go into the src folder and load DbLinq.sln in Visual Studio. Build the solution (which for me ran without errors).

If you want to run the MySQL example provided with the zip file, you’ll need to create the Northwind database on your local MySQL installation. An SQL file is provided which will allow you to do this. In the extracted folder from the zip file, go to the folder examples\DbLinq.MySql.Example\sql where you’ll find a file called create_Northwind.sql. You can use this file to create the database by opening a cmd window, cd to the folder where the SQL file is, then running mysql in command-line mode. You can then give the command “source create_Northwind.sql”, and this should create the database. Alternatively, there are several front-end programs such as sqlyog which can be used to load the file in a GUI.

Once you’ve got the Northwind database installed, you should be able to run the example program. In Visual Studio’s Solution Explorer, open the ‘examples’ folder and set DbLinq.MySql.Example as the startup project.  Open the Program.cs file and find the definition of the string connStr in the Main() method.  You’ll need to edit this to provide the login credentials for your own MySQL database. Once you’ve done that, you should be able to run the example and have it show you the results of a few LINQ commands.

If you know a bit of LINQ, you can play around with the Program class’s code at this point and experiment with accessing the Northwind database (although I found the program crashed when attempting to remove an item from the database). Obviously, though, you’ll want to use LINQ with your own MySQL databases at some point, so we’ll need to examine how you do that.

I mentioned above that Visual Studio provides a lot of background code to make LINQ work with various data sources, and that to get it to work with MySQL, you’ll need to provide this code. You might notice in the Northwind example that there is a large file called Northwind.cs, and if you look at the top of that file you’ll see it’s automatically generated code from a program called DbMetal. This is the one fiddly bit about using DbLinq: you’ll need to run DbMetal externally (outside Visual Studio) in order to generate the required code for the database you want to access.

DbMetal reads the structure of your database from MySQL and generates the interface code required to get LINQ to work with that database. Since each database has a different structure (different tables and so on), you’ll need to run DbMetal for each database you want to use. You’ll need to run it only once per database, unless you change the structure of the database by adding or deleting tables or adding or deleting columns from tables.

You’ll find a .bat file for running DbMetal in the src\DbMetal folder from your zip file. There is one .bat file for each database type, so for MySQL, look at run_myMetal.bat. If you open this file in notepad, you’ll see it looks like this:

REM: note that the '-sprocs' option is turned on

bin\DbMetal.exe -provider=MySql -database:Northwind -server:localhost -user:LinqUser -password:linq2 -namespace:nwind -code:Northwind.cs -sprocs

There are a few changes you’ll need to make to get this to work for your own database. First, there is no ‘bin’ folder below the one in which the .bat file is located, so the file won’t find DbMetal unless you delete the ‘bin\’ and then move the file to the folder where DbMetal.exe is located.

Second, of course, you’ll need to change the user and password to whatever is needed to access your MySQL installation. You will also need to change the name of the database, and you’ll probably also want to change the name of the namespace and code file that DbMetal will produce. I also deleted the -sprocs option since I got an error when it was there. Once you’ve done all that, you can run the .bat file in a cmd window, and it will produce the C# file (Northwind.cs in the example above). You can then copy this file into your Visual Studio project so you can start writing your own LINQ code on your own database.

To use the class generated by DbMetal, define the string connStr for connecting to the database as in the Northwind example, and then create an object from the DbMetal-generated class. For example, if your database is called Comics and you told DbMetal to create a file called Comics.cs in a namespace called comics you would add a “using comics;” line at the top of your file and then open a Comics object with lines:

            string dbServer = Environment.GetEnvironmentVariable("DbLinqServer") ?? "localhost";
            string connStr = String.Format("server={0};user id={1}; password={2}; database={3}"
                , dbServer, "<Your username>", "<Your password>", "Comics");

            Comics db = new Comics(new MySqlConnection(connStr));

One final thing you will need to do though: you’ll need to make sure the various dll files are available to Visual Studio. To do this, right-click on References in Solution Explorer and select Add Reference. Select the Browse tab and then navigate to the ‘build’ folder produced by building the original DbLinq project. To use DbLinq with MySQL, you’ll need to add DbLinq.dll and DbLinq.MySql.dll. To use MySQL itself, you’ll also need MySql.Data.dll, which is found in the ‘lib’ folder. You’ll know when you’ve got all the right files as without them, you’ll get compiler errors about symbols that can’t be found.

One final caution about DbLinq. As the web site itself says, it’s still prototype software and may not work for complex queries, so make sure you test it thoroughly before relying on it too much. For most simple queries, though, it should be fine.

Advertisements

DataGrids in WPF

In the last post, we saw how to connect a MySQL database to a WPF application, and how to bind a ComboBox to the DataTable containing the data from the database. In most applications, however, we’d like to view more than one record from the database at a time, and for that, the DataGrid is the ideal control. In the last post, we showed the UI containing a DataGrid for displaying the issues of a particular title of comic book. Here we’ll have a look at how this is achieved.

The process is similar to that used for binding a ComboBox to a DataTable, but a DataGrid is more versatile so there are a few tricks we need to get it to work properly. First, we need to get the data from the MySQL database, and we use the same technique as in the previous post. We create an ObjectDataProvider in the XAML:

    <ObjectDataProvider x:Key="BooksTable"
    ObjectType="{x:Type local:DatabaseTable}"
    MethodName="GetTable">
      <ObjectDataProvider.MethodParameters>
        <s:String>SELECT * FROM issues</s:String>
        <s:String>IssueYear, IssueMonth, IssueDay</s:String>
      </ObjectDataProvider.MethodParameters>
    </ObjectDataProvider>

In this case, we load all the columns from the ‘issues’ table in the database. The second string passed to the GetTable() method shows how we sort the data in the DataTable: first by IssueYear, then by IssueMonth and finally by IssueDay.

Now we need to see how to define a DataGrid that is bound to the DataTable and displays the columns we want. We have:

      <TabControl>
        <TabItem Header="Books" Margin="0"  DataContext="{StaticResource BooksTable}">
<!-- Code setting up properties of TabItem... -->
            <DataGrid ItemsSource="{Binding Mode=OneWay}" AutoGenerateColumns="False" IsSynchronizedWithCurrentItem="True"
                    x:Name="booksDataGrid" Margin="0,0,0,0" d:LayoutOverrides="Height" Grid.Row="2" Grid.ColumnSpan="2" HorizontalAlignment="Center">
              <DataGrid.Columns>
                <DataGridTextColumn Header="Vol" Width="Auto" Binding="{Binding Volume}"/>
                <DataGridTextColumn Header="#" Width="Auto" Binding="{Binding Number}"/>
                <DataGridTextColumn Header="Day" Width="Auto" Binding="{Binding IssueDay}"/>
                <DataGridTextColumn Header="Month" Width="Auto" Binding="{Binding IssueMonth}"/>
                <DataGridTextColumn Header="Year" Binding="{Binding IssueYear}"/>
                <DataGridTextColumn Header="Title" Binding="{Binding IssueTitle}" MaxWidth="400" Width="400"/>
                <DataGridHyperlinkColumn  Header="ComicVine" Binding="{Binding ComicVine}"
                                        ContentBinding="{Binding ComicVine, Converter={StaticResource ComicVineConverter}}"/>
              </DataGrid.Columns>
            </DataGrid>
        </TabItem>
      </TabControl>

We’ve shown the enclosing TabControl and TabItem since we set the data context of the TabItem to be the BooksTable StaticResource we created above. This is the default data context for all controls within this particular TabItem, so when we bind the DataGrid on line 4, the binding is to BooksTable.

The default behaviour of a DataGrid is to automatically generate a column for each column in the DataTable that it is displaying. We don’t want this to happen here, since we don’t want Key_Issues (the primary key for the issues table)  to be displayed. We therefore set AutoGenerateColumns to False.

The IsSynchronizedWithCurrentItem property determines whether the currently selected row in the DataGrid is synchronized with the CurrentItem in the collection view of the data source. Remember that WPF automatically inserts a collection view between a data source and the control which is bound to that source. This collection view maintains a CurrentItem marker that points to the currently selected item, and we want that item also to be selected in the DataGrid.

The other properties in the DataGrid deal mostly with its appearance, so we can move along to looking at the DataGrid’s column definitions. There are several pre-defined types of column that we can use. This application uses three of them: the DataGridTextColumn, which displays a text box in each cell in the grid, a DataGridHyperlinkColumn, which displays text as a hyperlink, and a DataGridComboBoxColumn, where each cell appears as text unless it is selected, in which case it turns into a ComboBox containing a fixed list of items (the DataGridComboBoxColumn is used in another part of the program, so we won’t examine it here). It is also possible to create your own custom type of DataGrid column, but we won’t need that here. The Books DataGrid displays all its columns as text, except for the ComicVine column, which is displayed as a hyperlink so the user can open the ComicVine web page for that comic book issue.

Each of the DataGridTextColumns is bound to one of the columns in the DataTable that is the data context. (The ComicVine column has an extra ContentBinding that we’ll get to later.)

As it stands, when the program starts, the DataGrid displays all the issues in the database, sorted by their dates. This isn’t terribly useful, so we need to arrange things so that when the user selects a title from the ComboBox, the DataGrid displays only those issues from that title. To do this, we add an event handler to the booksTitlesComboBox (the ComboBox we dealt with in the last post) to handle the SelectionChanged event. It looks like this:

    bool changingTitle = false;  // Prevent row changed events when changing title
    private void booksTitlesComboBox_SelectionChanged(object sender, SelectionChangedEventArgs e)
    {
      changingTitle = true;
      if (booksTitlesComboBox.SelectedValue != null)
      {
        DataTable booksTable = (DataTable)((DataSourceProvider)FindResource("BooksTable")).Data;
        int titleNumber = (int)booksTitlesComboBox.SelectedValue;
        databaseTable.GetTable("SELECT * FROM issues WHERE Title=" + titleNumber, booksTable);
      }
      changingTitle = false;
    }

The ‘changingTitle’ variable will be needed a bit later, so for now just ignore it. What we want to do is update the booksTable DataTable so that it contains only those issues corresponding to the currently selected title. We must first retrieve booksTable in the C# code, since it was defined in the XAML. This is done on line 7, where we used FindResource() to retrieve a resource with a given name. We need to cast this to a DataSourceProvider, and then extract its Data property, which we must then cast to a DataTable (complicated, but once we’ve done it, it’s the same format for retrieving any resource).

We then get the SelectedValue from booksTitleComboBox (remember that the SelectedValue is a Key_Titles value, not the textual Title; this is what we need for looking up issues based on their title).  On line 9, we construct a SQL query to select issues for that title. This uses a different version of the GetTable() method, which takes an existing DataTable and changes its contents. It looks like this:

    public void GetTable(String query, DataTable refreshDataTable)
    {
      refreshDataTable.Clear();
      String connString = "server=localhost;uid=root;pwd=xxxx;database=comics;";
      connection = new MySqlConnection(connString);
      adapter = new MySqlDataAdapter(query, connection);
      adapter.Fill(refreshDataTable);
    }

The current contents of the DataTable are cleared and then the table is refreshed by filling it using the MySqlDataAdapter with the revised query. Note that we don’t create a new DataTable object, so the Sort property we added when the table was created is still there. This means that the new data loaded into the table will still be sorted according to the year, month and day.

That’s all we need to do to refresh the DataGrid, since the DataGrid is bound to booksTable. Any changes to booksTable automatically are reflected in the DataGrid, and vice versa.

Editing data

By default, the cells in a DataGrid are editable, so to change the data for a particular issue, all we need to do is select the cell by double-clicking on it, and then type in the new text. Due to the data binding between the DataGrid and DataTable, these changes are automatically transmitted to the DataTable. However, these changes will not be passed on to the original MySQL database unless we write some code to make this happen. This is one place where using a MySQL database requires more hand-crafted code than using a DataSet for interacting with one of Microsoft’s databases such as SQL Server or Access. MySQL requires us to define a specific command for updating (or inserting and deleting) a record, so we need to see how to do this.

Since we want any change to the booksTable DataTable to be saved back to the MySQL database, we can add a handler for the RowChanged event in booksTable. We can do this by adding this line to the MainWindow() constructor:

      booksTable.RowChanged += new DataRowChangeEventHandler(booksTable_RowChanged);

The handler method is:

    void booksTable_RowChanged(object sender, DataRowChangeEventArgs e)
    {
      if (!changingTitle)
      {
        UpdateDBIssues();
      }
    }

Remember the ‘changingTitle’ variable we declared earlier? We need to call the UpdateDBIssues() method (which writes updates back to the MySQL database – we’ll look at it in a minute) only when the user actually makes changes to rows in the DataGrid by editing them. It turns out that when we change the title being viewed in the DataGrid, every row that gets inserted into booksTable by our call to GetTable() generates a RowChanged event, and if we called UpdateDBIssues() when that happened, we would end up duplicating all the records in the database. The ‘changingTitle’ parameter is a bit of a kludge, but it ensures that we update the database only when genuine changes to existing records have been made.

Now we need to see how to update the database. We have:

    private void UpdateDBIssues()
    {
      MySqlConnection connection = new MySqlConnection("server=localhost;uid=root;pwd=xxxx;database=comics;");
      string updateString = "UPDATE issues SET IssueTitle=?IssueTitle, Title=?Title, Volume=?Volume, Number=?Number, " +
        "IssueDay=?IssueDay, IssueMonth=?IssueMonth, IssueYear=?IssueYear, ComicVine=?ComicVine WHERE " +
        "Key_Issues=?oldKey_Issues";
      MySqlCommand updateCommand = new MySqlCommand(updateString, connection);
      updateCommand.Parameters.Add("?IssueTitle", MySqlDbType.VarChar, 100, "IssueTitle");
      updateCommand.Parameters.Add("?Title", MySqlDbType.Int32, 10, "Title");
      updateCommand.Parameters.Add("?Volume", MySqlDbType.Int32, 10, "Volume");
      updateCommand.Parameters.Add("?Number", MySqlDbType.VarChar, 10, "Number");
      updateCommand.Parameters.Add("?IssueDay", MySqlDbType.Int32, 10, "IssueDay");
      updateCommand.Parameters.Add("?IssueMonth", MySqlDbType.Int32, 10, "IssueMonth");
      updateCommand.Parameters.Add("?IssueYear", MySqlDbType.Int32, 10, "IssueYear");
      updateCommand.Parameters.Add("?ComicVine", MySqlDbType.VarChar, 100, "ComicVine");
      MySqlParameter parameter = updateCommand.Parameters.Add("?oldKey_Issues", MySqlDbType.Int32, 10, "Key_Issues");
      parameter.SourceVersion = DataRowVersion.Original;
      MySqlDataAdapter adapter = new MySqlDataAdapter();
      adapter.UpdateCommand = updateCommand;

      string insertString = "INSERT INTO issues (Key_Issues, IssueTitle, Title, Volume, Number, IssueDay, IssueMonth, IssueYear, ComicVine) " +
        "VALUES (?Key_Issues, ?IssueTitle, " + booksTitlesComboBox.SelectedValue + ", ?Volume, ?Number, ?IssueDay, ?IssueMonth, ?IssueYear, ?ComicVine)";
      MySqlCommand insertCommand = new MySqlCommand(insertString, connection);
      insertCommand.Parameters.Add("?Key_Issues", MySqlDbType.Int32, 10, "Key_Issues");
      insertCommand.Parameters.Add("?IssueTitle", MySqlDbType.VarChar, 100, "IssueTitle");
      insertCommand.Parameters.Add("?Volume", MySqlDbType.Int32, 10, "Volume");
      insertCommand.Parameters.Add("?Number", MySqlDbType.VarChar, 10, "Number");
      insertCommand.Parameters.Add("?IssueDay", MySqlDbType.Int32, 10, "IssueDay");
      insertCommand.Parameters.Add("?IssueMonth", MySqlDbType.Int32, 10, "IssueMonth");
      insertCommand.Parameters.Add("?IssueYear", MySqlDbType.Int32, 10, "IssueYear");
      insertCommand.Parameters.Add("?ComicVine", MySqlDbType.VarChar, 100, "ComicVine");
      adapter.InsertCommand = insertCommand;

      MySqlCommand deleteCommand = new MySqlCommand("DELETE FROM issues WHERE Key_Issues=?Key_Issues", connection);
      MySqlParameter delParameter = deleteCommand.Parameters.Add("?Key_Issues", MySqlDbType.Int32, 10, "Key_Issues");
      delParameter.SourceVersion = DataRowVersion.Original;
      adapter.DeleteCommand = deleteCommand;

      DataTable booksTable = (DataTable)((DataSourceProvider)FindResource("BooksTable")).Data;
      adapter.Update(booksTable);
    }

This method looks complicated, but it’s quite straightforward. We’ve included the code for inserting and deleting records as well, since we’ll be doing that shortly. Consider the update code. Lines 4 to 6 build the SQL for updating a record from the issues table. We need to add a parameter for each column we wish to update, and the names prefixed by a ? are place-holders for values that the command will read from booksTable for each row that has changed. The arguments to a call to Add() are: the place-holder name, such as ?IssueTitle, the data type of this parameter and the size of this data type (these can be read off the original definition in the database), and the name of the column to update.

The one special column is the Key_Issues column. This primary key is the one used to determine which record to update (it’s in the WHERE clause in the SQL). We determine which row to update by looking up the Key_Issues value. Line 16 adds this parameter to the command, and line 17 specifies that the value of Key_Issues is taken from the original version of the table (before the modification).

With the command and all its parameters built, we attach the command as the UpdateCommand property of the adapter on line 19. Similar commands are built for inserting and deleting rows on lines 21 to 37. Finally line 39 retrieves booksTable (the table containing the changed rows) and line 40 calls the Update() command, which is what actually writes the changes back to the MySQL database.

Deleting a row from the DataGrid is done by selecting the row and then pressing the Delete key.  By adding a handler for booksTable’s RowDeleted event which calls UpdateDBIssues(), the rows are also deleted from the original database.

Finally, we consider adding a new issue. This is most easily done by scrolling down to the last row in the DataGrid and typing in the new data, then hitting return. This adds a new row to booksTable, and since adding a row counts as a RowChanged event, UpdateDBIssues() is called and the ‘insert’ command adds the row to the database.

There’s one slight problem though. When we enter the data for a new row, we don’t specify the Key_Issues primary key. Ideally we would like this to be generated automatically. Fortunately, a DataTable has an AutoIncrement property for its columns. There’s one snag though: by default, AutoIncrement starts by assigning a value of 0 to the column and increments from there. In the MySQL database, of course, we will usually have a number of records already present, so the primary key Key_Issues will already be full up to some non-zero value. We therefore need to set the DataTable’s starting value equal to the maximum value of the column from the database. We can do that as follows. First, write a little method that gets the maximum value of a column:

    private int GetMaxKey(string column, string table)
    {
      string query = "SELECT max(" + column + ") FROM " + table + ";";
      MySqlConnection connection = new MySqlConnection("server=localhost;uid=root;pwd=xxxx;database=comics;");
      MySqlCommand maxCommand = new MySqlCommand(query, connection);
      maxCommand.Connection.Open();
      int maxKey = (int)maxCommand.ExecuteScalar();
      maxCommand.Connection.Close();
      return maxKey;
    }

This method uses SQL’s max() function to retrieve the maximum value of the given column from the given table. We use the ExecuteScalar() function to retrieve this single value. ExecuteScalar() returns the first element from a SQL query only, discarding the rest, and that’s just what we want here.

We use this value to set up the DataTable’s AutoIncrement property by inserting the following in MainWindow’s constructor:

      DataTable booksTable = (DataTable)((DataSourceProvider)FindResource("BooksTable")).Data;
      booksTable.Columns["Key_Issues"].AutoIncrement = true;
      booksTable.Columns["Key_Issues"].AutoIncrementSeed = GetMaxKey("Key_Issues", "issues") + 1;

This sets the AutoIncrement to start at one higher than the current maximum value, which is just what we need to synchronize the DataTable with the database. It is important that the primary key Key_Issues is not set to auto-increment in the MySQL database. The reason is that MySQL keeps track of the highest primary key value that has ever been used for a given table, which the Max() function returns the highest existing value for Key_Issues. If you’ve added some records and then deleted them, these two values will not be the same. By assigning the auto-increment to the DataTable only, we ensure that the same primary key is used both in the program and in the database.

By the way, even though it is easy enough to delete entries from the DataGrid by selecting them and pressing the delete key, it is sometimes handy to be able to do this in code. It took ages to discover how to do this, but I have a little method that does seem to work so I’ll throw it in here.

Suppose we add a button to the Books tab which will delete all selected rows when pressed. The event handler for this button will look like this:

    private void deleteButton_Click(object sender, RoutedEventArgs e)
    {
      if (booksDataGrid.SelectedItems.Count == 0) return;
      IList items = booksDataGrid.SelectedItems;
      IEnumerable<DataRowView> toDelete = items.Cast<DataRowView>();
      try
      {
        List<DataRowView> rows = new List<DataRowView>(toDelete);
        foreach (DataRowView row in rows)
        {
          row.Row.Delete();
        }
      }
      catch (InvalidCastException)
      {
        return;
      }
    }

On line 3, we check to make sure that at least one row is selected. Line 4 extracts a list of selected items from the DataGrid. This list is returned as a list that implements the IList interface, which is simply a list that can contain any data type, so at the moment the elements in this list are simply of the ‘object’ data type. Line 5 casts this list into an IEnumerable type whose elements are of type DataRowView (the view of a DataRow within a DataTable). The syntax in line 5 makes use of the Enumerable.Cast method, which is part of the System.Linq namespace, so you’ll need a ‘using System.Linq’ line at the top of your code file.

So far, so good, but there is a catch here. Since we want to delete elements from the DataTable (which, through data binding, will also delete rows from the DataGrid), we need to do it in such a way that we don’t disrupt the IEnumerable object. If we tried to delete objects directly from this object, we are trying to modify the collection while iterating over it, which will throw an exception. To get around this, we need to make a copy of the collection into an ordinary List, and use that for the iteration. That’s what we do on lines 8 to 12.

The try-catch block is there because after all the selected rows have been deleted, the DataGrid will position the selected row on the empty row at the bottom of the DataGrid if there are no other rows in the grid. This row has no associated entry in the DataTable, so attempting to access it throws an exception. There doesn’t seem to be any elegant way of detecting whether an row in the DataGrid has a partner in the DataTable, so it seems easiest to just catch the exception and ignore the error.

That completes the code for displaying a DataGrid bound to a DataTable, and allowing the user to edit, add and delete records from the database by editing the DataGrid. One final topic needs to be covered to complete the setup of this DataGrid, and that’s the configuration of the hyperlink column to display the links to the ComicVine website. We’ll have a look at that in the next post.

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.