Binding Controls to Data

You have now seen all of the various ways that you can use Visual Studio to create and manage databases. The following sections look at the tools available for consuming data within Windows forms or web applications.

An Introduction to Data Binding

There is a common problem and solution pattern at hand with applications that front databases. Typically, data has to be fetched from the database into the application, and the applications user interface has to be updated to display the data in an appropriate manner. For large datasets, the concept of paging comes into play. Because it is inefficient to load in, say, a 100MB dataset, a paging mechanism needs to be pressed into action to allow the user to move forward and back through the data "stream." After the data has safely made it into the application's UI, the application-to-database flow needs to be handled. For any pieces of data that have been changed, those changes have to be reconciled and committed back into the database.

Data binding is the term given to the implementation of a design pattern that handles all facets of this roundtrip of data from a data structure, into an application's controls, and back again (see Figure 15.27). Although the data structure will most commonly be a database, it could be any sort of container object that holds data, such as an array or a collection. With .NET, .NET further stratifies the concepts of data binding into simple data binding and complex data binding. Both of these terms refer to a control's intrinsic capabilities in the larger context of the data-binding process.

Figure 15.27. The data-binding process.

Simple Data Binding

Simple data binding is the capability for a control to bind to and display a single data element within a larger dataset. A TextBox control is a great example of a control commonly used in simple data-binding scenarios. You might use a TextBox, for example, to display the last name of an employee as it is stored within the employee table of a database.

Support for simple data binding is widespread throughout both the Windows and web forms controls. When you use the built-in capabilities of the Windows and Web Forms Designer, it is trivial to add a group of controls to a form and simple-bind them to a dataset (more on this in a bit).

Complex Data Binding

The term complex data binding refers to the capability of a control to display multiple data elements at one time. You can think of this as a "multirow" capability: If a control can be leveraged to view multiple rows of data at one time, then it supports complex data binding.

The DataGridView control (for Windows forms) and DataGrid control (for web forms) are premier examples of controls that were purpose-built to handle tabular (multirow and multicolumn) data.

Although the internals necessary to implement data binding are messy, complex, and hard to understand, for the most part the Visual Studio tools have abstracted the cost of implementing data binding out to a nice, easy, drag-and-drop model. Now let's look at how to rapidly build out support for roundtrip data binding.

Auto-Generating Bound Windows Forms Controls

Although there are various ways to approach and implement data-bound controls with Visual Studio, they all involve the same basic two steps. You need to

1.	Establish a data source.
2.	Map the data source members to controls or control properties.

From there, the Visual Studio Form Designers are capable of generating the correct controls and placing them on the form. All of the data-binding code is handled for you; all you need to worry about is the layout, positioning, and UI aspects of the controls.

As you might imagine, your form might have controls that use simple data binding or complex data binding or a mix of both. Now you're ready to look at the steps involved with creating a series of controls that will leverage both simple and complex data binding to display information from the AdventureWorks Employee table. In this scenario, you will work with the Windows Forms Designer. The ASP .NET Web Forms Designer works in a similar fashion, and you'll have a chance to investigate drag-and-drop approaches for data binding in the web environment in just a bit. As we have already established, the first step is selecting a data source.

Selecting a Data Source

In Visual Studio, make sure you are working inside a Windows Application project and use the Data Sources window to select a data source. If this window isn't already visible, select Show Data Sources from the Data menu in the IDE. If your current project doesn't have any defined data sources, you will need to create one. Select the Add New Data Source button in the toolbar of the window to start the Data Source Configuration Wizard. On the first page of this wizard (see Figure 15.28), you select the type of the data source. There are three options here:

Figure 15.28. Choosing the data source type.

• Database The data source resides as a table within a relational database.

• Web Service The data source is a web service that will return the data to be bound to the form controls.

• Object The data source is an object that will provide the data (this is useful in scenarios in which a business object from another layer of the application will be responsible for delivering the data to the form).

Because the concepts of data binding are most easily understood within the context of a database, we will use the database data source type as the underpinning for our walkthroughs in this chapter.

If you have selected the database data source type, the second page of the wizard focuses on selecting a connection for the database. Any connections previously established for other data sources or for use in the Server Explorer will show up here by default in the drop-down (see Figure 15.29). You also have the option of specifying a new connection. If the connection string to the database has private information such as a user password, you have the option at this point to exclude that information from the string.

