Tag Archives: DataGrid

LINQ for XML – the basics

LINQ provides a library of classes and methods that allow XML to be generated and imported quite easily (certainly more easily than with previous .NET libraries).

We’ll assume the reader is familiar with the basics of XML syntax and dive in with a simple little program that allows the user to enter some details for books in a library, then store this data to a disk file as XML (and of course to read in data from an XML file and display it).

The GUI is a WPF DataGrid and a menu for handling file operations, as shown:

We’ll represent the data internally using a Book class to represent each book, and an ObservableCollection to represent the collection of books. The data structures are similar to those that we used in discussing data binding to lists and combo boxes. The Book class is a bit simpler than it was there:

using System.ComponentModel;
namespace LinqXml02
{
  public 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
      {
        author = value;
        Notify("Author");
      }
    }

    string title;

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

    decimal price;

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

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

The ObservableCollection is created in a special class called Library:

using System;
using System.Collections.ObjectModel;

namespace LinqXml02
{
  public class Library
  {
    Random rand = new Random();
    private decimal BookPrice()
    {
      decimal price = rand.Next(0, 5000) / 100m;
      return price;
    }

    public ObservableCollection<Book> GetLibrary()
    {
      ObservableCollection<Book> library = new ObservableCollection<Book>();
      return library;
    }
  }
}

This class serves as a resource in the XAML file:

<Window x:Class="LinqXml02.MainWindow"
        xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
        xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
        xmlns:local="clr-namespace:LinqXml02"
        Title="MainWindow" Height="350" Width="525">
    <Window.Resources>
        <ObjectDataProvider x:Key="LibraryGrid"
                            ObjectType="{x:Type local:Library}"
                            MethodName="GetLibrary"/>
    </Window.Resources>
    <Grid DataContext="{StaticResource LibraryGrid}" HorizontalAlignment="Stretch">
        <Grid.RowDefinitions>
            <RowDefinition Height="Auto"/>
            <RowDefinition/>
        </Grid.RowDefinitions>
        <Menu VerticalAlignment="Top">
            <MenuItem Header="_File">
                <MenuItem x:Name="saveMenuItem" Header="_Save" HorizontalAlignment="Left" Width="145" Click="saveMenuItem_Click"/>
                <MenuItem x:Name="saveAsMenuItem" Header="Save _as" HorizontalAlignment="Left" Width="145" Click="saveAsMenuItem_Click"/>
                <MenuItem x:Name="openMenuItem" Header="_Open" HorizontalAlignment="Left" Width="145" Click="openMenuItem_Click"/>
                <Separator HorizontalAlignment="Left" Width="145"/>
                <MenuItem x:Name="exitMenuItem" Header="E_xit" HorizontalAlignment="Left" Width="145" Click="exitMenuItem_Click"/>
            </MenuItem>
        </Menu>
        <DataGrid x:Name="bookGrid" Grid.Row="1" ItemsSource="{Binding}" AutoGenerateColumns="False"  HorizontalAlignment="Stretch">
            <DataGrid.Columns>
                <DataGridTextColumn Header="Author" Binding="{Binding Author}" Width="45*"/>
                <DataGridTextColumn Header="Title" Binding="{Binding Title}"  Width="45*"/>
                <DataGridTextColumn Header="Price" Binding="{Binding Price}"  Width="10*"/>
            </DataGrid.Columns>
        </DataGrid>

    </Grid>
</Window>

On lines 7 to 9 we create the resource, then use it as the data context for the Grid on line 11. The DataGrid defined on line 25 uses this data context as the binding for its ItemsSource property, and then we define the three columns, each bound to a property in the Book class. We could have used the auto-generate column feature of a DataGrid, but that doesn’t allow us to customize the widths of the columns, which we’ve done here by assigning each of the Author and Title columns 45% of the horizontal width, with Price getting the remaining 10%.

With the data structures set up and the binding in place, we could run the program and enter some book data, and the data binding will automatically update the ObservableCollection as we enter data into the DataGrid. However, at this stage we have no way of saving the data thus entered. For that we introduce the XML.

First, we’ll have a look at the event handlers for the Save and Save As menu items.

    string saveFilename = "";
    private void saveAsMenuItem_Click(object sender, RoutedEventArgs e)
    {
      SaveFileDialog saveDialog = new SaveFileDialog();
      saveDialog.Filter = "XML file|*.xml";
      saveDialog.Title = "Save library";
      if (saveDialog.ShowDialog() == true)
      {
        saveFilename = saveDialog.FileName;
        saveMenuItem_Click(sender, e);
        Title = "Library - " + saveDialog.FileName;
      }
    }

    private void saveMenuItem_Click(object sender, RoutedEventArgs e)
    {
      if (saveFilename.Equals(""))
      {
        saveAsMenuItem_Click(sender, e);
      }
      else
      {
        XElement saveLibraryXml = XmlFromLibrary();
        saveLibraryXml.Save(saveFilename);
      }
    }

