Tuning and Augmenting
the Code
Although VS generates code to support some of the
drag-and-drop operations, there are missing links and
blocks of code that you need to add to get the project
to work as expected. Part of the problem is because the
example uses used stored procedures and deletes two
of the FillToolStrip controls, which provide another
way to navigate between rows in the parent DataTable
and related children DataTables. I deleted the controls
so that the application pulls only the appropriate children
for a selected parent row. Let’s walk through the
application at a functional basis to see where you need
to fill in the blanks.
Implementing Form_Load. Microsoft’s developers
and I don’t always agree on connection strategies, but
in this case, I think I can fully justify my approach. I
open the Connection objects that the TableAdapter
objects use and leave them open for the life of the
application, as Web Listing 2 shows. By doing so, you
don’t have to wait for an available connection when
you want to scroll to another customer, execute the
Fill methods, or post an update to the database. The
default behavior has the Fill and Update methods
open the Connection objects just before the query (or
Data Manipulation Language—DML—operation) is
executed. With this approach, the Connection pool
must locate a suitable matching connection, and the
SQL Server must reset and reconfigure the connection
each time it’s referenced. Not only does this process
take time, but it makes debugging the operations by using SQL Server Profiler harder because the trace
stream is clogged with detritus that’s not needed for
Windows Forms applications—especially those that
don’t scale beyond a few hundred users.
Implementing the Fill button. The FillToolStrip control
captures the two input parameters (i.e., State Wanted,
Name Hint) and exposes a Fill button that the user can
click to populate the list of customers. As implemented,
the FillToolStripButton_Click event executes the Get-
CustomersByStateTableAdapter.Fill method, passing
in parameters from the FillToolStrip TextBox controls.
However, this operation doesn’t automatically populate
the child rowsets, so you’ll also need to call the Orders
and Items Fill methods. Each method is a focused query
that returns orders for only a specific customer and
items from only a specific order. You also need to add an
exception handler for when there are no customers within
the given range. To ensure that the user doesn’t try to
add rows before the initial rowset population, I disabled
the BindingNavigator and reenabled it when the query
returned at least one row. Web Listing 3 shows the code
for this routine.
Implementing the DataError event. If you aren’t
careful, you’ll include columns in your DataGridView
that it can’t handle, including an unrecognized binary
value such as a TimeStamp. If you don’t hide this
column or if your code (or user) generates a value the
DataGridView can’t handle, you’ll need to implement
the DataError event. This code can do anything you
want, including cancel the problem-causing operation.
Web Listing 4 shows my routine, which handles the
DataError events for both DataGridView controls,
dumping exceptions to the Debug window so that I
can review them later.
Populating rowsets for the child DataTables. When
a user selects a certain customer to view and the list of
customers is initially populated, you need to populate
the lists of applicable child orders and items. Two event
handlers, which are triggered by the BindingSource PositionChanged event, manage this process. These
events fire when the user selects another customer
or another order for a specific customer. Each of the
event handler routines call the appropriate TableAdapter
Fill method, passing the current customer ID
and order ID to focus the query on rows related only
to that customer, as Web Listing 5 shows.
Saving data to the database. You also need to implement
the SaveItem_Click event, as exposed by the
BindingNavigator ToolStrip control, for when a user
(or your code) wants to save changes. Part of this code
is implemented for you by the drag-and-drop operations,
but these operations deal with only the top-level
parent control. Saving the data to the database is done
in two phases. The first phase validates and commits
any changes in the bound controls to the underlying
DataTable row through the Validate and EndEdit
methods, which Web Listing 6 shows.
The second phase of the update operation steps
through the parent/child/grandchild hierarchy (e.g.,
Customer/Order/Item) and posts any changes to
the database. These operations must occur in the
correct order to satisfy the primary key–foreign key
constraints that you defined in the TableAdapter
Designer. These are client-side constraints enforced
by ADO.NET Framework classes that prevent your
code from deleting parents that still have children and
adding children with no parents. You implement the
constraints by coding DataRelation objects that define
how the relationships are enforced. Don’t remember
coding any of these objects? You didn’t—the TableAdapter
Configuration Wizard did it for you when you
mapped the primary key and foreign key relationships.
Just dig into the CustomerDataSet.Designer.vb file to
see the definitions.
Setting server-side constraints. Of course, you
should also implement similar constraints on SQL
Server by setting up primary key–foreign key constraints
and cascading operations where appropriate.
In most databases, the DBA ensures that these constraints
are implemented so that you can’t accidentally
break the database’s referential integrity. The tools for
defining these relationships are included in VS’s Server
Explorer (through the database diagramming tool) or
in SSMS’s Object Explorer. I defined these constraints
when I created the Customer database so that if my
VS code makes a mistake, the database isn’t affected.
These constraints can also help make handling hierarchical
updates easier because you can program them
to cascade the deletes. For example, you can program
the constraints so that when you delete the customer
Fred, all of his orders and the items associated with
those orders are also deleted. However, you should use
triggers or other safeguards to monitor this kind of
operation and prevent customers from being arbitrarily
deleted—especially if they still owe you money.
Using transactions to protect referential integrity.
Binding these separate mission-critical operations into
a single atomic transaction makes a lot of sense. You
can find many articles about using transactions, but I
think the simplest approach is to use the System.Transactions
namespace, which lets the .NET Framework
handle transactions and reduces the amount of code
you have to write to implement them. Web Listing 7 shows the code that steps through the hierarchy and
posts changes to the database. It also illustrates the use
of the System.Transactions.TransactionScope class.
The updates add any new parents (e.g., Customers)
first, followed by any new children (e.g., Orders and
then Items). Combined with the add operations, you
can include the changes to existing parents and children
because they don’t affect the hierarchy. However,
I don’t recommend changing the primary key—that
change can be safely implemented only by deleting
the original row and adding a new row with the new
primary key.
After the add and change operations are done,
you’re ready to execute the delete operations. In each
case, you use the ADO.NET Select method to filter for
just the rows you need based on the RowState property.
When all operations are complete, you let the .NET
Framework know that the transaction should be committed
by calling the Complete event; otherwise, the
transaction is automatically rolled back.
The Road Less Traveled
The sample application can also import pictures,
handle new row initialization, and provide a list of
addresses through the Addresses DataTable, which the
Web-exclusive sidebar “Additional Features” describes.
As you can see, you can create an application that can
manage hierarchically related rowsets derived from
almost any source—even stored procedures. There are
a few stumbling blocks along the way because I don’t
think Microsoft expected developers to take this route.
Although it’s easier to reference the base tables, that
approach makes one big assumption: that the DBA
will let you. Most DBAs hide and protect the base
tables very carefully. You’ll find that this technique is
more palatable to DBAs and even lets you change the
stored procedure logic as long as you don’t change the
signature, which is the pattern of input parameters and
output columns being returned.
I’ve also had a chance to look at VS 2008, which
includes a TableAdapterManager class that promises
to make the development process easier. The new class
is designed to replace much of the code that I had to
write for the UpdateHierarchy routine with a single
method call. Stay tuned for an article on the new dataaccess
features exposed in VS 2008 and SQL Server
2008.
End of Article
Prev. page
1
2
3
[4]
next page -->
