Using Objects is Still a Viable Alternative

Some developers I know don't use any automation whatsoever. They type code for all of the objects they use, set the properties in code, and generally make things as difficult as possible.

Unfortunately, Microsoft removed some of the automation from Visual Studio 2005. You can no longer drag and drop objects from Server Explorer onto a form and rely on Visual Studio to create the required objects for you. If you look at the Data folder of the Toolbox, you'll notice that the required controls are completely gone. Therefore, the first task is to get the required controls back into the Toolbox.

To add the required controls to Visual Studio, open the Data folder of the Toolbox. Right click within the folder, and select Choose Items from the context menu. You'll see the Choose Toolbox Items dialog box shown in Figure 5.

Figure 5: Add the controls you need to work with database objects to your Toolbox.

You need three objects when taking the object approach to database management: a connector, an adapter, and a DataSet. The Toolbox already contains the DataSet control, so you don't need to add it. Figure 5 shows the other two controls for working with SQL Server, the SqlConnection and SqlDataAdapter controls.

After you add the controls, drag and drop a SqlConnection control onto the form. You'll need to add connection information to the ConnectionString property. The easiest way to add this entry is to create a data connection using Server Explorer, just as I described earlier in using the Visual Studio 2005 approach. Once you create the connection, you can simply select it in the ConnectionString property. Nothing could be easier.

Now that you have a connection in place, drag and drop a SqlDataAdapter control onto the form. You'll immediately see the Data Adapter Configuration Wizard. Follow the prompts to access tables, views, or sprocs. You can't access functions using this technique. I suggest strongly that you rely on sprocs, whenever possible, for security reasons. A sproc limits user access to your data, so even if your application (especially a Web application) is compromised, you can limit the damage. No matter which approach you choose, follow the prompts to setup the data adapter.

At this point, you can right click the SqlDataAdapter and choose Generate DataSet from the context menu. Visual Studio automatically generates the DataSet you need.

Now, in Visual Studio 2003, you'd drag and drop display controls onto the form and begin selecting properties for displaying the data. However, something different happens in Visual Studio 2005. You get the same features shown in Figure 3. That's right — you get complete display flexibility, despite using an object approach to working with the data. Consequently, you can configure the DataSet such as you would otherwise, and then drag and drop objects from the Data Sources window to your form.

Choosing a Dataset Flavor

I really like the new Dataset modeling techniques in Visual Studio 2005 when I need to work with the data visually. The automation the Microsoft provides greatly reduces my workload and makes working with data quite fast. In addition, I find it quite easy to create complex relationships, and I know at the outset that those relationships are precisely what I want because I can see the connections. Think of the Visual Studio 2005 method as the best solution when the Microsoft automation will work for your needs.

The only problem is that the Microsoft solution doesn't work in every situation. Sometimes I need to write special business logic, and I can't do it using SQL Server constructions such as sprocs. Sometimes the Microsoft solution is so convoluted that the automation actually costs me time; I fight with the IDE, rather than work with it. This is the time to choose the older Visual Studio 2003 approach to working with databases. The older DataSet approach has a warehouse full of debugged and reliable code behind it. You also gain considerable programming flexibility. Working with the DataSet is easy from a programming perspective.

I see people updating their code all the time to follow the latest Microsoft trends when such an update doesn't make sense. If your current application uses the older DataSet approach, keep using it unless you have a very good reason to change. After all, you already have all of the debugged code you need to make the application run.