The easiest way to create a DataAdapter is to simply drag one from the toolbox and drop it onto a form or other container. As a learning exercise, let's do this, and dissect the results.
Creating the Demo Project
First, create a Windows Forms project. Name the project ADOBook06-01. If you don't want to follow along with the steps, place a data grid on the form, and set the Dock property to Fill. Next , place a Panel on the form and dock it to the bottom. The grid should fill the top of the form outside the panel. If it does not, right-click on the grid and select Bring to Front. This should fix the problem. Place three buttons on the panel and set the Text property as in Figure 6.1. Finally, place a label and a text box on the form. This will be used for a partial string to use in queries. Your form should look like Figure 6.1. We'll use this form for our examples in this chapter.
Figure 6.1. Roughed-out Data Adapter Demo form.
When you drag and drop a DataAdapter for either client (SQL or OLEDB), the DataAdapter Configuration wizard opens automatically. First, you must choose a connection. You may either select a predefined connection you have created in the Server Explorer, or create one. See Figure 6.2.
Figure 6.2. The Choose Data Connection page.
Whichever connection you choose here will become the connection the four Command objects will use. Once you've chosen your connection click Next. On the next page, you will choose how the DataAdapter will get its data and how it will update the database. The choices are SQL statements or stored procedures, as in Figure 6.3.
Figure 6.3. Choose Query Type page.
We will go through each of these choices. The three choices are:
SQL statements With this choice, the DataAdapter uses SQL statements to do all its work. You will enter a Select statement, then the DataAdapter will generate the Insert, Update, and Delete statements for you.
Create stored procedures You enter an SQL Select statement and the DataAdapter will generate four new stored procedures that will be used to access the database.
Use existing stored procedures You will have to tell the DataAdapter which existing procedures to use. This is useful in certain security-sensitive industries (such as banking) which do not allow direct access to tables.
Select the SQL statements option and click Next. The next page that opens allows you to key in an SQL Select statement or you can use the Query Builder to create the statement graphically ( illustrated in Figure 6.3). Figure 6.4 shows the SQL statements page.
Figure 6.4. The SQL statements page.
To use the Query Builder, click the button. Any SQL that is already entered will be parsed and the Query Builder will be filled in for you. If there is no statement, an empty Query Builder screen will open . The Query Builder with the SQL statement shown in Figure 6.4 loaded looks as in Figure 6.5.
Figure 6.5. The Query Builder with the Select statement loaded.