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 [email protected].