Data Visualization in WPF with LINQ to SQL and Data Binding

• Download executable - 511.92 KB
• Download source code - 525.1 KB

Contents

• Introduction
• Requirements
• LINQ to SQL
• Styles in the Application
• Sorting and Grouping Data
• Filtering Data
• Conclusion
• History

Introduction

WPF introduced many new features to .NET development: separation of code and interface, data binding and styles are some of them. These new features allow the development of applications that reach a new level of user experience. The developer can concentrate on the business logic while the designer can create a UI that gives the user the best user experience.

Another great feature introduced in WPF is data binding, where you can link object properties to the user interface with no need for code.

This article will show how to use data binding and styles to show data coming from a Microsoft SQL database using the new object-relational model introduced in Visual Studio 2008, LINQ to SQL, allowing grouping, sorting and filtering of data with almost no code.

Requirements

To build the article project, you will need to use Visual Studio 2008 and .NET Framework 3.5 to use the new features introduced: LINQ, Lambda Expressions and type inference. You will also need to have Microsoft SQL Server 2005 installed, as we are using an SQL Server database.

LINQ to SQL

Visual Studio 2008 introduced a new technology named LINQ (Language Integrated Query), which allows a standard way to work with data, with the same query language, no matter where it comes from. It has several "flavors" that access data coming from different sources:

• LINQ to Objects - access data coming from CLR collections
• LINQ to SQL - access data from Microsoft SQL databases
• LINQ to Entities - access data from ADO.NET entity providers. This data access is still in beta and should be released in 2008
• LINQ to Datasets - access data from typed or untyped datasets
• LINQ to XML - access data from XML sources

Using LINQ, you can query the data with the same syntax for any kind of data, using something like:

var query = from d in ObjectCollection
            select d;

In the command above, the var keyword in the query is another new feature introduced in C# 3.0/Visual Studio 2008. It doesn't mean that C# is becoming a loosely typed language. C# continues to be a strong typed language, but the type of the query variable is inferred by the compiler: at compile time, the compiler finds out its type and generates the Intermediate Language (IL) for the variable type.

The query language is very powerful, you can filter, sort and do aggregates (sum, count, average, etc.) on the data.

LINQ to SQL allows the access to Microsoft SQL databases in an object-oriented way: with it, you have full access to the database, its tables, stored procedures and relations and still have the features available in Visual Studio, like debugging and Intellisense. You can also use data binding in WPF applications, and we will use this feature to show the database tables in a WPF ListBox.

To use LINQ to SQL, we must add a model to the project, using the "Add new item" project context menu item and selecting "LINQ to SQL classes". This will add a blank LINQ to SQL model. Dropping the tables from the server Explorer into the model will create a class model like the one shown in Figure 1.

In this project we are using a Microsoft SQL Server database with two tables, Albums and Songs. Albums contain the names of the Beatles albums, while Songs contain the Beatles songs data. A foreign key links the songs to their albums. As you can see in Figure 2, the foreign key is mapped as a relation between the classes. The Album class has a Songs property, a list of songs in the album. On the other side, the Song class has an Album property pointing to its album data.

To access this data we must create a LINQ query. This is done in the window constructor, in Window1.xaml.cs:

BeatlesDataContext dc = new BeatlesDataContext();

var query = from s in dc.Songs
            select s;
dataListBox.ItemsSource = query.ToList();

In the first line, we create a DataContext. It is responsible for the mapping between the database and the objects used in our queries. The second line is the LINQ query. You can notice that, as you type, you get Intellisense for the data and Albums and Songs are two members of the DataContext dc. The third line links the query results to the ListBox. We are using databinding here, saying that the items in the ListBox must be filled with the query results. If we run the application at this point, we should get something like Figure 2:

That's not what we expect: WPF doesn't know what to show, so it shows the result of the ToString() method of the Song class, one for each song. One way to change this and show the album name is to use the DisplayMemberPath ListBox property to say what we want to display:

<ListBox x:Name="listBox1" Margin="10" DisplayMemberPath="Name"/>

Doing this will only show the songs' names in the list, but we want more than that: we want to show two lines for each song, one with the song name and duration and the other with the album name. We can do this using a template for the items in the list:

