Tutorial: Creating a Series Using Data Binding

Applies to: Functional Programming

Authors: Tomas Petricek and Jon Skeet

Get this book in Print, PDF, ePub and Kindle at manning.com. Use code “MSDN37b” to save 37%.

Summary: This tutorial shows how to specify a data source for a chart series declaratively using data binding. It demonstrates binding a series to a collection of values, binding to F# tuples, and binding to external data sources such as CSV files.

This topic contains the following sections.

• Introducing Data Binding
• Initializing Charts and a Data Series
• Creating a Data Series from Simple Collections
• Loading the Data from External Sources
• Summary
• Additional Resources
• See Also

This article is associated with Real World Functional Programming: With Examples in F# and C# by Tomas Petricek with Jon Skeet from Manning Publications (ISBN 9781933988924, copyright Manning Publications 2009, all rights reserved). No part of these chapters may be reproduced, stored in a retrieval system, or transmitted in any form or by any means—electronic, electrostatic, mechanical, photocopying, recording, or otherwise—without the prior written permission of the publisher, except in the case of brief quotations embodied in critical articles or reviews.

Introducing Data Binding

Data binding is a declarative approach to providing data to .NET user interface controls. Instead of creating all data points of a chart series explicitly and adding them to the series, data binding just specifies a data source and the control creates the series automatically. Data binding in Microsoft Chart Controls is similar to data binding elsewhere in .NET. The data source can be a collection of .NET objects and data binding specifies the mapping between the data source and the properties of data points such as X- and Y values or a label. Data binding can be used with a wide range of data sources including in-memory collections of data, SQL databases, or CSV and Excel files, among others. This tutorial covers the following topics:

• Generate data series using F# sequence expressions

• Bind extended properties to in-memory objects

• Bind to external data sources such as databases and CSV files

When creating charts using data binding, it is still needed to create the chart, chart area, and data series explicitly and configure various properties of these objects. This article shows complete code that can be executed, but it doesn't discuss the initialization in detail. For more information, see Tutorial: Getting Started with Microsoft Chart Controls or Additional Resources at the end of this article.

Once a data series is created, the data binding is performed using the DataBindY, DataBindXY, or DataBind methods of the Points property (representing a collection of data points of the series). These methods instruct the control how to load and use the data. The article demonstrates all of the three options.

Initializing Charts and a Data Series

This tutorial uses F# Interactive. To make the examples short and self-contained, this section implements two helper functions. The function createChart constructs a new chart object with a single chart area and adds a new data series to the area. The addSeries function creates a new series using the specified chart type and adds it to a given chart:

#r "System.Windows.Forms.DataVisualization.dll"

open System
open System.Drawing
open System.Windows.Forms
open System.Windows.Forms.DataVisualization.Charting

/// Add data series of the specified chart type to a chart
let addSeries typ (chart:Chart) =
    let series = new Series(ChartType = typ)
    chart.Series.Add(series)
    series

/// Create form with chart and add the first chart series
let createChart typ =
    let chart = new Chart(Dock = DockStyle.Fill, 
                          Palette = ChartColorPalette.Pastel)
    let mainForm = new Form(Visible = true, Width = 700, Height = 500)
    let area = new ChartArea()
    area.AxisX.MajorGrid.LineColor <- Color.LightGray
    area.AxisY.MajorGrid.LineColor <- Color.LightGray
    mainForm.Controls.Add(chart)
    chart.ChartAreas.Add(area)
    chart, addSeries typ chart

The listing starts by referencing the Windows Forms version of Microsoft Chart Controls and opening the necessary .NET namespaces. The addSeries function simply creates a new series and adds it to the existing chart. The second function, createChart, uses addSeries to create the first series of the chart. Before adding the series, it needs to create a form, add the chart to it and also create the default chart area. The function returns a tuple containing the chart object and the data series, so that the chart can be further configured and data for the series can be added.

The following section uses these two helpers to look at a first example of data binding. The simplest case is binding the data series to a simple in-memory sequence of numeric values.

Creating a Data Series from Simple Collections

The simplest use of data binding is to bind data points of a series to a collection of floating point numbers or integers. The collection can be any type implementing the seq<'T> interface including arrays, F# lists and most of the standard .NET collection types.

Binding Y Values to a Collection

