Professional Visual Studio 2005 (2006) [eng]

Chapter 38

Figure 38-10

Adding a data source is straightforward. If the Data Sources window is currently empty, the main space will contain an Add a New Data Source link. Otherwise, click the Add New Data Source button at the top of the tool window, or use the Data Add New Data Source menu command.

In the Data Source Configuration Wizard that is displayed, you must first choose the type of data source to which you’re connecting. Use the obvious database type and click Next to go to the Choose Your Data Connection page (see Figure 38-11).

Figure 38-11

516

Visual Database Tools

The Choose Your Data Connection page of the wizard is where you select the database to which you’ll be connecting the data source. The drop-down list is populated with all data connections already defined in both the application and the Server Explorer, but you can use the New Connection button to link to a database that is not listed. (Clicking the New Connection button displays the Add Connection dialog described previously.)

When you select the connection that Visual Studio is to use for the data source, the full connection string is displayed in the lower pane, enabling you to confirm that the database file you’re connecting to is the one you intended.

When you’re connecting to a SQL Server database file outside your project structure, Visual Studio warns you that it is outside the project definition. At this point, you can choose to copy the file to the project directory or use the existing file (see Figure 38-12).

Figure 38-12

The choice you make here has direct consequences on the execution of your application. If you choose to copy the file to the project directory, be aware that the database file will be included as part of the build process and copied to the project’s output folder every time you compile and run the application. This enables you to define a default set of data in the table, which can then be used as a basis for your testing of the application, rather than having to manually reset any changes you make during a debug session.

Conversely, if you choose not to copy the file, any changes you make during a debugging session are saved permanently to the database. If you choose to copy the database file into the project but do not want the database copied over each time you run the application, you can change the Copy to Output property of the database.

You can access the Properties page for a database file in your project via the Solution Explorer.

The last step is to choose which database objects should be included in the Data Source (see Figure 38-13). Expand the object types you wish to include and select the individual items that are to make up your Data Source.

As demonstrated in Figure 38-13, you can select a subset of fields from within a table (or a view), and you can include stored procedures and functions. You can access this page at a later stage by clicking the Configure DataSet with Wizard button at the top of the Data Sources window. When you’ve selected the objects, click Finish to have Visual Studio build the Data Source XML schema definition that controls how the data is used in your application.

517

Chapter 38

Figure 38-13

Using Databases

Data Sources can be used in code, bound to individual controls, or used to automate creating entire visual interfaces to your data. This section looks at the ways Visual Studio 2005 helps you define these data connections from a visual standpoint. If you want to investigate further how to manage the connections in code, please take a look at Professional ADO.NET 2: Programming with SQL Server 2005, Oracle, and MySQL (Wrox, 2005).

Editing Data Source Schema

If you need to edit your Data Source schema once you have added it to the project, you can do so with the schema designer. Shown in Figure 38-14, this editor window displays a visual representation of each of the tables and views defined in the Data Source, along with any relationships that connect them.

In this example, two tables named Customers and Stores are connected by the Store.ID and Customers.StoreLastVisitedID fields. You can easily see which fields are the primary keys for each table; and to reduce clutter while you’re editing the tables, you can collapse either the field list or the queries list in the TableAdapter defined for the table.

To perform actions against a table, either right-click the table or individual field and choose the appropriate command from the context menu, or use the main Data menu that is added to the menu bar of the IDE while you’re editing the database schema.

518

Visual Database Tools

Figure 38-14

To change the SQL for a query that you’ve added to the TableAdapter, first select the query you wish to modify and the use the Data Configure menu command. The TableAdapter Configuration Wizard will appear, displaying a text representation of the existing query string (see Figure 38-15). You can either use the Query Builder to visually create a new query or simply overwrite the text with your own query.

Figure 38-15

519

Chapter 38

You can optionally have additional, associated queries for insert, delete, and update functionality generated along with the default SELECT query. To add this option, click the Advanced Options button and check the first option. The other options here enable you to customize how the queries will handle data during modification queries.

Figure 38-16 shows a sample Query Builder, which works in the same way as the View designer discussed earlier in this chapter. You can add tables to the query by right-clicking in the top area and choosing Add Table from the context menu, or by editing the SELECT statement in the text field.

Figure 38-16

To confirm that your query will run properly, use the Execute Query button to preview the results in the dialog before saving it.

These functions also work when adding a new query to a TableAdapter except that you can choose to use SQL statements or a stored procedure for the final query definition.

Data Binding Controls

Most Windows Forms controls can be bound to a Data Source once the Data Source has been added to the project. Add the control to the form and then access the Properties window for the control and locate the (Data Bindings) group. The commonly used properties for the particular field will be displayed, enabling you to browse to a field in your Data Source (or an existing TableBindingSource object). For example, a TextBox will have entries for both the Text and Tag properties.

520

Visual Database Tools

When you click the drop-down arrow next to the property you want to bind to a data element, the data bindings property editor will be displayed (see Figure 38-17). Any data sources defined in your project appear under the Other Data Sources Project Data Sources node. Expand the Data Source and table until you locate the field you want to bind to the property.

