ToolKit
Languages: All .NET Languages
ASP.NET Versions: 1.0 | 1.1
The Data Adapter Configuration Wizard
This VS.NET Tool Provides Power and Convenience
By Ken McNamee
Most wizards are designed to give
you a decent head start in your development efforts. Rarely, a wizard comes
along that can write most, if not all, of the necessary code for you. Visual
Studio.NET's Data Adapter Configuration Wizard is one of those rare,
well-designed breeds that - in most instances - can almost completely configure
your Web application for database operations.
Oddly, it's also one of the most
difficult-to-find capabilities in VS.NET. You almost have to stumble upon it,
but once you do, you'll probably find yourself using it often. This wizard will
automatically create all the SQL statements and ADO.NET code for selecting,
updating, inserting, and deleting from a database. In addition, it can create
stored procedures instead of using SQL statements - or even make use of
existing stored procedures if that's your preference. It even has an option for
managing optimistic record concurrency.
Drag and Drop Data
The Data Adapter Configuration
Wizard is as simple to use as it is powerful. There are a couple of ways to get
it started, and they both involve adding a DataAdapter object to the
design surface of an ASP.NET Web form. You can choose to drag the SqlDataAdapter
item from the Data toolbox onto the Web form and the wizard will display
immediately. If you choose this option, you'll then have to make additional
choices in the wizard, such as how to connect to the database and on what
tables you want to operate.
Another option - and the one I
prefer, as displayed in Figure 1 - is to drag a table from the Server Explorer
onto the Web form's design surface. Unlike the previous method, the wizard
isn't displayed immediately. Assuming that you're using SQL Server, you'll
notice that dragging the table onto the form has caused VS.NET to add a SqlConnection
and a SqlDataAdapter to the Web form. In addition, if you look at the
code-behind file you'll see that there are many lines of new ADO.NET code added
to configure the SqlDataAdapter for completely managing data operations
on the table that you dragged and dropped. Not bad for one simple drag and
drop, huh? And we haven't even gotten to the wizard yet.
Figure 1: Getting started with the
Data Adapter Configuration Wizard is as easy as dragging a table from the
Server Explorer, dropping it on a Web form, and selecting Configure Data
Adapter from the context menu.
Configure the Data Adapter
As you can see in Figure 1, there
are some intriguing choices if you right-click on the SqlDataAdapter.
The one we are concerned with now is Configure Data Adapter because
this will actually kick off the wizard. Clicking past the wizard's intro screen
you are presented with database connection options. This should be filled in if
you had previously set up a data connection in the Server Explorer. If not, you
can choose to configure a new connection here. This will be used to initialize
the SqlConnection object that is sitting on your Web form alongside the SqlDataAdapter.
The next wizard screen - as
displayed in Figure 2 - requires you to choose from three options for querying
the database: Use SQL statements, Create
new stored procedures, or Use existing stored procedures.
Using stored procedures is almost always the best practice in terms of
performance, security, and maintenance. You should choose to use stored
procedures even if you are new to them because the wizard will completely
handle creating them for you. If you are no stranger to stored procedures you
might still want to have the wizard create them for you. It will create most of
the tedious boilerplate code that you can later tweak to your heart's content.
Choose the third option if you have existing stored procedures in the database
that you'd like to use. There is an interface for matching the parameters with
the appropriate columns.
Figure 2: The wizard lets you choose
from using plain SQL statements or the best practice recommendation: new or
existing stored procedures.
If you choose to create new stored
procedures, the last screen allows you to provide names for the procedures. In
addition, you can tell the wizard to only generate the database script for
creating the procedures and not to actually create them. This is handy if you
don't currently have a connection or access permissions to the database. You
can take the stored procedure creation script and execute it yourself when you
do have a connection, or give it to a database administrator to execute. It's
the little convenience details like this one that make this wizard a real
winner.
The only problem with this wizard
is that it's so well hidden. It would be far more convenient if there were a
menu option under Tools that could kick off the process.
In fact, very few developers that I've come in contact with - whether they be
junior, intermediate, or senior - even know of its existence, let alone its
worth. So take this wizard for a spin and decide what worth it has for you. From
my point of view, there's a large amount to gain in productivity - and almost
nothing to lose.
Ken McNamee is a
senior software developer with Vertigo Software, Inc., a leading provider of
software development and consulting services on the Microsoft platform. Prior
to this, he led a team of developers in re-architecting the Home Shopping
Network's e-commerce site, http://www.HSN.com,
to 100% ASP.NET with C#. Readers can contact him at mailto:editors@devproconnections.com.