Figure 15.29. Selecting the connection.

The next step in the wizard allows you to save the connection string information to your application's local configuration file. Saving the information is usually a good idea because it allows you to tweak the string when needed (such as when changing database environments), but caution should be used if you have elected to store sensitive information in the string (refer to the previous wizard page).

On the final page of the wizard, shown in Figure 15.30, you indicate which of the objects in the database should be used for the source data. You can select from any of the data elements present in any of the various tables, views, stored procedures, or user-defined functions in the database. For purposes of this example, select a few employee table data columns that are of interest: Employee ID, Title, Birth Date, Gender, Hire Date, and Modified Date.

Figure 15.30. Selecting the data source objects.

At the conclusion of the wizard, your selected data source will be visible in the Data Sources window (see Figure 15.31).

Figure 15.31. The Data Sources window.

Note

Behind the scenes, Visual Studio is really just using the data source information collected in the Data Source Configuration Wizard to create a typed dataset. This dataset is then stored as a project item in the current project.

With the data source in place, you're ready to move on to the next step: mapping the data source elements to controls on your form.

Mapping Data Sources to Controls

The really quick and easy way to create your data-bound controls is to let Visual Studio do it for you. From the Data Sources window, click on the drop-down button on the data source name to reveal a menu (see Figure 15.32).

Figure 15.32. Changing the data table mapping.

This menu enables you to set the control generation parameters and really answers the question of what controls you want generated based on the table in the data source. By setting this to DataGridView, you can generate a DataGridView control for viewing and editing your data source. The Details setting allows you to generate a series of simple data-bound controls for viewing or editing data in the data source. For this example, select Details and then drag and drop the data source itself from the Data Sources window and onto a blank form.

Figure 15.33 shows the results. In just two short steps, Visual Studio has done all of the following for you:

Figure 15.33. Auto-generated controls: viewing employee data.

• Auto-generated a set of Label, TextBox, and DateTimePicker controls

• Auto-generated a tool strip with controls for navigating between records in the data source, saving changes made to a record, deleting a record, and inserting a new record

• Created all of the necessary code behind the scenes to establish a connection to the data source, read from the data source, and commit changes to the data source

You have essentially created an entire data-enabled application from scratch with absolutely no coding on your part.

The approach of using simple data binding may not fit in to the user interface design, so you always have the option of working in the complex data binding world and using the DataGridView as an alternative. Figure 15.34 shows the results of auto-generating a DataGridView instance using this same process.

Figure 15.34. An auto-generated DataGridView.

Customizing Data Source Mappings

Refer again to Figure 15.31 and look at the individual data elements that show up under the Employee data source. Each of these is displayed with a name and an icon. The name is, of course, the name of the data element as defined in the database. The icon represents the default mapping of that data type to a .NET control. For example, the Title field maps to a TextBox control, while the BirthDate field maps to a DateTimePicker control. Visual Studio actually attempts to provide the best control for any given data type. But feel free to manually indicate the specific control you want used. If you wanted to display the value of the Employee ID column in a label instead of a text box (in recognition of the fact that you cannot edit this value), it would be easy enough to change this before generating the controls by selecting the EmployeeID column in the Data Sources window and then clicking on the drop-down arrow to select Label instead of TextBox.

In addition to changing the control to data type mapping on an individual level, you can affect the general default mappings that are in place by selecting the Customize option from that same drop-down menu. This will pop up the Visual Studio Options dialog box with the Windows Forms Designer page selected. Using the settings there (see Figure 15.35), you can specify the default control type that you want applied for each recognized data type.

Figure 15.35. Customizing the data to control type mappings.

Manually Binding Windows Forms Controls

In many situations, you don't want Visual Studio to create your data-bound controls for you, or you may need to bind existing controls to a data source. Data binding in these cases is just as simple and starts with the same step: creating or selecting a data source. Some controls, such as the DataGridView, have smart tag options for selecting a data source. Others don't have intrinsic data dialog boxes associated with them but can be bound to data just as easily by working, again, with the Data Sources window.

Binding the DataGridView

