Try it: Display data from a sample SQL database

Article
10/10/2017

This page applies to WPF projects only

In Microsoft Expression Blend, you can work with XML data sources and common language runtime (CLR) object data sources. XML data sources are simple to work with, but CLR object data sources are much more complex. The following procedures show you how to display data from a CLR data source in your Expression Blend application. The first two tasks involve obtaining data from a sample database and converting the data to a format that Expression Blend can bind to. The third task involves creating an Expression Blend project that has elements that are bound to the data.

Note

For Microsoft Silverlight applications, see Silverlight Data Binding on MSDN.

Note

To follow the procedures in this topic as they are written, you must have Microsoft Visual Studio 2008 and Microsoft SQL Server 2008 with the AdventureWorks sample database installed. For more information about how to obtain the AdventureWorks sample, see Installing AdventureWorks Sample Databases and Samples on MSDN.

If you prefer, you can create a DataTable or DataSet from another database. If you do this, you can adapt the first and second tasks in this topic to suit the data that you have. The principles will be the same as long as you use a CLR (or Microsoft .NET Framework) data source.

Defining and filling a DataTable

The following procedure describes how to create a class library in Visual Studio 2008 to populate an instance of a DataTable with data from the AdventureWorks sample database.

To define and fill a DataTable

On the File menu of Visual Studio 2008, point to New, and then click Project.
In the New Project dialog box, under Project Types, click Visual C#. Under Templates, click Class Library. Name the new project AWDataSource, and then click OK.

Visual Studio generates the code for your new class library project and opens the Class1.cs file for editing.
In the Class1.cs file, change the name of the public class definition from Class1 to ProductPhotosCollection (this name is more descriptive).
In Solution Explorer, right-click the name of your project (AWDataSource), point to Add, and then click New Item.
In the Add New Item dialog box, select DataSet from the list of templates, name the item ProductPhotos.xsd, and then click Add.

A dataset is added to your project in the form of a schema file and supporting class files. Additionally, the schema file is opened for editing.

Note

A dataset is an object that contains data tables in which you can temporarily store data in memory to run your application. For more information, see Datasets in Visual Studio overview on MSDN.
In Server Explorer, right-click Data Connections, and then click Add Connection.
In the Choose Data Source dialog box, the Data source field should already list Microsoft SQL Server (SqlClient).

Tip

If you are not going to use a SQL Server database, click the Change button to select your alternative data source. The following steps will not match what you will see in the Choose Data Source dialog box of Visual Studio.
In the Server Name field, enter the name of the instance of SQL Server on which the AdventureWorks database is installed.

Tip

If you are going to use a SQL Server database other than the AdventureWorks database, select the name of the server that has your SQL Server database installed.
Under Log on to the server, select the authentication method that is required to log on to your instance of SQL Server. You might have to contact the server administrator for that information. Windows Authentication uses your current logon credentials. SQL Server Authentication requires the user name and password of the account that is configured to have access to your database.
Under Connect to a database, select the AdventureWorks database, which will be visible only if your logon credentials are correct, if the AdventureWorks database is installed on your computer, and if your computer is running SQL Server.
Click the Test Connection button. If the test connection is unsuccessful, see your SQL Server administrator for help.
Click OK to complete the creation of the data connection. In Server Explorer, a new connection appears under the Data Connections node named <servername>.AdventureWorks.dbo, where <servername> is the name of your server.
In Server Explorer, expand the new <servername>.AdventureWorks.dbo connection node, expand the Tables node, and then locate the ProductPhoto table. With the ProductPhotos.xsd file open on the design surface, drag the ProductPhoto table from ServerExplorer onto the design surface. You now have a typed dataset that can connect to the AdventureWorks database and return the contents of the ProductPhoto table.
In the Class1.cs file, add the following method inside the ProductPhotosCollection class:
```
private void GetData()
    {
        ProductPhotosTableAdapters.ProductPhotoTableAdapter da =
            new ProductPhotosTableAdapters.ProductPhotoTableAdapter();
        ProductPhotos.ProductPhotoDataTable dt = da.GetData();
    }
```
The ProductPhotosTableAdapters namespace is defined in the ProductPhotos.Designer.cs file, which was generated by Visual Studio when you created the ProductPhotos DataSet. You now have a method that will fill an instance of a ProductPhotos DataTable with data when your application is run.
Build your project (CTRL+SHIFT+B) to make sure that it contains no errors.

Adapting the data collection to a WPF collection

The following procedure describes how to create a class library in Visual Studio 2008 to convert data from a DataTable to an ObservableCollection so that Expression Blend (or any application that uses Windows Presentation Foundation (WPF)) can bind to the data. You will define a ProductPhoto class to represent the data in a table row, add a collection of ProductPhotos to ProductPhotosCollection as a private member, and then add a public accessor (a get method) so that code from outside the class can access it.

