Try it: Display data from a sample SQL database

Artikel
02/20/2013

This page applies to WPF projects only

In Blend for Visual Studio 2012, 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 Blend application. The first two tasks involve obtaining data from a sample database and converting the data to a format that Blend can bind to. The third task involves creating an Blend project that has objects 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 2010 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.

Define and fill a DataTable

The following procedure describes how to create a class library in Visual Studio 2010 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 2010, 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.

Adapt the data collection to a WPF collection

The following procedure describes how to create a class library in Visual Studio 2010 to convert data from a DataTable to an ObservableCollection so that 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 2010, 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.

On the File menu, click New Item. In the New Item box, click Class. In the Name box, type DelegateCommand.cs.

In the new page, press Ctrl+A to select all of the content, and then press Delete. Copy and then paste the following code into the page that you just created.

namespace AWDataSource
{
    using System;
    using System.Windows.Input;

    ///
    /// DelegateCommand is a simplified version of ICommand in WPF. You can wrap one of these around any method,
    /// and thus bind any command on any WPF object to your method.
    ///
    /// DelegateCommand also supports an IsEnabled property that you can use to turn the command on and off.
    ///
    public sealed class DelegateCommand : ICommand
    {
        // Type signature of the method that DelegateCommand works with - returns void, no arguments.
        public delegate void SimpleEventHandler();

        // Remember the method so that it can be called at the right time.
        private SimpleEventHandler handler;

        // Maintain the enabled state.
        private bool isEnabled = true;

        // Simple constructor: Pass in the method that needs to be called when the command executes.
        public DelegateCommand(SimpleEventHandler handler)
        {
this.handler = handler;
        }

        #region ICommand implementation

        // Executing the command is as simple as calling the method.
        void ICommand.Execute(object arg)
        {
 this.handler();
        }

        // Saying whether the command can be executed.
bool ICommand.CanExecute(object.arg)
        {
            return this.IsEnabled;
        }

        // This is the event that the command architecture of WPF listens to so it knows when to update
        // the UI on command enable/disable.
        public event EventHandler CanExecuteChanged;
        #endregion

        // Public visibility of the isEnabled flag - note that when it is set, the event must be raised
        // so that WPF knows to update any UI that uses this command.
        public bool IsEnabled
        {
            get { return this.isEnabled; }
            set
            {
this.isEnabled = value;
 this.OnCanExecuteChanged();
            }
        }

        // Simple event propagation that makes sure that someone is listening to the event before raising it.
        private void OnCanExecuteChanged()
        {
            if (this.CanExecuteChanged != null)
            {
 this.CanExecuteChanged(this, EventArgs.Empty);
            }
        }
    }
}

On the File menu, click Save.

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 Blend (or any WPF) application. This class will be either ProductPhotosCollection or an equivalent class if you defined your own.

Bind procedures to the data source in Blend

The following procedure describes how to create a very simple 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 list-details view. The left pane, named the list 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 object is selected in another pane is accomplished by using data synchronization between controls.

To bind procedures to the data source

In 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. 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 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 Working with 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 Create data source , and then click Create Object Data Source.
In the Create 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 object, notice that a shaded bounding box appears around its name.

Tip

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

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

You can select an object 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 object 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> object 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 Tools panel, double-click Button to draw a button 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 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] object 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 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 objects 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 objects, complete with updated data to display to the user. Each item that is displayed in the ListBox object 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 panel (for example, each object 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 objects looks like. Next to each data item is a drop-down list that determines the object that will be used to present the data field (StackPanel and TextBlock objects). Next to that is a label that indicates to which of the properties of the object 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 object 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 object 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] object 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] object.
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.