Grab a DataGridView from the Toolbox and drag it onto the form's surface. After you've created the control, select its smart tag glyph and use the drop-down at the top of the task list to select the data source to bind to (see Figure 15.36).

Figure 15.36. Selecting the DataGridView's data source.

With a data source selected, you have again managed to develop a fully functional application with two-way database access. All of the code to handle the population of the grid and to handle committing changes back to the database has been written for you.

Customizing Cell Edits

The power of the DataGridView lies in its capability to both quickly bind to and display data in a tabular format and also to provide a highly customized editing experience. As one small example of what is possible in terms of cell editing, follow through with the Employee table example. When you auto-generated form controls to handle Employee table edits, you ended up with DateTimePicker controls to accommodate the date- and time-based data in the table. With the DataGridView, the cell editing experience is a simple text box experience: Each cell contains text, and you can edit the text and save it to the database. But you can provide a more tailored editing experience. You can use a variety of stock controls (such as the DataGridViewButtonColumn, DataGridViewComboBoxColumn, and others that inherit from DataGridViewColumn; see Chapter 14, "Building Windows Forms") to display data within the columns of the grid.

For instance, you can use the DataGridViewComboBoxColumn class to provide a drop-down edit for the Gender column in the grid. To do this, you first need to change the default column type. Select the grid control, open the smart tag glyph, and select the Edit Columns action. In the Edit Columns dialog box, find the column for the employee gender data and change its column type to DataGridViewComboBoxColumn. (see Figure 15.37).

Figure 15.37. Changing the column type.

With the column type changed, you now need to specify how the grid should retrieve the list of possible values to display in the drop-down; the grid is smart enough to already know to use the underlying gender values from the table to select the one value to display in the grid. To handle the list of possible values, you could hard-code them in the column (see the Items property in Figure 15.37), or you could wire up a separate querysomething along the lines of SELECT DISTINCT(Gender) FROM Employeesand have that query provide the list of possible values. Because constructing another query or data source is easy and doesn't lead to a brittle hard-coded solution, that's the approach we'll investigate here. To create a query to feed the combo-box column, you can visit the Data Sources window, select the Add New Data Source action, and follow the same steps you followed before to add the original Employee data source. This time, though, select only the Gender column.

After the data source is created, right-click on the data source and select Edit DataSet with Designer. Visual Studio's XSD Designer will launch. In the designer (see Figure 15.38), you can see the Fill query used to populate the dataset. If you click on the query (that is, click on the last row in the table graphic in the designer window), you can use the Properties window to directly edit the SQL for the query. By modifying this to reflect SELECT DISTINCT syntax, you can return the valid gender values for inclusion in the grid.

Figure 15.38. Changing the query for a data source.

Figure 15.39 shows the results of these efforts. If you need to implement a cell edit control that doesn't currently exist, you need to roll your own by inheriting from the DataGridViewColumn base control. This employee grid could benefit from a DateTimePicker control for the date- and time-based data such as birth date and hire date.

Figure 15.39. A drop-down within a DataGridView.

Note

If you look in the MSDN documentation, there is a specific example of creating a DataGridViewDateTimePickerColumn control and then wiring it up within the grid. Search for the phrase "How to: Host Controls in Windows Forms DataGridView Cells."

Binding Other Controls

For other controls that don't have convenient access to binding via their smart tag, you can leverage the Data Sources window. Drag a data source from the Data Sources window and drop it onto an existing control. The designer will create a new binding source, set it appropriately, and then make an entry in the control's DataBinding collection. If you try to drag a data element onto a control that doesn't match up (for instance, dragging a character field onto a check box), the drop operation won't be allowed.

Data Binding with Web Controls

Although the general concepts remain the same, data binding web-based controls is a slightly different game than in the Windows forms world. The first obvious difference is that data sources for web forms are implemented by data source controls in the System.Web.UI.WebControls namespace; there is no concept of the Data Sources window with web applications. Because of this, instead of starting with a data source, you instead need to start with a data control and then work to attach that control to a data source.

Selecting a Data Control

There are five primary controls that you will work with in a web application to deliver data-bound functionality:

• GridView Control Provides a tabular presentation similar to the DataGridView control.

