第 82 页

sorting and filtering properties, and the area directly beneath the tabular representation shows the

SQL that is used to achieve the view you’ve specified. Changes can be made in any of these three

panes with the other panes being dynamically updated with the changes.

The bottom part of the view designer can be used to execute the view SQL and preview the results.

To execute this view, select Execute SQL from the right-click context menu on any of the panes, or

click the button with the same name from the View Designer toolbar.

stored Procedures and functions

To create and modify stored procedures and functions,

Visual Studio 2010 uses a text editor such as the one

shown in Figure 26-8. Since there is no IntelliSense

to help you create your procedure and function

definitions, Visual Studio doesn’t allow you to save

your code if it detects an error.

To help you write and debug your stored procedures

and functions, there are shortcuts to Insert SQL, Run

Selection, and Execute from the right-click context

menu for the text editor. Inserting SQL displays the

Query Builder shown earlier in Figure 26-7 as a modal dialog. Run Selection attempts to execute any

selected SQL statements, displaying the results in the Output window. Finally, the Execute shortcut

runs the entire stored procedure or function. If they accept input parameters, a dialog similar to

fiGure 26-8

Database Windows in Visual studio 2010 .

555

Figure 26-9 is displayed, in which you can

specify appropriate test values. Again, the

results are displayed in the Output window.

Database Diagrams

You can also create a visual representation of

your database tables via database diagrams. To

create a diagram, use the Data . Add New .

Diagram menu command or right-click the Database Diagrams node in the Server Explorer and

choose Add New Diagram from the context menu.

When you create your first diagram in a database, Visual Studio may prompt you to allow it to

automatically add necessary system tables and data to the database. If you disallow this action, you

won’t be able to create diagrams at all; so it’s just a notification, rather than an optional action to take.

The initial process of creating a diagram enables you to choose which tables you want in the diagram,

but you can add tables later through the Database Diagram menu that is added to the IDE. You can

use this menu to affect the appearance of your diagram within the editor too, with zoom and page

break preview functionality as well as being able to toggle relationship names on and off.

Because database diagrams can be quite large, the IDE has an easy way of navigating around the

diagram. In the lower-right corner of the Database Diagram editor in the workspace is an icon

displaying a four-way arrow. Click this icon and a thumbnail view of the diagram appears, as

shown in Figure 26-10.

fiGure 26-9

fiGure 26-10

556 .

chaPter 26 ViSuAl dATAbASe ToolS

Just click and drag the mouse pointer around the thumbnail until you position the components you

need to view and work with in the viewable area of the IDE.

the data sources window

The Data Sources window, which typically appears in the same tool window area as the Solution

Explorer, contains any active data sources known to the project, such as datasets (as opposed to the

Data Connections in the Server Explorer, which are known to Visual Studio overall). To display

the Data Sources tool window, use the Data

. Show Data Sources menu command.

The Data Sources window has two main

views, depending on the active document

in the workspace area of the IDE. When

you are editing code, the Data Sources

window displays tables and fields with icons

representing their types. This aids you as you

write code because you can quickly reference

the type without having to look at the table

definition. This view is shown on the right

image of Figure 26-11.

When you’re editing a form in Design view, however, the Data Sources view changes to display the

tables and fields with icons representing their current default control types (initially set in the Data

UI Customization page of Options). The left image of Figure 26-11 shows that the text fields use

TextBox controls, whereas the ModifiedDate field uses a DateTimePicker control. The icons for the

tables indicate that all tables will be inserted as DataGridView components by default as shown in

the drop-down list.

In the next chapter you learn how to add and modify data sources, as well as use the Data Sources

window to bind your data to controls on a form. Data classes or fields can simply be dragged from

the Data Sources window onto a form in order to wire up the user interface.

editinG data

Visual Studio 2010 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 as

shown in Figure 26-12, enabling you to edit

it to contain whatever default or test data

you need to include. By using the buttons

at the bottom of the table, you can navigate

around the returned records and even create

new rows. As you edit information, the table

editor displays indicators next to fields that

have changed.

fiGure 26-11

fiGure 26-12

Previewing Data .

557

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. This can be useful for customizing the SQL statement that is being used to retrieve

the data — for example, to filter the table for specific values, or just to retrieve the first 50 rows.

PreViewinG data

You can also 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 the query you want to test

and choose Preview Data from the context menu. Alternatively, select Preview Data from the right-

click context menu off any data source in the Data Sources tool window.

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 26-13. 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.

If you want to change to a different query, you can do so with the Select an object to preview drop-

down list. This list contains other queries in the same data source, other data sources, and elsewhere

in your solution. If the query you’re previewing requires parameters, you can set their values in the

Parameters list in the top-right pane of the dialog. Clicking the Preview button submits the query

to the appropriate data source and displays the subsequent results in the Results area of the Preview

Data dialog box.

fiGure 26-13

558 .

chaPter 26 ViSuAl dATAbASe ToolS

suMMary

With the variety of tools and windows available to you in Visual Studio 2010, you can easily create

and maintain databases without having to leave the IDE. You can manipulate data as well as

define database schemas visually using the Properties tool window in conjunction with the Schema

Designer view.

Once you have your data where you want it, Visual Studio keeps helping you by providing a set

of drag-and-drop components that can be bound to a data source. These can be as simple as a

