Hierarchical TableAdapters 301
Saving Data to the Database
When the user (or your code) decides that it's time to save the changes made, you'll need to implement the SaveItem_Click event as exposed by the BindingNavigator ToolStrip control. Yes, part of this code is implemented for you by the drag-and-drop operations, but it does not deal with any but the top-level parent control. Saving the data to the database is done in two phases. The first phase validates and commits any changes made in the bound controls to the underlying DataTable row via the Validate and EndEdit methods. These routines are codes as shown in Figure 11:
Phase two of the update operation steps through the parent/child/grandchild hierarchy (Customer/Order/Item) and posts any changes to the database. As I describe in my book, these operations must take place in the correct order to satisfy the PK/FK constraints as you defined in the TableAdapter Designer. Yes, 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. The constraints are implemented behind the scenes by DataRelation objects that define how the relationships are to be enforced. Don't remember coding any? Well, you didn't—the TableAdapter Configuration Wizard did it for you. Dig into the CustomerDataSet.Designer.vb file and you'll see where these are defined. Are these constraints the same as those already implemented in the database? Perhaps, but only perhaps.
Figure 12: Updating the hierarchical DataSet
As you can see, it's entirely possible to create an application that can handle hierarchically related rowsets derived from virtually any source—even stored procedures. There are a few stumbling blocks along the way because Microsoft didn't expect ordinary developers to take this route. Yes, it's easier to reference the base tables, but that makes a couple of assumptions: the DBA will permit base table access and you aren't concerned with scalability. Most DBAs hide and protect the base tables more carefully than the box of chocolates kept hidden in the back of the fourth filing cabinet on the left. You'll find that this stored procedure approach is more palatable to the DBA and even permits you to change the logic in the stored procedure as long as you don't change the signature—the pattern of input parameters and output columns being returned. Your performance and scalability will be a lot better as well.
About the Author
William (Bill) Vaughn is an industry-recognized author, mentor, and subject-matter expert on Visual Studio, SQL Server, Reporting Services, and data access interfaces. He's worked in the computer industry for over thirty-five years—working with mainframe, minicomputer, and personal computer systems as a developer, manager, architect, trainer, marketer, support specialist, writer, and publisher. In 2000, after 14 years at Microsoft, Bill stepped away to work on his books, mentoring, and independent training seminars. He's written seven editions of the Hitchhiker's Guide to Visual Basic and SQL Server and three editions of ADO.NET and ADO Examples and Best Practices for Visual Basic (and C#) Programmers. He and Peter Blackburn also wrote the critically acclaimed Hitchhiker's Guide to SQL Server 2000 Reporting Services.
Bill is a top-rated speaker and frequents conferences all over the world including TechEd, Visual Studio/SQL Connections, DevTeach, and many others. He's also written a wealth of articles for magazines such as MSDN, SQL Server Magazine, Visual Basic Programmer's Journal, .NET Magazine, and many others as well as a regular editorial for Processor magazine. Bill spends considerable time answering questions on the public newsgroups and speaking at INETA user group meetings all over the country and at other speaking venues all over the world. He's available for consulting, mentoring, or custom training. See www.betav.com or www.betav.com/blog/billva for his current schedule and course catalog.
Page 5 of 5