The following example uses a simple sequence expression that generates 100 values of a function that adds two sine curves. The values are displayed using a line graph, which gives an illusion of a smooth curve (because the number of points is large enough). The snippet could iterate over it and use the Add method of the collection series.Points to add individual points one by one. Using data binding, the same thing can be done using a single call to the DataBindY method:

let data = 
    [ for i in 0 .. 100 do
          yield 2.0 * sin (float i / 20.0) + sin (float i / 10.0) ]

let chart, series = createChart SeriesChartType.Line
series.BorderWidth <- 3
series.Points.DataBindY(data)

The code to generate data is a completely standard F# list expression that generates a value of type list<float>. To generate a chart, the snippet first creates a new form containing a chart and a single line series. Next, it configures the series to use a thicker line. Finally, the last line binds the Points collection to the data value. This is done using DataBindY which takes a collection of Y values only. As shown in Figure 1, values on the x-axis are generated automatically and correspond to the number of points.

Figure 1. A plot of a function generated using a list comprehension

The DataBindY method doesn’t provide any way for specifying values displayed on the x-axis, so it is useful only when the x-axis isn’t relevant or when the automatically generated X values are suitable. Values for both of the axes can be specified using the DataBindXY method.

Binding X and Y Values to Separate Collections

The next example demonstrates how to provide values for both of the axes. It creates a doughnut chart that shows the population in different continents. The Y value is the number of inhabitants (in thousands) and the X value is a name of the continent. The following example binds the data to two separate collections:

let values = [ 1033043; 4166741; 732759; 588649; 351659; 35838  ]
let names  = [ "Africa"; "Asia"; "Europe"; "South America";
               "North America"; "Oceania" ]

let chart, series = createChart SeriesChartType.Doughnut
series.Font <- new Font("Verdana", 11.0f)
series.Points.DataBindXY(names, values)

The snippet again uses F# lists to store the data, but any other collection would work as well. After initializing the data, the snippet creates a chart with a single doughnut data series, changes the default font, and binds the data to the series.

The DataBindXY method is overloaded, but the version used here takes two parameters. The first one is a collection of X values and the second one is a collection of Y values. The X values may also be a collection of arbitrary strings because they are used as labels for the pie regions. Figure 2 shows the resulting chart.

Figure 2. A pie chart created from a list of values and a list of names

Before looking at the most general method for data binding (named just DataBind), the following section discusses another overload of DataBindXY. It is quite useful as it can be used to bind data to F# tuples.

Binding to a Collection of Tuples

The previous example worked with two separate collections. This can easily lead to errors if the collections are ordered differently. A more convenient way of representing the information is to use a list of tuples. The type of the data used in the next example is list<string * int>. In order to use data binding with tuples, it is important to understand how F# tuples are represented.

Note

Since .NET 4.0, an F# tuple type is represented using one of the System.Tuple<...> types (the type is overloaded by the number of type parameters). In the earlier versions of F#, the type is included in the FSharp.Core.dll library but is essentially the same. The type simply stores the values of a tuple and has properties Item1, Item2, and so on, depending on which of the overloaded tuple types is used. When using a tuple of two elements from C#, the elements can be accessed using properties Item1 and Item2.

To use a collection of tuples, the snippet needs to specify how to get the X- and Y values from the tuple. This can be done using another overload of DataBindXY. The overload takes four arguments that can be used to specify that the X value is stored in the Item1 property and similarly for the Y value:

let data = 
  [ "Africa", 1033043; "Asia", 4166741; 
    "Europe", 732759; "South America", 588649; 
    "North America", 351659; "Oceania", 35838  ]

let chart, series = createChart SeriesChartType.Doughnut
series.Font <- new Font("Verdana", 11.0f)
series.Points.DataBindXY(data, "Item1", data, "Item2")