• DetailsView Control Displays a single record from a data source; with a DetailsView control, every column in the data source will show up as a row in the control.

• FormView Control Functions in the same way as the DetailsView control with the following exception: It doesn't have a built-in "default" for the way that the data is displayed. Instead, you need to provide a template to tell the control how exactly you want the data rendered onto the web page.

• Repeater Control Simply renders a list of individual items fetched from the attached data source. The specifics of how this rendering looks are all controlled via templates.

• DataList Control Displays rows of information from a data source. The display aspects are fully customizable and include header and footer elements.

For demonstration purposes, continue working with the AdventureWorks employee table and see how you can implement a data-bound web page for viewing employee records.

Using the GridView

First, with a web project open, drag a GridView control from the Toolbox onto an empty web page. The first thing you will notice is that the GridView's smart tag menu is just as efficient as the DataGridView's menu. You are directly prompted to select (or create and then select) a data source as soon as you drop the control onto the web page surface (see Figure 15.40).

Figure 15.40. Selecting the GridView's data source.

Selecting the <New Data Source…> option will use the same data source wizard (refer to Figure 15.28) to collect information about your data source and add it to the project.

Once again, because of the data binding support in the designer, you now have a fully functional application without writing a line of code. Figure 15.41 shows this admittedly ugly web page with live employee data.

Figure 15.41. Employee records in the GridView.

Thankfully, you can just as easily put some window dressing on the table and make it look nice as well. By using the GridView's smart tag menu again, you can select the Auto Format option to apply several different flavors of window dressing to the table (see Figure 15.42). And, of course, by applying a style sheet, you can really impact the look and feel of the page.

Figure 15.42. Autoformatting options for the GridView control.

Updating Data with the GridView

Creating the web grid was easy, and no data access coding was required on your part, but there is one thing missing here: How can you update data back to the database? The GridView you currently have is great for static reporting, but what if you want to edit data within the grid just like you did earlier in the Windows forms application? The key here is a set of properties on the GridView: AutoGenerateEditButton and AutoGenerateDeleteButton. When you set these properties to true, the GridView will automatically include an Edit and a Delete link. The Edit link comes fully baked with rendering code so that when it is clicked, that particular row in the grid will become editable. In Figure 15.43, notice that by setting the AutoGenerateEditButton to TRue and then clicking on one of the edit links, you now have a fully interactive set of columns that you can use to modify the record's data.

Figure 15.43. Editing a record in the GridView

After changing the data in one or more of the columns, you can click on the Update link to send the data back to the database. For the update to work, however, you need to explicitly tell the data source control (in this case, a SqlDataSource control) what query to use for processing updates. This is done with the SqlDataSource.UpdateQuery property. By specifying a parameterized UPDATE query in this property, you have fully informed the data source on how to deal with updates. You can take advantage once more of the Query Builder window to write this query for you: Select the data source control on the web form, and in the Properties window, select the UpdateCommand property. This will launch the Query Builder window and allow you to construct the parameterized update command (see Figure 15.44).

Figure 15.44. Specifying an UpdateCommand query.

With that last piece of the puzzle in place, you now have a fully implemented and bound grid control that pages data in from the database and commits changes back to the database.

Note

To implement delete capabilities for a record, you perform the same steps using the DeleteQuery property and setting the AutoGenerateDeleteButton to True.

Data Source Controls

As mentioned, data sources are surfaced through one or more data source controls placed onto the web form. In the GridView example, the designer actually adds a SqlDataSource control to the form for you (based on your creation of a new db-based data source). But there is nothing preventing you from adding one or more data source controls to a web page directly. Just drag the control from the Toolbox onto the form surface. Table 15.2 itemizes the available data source controls.

Table 15.2. Data Source Controls

Data Source Control	Description
ObjectDataSource	Exposes other classes as data sources.
SqlDataSource	Exposes a relational database as a data source. Microsoft SQL Server and Oracle databases can be accessed natively; ODBC and OLE DB access is also supported.
AccessDataSource	Exposes a Microsoft Access database as a data source.
XmlDataSource	Exposes an XML file as a data source.
SiteMapDataSource	A special case data source that exposes an ASP .NET site map as a data source.

After configuring the data source, you can then visit any data-aware control and bind it to the source.