第 83 页

3 The next screen prompts you to select the database connection to use. To create a new

connection, click the New Connection button, which opens the Add Connection dialog.

The attributes displayed in this dialog are dependent on the type of database you are

connecting to. By default, the SQL Server provider is selected, which requires the Server

name, authentication mechanism (Windows or SQL Server), and Database name in order

to proceed. There is a Test Connection that you can use to ensure you have specified valid

properties.

4 After you specify a connection, it is saved as an application setting in the application

configuration file.

When the application is later deployed, the connection string can be modified to point

to the production database. This process can often take longer than expected to ensure

that various security permissions line up. Because the connection string is stored in the

configuration file as a string without any schema, it is quite easy to make a mistake when

making changes to it. In Chapter 37 you learn more about connection strings and how you

can customize them for different data sources.

562 . chaPter 27 dATASeTS And dATAbinding

5 After specifying the connection,

the next stage is to specify the data

to be extracted. At this stage you

are presented with a list of tables,

views, stored procedures, and

functions from which you can select

what to include in the DataSet.

Figure 27 - 1 shows the fi nal stage

of the Data Source Confi guration

Wizard with a selection of columns

from the Customer table in the

AdventureWorksLT database.

Checking the Enable Local Database

Caching checkbox gives you offl ine,

or disconnected, support for working

with your data. This makes use of

Synchronization Services for ADO.NET to synchronize data between your application and

the database, and is covered in more detail in Chapter 34 .

fiGure 27 - 1

A little - known utility within Windows can be used to create connection strings,

even if Visual Studio is not installed. Known as the Data Link Properties dialog,

you can use it to edit Universal Data Link fi les, fi les that end in . udl. When

you need to create or test a connection string, you can simply create a new text

document, rename it to something.udl , and then double - click it. This opens the

Data Link Properties dialog, which enables you to create and test connection

strings for a variety of providers. Once you have selected the appropriate

connection, this information will be written to the UDL fi le as a connection

string, which can be retrieved by opening the same fi le in Notepad. This can be

particularly useful if you need to test security permissions and resolve other data

connectivity issues.

You will probably want to constrain the DataSet so it doesn ’ t return all the

records for a particular table. You can do this after creating the DataSet, so for

the time being simply select the information you want to return. The editor ’ s

design makes it easier to select more information here and then delete it from the

designer, rather than create it afterwards.

Datasets overview .

563

6 .

Click Finish to add the new DataSet to the Data

Sources window, shown in Figure 27-2, where

you can view all the information to be retrieved

for the DataSet. Each column is identified with

an icon that varies depending on the data type

of the column. In the left image (displayed

when using a design surface) the icons represent

the default visual control that will be used

to represent the column; in the right image

(displayed when in a code window) the icons

indicate the data type.

the dataset designer

The Data Source Configuration Wizard uses

the database schema to guess the appropriate

.NET type to use for the DataTable columns.

In cases where the wizard gets information

wrong, it can be useful to edit the DataSet

without the wizard. To do this, right-click

the DataSet in the Data Sources window

and select Edit DataSet with Designer from

the context menu. Alternatively, you can

open the Data Sources window by double-

clicking the XSD file in the Solution Explorer

window. This opens the DataSet editor in the main window, as shown in the example in Figure 27-3.

Here you start to see some of the power of using strongly typed DataSets. Not only has a strongly

typed table (Customer) been added to the DataSet, you also have a CustomerTableAdapter. This

TableAdapter is used for selecting from and updating the database for the DataTable to which it is

attached. If you have multiple tables included in the DataSet, you will have a TableAdapter for each.

Although a single TableAdapter can easily handle returning information from multiple tables in the

database, it becomes difficult to update, insert, and delete records.

As you can see in Figure 27-3, the CustomerTableAdapter has been created with Fill and GetData

methods, which are called to extract data from the database. The following code shows how you

can use the Fill method to populate an existing strongly typed DataTable, perhaps within a

DataSet. Alternatively, the GetData method creates a new instance of a strongly typed DataTable:

Vb

Dim ta As New AdventureWorksLTDataSetTableAdapters.CustomerTableAdapter

'Option 1 - Create a new CustomerDataTable and use the Fill method

Dim customers1 As New AdventureWorksLTDataSet.CustomerDataTable

ta.Fill(customers1)

'Option 2 - Use the GetData method which will create a CustomerDataTable for you

Dim customers2 As AdventureWorksLTDataSet.CustomerDataTable = ta.GetData

fiGure 27-2

fiGure 27-3

564 .

chaPter 27 dATASeTS And dATAbinding

In Figure 27-3, the Fill and GetData methods appear as a pair because they make use of the same

query. The Properties window can be used to configure this query. A query can return data in one

of three ways: using a text command (as the example illustrates), a stored procedure, or TableDirect

(where the contents of the table name specified in the CommandText are retrieved). This is specified

in the CommandType field. Although the CommandText can be edited directly in the Properties

window, it is difficult to see the whole query and easy to make mistakes. Clicking the ellipsis button

(at the top right of Figure 27-3) opens the Query Builder window, shown in Figure 27-4.

fiGure 27-4

The Query Builder dialog is divided into four panes. In the top pane is a diagram of the tables

involved in the query, and the selected columns. The second pane shows a list of columns related

to the query. These columns are either output columns, such as FirstName and LastName, or a

condition, such as the Title field, or both. The third pane is, of course, the SQL command that is

to be executed. The final pane includes sample data that can be retrieved by clicking the Execute

Query button. If there are parameters to the SQL statement (in this case, @Title), a dialog is

displayed, prompting for values to use when executing the statement.

To change the query, you can make changes in any of the first three panes. As you move

between panes, changes in one field are reflected in the others. You can hide any of the panes by