checkbox or textbox, or as feature-rich as a DataGridView component with complete table views.

In the next chapter you learn how being able to drag whole tables or individual fields from the Data

Sources window onto a form and have Visual Studio automatically create the appropriate controls

for you is a major advantage for rapid application development.

27

Datasets and DataBinding

what’s in this chaPter?

.

Creating DataSets

.

Connecting visual controls to a DataSet with DataBinding

.

How BindingSource and BindingNavigator controls work together

.

Chaining BindingSources and using the DataGridView

.

Using Service and Object data sources

A large proportion of applications use some form of data storage. This might be in the form

of serialized objects or XML data, but for long-term storage that supports concurrent access

by a large number of users, most applications use a database. The .NET Framework includes

strong support for working with databases and other data sources. This chapter examines how

to use DataSets to build applications that work with data from a database.

In the second part of this chapter you see how to use DataBinding to connect visual controls

to the data they are to display. You see how they interact and how you can use the designers to

control how data is displayed.

The examples in this chapter are based on the sample AdventureWorksLT database that is

available as a download from http://professionalvisualstudio.com/link/1029A.

datasets oVerView

The .NET Framework DataSet is a complex object that is approximately equivalent to an

in-memory representation of a database. It contains DataTables that correlate to database

tables. These in turn contain a series of DataColumns that define the composition of

each DataRow. The DataRow correlates to a row in a database table. It is also possible to

560 .

chaPter 27 dATASeTS And dATAbinding

establish relationships between DataTables within the DataSet in the same way that a database has

relationships between tables.

One of the ongoing challenges for the object-oriented programming paradigm is that it does

not align smoothly with the relational database model. The DataSet object goes a long way

toward bridging this gap, because it can be used to represent and work with relational data in an

object-oriented fashion. However, the biggest issue with a raw DataSet is that it is weakly typed.

Although the type of each column can be queried prior to accessing data elements, this adds

overhead and can make code very unreadable. Strongly typed DataSets combine the advantages of

a DataSet with strong typing (in other words, creating strongly typed properties for all database

fields) to ensure that data is accessed correctly at design time. This is done with the custom tool

MSDataSetGenerator, which converts an XML schema into a strongly typed DataSet, essentially

replacing a lot of run time type checking with code generated at design time. In the following code

snippet, you can see the difference between using a raw DataSet, in the first half of the snippet, and

a strongly typed DataSet, in the second half:

Vb

'Raw DataSet

Dim nontypedAwds As DataSet = RetrieveData()

Dim nontypedcustomers As DataTable = nontypedAwds.Tables("Customer")

Dim nontypedfirstcustomer As DataRow = nontypedcustomers.Rows(0)

MessageBox.Show(nontypedfirstcustomer.Item("FirstName"))

'Strongly typed DataSet

Dim awds As AdventureWorksLTDataSet = RetrieveData()

Dim customers As AdventureWorksLTDataSet.CustomerDataTable = awds.Customer

Dim firstcustomer As AdventureWorksLTDataSet.CustomerRow = customers.Rows(0)

MessageBox.Show(firstcustomer.FirstName)

Code snippet CustomersForm.vb

c#

// Raw DataSet

DataSet nontypedAwds = RetrieveData();

DataTable nontypedcustomers = nontypedAwds.Tables["Customer"];

DataRow nontypedfirstcustomer = nontypedcustomers.Rows[0];

MessageBox.Show(nontypedfirstcustomer["FirstName"].ToString());

// Strongly typed DataSet

AdventureWorksLTDataSet awds = RetrieveData();

AdventureWorksLTDataSet.CustomerDataTable customers = awds.Customer;

AdventureWorksLTDataSet.CustomerRow firstcustomer =

customers.Rows[0] as AdventureWorksLTDataSet.CustomerRow;

MessageBox.Show(firstcustomer.FirstName);

Code snippet CustomersForm.cs

Using the raw DataSet, both the table lookup and the column name lookup are done using string

literals. As you are likely aware, string literals can be a source of much frustration and should be

used only within generated code, and preferably not at all.

Datasets overview .

561

adding a data source

You can manually create a strongly typed DataSet by creating an XSD using the XML

schema editor. To create the DataSet, you set the custom tool value for the XSD file to be the

MSDataSetGenerator. This will create the designer code file that is needed for strongly typed

access to the DataSet.

Manually creating an XSD is difficult and not recommended unless you really need to; luckily in

most cases, the source of your data will be a database, in which case Visual Studio 2010 provides a

wizard that you can use to generate the necessary schema based on the structure of your database.

Through the rest of this chapter, you see how you can create data sources and how they can be

bound to the user interface. To get started, create a new project called CustomerObjects, using the

Windows Forms Application project template.

Although this functionality is not available for ASP.NET projects, a workaround

is to perform all data access via a class library.

To create a strongly typed DataSet from an existing database, select Add New Data Source from the

Data menu, and follow these steps:

1 The first step in the Data Source Configuration Wizard is to select the type of data source to

work with — a Database, Service, Object, or SharePoint data source. In this case, you want

to work with data from a database, so select the Database icon and click Next.

2 With the introduction of the ADO.NET Entity Framework there are now two different data

models that you can choose to represent the mapping between database data and .NET

entities, being a Dataset or an Entity Data Model. The Entity Framework is covered in

Chapter 29. Double-click the DataSet icon to continue.