<Window.Resources>
  <!-- Data template for the listbox items -->
  <DataTemplate x:Key="SongsTemplate">
    <StackPanel>
      <StackPanel Orientation="Horizontal">
        <TextBlock Text="{Binding Path=Name}" FontWeight="Bold" />
        <TextBlock Margin="5,0,0,0" Text="{Binding Path=Duration}"/>
      </StackPanel>
      <TextBlock Text="{Binding Path=Album.Name}" />
    </StackPanel>
  </DataTemplate>
</Window.Resources>

As we said before, the relation between the Albums and Songs tables are mapped into the object model, so the Song class has an Album member, containing the album data. We use this feature to show the album name in the list with <TextBlock Text="{Binding Path=Album.Name}" />. We must say that the items template for the ListBox is the one we've created:

<ListBox HorizontalAlignment="Stretch" Margin="5" Name="listBox1"
    VerticalAlignment="Stretch" HorizontalContentAlignment="Stretch"
    ItemTemplate="{StaticResource SongsTemplate}"/>

Styles in the Application

Until now, we have introduced some new LINQ concepts, but WPF has a lot more to offer. We can change the appearance for the ListBox items through a style:

  <!-- Style for the Listbox items - Show border and content -->
  <Style TargetType="ListBoxItem" x:Key="SongsItemContainerStyle">
    <Setter Property="Template">
      <Setter.Value>
        <ControlTemplate TargetType="ListBoxItem">
          <Border x:Name="outsideBorder" Background="#FDF356"
              Margin="2" CornerRadius="3" Padding="5"
              BorderBrush="Black" BorderThickness="1" >
            <ContentPresenter Margin="2" RecognizesAccessKey="True"
                HorizontalAlignment="Stretch"/>
          </Border>
          <!-- Trigger when item is selected - change border stroke and background -->
          <ControlTemplate.Triggers>
            <Trigger Property="IsSelected" Value="True" >
              <Setter TargetName="outsideBorder" Property="Background" Value="#FBA23A"/>
            </Trigger>
          </ControlTemplate.Triggers>
        </ControlTemplate>
      </Setter.Value>
    </Setter>
  </Style>

This style shows a border around the items and has an associated trigger: when the item is selected, the border color and background change to give a visual feedback for the user. We must assign the style to the ListBox, using the ItemContainerStyle property:

 <ListBox x:Name="dataListBox" Grid.Row="1" Margin="5"
    ItemTemplate="{StaticResource SongsTemplate}"
    ItemContainerStyle="{StaticResource SongsItemContainerStyle}" />

We have more data to show, and the best way to do it is to add a tooltip, so the extra information is shown when the mouse is over an item. The tooltip is also customizable, we can create a template for the tooltip with the data we want to show:

  <!-- Style for the tooltip - Show border and content -->
  <Style TargetType="{x:Type ToolTip}">
    <Setter Property="Template">
      <Setter.Value>
        <ControlTemplate TargetType="{x:Type ToolTip}">
          <Border Background="#698390" Opacity="0.95" Margin="2"
               CornerRadius="3" Padding="5" BorderBrush="Black"
               BorderThickness="1" >
             <ContentPresenter Margin="10,5,10,5"
               HorizontalAlignment="Center"
               VerticalAlignment="Center"
               TextBlock.Foreground="Black" TextBlock.FontSize="12"/>
          </Border>
        </ControlTemplate>
      </Setter.Value>
    </Setter>
    <Setter Property="FontStyle" Value="Italic" />
    <Setter Property="Foreground" Value="White" />
    <Setter Property="Placement" Value="Top" />
    <Setter Property="HorizontalOffset" Value="20" />
  </Style>

With this template, the tooltip is shown with rounded corners. The content for the tooltip is assigned in the Data template for the list items:

   <!-- Template for the tooltip - Show album image and extra data -->
      <StackPanel.ToolTip>
        <StackPanel Orientation="Horizontal">
          <Border CornerRadius="2" BorderBrush="#FFFCF7" Padding="2"
               BorderThickness="2">
            <Image Width="117" Height="117"
                 Source="{Binding Path=Album.Cover,
                 Converter={StaticResource CoverConvert}}" />
          </Border>
         <StackPanel Margin="5" MaxWidth="600">
            <StackPanel Margin="5" Orientation="Horizontal">
              <TextBlock FontWeight="Bold"
                     Text="{Binding Path=Album.Name}" />
              <TextBlock Margin="5,0,0,0" Text="(" />
              <TextBlock Text="{Binding Path=Album.Year}"
                    HorizontalAlignment="Right" />
              <TextBlock Text=")" />
            </StackPanel>
            <TextBlock Text="{Binding Path=Recording}"/>
            <TextBlock Text="{Binding Path=RecordingPlace}"/>
            <TextBlock Text="{Binding Path=Details,
                  Converter={StaticResource DetailConvert}}"
                  TextWrapping="Wrap"/>
          </StackPanel>
        </StackPanel>
      </StackPanel.ToolTip>