This creates the same chart as the previous example, but the chart is created from a different data structure. The first two arguments of DataBindXY specify the data source for the X values and the property used to retrieve them. The last two arguments specify the Y values in a similar fashion. The method makes it possible to use different data sources for X- and Y values, but the snippet above doesn’t need to do that. It uses data as the source for both X- and Y values but with different properties. As discussed earlier, the Item1 property refers to the first element of the tuple (the continent's name) and the Item2 property returns the value (the population).

Binding to F# tuples is just a special case of binding to any .NET object in general. The object may be any type including F# types such as records, discriminated unions, or object types. The next section explores some of the possibilities and also shows how to bind values to additional properties such as a label.

Using Advanced Binding to Objects

This section looks at a more complex example of data binding. It implements an F# object type to store information about continents. Aside from the name of the continent, it also stores the population of the continent in years 1900 and 2000. This data is used to generate a chart that contains two column chart series (of different colors). It displays the population in the two years together with a label that shows the numeric value in a user-friendly format.

The first listing implements the ContinentInfo type. It is written as an F# class, but a record or discriminated union would work too. The type exposes all relevant information as properties, so that they can be used in data binding:

type ContinentInfo(name:string, pop2000, pop1900) = 

    // Format the number as a user-friendly string
    let format v = 
        let m = v / 1000
        if m < 1000 then sprintf "%d mil." m
        else sprintf "%d bil." (m / 1000) 

    member x.Name = name
    member x.Population2000 = pop2000
    member x.Population1900 = pop1900
    member x.FriendlyValue2000 = format pop2000
    member x.FriendlyValue1900 = format pop1900

The type mostly just exposes the values that were passed to it in the constructor. The only additions are the two FriendlyValueXYZ properties that return a string with a readable version of the number. Values over 1 billion are formatted as “xyz bil.” and all other values are shown in millions.

The next step is to obtain the information. The example assumes that the data set is already in memory, so it just creates a list of ContinentInfo values. It is also possible to bind a series directly to an external data source (e.g. CSV file), but that will be covered later:

let data = 
  [ ContinentInfo("Africa", 767000, 133000)
    ContinentInfo("Asia", 3634000, 947000)
    ContinentInfo("Europe", 729000, 408000)
    ContinentInfo("South America", 511000, 74000)
    ContinentInfo("North America", 307000, 82000)
    ContinentInfo("Oceania", 30000, 6000) ]

The next snippet can finally show the most interesting part of the example—creating a chart and binding it to our in-memory data set. The listing creates a chart series for both of the years, so it first calls createChart (which automatically adds one series) and then uses addSeries again to create the second one. Then, it uses the DataBind method once for each chart series. In addition to binding the X and Y values, it also specifies the label:

let chart, pop1900 = createChart SeriesChartType.Bar
let pop2000 = chart |> addSeries SeriesChartType.Bar

pop1900.Points.DataBind
  (data, "Name", "Population1900", "Label = FriendlyValue1900")
pop2000.Points.DataBind
  (data, "Name", "Population2000", "Label = FriendlyValue2000") 

Unlike DataBindXY, the DataBind method takes only a single data source. This means that both the X value and the Y value have to be stored together in a single collection. In the above example, the X value is the name of the continent and the Y value is one of the two properties that return population as an integer. As shown in Figure 3, the y-axis of the chart actually shows the names, but that’s because the bar chart reverses the axes. The convention is that “X value” always refers to an item and “Y value” to the number associated with the item.

Finally, the last argument to the method is a string that can be used to specify any additional data binding. The string passed as the last parameter is a comma-separated list of additional assignments. The snippet specifies that the FriendlyValue1900 and FriendlyValue2000 properties should be used as values of the Label property of the created DataPoint objects.

Figure 3. A bar chart with labels generated by an object member

The most common use of Microsoft Chart Controls from F# is to visualize data sets that are a result of some calculation, data processing, or complex data retrieval (such as web crawling) implemented in F#. In these situations, the data is already loaded in memory and a chart can be created using one of the data binding techniques discussed so far. However, sometimes it is also useful to display data from an external data source. Data binding can help in this case too.

Loading the Data from External Sources

Data binding methods in Microsoft Chart Controls can be used to create charts from a wide range of external data sources. Most of them are available through ADO.NET objects such as DataSet and DataReader. This section looks at examples of using two ADO.NET providers. The first example uses the Microsoft SQL Server provider to generate a chart from a database query. The later examples demonstrate how to use the OLE DB provider together with Microsoft Data Access Components (MDAC) to create a chart from data in a CSV file.

Binding to a Result of an SQL Query

Running an SQL query using ADO.NET returns an SQLDataReader with the results. The object implements the non-generic IEnumerable interface, so it can be passed as an argument to various DataBind methods that were explored in the previous sections.

Before discussing the data binding code, it is useful to explain how to configure ADO.NET. The following snippet shows a connection string to the database and a sample SQL query. The connection string connects to the default SQL Server Express instance (named .\SQLExpress), which is installed with Visual Studio 2010. It loads a database from the specified file name (which can be downloaded at the end of the article). The sample uses a well-known sample database named Northwind. The database query generates average prices of products in each category:

let connString = 
  "Data Source=.\SQLExpress;Integrated Security=true;" + 
  "User Instance=true;AttachDBFilename=C:\Database\NORTHWND.MDF"

let query = 
  "SELECT p.Price, c.CategoryName
   FROM (SELECT AVG(UnitPrice) AS Price, CategoryID
         FROM Products GROUP BY CategoryID) AS p 
   INNER JOIN Categories AS c ON c.CategoryID = p.CategoryID"

The query first uses GROUP BY to get the average price for products in every category and then uses INNER JOIN to find the name of the category. The result is a table containing CategoryName and AvgPrice columns for all categories. The resulting data can be visualized using a simple column chart that uses names as X values and an average price as Y values.

The following snippet first references System.Transactions.dll. Although it doesn't use transactions in the code, the library is needed for the Visual Studio IntelliSense in F# because it is used in the public API of the Microsoft SQL Server provider. The rest of the snippet creates a database connection, runs the query and uses DataBind to bind the database data to a column chart series:

#r "System.Transactions.dll"
open System.Data.SqlClient

do
    let chart, series = createChart SeriesChartType.Column
    use conn = new SqlConnection(connString)
    use cmd = new SqlCommand(query, conn)
    conn.Open()

    series.Palette <- ChartColorPalette.EarthTones
    use reader = cmd.ExecuteReader()
    series.Points.DataBindXY(reader, "CategoryName", reader, "Price")

To run the query, the snippet constructs SqlConnection and SqlCommand objects, opens the connection and then calls the ExecuteReader method. The result of the call can then be passed as an argument to the DataBind method. The whole code snippet is wrapped inside a do block, which makes it possible to use the use construct to dispose of objects after they have been used. After the chart is created and the data is loaded, the connection to the SQL server will be closed.

Before doing the data binding, the snippet also changes the series' Palette. Without doing that, all columns in the chart would have the same color (the chart's palette is ordinarily used to generate a single color for each series). When a palette is set for an individual series, the colors from the palette are used for individual values (in this case, columns). Finally, the last line binds the chart series to the data source using the DataBindXY method. The method takes data sources for both the X- and y-axesy-axes and the properties or column names to bind to. Note that a single SqlDataReader object can be used as the data source for both the X- and Y values. However, it is not possible to run the ExecuteReader method multiple times (because only a single data reader can be opened at a time). The result of running the snippet is shown in Figure 4.