Tip

If you followed the previous procedure in this topic, you now have a DataTable instance to work with that contains data from the AdventureWorks ProductPhoto database table. If you have a DataTable or a DataSet that was filled from another source, you can adapt the data table name and field names in the following steps to suit the data that you have.

To adapt the data collection to a WPF collection

In Visual Studio 2008, right-click your project name in Solution Explorer, and then click Add Reference. On the .NET tab, select the WindowsBase assembly. If you do not see the WindowsBase assembly listed, click the Browse tab and locate the WindowsBase.dll assembly in your %SystemDrive%\Program Files\Reference Assemblies\Microsoft\Framework\v3.0 folder.
Click OK. The WindowsBase assembly implements the System.Collections.Object.ObservableCollection class.
At the top of the Class1.cs file, add the following statement:
```
using System.Collections.ObjectModel;
```
Also in the Class1.cs file, add the following ProductPhoto class definition to the AWDataSource namespace so that you have a class to work with:
```
public class ProductPhoto { }
```

Add the following member to the ProductPhotosCollection class:

private ObservableCollection<ProductPhoto> productPhotos =
    new ObservableCollection<ProductPhoto>();

Add the following accessor method to the ProductPhotosCollection class:
```
public ObservableCollection<ProductPhoto> ProductPhotos
{ get { return this.productPhotos; } }
```
The next steps involve copying the ID, the modified date, and the two photos from the DataTable into the ObservableCollection.
Right-click your project name in Solution Explorer, and then click Add Reference. Add a reference to the PresentationCore assembly.

At the top of the Class1.cs file, add the following statements:

using System.Windows.Media;
using System.Windows.Media.Imaging;

Add members to the ProductPhoto class so that the class looks like the following:

public class ProductPhoto
    {
        // Public Accessors to the private properties.
        public int ID { get { return id; } }
        public ImageSource ThumbNailPhoto { get { return thumbNailPhoto; } }
        public ImageSource LargePhoto { get { return largePhoto; } }
        public DateTime ModifiedDate { get { return modifiedDate; } }

        // Constructor.
        public ProductPhoto(int id, byte[] thumbNailPhoto, byte[] largePhoto,
            DateTime modifiedDate)
        {
            this.id = id;
            this.thumbNailPhoto = ByteArrayToImageSource(thumbNailPhoto);
            this.largePhoto = ByteArrayToImageSource(largePhoto);
            this.modifiedDate = modifiedDate;
        }

        // Private properties.
        private int id;
        private ImageSource thumbNailPhoto;
        private ImageSource largePhoto;
        private DateTime modifiedDate;

        // Supporting method.
        private ImageSource ByteArrayToImageSource(byte[] data)
        {
            BitmapImage image = null;
            if (null != data)
            {
                image = new BitmapImage();
                image.BeginInit();
                image.StreamSource = new System.IO.MemoryStream(data);
                image.EndInit();
            }
            return image;
        }
    }

Add the following code to the ProductPhotosCollection class at the end of the GetData method so that the method copies the DataTable into the ObservableCollection:

productPhotos.Clear();
   foreach (ProductPhotos.ProductPhotoRow row in dt)
   {
       productPhotos.Add(new ProductPhoto(
           row.ProductPhotoID,
           row.ThumbNailPhoto,
           row.LargePhoto,
           row.ModifiedDate));
   }

Now, as a convenient way of triggering the ProductsPhotosCollection.GetData method, you can implement a Command.

Right-click your project name in Solution Explorer, click Add, and then click Existing Item.
In the Add Existing Item dialog box, browse to the DelegateCommand.cs file in the Expression Blend samples folder, %SystemDrive%\Program Files\Microsoft Expression\Blend\Samples\<language>\ColorSwatch, and then click Add.
Change the namespace from ColorSwatch to your namespace name (AWDataSource). The code in the DelegateCommand.cs file enables you to bind any command to your method.
In the Class1.cs file, add the following member to the ProductPhotosCollection class:
```
private DelegateCommand getDataCommand;
```

Add the following constructor to the ProductPhotosCollection class to initialize the command:

public ProductPhotosCollection()
    {
        getDataCommand = new DelegateCommand(delegate() { GetData(); });
    }

Finally, expose the command by adding the following accessor method to the ProductPhotosCollection class:
```
public DelegateCommand GetDataCommand { get { return getDataCommand; } }
```
Build your project (F5) to make sure that it contains no errors. You now have a class that you can use as a data source in an Expression Blend (or any WPF) application. This class will be either ProductPhotosCollection or an equivalent class if you defined your own.

