Introduction

Most of the folks who create data access applications eventually need to save data back to the database. There are any number of strategies that you can use to accomplish this using Visual Studiosome of which involve drag-and-drop update or wizard-generated code that Visual Studio cobbles together based on the SELECT statement. Other, somewhat more complex approaches require that you create the T-SQL action queries yourself or depend on existing stored procedures to make the changes. Sure, Visual Studio has gotten (a bit) smarter when it comes to generating UPDATE statements at design time, but it's still fairly limitedas you'll discover later in our tour of this topic.

When it comes to updates, the fundamental problem with Visual Studio is that it takes a relatively simplistic approach. It assumes that your database is simply a collection of individual tables that can be updated willy-nilly. Most serious databases don't fall in this categorythey are relational in every sense. They include many parent-child/primary/foreign-key relationships and don't take kindly to changes that don't follow a fairly rigid discipline. For example, you must add parent rows before child rows and delete children before deleting their parents. At this point in time, the Visual Studio code generators do not deal with this reality.

When I poll a conference session filled with Visual Studio/SQL Server developers, 90% of them don't expose the base tables at allthey expose stored procedures or views to protect the data and referential integrity, whether or not they're using SQL Server. I've talked about this on any number of occasions here and in my sessions. These stored procedures are usually fairly sophisticated, as they accept parameters that are fed to not one, but perhaps a half-dozen or more UPDATE statements in a single atomic operation. Visual Studio does little to help with this (fairly common) paradigm, so until they catch up, we (you and I) are on our own. This chapter should help you understand the limited amount of help Visual Studio can provide, as well as get a handle on what you'll have to do to fill in where Visual Studio exposes gaps in the roadway. I'll also show you how to set up calls that bypass the DataAdapter or TableAdapter Update methodsa technique you'll need when your database gets more sophisticated.