unchecking that pane from the Panes item of the right-click context menu. Conditions can be added

using the Filter column. These can include parameters (such as @Title), which must start with the

@

symbol.

Binding Data .

565

Returning to the DataSet designer, and the

Properties window associated with the Fill

method, click the ellipsis to examine the list

of parameters. This shows the Parameters

Collection Editor, as shown in Figure 27-5.

Occasionally, the Query Builder doesn’t get

the data type correct for a parameter, and

you may need to modify it using this dialog.

Also from the Properties window for the

query, you can specify whether the Fill

and/or GetData methods are created, using

the GenerateMethods property, which has

values Fill, Get, or Both. You can also

specify the names and accessibility of

the generated methods.

bindinG data

The most common type of application is one that retrieves data from a database, displays the

data, allows changes to be made, and then persists those changes back to the database. The

middle steps that connect the in-memory data with the visual elements are referred to as

DataBinding. DataBinding often becomes the bane a of developer’s existence because it has been

difficult to get right. Most developers at some stage or another have resorted to writing their own

wrappers to ensure that data is correctly bound to the controls on the screen. Visual Studio 2010

dramatically reduces the pain of getting two-way DataBinding to work. The examples used in the

following sections again work with the AdventureWorksLT sample database. For simplicity, you’ll

work with a single Windows application, but the concepts discussed here can be extended over

multiple tiers.

In this example, you build an application to assist you in managing the customers for

AdventureWorks. To begin, you need to ensure that the AdventureWorksLTDataSet contains the

Customer and Address tables. (You can reuse the AdventureWorksDataSet from earlier by clicking

the Configure DataSet with Wizard icon in the Data Source window and editing which tables are

included in the DataSet.) With the form designer (any empty form in your project will do) and Data

Sources window open, set the mode for the Customer table to Details using the drop-down list.

Before creating the editing controls, tweak the list of columns for the Customer table. You’re not

that interested in the CustomerID, NameStyle, PasswordHash, PasswordSalt, or rowguid fields,

so set them to None (again using the drop-down list for those nodes in the Data Sources window).

ModifiedDate should be automatically set when changes are made, so this field should appear as a

label, preventing the ModifiedDate from being edited.

fiGure 27-5

566 .

chaPter 27 dATASeTS And dATAbinding

Now you’re ready to drag the Customer

node onto the form design surface. This will

automatically add controls for each of the

columns you have specified. It will also add

a BindingSource, a BindingNavigator,

an AdventureWorksDataSet, a

CustomerTableAdapter, a TableAdapter

Manager, and a ToolStrip to the form as

shown in Figure 27-6.

At this point you can build and run this

application and navigate through the records

using the navigation control, and you can also take

the components apart to understand how they

interact. Start with the AdventureWorksDataSet

and the CustomerTableAdapter, because

they carry out the background grunt work of

retrieving information and persisting changes

to the database. The AdventureWorksDataSet that is added to this form is actually an instance of the

AdventureWorksDataSet class that was created by the Data Source Configuration Wizard. This instance

will be used to store information for all the tables on this form. To populate the DataSet, call the Fill

method. If you open the code file for the form, you will see that the Fill command has been called from

the Click event handler of the Fill button that resides on the toolstrip.

Vb

Private Sub FillToolStripButton_Click(ByVal sender As Object,

ByVal e As EventArgs) _

Handles FillToolStripButton.Click

Try

Me.CustomerTableAdapter.Fill(Me.AdventureWorksLTDataSet.Customer,

TitleToolStripTextBox.Text)

Catch ex As System.Exception

System.Windows.Forms.MessageBox.Show(ex.Message)

End Try

End Sub

Code snippet CustomersForm.vb

c#

private void fillToolStripButton_Click(object sender, EventArgs e){

try{

this.customerTableAdapter.Fill(

this.adventureWorksLTDataSet.Customer, titleToolStripTextBox.Text);

}

catch (System.Exception ex){

System.Windows.Forms.MessageBox.Show(ex.Message);

}

}

Code snippet CustomersForm.cs

fiGure 27-6

Binding Data .

567

As you extend this form, you’ll add a TableAdapter for each table within the AdventureWorksDataSet

that you want to work with.

bindingsource

The next item of interest is the CustomerBindingSource that was automatically added to the

non-visual part of the form designer. This control is used to wire up each of the controls on

the design surface with the relevant data item. In fact, this control is just a wrapper for the

CurrencyManager. However, using a BindingSource considerably reduces the number of event

handlers and custom code that you have to write. Unlike the AdventureWorksDataSet and the

CustomerTableAdapter — which are instances of the strongly typed classes with the same

names — the CustomerBindingSource is just an instance of the regular BindingSource class that

ships with the .NET Framework.

Take a look at the properties of the CustomerBindingSource so you can see what it does.

Figure 27-7 shows the Properties window for the CustomerBindingSource. The two items of

particular interest are the DataSource and DataMember properties. The drop-down list for the

DataSource property is expanded to illustrate the list of available data sources. The instance of

the AdventureWorksDataSet that was added to the form is listed under CustomerForm List

Instances. Selecting the AdventureWorksDataSet type under the Project Data Sources node creates

another instance on the form instead of reusing the existing DataSet. In the DataMember field, you

need to specify the table to use for DataBinding. Later, you’ll see how the DataMember field can be

used to specify a foreign key relationship so you can show linked data.

So far you have specified that the CustomerBindingSource will bind data in the Customer table of

the AdventureWorksDataSet. What remains is to bind the individual controls on the form to the

BindingSource and the appropriate column in the Customer table. To do this you need to specify

a DataBinding for each control. Figure 27-8 shows the Properties grid for the FirstNameTextBox,