In this tooltip we are showing a lot of data: the album cover image, the album name and year, the recording data, place and details for the song, but there is a catch: the database stores only the cover name, while the physical file also has the folder name and extension. To allow the binding of the cover name to the image source, we must create a converter that will take the name of the cover in the database and return a valid URI for the file. Converters are code classes that implement the IConvertValue interface. This interface has two methods, Convert and ConvertBack. Convert will make the conversion from the database value to the URI and ConvertBack will not be needed. The implementation of the class is:

{
    #region IValueConverter Members

    public object Convert(object value, Type targetType,
      object parameter, System.Globalization.CultureInfo culture)
    {
      return new Uri("..\\AlbumsBeatles\\" + value.ToString().Trim() +
          "-A.jpg", UriKind.Relative);
    }

    public object ConvertBack(object value, Type targetType,
      object parameter, System.Globalization.CultureInfo culture)
    {
        throw new NotSupportedException();
    }

    #endregion
}

As we don't need the conversion from the URI to the cover name, we throw an exception for the ConvertBack method. We must add a new XAML namespace to use the converter and declare it in the resource section:

    "xmlns:src="clr-namespace:Views"
    ...
    <!-- Converter for the cover name to Image URI -->
    <src:NametoURIConverter x:Key="CoverConvert" />

The Details field is a string field with tab characters (#9 - \t) separating the lines. We must create a converter that will change the tab characters to new line characters. This is done with the following converter:

public class TabToNewLineConverter : IValueConverter
{
    #region IValueConverter Members

    public object Convert(object value, Type targetType,
      object parameter, System.Globalization.CultureInfo culture)
    {
        return value.ToString().Replace('\t','\n').Trim();
    }

    public object ConvertBack(object value, Type targetType,
      object parameter, System.Globalization.CultureInfo culture)
    {
        throw new NotImplementedException();
    }

    #endregion
}

It is declared with this XAML snippet:

    <!-- Converter for the details - converts tabs to new lines -->
    <src:TabToNewLineConverter x:Key="DetailConvert" />

Sorting and Grouping Data

One way to sort the data is to change the LINQ query to add an OrderBy clause:

BeatlesDataContext dc = new BeatlesDataContext();

var query = from s in dc.Songs
            orderby s.Album.Name
            select s;

dataListBox.ItemsSource = query.ToList();

or

BeatlesDataContext dc = new BeatlesDataContext();

var query = from s in dc.Songs
            select s;

dataListBox.ItemsSource = query.OrderBy(s =>s.Album.Name).ToList();

The expression s => s.Album.Name is a new feature in C# 3.0 called Lambda Expression. It's an anonymous method that can be read as: given a parameter s (of type Song, inferred by the compiler), return the album name for that song.

The changed queries would require a new query to the database and would show the data in the new order, but that's not the best way to do it in WPF. WPF has the concept of Views: once you have a set of data, you can create views on it and sort, group or even filter it with no need to query the database again. To sort a view, we add a SortDescription to the SortDescriptions property of the view:

ICollectionView view = CollectionViewSource.GetDefaultView(dataListBox.ItemsSource);
view.SortDescriptions.Clear();
view.SortDescriptions.Add(new SortDescription(
  (sender as RadioButton).Tag.ToString(), ListSortDirection.Ascending));

That's enough to sort the list. The sorting order is determined by clicking on a RadioButton with the desired order:

<StackPanel Orientation="Horizontal" Grid.Row="1"
      Background="Transparent">
  <TextBlock Text="Sort by: " Margin="10,5" Foreground="Yellow"
      FontWeight="Bold" />
  <RadioButton x:Name="radioButton1" Tag="Name"
      Click="RadioButton_Click" Content="Name" />
  <RadioButton x:Name="radioButton2" Tag="Album.Name"
      Click="RadioButton_Click" Content="Album" />
  <RadioButton x:Name="radioButton3" Tag="Duration"
      Click="RadioButton_Click" Content="Duration" />
  <RadioButton x:Name="radioButton4" Tag="Album.Name"
      Click="RadioButton_Click" Content="Grouped" />
</StackPanel>

The RadioButton's Tag property stores the sort order used as the first parameter in the SortDescription constructor. The code to sort is executed in the RadioButton_Click event handler.

Grouping is almost the same as sorting: you must create a new GroupDescription and add it to the GroupDescriptions property of the view:

view.GroupDescriptions.Clear();
if (sender == radioButton4)
{
    view.GroupDescriptions.Add(
        new PropertyGroupDescription("Album.Name"));
    view.SortDescriptions.Add(new SortDescription("Name",
        ListSortDirection.Ascending));
}

If we execute the code now, the grouping isn't shown. We must also add a GroupStyle to the ListBox:

<ListBox.GroupStyle>
  <GroupStyle HeaderTemplate="{StaticResource GroupTemplate}" />
</ListBox.GroupStyle>

The template for the group is:

<!-- Data template for the group -->
<DataTemplate x:Key="GroupTemplate">
  <Border Background="{StaticResource Brush_GroupBackground}"
      CornerRadius="10" Height="Auto" Padding="10" Margin="5">
    <StackPanel Orientation="Horizontal">
      <Border CornerRadius="2" BorderBrush="#FFFCF7" Padding="2"
            BorderThickness="2">
        <Image Width="117" Height="117"
              Source="{Binding Path=Items[0].Album.Cover,
              Converter={StaticResource CoverConvert}}" />
      </Border>
      <TextBlock Text="{Binding Name}" Foreground="White"
         FontFamily="Tahoma" FontSize="18" FontWeight="Bold"
         VerticalAlignment="Center" Margin="5,0,0,0"/>
    </StackPanel>
  </Border>