Figure 4. A column chart generated from the Northwind database

ADO.NET can be used not only for obtaining data from databases but also as an interface for other data access technologies. The following section demonstrates how to use the OLE DB API to read the data from two common types of text files.

Binding to Data from CSV Files

OLE DB is a uniform data-access API that was developed prior to ADO.NET. It provides a layer for accessing data from various data sources. There are many OLE DB providers that add support for accessing data from databases, text files, and other sources. These providers can be accessed from ADO.NET using classes from the System.Data.OleDb namespace. These types wrap OLE DB sources and expose them using the ADO.NET provider model.

The example assumes that there are two CSV files (population_1900.csv and population_2000.csv) in the C:\Data directory. The files contain names of continents and the size of the population in the year denoted in the file name. The content of the latter file is as follows:

Asia, 3634

Australia/Oceania, 30

Africa, 767

Europe, 729

North America, 307

South America, 511

The file is relatively simple, but the same approach would work for more complicated CSV files. For example, if the name of a continent contained a comma, the name would be wrapped in quotes. The data can be loaded using the following connection string:

let connString = 
  "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+ 
  "C:\\Data;Extended Properties=\"Text;HDR=No;FMT=Delimited\"";

The connection string first specifies the OLE DB provider and gives it the folder containing the CSV files as the Data Source parameter. The Extended Properties parameter is perhaps the most interesting because it specifies that the provider should read data from some text file (the Text prefix). The assignment FMT=Delimited specifies that the text file contains values separated with a delimiter (e.g., comma). Another option would be FMT=Fixed when using a file with fixed-column length.

The rest of the code is going to be surprisingly simple. The snippet loads data from both of the files and displays them as two bar charts in a single graph. To do that, it uses objects from the System.Data.OleDb namespace. The most interesting part is creating two OleDbCommand objects using an SQL-like query that the OLE DB interface executes:

open System.Data.OleDb

