What Is a DataAdapter Object?

Creating DataAdapter Objects in Visual Studio .NET

Now that you've learned about the key features of the DataAdapter, let's see how you can use Visual Studio .NET to create DataAdapter objects in your applications more quickly.

Dragging and Dropping a DataAdapter from the Toolbox

Both the OleDbDataAdapter and SqlDataAdapter are available on the Data tab of the Visual Studio .NET Toolbox. Dragging either DataAdapter from the Toolbox onto a designer, as shown in Figure 5-3, will create a new DataAdapter in the designer's Components Tray. This process also launches the Data Adapter Configuration Wizard.

Figure 5-3.

Dragging and dropping a DataAdapter onto a designer

Using the Data Adapter Configuration Wizard

The Visual Studio .NET Data Adapter Configuration Wizard allows you to configure your DataAdapter objects without having to write any code.

Once you've launched the wizard and acknowledged the standard welcome page, you'll see a page where you can specify a connection to your database. This page, shown in Figure 5-4, is similar to the Choose Connection page of the Data Form Wizard. It lists the connections available and lets you create new connections. If you've selected an OleDbDataAdapter, the wizard will list all of the data connections available in Server Explorer. If you've selected the SqlDataAdapter, only the connections that you've added through the SQL Server OLE DB provider will be available. Select the connection you want to use to communicate with your database and then click Next to move to the next page of the wizard.

Figure 5-4.

Selecting a connection for your DataAdapter

Next, the wizard will ask you to specify a query type for your DataAdapter. You can either enter a SQL statement (such as SELECT CustomerID, CompanyName FROM Customers) or rely on stored procedures. The Data Adapter Configuration Wizard can even help you build stored procedures based on a query that you specify. For now, we'll focus on the simplest case—using a SQL statement. We'll look at using stored procedures in a DataAdapter in Chapter 10, when we discuss updating your database. Select Use SQL Statements, as shown in Figure 5-5, and then click Next.

Figure 5-5.

Specifying a query type for your DataAdapter

The next wizard page will display a text box for your query, as shown in Figure 5-6. The page also lets you set some advanced options related to updating your database. We'll take a closer look at those options in Chapter 10.

Figure 5-6.

Specifying a query string for your DataAdapter

This wizard page also has a button labeled Query Builder. Click this button to launch Query Builder, which provides a simple user interface for building queries, similar to the one used by Microsoft Access. Query Builder can be helpful if you're not completely sure of the query you want to use for your DataAdapter. When you launch Query Builder, you will see a dialog box (shown in Figure 5-7) that lists the tables, views, and functions available in your database.

Query Builder makes it simple to select the table(s) and field(s) to return, define the search criteria, and specify the sort order for your query. You can even execute your query and examine the results from within Query Builder by right-clicking in one of the panes and choosing Run from the shortcut menu. Figure 5-8 shows many of Query Builder's features. You can click on columns in the top pane to add them to the results of your query. You can specify sort orders or criteria in the second pane. If you're more comfortable typing in the SQL for your query, you can do so in the third pane. Changes in any one of the top three panes affect the other two.

Figure 5-7.

Query Builder's Add Table dialog box

Figure 5-8.

Using Query Builder to construct your query

After you've constructed your query, click OK and Query Builder will return the query string that you built to the Data Adapter Configuration Wizard. When you click Next, the wizard will take your query and connection information and generate and configure your new DataAdapter. The wizard will display the results of this process on its next page, as shown in Figure 5-9.

Figure 5-9.

The Data Adapter Configuration Wizard's results

In order to configure your DataAdapter, the wizard must query your database for metadata such as table names, column names, and primary key information. If the wizard is unable to generate updating logic because your query references multiple tables or does not have a primary key or your database does not return this metadata, it will indicate on this page which attributes of your DataAdapter it was unable to set.

After you've built your DataAdapter using the wizard, you can select it in the designer's Components Tray and examine its settings in the Properties window. Figure 5-10 shows how you can drill down into the properties of your new DataAdapter. The query you entered in the wizard appears in the CommandText property of the DataAdapter object's SelectCommand.

The Data Adapter Configuration Wizard is reentrant. You can select a DataAdapter in your designer's Components Tray and click the Configure Data Adapter link in the lower half of the Properties window to reconfigure your DataAdapter by running through the wizard again.

Figure 5-10.

Examining properties of your new DataAdapter

Dragging and Dropping from Server Explorer

You can also create DataAdapter objects by dragging and dropping a table or view from Server Explorer onto your designer. This might sound like a good idea at design time, when your database might contain only a few sample rows per table, but you have to consider how much data your DataAdapter will fetch when you deploy your application, especially if the tables in your database will continually grow in size. But if you get a note from your mother that says you promise not to fetch thousands of rows from your database through your DataAdapter, I guess it's all right.

While you can't set a filter on the rows you retrieve by dragging and dropping from Server Explorer, you can indicate which columns you want to include in your DataAdapter object's query by selecting specific columns from your table or view in Server Explorer. Hold down the Ctrl key while clicking on the desired columns to select them. The result is shown in Figure 5-11.

Figure 5-11.

Selecting specific columns from your table for your DataAdapter

Previewing the Results of Your DataAdapter

I usually enter my query by hand rather than launch Query Builder. Years of programming have helped me learn a lot of things about myself. Among them are: I'm stubborn, which explains why I occasionally scoff at wizards as well as why I continue to believe that this will be the Red Sox's year; also, I'm not the world's best typist. So, when I enter queries by hand, they don't always do what I expect them to do. When that happens, I run the query in an ad-hoc query tool and examine the results to try to figure out where I went wrong. Query Builder is helpful, but there's an easier way to examine the data returned by your DataAdapter at design time.

When you select a DataAdapter in a designer's Components Tray, you'll see a link in the lower half of the Properties window labeled Preview Data. Click this link to display the Data Adapter Preview dialog box, shown in Figure 5-12. To view the data your DataAdapter returns, click the Fill Dataset button.

Figure 5-12.

Previewing data returned by a DataAdapter

Examining the Code that the Wizard Generated

I strongly recommend looking at the code that the wizard generated. The code might not always be easy to read, and it might not be the most efficient or highly recommended approach, but it's a great way to learn how the different objects in the ADO.NET object model interact. As you read about the DataAdapter, Command, and Parameter objects in this book, you can refer back to the code that the Data Adapter Configuration Wizard generated to reinforce the lessons in the text.

You can find the wizard-generated code in your component's InitializeComponent procedure, which resides in the hidden designer-generated code region.



Microsoft ADO. NET Core Reference
Microsoft ADO.NET (Core Reference) (PRO-Developer)
ISBN: 0735614237
EAN: 2147483647
Year: 2002
Pages: 104
Authors: David Sceppa

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net