The Data Adapter Configuration Wizard

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 giveyou a decent head start in your development efforts. Rarely, a wizard comesalong that can write most, if not all, of the necessary code for you. VisualStudio.NET's Data Adapter Configuration Wizard is one of those rare,well-designed breeds that - in most instances - can almost completely configureyour Web application for database operations.

Oddly, it's also one of the mostdifficult-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 willautomatically create all the SQL statements and ADO.NET code for selecting,updating, inserting, and deleting from a database. In addition, it can createstored procedures instead of using SQL statements - or even make use ofexisting stored procedures if that's your preference. It even has an option formanaging optimistic record concurrency.

Drag and Drop Data

The Data Adapter ConfigurationWizard is as simple to use as it is powerful. There are a couple of ways to getit started, and they both involve adding a DataAdapter object to thedesign surface of an ASP.NET Web form. You can choose to drag the SqlDataAdapteritem from the Data toolbox onto the Web form and the wizard will displayimmediately. If you choose this option, you'll then have to make additionalchoices in the wizard, such as how to connect to the database and on whattables you want to operate.

Another option - and the one Iprefer, as displayed in Figure 1 - is to drag a table from the Server Exploreronto the Web form's design surface. Unlike the previous method, the wizardisn't displayed immediately. Assuming that you're using SQL Server, you'llnotice that dragging the table onto the form has caused VS.NET to add a SqlConnectionand a SqlDataAdapter to the Web form. In addition, if you look at thecode-behind file you'll see that there are many lines of new ADO.NET code addedto configure the SqlDataAdapter for completely managing data operationson the table that you dragged and dropped. Not bad for one simple drag anddrop, huh? And we haven't even gotten to the wizard yet.

Figure 1: Getting started with theData Adapter Configuration Wizard is as easy as dragging a table from theServer Explorer, dropping it on a Web form, and selecting Configure DataAdapter from the context menu.

Configure the Data Adapter

As you can see in Figure 1, thereare some intriguing choices if you right-click on the SqlDataAdapter.The one we are concerned with now is Configure Data Adapter becausethis will actually kick off the wizard. Clicking past the wizard's intro screenyou are presented with database connection options. This should be filled in ifyou had previously set up a data connection in the Server Explorer. If not, youcan choose to configure a new connection here. This will be used to initializethe SqlConnection object that is sitting on your Web form alongside the SqlDataAdapter.

The next wizard screen - asdisplayed in Figure 2 - requires you to choose from three options for queryingthe database: Use SQL statements, Createnew stored procedures, or Use existing stored procedures.Using stored procedures is almost always the best practice in terms ofperformance, security, and maintenance. You should choose to use storedprocedures even if you are new to them because the wizard will completelyhandle creating them for you. If you are no stranger to stored procedures youmight still want to have the wizard create them for you. It will create most ofthe 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 databasethat you'd like to use. There is an interface for matching the parameters withthe appropriate columns.

Figure 2: The wizard lets you choosefrom using plain SQL statements or the best practice recommendation: new orexisting stored procedures.

If you choose to create new storedprocedures, the last screen allows you to provide names for the procedures. Inaddition, you can tell the wizard to only generate the database script forcreating the procedures and not to actually create them. This is handy if youdon't currently have a connection or access permissions to the database. Youcan take the stored procedure creation script and execute it yourself when youdo have a connection, or give it to a database administrator to execute. It'sthe little convenience details like this one that make this wizard a realwinner.

The only problem with this wizardis that it's so well hidden. It would be far more convenient if there were amenu option under Tools that could kick off the process.In fact, very few developers that I've come in contact with - whether they bejunior, intermediate, or senior - even know of its existence, let alone itsworth. So take this wizard for a spin and decide what worth it has for you. Frommy point of view, there's a large amount to gain in productivity - and almostnothing to lose.

Ken McNamee is asenior software developer with Vertigo Software, Inc., a leading provider ofsoftware development and consulting services on the Microsoft platform. Priorto this, he led a team of developers in re-architecting the Home ShoppingNetwork's e-commerce site, http://www.HSN.com,to 100% ASP.NET with C#. Readers can contact him at [email protected].