</DataTemplate>

We are showing the Album cover and the Album name with a gradient background.

Filtering Data

Filtering data in WPF is also very simple: we only have to pass a Lambda Expression to the Filter property of the view. This expression must return a boolean true for the records that must be shown. To filter the data, we will use a TextBox and, as the user types in the box, a new filter is generated. The event handler for the TextChanged event is:

private void filterBox_TextChanged(object sender, TextChangedEventArgs e)
{
    ICollectionView view =
       CollectionViewSource.GetDefaultView(dataListBox.ItemsSource);
    view.Filter = m =>
       ((Song)m).Name.ToLower().Contains(filterBox.Text.ToLower());
}

The expression will return true if the song name contains the text typed in the TextBox. That way, when the user types something in the TextBox, only the songs that contain the text will be shown.

Conclusion

WPF introduced great new capabilities to the .NET development: data binding and styles are just some of them. With these two, you can give to your users a new level of user experience, changing completely the way the data is shown and linking the presentation with data with almost no code-behind. This can be highly enhanced with sorting, grouping and filtering.

Besides these capabilities, LINQ brings an Object-Relational model that fits nicely in the WPF data binding model: with these two technologies together, developing applications that interact with database data becomes a really easy task.

History

• 12/21/2007 - Added table of contents
• 12/21/2007 - Initial release

You must Sign In to use this message board.

FAQ   Search Messages    Layout Expand All MessagesThread ViewNo Javascript (slow)No Javascript Preview    Per page 102550

Msgs 1 to 2 of 2 (Total in Forum: 2) (Refresh) FirstPrevNext

how we can delete and add album using linq? benkirane 0:55 27 Feb '08   Hi, I want to known, how we can delete, add (CRUD) album using linq to sql with wpf databindin. Thanks a lots for this great tutorial Sign In·View Thread·PermaLink Re: how we can delete and add album using linq? Bruno Sonnino 10:47 28 Apr '08   LINQ queries are collection of objects. If you do something like dc.Songs.Add(new Song() {....}) you are including a new record in the Songs table. You can delete a record using dc.Songs.Remove(song), where "song" is a song in the collection. Updating is a simple matter of changing the object properties. You must commit the changes using dc.SubmitChanges() Bruno Sign In·View Thread·PermaLink

Last Visit: Thursday, January 8, 2009 3:08 AM     Last Update:Thursday, January 8, 2009 3:08 AM