Binding to the data source in Expression Blend

The following procedure describes how to create a very simple Expression Blend application that has a ListBox control that is bound to your data source. The application uses a common user interface design pattern known as a master-details view. The left pane, named the master pane, will contain the product list. Whenever you select a product in this pane, the details about that product will be displayed in the right pane, named the details pane. Updating the content of one pane when an element is selected in another pane is accomplished by using data synchronization between controls.

To bind procedures to the data source in Expression Blend

In Expression Blend, click File, and then click New Project.
In the New Project dialog box, select the WPF Application project type. This creates a project for a Windows-based application that you can build and run while you are designing it. The other option is a WPF Control Library project, which you can use for designing controls for use in other Windows-based applications.
In the Name text box, type AWProductPhotos. Leave Language set to the default, because this procedure has no handwritten code.
Click OK. Expression Blend loads your new project and displays it for editing.

Tip

If you have a project open already, you might see a dialog box asking if you want to save or discard your current project before opening the new one.
After your new project is loaded into memory, save it to disk by clicking Save All on the File menu. The Name text box should already include the name AWProductPhotos, so click OK.

Warning

As you work in Expression Blend, your changes are not automatically saved to your hard disk, so make sure to manually save your files early and often. For more information, see Managing solutions, projects, and files.
On the Project menu, click Add Reference.
In the Add Reference dialog box, browse to the AWDataSource.dll file that you built at the end of the second task in this topic to add a reference to it. The AWDataSource.dll file will likely be in the bin/Debug folder of your AWDataSource project.
Click OK. The AWDataSource.dll is now a part of your project. If you expand the References node in the Projects panel, you'll see a reference to AWDataSource.dll.
In the Data panel, click Add live data source , and then click Define New Object Data Source.
In the Define New Object Data Source dialog box, expand the AWDataSource node, select ProductPhotosCollection, and then click OK.

In the Data panel, a data source named ProductPhotosCollectionDS has been added to your project. The ProductPhotosCollectionDS data source represents the structure of an instance of the CLR class that you referenced. Expand ProductPhotosCollectionDS and ProductPhotosCollection to see the structure. In a later step in this task, you will drag data onto the artboard from in the Data panel to create new controls.
In the Objects and Timeline panel, click LayoutRoot to activate it. When you activate the element, notice that a shaded bounding box appears around its name.

Tip

When you want to add child elements to an element or perform other special operations, double-click the parent element in the Objects and Timeline panel, and then add the child element. This action is called activating. The activated element is identified by a shaded bounding box around the element name.

When you want to change the properties of an element, click the element name in the Objects and Timeline panel. This action is called selection. The selected element is identified by a highlighted background color.

You can select an element without activating it.
In the Tools panel, click Selection. On the artboard, move your pointer over the thick ruler area at the top of LayoutRoot. A column ruler will follow your pointer, indicating where a new column divider will be positioned if you click.
Click to create a new column divider, making the left column about the same width as the right column. The left column will contain a list of product photo thumbnails, and the right column will contain a large photo that represents the selected list item. A column divider appears inside LayoutRoot.

Tip

To be able to see the whole LayoutRoot element on the artboard, you might have to zoom out. To zoom out, enter a zoom value in the text box at the bottom of the artboard , click the arrow next to the zoom value, or rotate your mouse wheel while pressing the CTRL key.
On the artboard, move your pointer over the thick ruler area on the left side of LayoutRoot. Click to create a new row divider, making the top row large enough to fit a button into. Click the open padlock icon that appears next to the top row to lock the row to a fixed height.

Tip

To see the results of the action described in the previous step, right-click LayoutRoot in the Objects and Timeline panel and click View XAML (to view the Extensible Application Markup Language, or XAML). This switches your artboard to XAML view, with the LayoutRoot code highlighted. To see it better, on the Window menu, click Hide Panels (press F4 or TAB).

A <Grid.ColumnDefinitions> section and a <Grid.RowDefinitions> section have been added to the <Grid> element that represents the LayoutRoot. The Width and Height attributes use star-sizing, which means that the sizes of the columns are proportional to each other. For example, a Width of 2* produces a column that is double the size of a column whose width is set to *. The row that you locked does not use star-sizing, because its height is fixed.

When you finish looking at the XAML code, remember to switch back to Design view and restore the panels (press F4 or the TAB key).
In the Data panel, drag GetDataCommand (from under ProductPhotosCollection) into the top-left grid cell on the artboard.
In the drop-down list that appears, click Button.
In the Create Data Binding dialog box, in the Property of drop-down list, choose Command, and then click OK. This action creates a new button that is bound to the GetDataCommand accessor method in your AWDataSource class. At run time, when the button is clicked, it performs the GetDataCommand on the ProductPhotosCollection data source, and, as in the second task in this topic, the implementation of that command calls the GetData method.