The SaveFileDialog (and OpenFileDialog) classes are in the old Microsoft.Win32 namespace, but they still seem to work well enough. In order to allow us to save changes to a currently open file, we have an auxiliary string called saveFilename. If this string has zero length, then we open the SaveFileDialog to get the user to select a filename. The dialog has a filter that displays only .xml files.

Once a file has been chosen, the saveMenuItem_Click() handler is called, and the method XmlFromLibrary() is called. We’ll consider this in a moment, but first we need to describe the XElement class.

In LINQ’s handling of XML, all XML tags are represented by XElement objects. There is no need for a separate, top-level document object in which to place the XElements; XElement itself can serve as the top level, and all lower levels.

Nested tags in the XML are represented simply as nested XElement objects. This gives the C# code a structure that is easy to understand for the human reader.

Now we can have a look at XmlFromLibrary():

    private XElement XmlFromLibrary()
    {
      ObservableCollection<Book> library = (ObservableCollection<Book>)((ObjectDataProvider)FindResource("LibraryGrid")).Data;
      XElement libraryElement =
        new XElement("LIBRARY",
          library.Select(book =>
            new XElement("BOOK",
              new XElement("AUTHOR", book.Author),
              new XElement("TITLE", book.Title),
              new XElement("PRICE", book.Price))));
      return libraryElement;
    }

After retrieving ‘library’ from the Windows resources, we create the XML representation of the library with a single C# statement. The top level object is libraryElement, which is given the tag LIBRARY. The second argument to its contructor is built using a LINQ Select() call on library. Remember that library consists of a list of Book objects, so we simply iterate through each Book in the list, and construct a new XElement for each Book. Within the Book’s XElement, we add 3 more XElements for the Author, Title and Price fields.

And that’s it. The code is very clean. Back in saveMenuItem_Click(), we simply call the Save() method from the XElement object to save the file to disk. The resulting file for the books shown in the picture above is:

<?xml version="1.0" encoding="utf-8"?>
<LIBRARY>
  <BOOK>
    <AUTHOR>Asimov, Isaac</AUTHOR>
    <TITLE>I, Robot</TITLE>
    <PRICE>3.50</PRICE>
  </BOOK>
  <BOOK>
    <AUTHOR>Niven, Larry</AUTHOR>
    <TITLE>Ringworld</TITLE>
    <PRICE>4.95</PRICE>
  </BOOK>
  <BOOK>
    <AUTHOR>Asimov, Isaac</AUTHOR>
    <TITLE>Foundation</TITLE>
    <PRICE>2.25</PRICE>
  </BOOK>
  <BOOK>
    <AUTHOR>Simak, Clifford D.</AUTHOR>
    <TITLE>Buckets of Diamonds</TITLE>
    <PRICE>5.00</PRICE>
  </BOOK>
</LIBRARY>

The Save() method produces the usual first line of an XML file, and then writes out the XML itself, all neatly indented.

To read the XML file back into the program, we need to construct the internal ObservableCollection from the XML. This is almost as easy as producing the XML in the first place. Here’s the code for the Open menu item, and the associated LibraryFromXml() method that reads the XML:

    private void openMenuItem_Click(object sender, RoutedEventArgs e)
    {
      OpenFileDialog openDialog = new OpenFileDialog();
      openDialog.DefaultExt = ".xml";
      openDialog.Filter = "XML documents (.xml)|*.xml";
      bool? result = openDialog.ShowDialog();
      if (result == true)
      {
        XElement libraryXml = XElement.Load(openDialog.FileName);
        Title = "Library - " + openDialog.FileName;
        LibraryFromXml(libraryXml);
        saveFilename = openDialog.FileName;
      }
    }

    private void LibraryFromXml(XElement libraryXml)
    {
      ObservableCollection<Book> library = (ObservableCollection<Book>)((ObjectDataProvider)FindResource("LibraryGrid")).Data;
      library.Clear();
      var bookElements = libraryXml.Elements("BOOK");
      foreach (XElement book in bookElements)
      {
        Book addBook = new Book(
          (string)book.Element("AUTHOR"),
          (string)book.Element("TITLE"),
          (decimal)book.Element("PRICE"));
        library.Add(addBook);
      }
    }

In the openMenuItem_Click() handler, we use the static XElement.Load() method to read the XML from the file into an XElement.

In LibraryFromXml() we again retrieve the library resource and clear it of existing data. Then we call the Elements() method on the XElement to retrieve a list of BOOK tags. This produces an IEnumerable list of XElements for the BOOK objects in the original XML. For each of these, we simply create a Book object by extracting the AUTHOR, TITLE and PRICE XElements for each BOOK, and then add this Book object to the library. The data binding takes care of the rest, so the DataGrid is automatically updated to display the list of books we read in.

There’s a lot more that can be done with LINQ and XML, but this little example should show you that for saving and reading basic XML, LINQ is easy to use.

Code for this post available here.

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.