Figure 38-17

Visual Studio then automatically creates a TableBindingSource component and adds it to the form’s designer view (it will be added to the tray area for nonvisual controls) along with the other data-specific components necessary to bind the Data Source to the form. This is a huge advance since the last version of Visual Studio, in which you had to first define the data adapters and connections on the form before you could bind the fields to controls.

If you need to bind a control to a property that is not listed as a common property for that control type, select the (Advanced) property from within the (Data Bindings) entry in the Properties window and click the ellipsis button to display the Formatting and Advanced Binding dialog, shown in Figure 38-18 for a TextBox control.

Figure 38-18

521

Chapter 38

Locate the field you wish to bind, and then select the corresponding binding setting from either a Data Source or existing TableBindingSource owned by the form. You can also customize the formatting of the data at this point, even for the common properties.

Data Controls

The Toolbox has a whole group of controls that are directly related to databases. The Data controls are used to bind a Data Source to your form, and include the easy-to-use DataGridView. Previous versions of Visual Studio had two components that you used in conjunction with each other to create a tabular view of the data in a table: DataGrid and DataView. The DataGridView combines the functionality of these two controls into one component that can connect to a Data Source, define what data editing features can be performed, and customize the view in the grid area.

All of these settings can be controlled through the smart tag task pane associated with the DataGridView component (see Figure 38-19). You can select which columns from the Data Source should be shown, and whether a user can add, edit, or delete data or even move the columns around. You can change the query that will be used to populate the grid with data and preview how the data will fill the grid at design time.

Figure 38-19

Data Binding Controls the Easy Way

While you may have thought the previous walkthrough showing how to add data-bound fields to your forms was straightforward, there’s an even easier way that takes a lot of the guesswork out of binding the data to the controls. You may recall from the discussion about data sources that there are two views of the tables and fields in that tool window (refer to Figure 38-10).

522

Visual Database Tools

The second view mentioned is used specifically to ease data-bound forms development. To implement this feature, simply drag and drop the table or individual field you need onto the form’s design surface. Visual Studio automatically generates a control based on the type specified in the corresponding node in the Data Sources window and binds it to a DataSet and TableBindingSource (also creating them if it needs to) on the form.

If the form already has DataSet and TableBindingSource components, Visual Studio will ask you which ones you want to use.

If you’re not happy with the default control type for a particular field, first select the field in the Data Sources window, and then access the drop-down list and choose the control type you want to use instead. The drop-down list only displays the control types that have been chosen to work for that data type (see the section “Changing the Default Control Type” a bit later in this chapter).

If Visual Studio has to create the underlying data components to connect the control to the Data Source instead of using existing controls, it will also automatically add a BindingNavigator control to the form’s design surface, which is automatically docked to the top of the form. This BindingNavigator control provides the data navigation and editing tools required to view and change the data.

The complex form design shown in Figure 38-20 was performed through two simple operations. First, the sample Stores table was added to the form by selecting the Details view in the Data Sources list and dragging the whole table to the form. This added the BindingNavigator along with the associated

DataSet, BindingSource, and TableAdapter components, in addition to the Label and TextBox combinations for the fields belonging to the Store table.

Figure 38-20

523

Chapter 38

Second, the Customers table was added to the form using the default DataGridView control type, with its own associated data components. The controls were then resized and repositioned, and the form layout was done — without any code needing to be written or even any data binding properties being set manually.

In fact, if you take a look at the Properties window for any of the TextBox controls, you’ll see that Visual Studio automatically creates the appropriate (Data Binding) values to connect them without you lifting a finger.

Changing the Default Control Type

You can change the default control for each data type by going into the Data UI Customization page in the Visual Studio Options dialog. This options page is located under the Windows Forms Designer group (see Figure 38-21).

Figure 38-21

From the drop-down, select the data type you want to change and then pick which control type is to be associated with that kind of data. Note that you can select multiple control types to associate with the data type, but only one can be the default used by the Data Sources to set the initial control types for the fields and tables.

Managing Test Data

Visual Studio 2005 also has the capability to view and edit the data contained in your database tables. To edit the information, use the Data Show Table Data menu command after you highlight the table you want to view in the Server Explorer.

You will be presented with a tabular representation of the data in the table, enabling you to edit it to contain whatever default or test data you need to include. As you edit information, the table editor will display indicators next to fields that have changed (see Figure 38-22).

524

Visual Database Tools

Figure 38-22

You can also show the diagram, criteria, and SQL panes associated with the table data you’re editing by right-clicking anywhere in the table and choosing the appropriate command from the Pane submenu.

Previewing Data

You can preview data for different Data Sources to ensure that the associated query will return the information you expect. In the database schema designer, right-click on the query you want to test and choose Preview Data from the context menu.

The Preview Data dialog is displayed with the object list defaulted to the query you want to test. Click the Preview button to view the sample data, shown in Figure 38-23. A small status bar provides information about the total number of data rows that were returned from the query, as well as how many columns of data were included.

Figure 38-23

525