do 
    use conn = new OleDbConnection(connString)
    use cmd1900 = 
        new OleDbCommand("SELECT * FROM population_1900.csv", conn)
    use cmd2000 = 
        new OleDbCommand("SELECT * FROM population_2000.csv", conn)
    conn.Open()
    use rdr1900 = cmd1900.ExecuteReader()
    use rdr2000 = cmd2000.ExecuteReader()
    // TODO: Add rest of the chart creation here

The code that implements the data retrieval is wrapped in a do block (just like in the earlier SQL example), so that the resources are correctly disposed. Without that, the CSV files could remain opened by the OLE DB provider until the objects are garbage collected.

The snippet first creates a connection and two commands. When working with CSV file, the name of the file is used as the name of a table in the FROM clause (the file must be located in a directory specified by the connection string). After the commands are created, the snippet opens a connection and creates two readers for the data binding. The following snippet shows the remaining code that creates the chart (note that the snippet needs to be placed inside the do block, so that the readers are in scope and sent to F# Interactive as a single command):

let chart, pop1900 = createChart SeriesChartType.Bar
let pop2000 = chart |> addSeries SeriesChartType.Bar
pop1900.Points.DataBindXY(rdr1900, "0", rdr1900, "1")
pop2000.Points.DataBindXY(rdr2000, "0", rdr2000, "1")

Just like in the earlier sample showing the change of population, the snippet creates a chart with two data series. This is done by calling createChart and then adding a series using addSeries. Next, the snippet calls the DataBindXY method to display the chart. The data reader is used as a source for both the X- and the Y values. The table in the CSV file doesn’t have any column names, so the binding uses the index of the column (using a string containing the numeric value). The result of running the code is shown in Figure 5.

Figure 5. A bar chart showing the data from two CSV files

Summary

This tutorial discussed how to use the data binding features of Microsoft Chart Controls from F#. Data binding is done using one of the few methods in the Point collection of a chart series. The basic methods that were discussed are DataBindY (when specifying a collection of Y values) and DataBindXY (which allows specifying both X- and Y values). The examples used these methods to bind the data series to a simple list of numbers and to a collection of tuples. The tutorial also looked at a more general method named DataBind, which can be used to specify additional properties of a data series, such as a label.

The next part of the tutorial focused on loading the data from external data sources. The first example used ADO.NET to read the data from a database and bind a chart series to the values from an SQLDataReader. The next example demonstrated using OLE DB providers, which make it possible to use other sources of data including CSV files or Microsoft Excel and Microsoft Access documents.

Additional Resources

Another interesting feature of the Chart Controls library is that it can be used for showing real-time data. The next tutorial demonstrates this feature using several examples. The previous tutorial discussed the basics of the library:

• Tutorial: Getting Started with Microsoft Chart Controls

• Tutorial: Getting Started with Microsoft Chart Controls

The sample in this tutorial uses the Chart Controls library directly using the types available in .NET 4.0. An alternative option is to use the FSharpChart library, which builds on top of Chart Controls but adds more F#-friendly API. For more information see the following articles:

• Overview: Getting Started with the FSharpChart Library

• Tutorial: Visualizing Stock Prices Using F# Charts

• How to: Create Standard Charts in F#

To download the code snippets shown in this article, go to https://code.msdn.microsoft.com/Chapter-6-Visualizing-Data-c68a2296

See Also

This article is based on Real World Functional Programming: With Examples in F# and C#. Book chapters related to the content of this article are:

• Book Chapter 4: “Exploring F# and .NET libraries by example” demonstrates how to create a simple charting application from scratch. This chapter is useful for learning F# programming when focusing on working with data.

• Book Chapter 12: “Sequence expressions and alternative workflows” explains how to work with in-memory data sets in F# using sequence expressions and higher-order functions.

• Book Chapter 13: “Asynchronous and data-driven programming” shows how to use asynchronous workflows to obtain data from the internet, how to convert data to a structured format, and how to chart it using Excel.

The following MSDN documents are related to the topic of this article:

• Chart Controls is a MSDN section dedicated to working with Microsoft Chart Controls with many examples in C# and Visual Basic. It includes a comprehensive reference with all chart types and properties.

• Binding Data to Series (Chart Controls) discusses binding data to Chart Controls. It compares alternative methods including DataBind, DataBindXY, DataBindTable, and others.

• Retrieving and Modifying data in ADO.NET

Previous article: Tutorial: Getting Started with Microsoft Chart Controls

Next article: Tutorial: Creating Charts with Real-Time Data