Now, click Finish in the Data Source Configuration
Wizard. Although you’re far from finished, that’s as
far as the wizard will take you. However, this process
builds a strongly typed TableAdapter for each rowset
that the stored procedures return and adds the CustomerDataSet.
xsd file to the project. The Data Sources
window also exposes the new TableDef objects.
The behavior of the Data Sources window changes
depending on the currently exposed editing window.
For example, if you set focus to the Form UI design
window Form1.vb (Design), you can choose how
individual TableAdapter objects in the Data Sources
window are used when generating UI controls. But
don’t start dragging any objects yet because you
haven’t manually set up relationships between the
rowsets returned by the stored procedures. Note that
if you had created TableAdapter objects from the base
tables, VS would have derived the DataRelation objects
automatically.
Also note that the TableAdapter Fill method is
automatically configured to capture any input parameters
you’ve defined. In this case, the Fill method will
need values supplied for the @StateWanted and @
NameHint parameters.
Reconfiguring TableAdapter
Objects
Because you chose three stored procedures that return
selected Customer, Order, and Items rowsets, you need
to reconfigure the TableAdapter objects to handle these
rowsets correctly. The following steps walk you through
the process of doing so, which you’ll repeat for each of the three TableAdapter objects that the Data Source
Configuration Wizard generated.
First, double-click the CustomerDataSet.xsd file
in VS’s Solution Explorer to open the TableAdapter
Designer. Starting with the GetCustomersByState
TableAdapter, which returns the root parent Customers
rowset, right-click the top of the window and
select Configure. Doing so opens the TableAdapter
Configuration Wizard, which Figure 1 shows. In this
wizard, you can point to the correct Insert, Update,
and Delete stored procedures that the TableAdapter
Update method calls. Be sure to choose the correct
stored procedures—mixing them up can have bad
consequences. I used VS to generate the initial version
of these procedures. Once VS (or you) create the
stored procedures, you can define the appropriate input
parameters, output rowsets, and RETURN values.
From there, you can add custom logic as needed.
Note how the initial rowset columns from the Get-
CustomersByState stored procedure are mapped to
the Insert command’s source columns. If the columns
that the stored procedures return don’t match all the
required columns exposed by the SELECT query, a
warning message will be displayed.
Now, click Next. Because the code doesn’t use the
DataTable option, it’s okay to disable it. Click Next
again to construct the code needed to link your Table-
Adapter to the appropriate stored procedures, and then
click Finish to commit the operations. Repeat these
steps for the two child rowsets, Orders and Items.
Defining Client-Side
Inter-Rowset Relationships
Because SQL Server doesn’t define relationships
between independent rowsets, it’s up to you and VS
to define the DataRelation objects between the three
rowsets generated by your TableAdapter objects. This
process is fairly easy once you figure out how to select
the correct primary key–foreign key index values in
the UI. (As I noted earlier, these relationships are
generated automatically when you build data sources
based on base tables that have established server-side
relationships.)
When implemented, these relationships establish
primary key–foreign key constraints that prevent
changes to the database that would break referential
integrity rules. That is, the relationships prevent parents
from being deleted when they still have children and
children from being added where no related parents
exist. These constraints can also prevent duplicate rows
(based on the primary key columns) from being added
to the database.
Most DBAs set up these constraints whenever they
create a relational database. You can do so by using
VS’s database diagramming tool, which you can find
in Server Explorer’s Database Diagrams folder. This tool works much like the TableAdapter Configuration
Wizard to let you specify not only indexes, but also
primary key–foreign key relationships.
Now, let’s look at how you create these client-side
DataSet DataRelation objects. Starting with the parent
rowset (GetCustomersByState), click the primary key
column (CustID) and drag it to the left. If you drag
down, you’ll select additional columns, which is what
you want to do for the GetOrdersByCustomer Table-
Adapter because there are two columns that define the
primary key. After you’ve selected only the primary
key columns, drag them to the left, hesitate to let VS
generate the pointer, and then drag the
pointer to the child table’s TableAdapter
(i.e., GetOrdersByCustomer)
and drop. Doing so opens the Relation
dialog box, which links the two
TableAdapter objects by primary
key and foreign key columns, as
Figure 2 shows.
In the Relation dialog box, set
each foreign key column to match
a column in the Key Columns list,
which should contain all primary
key columns for the parent Table-
Adapter. In my design, these column
names are the same from parent to
child, but they don’t have to be. Also
set the Choose what to create option
to Both Relation and Foreign Key
Constraint and set all rules to Cascade
to ensure that if a parent row
is deleted, the child row(s) are also
deleted.
You need to repeat this process for the next
parent–child relationship. You can either drag and
drop or simply right-click the top border of the parent
TableAdapter window and select Add, Relation. Be
very careful: The TableAdapter objects are listed in
alphabetical order, so it’s easy to choose the wrong TableAdapter as the parent or child. In this case, because
there are two parts to the primary key in the parent
rowset (Orders), two Key Columns are paired with two
Foreign Key Columns in the child table. Again, make
sure you set the rules to cascade changes.
Continue to page 3
Prev. page
1
[2]
3
4
next page 