Tip

You can move and resize the new button on the artboard by clicking the Selection tool in the Tools panel, selecting the new button on the artboard or in the Objects and Timeline panel, and then using the adorners on the artboard. For more information, see Reposition or translate an object and Resize or scale an object.

Tip

You can access data bindings in the Properties panel. To reopen the Create Data Binding dialog box after you have already created the data binding, first select the Button in the Objects and Timeline panel. Next, under Miscellaneous in the Properties panel, click Advanced property options next to the Command property, and then click Data Binding.
With [Button] selected in the Objects and Timeline panel, look for the Content property under CommonProperties in the Properties panel. Set the Content property by entering the text Get Product Photos, and then press ENTER.
Move and resize the [Button] element by clicking the Selection tool in the Tools panel and then using the adorners on the artboard. Make [Button] fit into the top-left grid cell. Then, under Layout in the Properties panel, set the following properties:
- Set the Width and Height properties to Auto.
- Set the Margin properties to 0.
- Set the HorizontalAlignment and VerticalAlignment properties to Center.
These settings make sure that the button is only as large as it has to be to fit the text in the Content property, and the settings also center the button in the grid cell.
In the Data panel, drag ProductPhotos (Array) into the lower-left grid cell on the artboard.
In the drop-down list that appears, click ListBox.
In the Create Data Binding dialog box, in the Property of drop-down list, choose ItemsSource, and then click OK.

Tip

A data template is essentially a tree of UI elements that can contain bindings to data. Whenever data of a particular type has to be displayed, the appropriate data template is selected and the template formats a new copy of its tree of elements, complete with updated data to display to the user. Each item that is displayed in the ListBox element is an object of the ProductPhoto type that you implemented in the second task in this topic.
In the Create Data Template dialog box, select the New Data Template and Display Fields radio button. This option defines the structure of the data type that you dragged from the Data palette (for example, each element in a collection of ProductPhoto objects). You can now bind to any parts of the data structure and therefore define what the data template's tree of elements looks like. Next to each data item is a drop-down list that determines the element that will be used to present the data field (StackPanel and TextBlock elements). Next to that is a label that indicates to which of the properties of the element the data item will be bound.
Clear the LargePhoto option because you want to display it only in the ListBox.
The ModifiedDate data field is currently of type StackPanel, but you have to change the control to an element type that is more appropriate for displaying that data type. In the drop-down list next to ModifiedDate, choose TextBlock. The label automatically changes to Text.
The ThumbNailPhoto data field is currently of type ImageSource, but you have to change the control to an element type that is more appropriate for displaying that data type. In the drop-down list next to ThumbNailPhoto, choose Image. The label automatically changes to Source.
Click OK. This inserts a new ListBox into the document.

Tip

A data template is a resource. To modify the ProductPhotosTemplate data template after you have created it, expand the Window1.xaml node in the Resources panel, expand Window, and then click the button next to ProductPhotosTemplate.
With the [ListBox] element selected in the Objects and Timeline panel, under Layout in the Properties panel, do the following:
- Set the Width and Height properties to Auto.
- Set the Margin property to 8.
- Set the HorizontalAlignment and VerticalAlignment properties to Center.
These settings make sure that the ListBox almost completely fills the lower-left grid cell.
In the Tools panel, select Image .

Tip

If you do not see the Image control in the Tools panel, you can locate it by clicking Assets . After you select a control from this gallery, its icon appears in the Tools panel underneath the Assets button.
On the artboard, draw a new Image in the lower-right grid cell, almost filling the whole cell.
With [Image] selected in the Objects and Timeline panel, look for the Source property under Common Properties in the Properties panel. Click the name of the Source property, and then in the drop-down list that appears, select Data Binding.
In the Create Data Binding dialog box, select the Element Property tab, because you are going to bind the data to a property of the [ListBox] element.
Under Scene Elements, expand Window and LayoutRoot, and then select your ListBox ([System.WIndows.Controls.ListBox]).
In the Show drop-down list, select All Properties. This displays all properties that are available to be bound to, not just those of the same data type as the Source property (String).
Under Properties, select SelectedItem : (Object).
Select the Use a custom path expression check box. The default expression is SelectedItem. Change it to SelectedItem.LargePhoto so that you are binding to the LargePhoto member of the currently selected ProductPhoto object.
Click Finish.
On the Project menu, click Test Project (or press F5). When the application starts, test the application by clicking Get Product Photos. When the list box loads with data, step through its items and verify the large photo that appears in the right column.