The DataAdapter is an object that's supported by the managed providers for ADO.NET. It acts like a bridge between the DataSet and the data source. DataAdapter's are used to exchange data between a DataSet and a data source, and in most of the applications, this means reading data from the database into a DataSet and writing back from the DataSet to the database.
Database applications can be created with relative ease using the Data Adapter Configuration Wizard. The first step is to start a new project. Drag the sqlDataAdapter from the Data panel of the toolbox on to the form. This will start the wizard. Once the DataAdapter Configuration wizard is invoked, we have to establish a connection just like we did with the Data Form Wizard. Having established an existing connection, or after creating a new connection, the wizard lets us choose the type of query we want it to execute.
The option to use SQL statements will make the wizard generate Insert, Update and Delete statements, depending upon the select statement given by us. It will use the same filter that we have used in the where condition.
This time around, instead of SQL statements, the wizard generated stored procedures because they are precompiled and therefore they run faster. Stord procedures should be your primary choice for maximum performance.
If we wanted to, we could also use an existing stored procedure:
The screen above generates SQL statements. We can either enter our own select statement like we have done above, or the query builder can be used. It lets us select tables and the fields that we require to be displayed.
The advanced options allow us to specify following options:
1. Generate SQL statements for Insert/Update/Delete.
2. Use optimistic concurrency: This will make the wizard to include logic to test whether a record has changed since you read it from the database.
3. Refresh the DataSet: This will make the record read the updated records once again and give a refreshed view of the data.
Finally, the wizard presents us with the view of everything it has done before finishing the task.
Now that we can use the configured data adapter in our project the DataAdapter object that we just created will be added to a separate component tray which doesn't show up on the form.
One thing we have to remember here is that the DataAdapter assumes that it will be reading and writing to the same table ,and if this is not the case we will have to change the table mappings.
The next step is to create the DataSet. Selecting "Generate DataSet" with the red arrow creates it. After selecting Generate DataSet, we are present with the options for its name and such details.
Once the DataSet is created, we are ready to work with the data. The data can be viewed with the Preview Data context menu of the form (This option is also available on the Data menu). The preview data context menu brings up a preview form and clicking the "Load"’ button displays it.
Now that the DataSet is ready, let's see how we can manipulate it. I have selected the same authors' table from the pubs database for my DataSet. You can use whatever table you like for this. Place a DataGrid control on the form and set its DataSource property to the DataSet we have just created. Add a button to the form for loading the data; we only need one line of code to fill our grid with data!
The code for the click event of the button looks like this:
Private Sub btnLoad_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLoad.Click
SqlDataAdapter1.Fill(DataSet11, "Authors")
End Sub |
Run and test the program. When you click the Load button, the output should be similar to that shown above.
To summarize what we've done so far, both the wizards (the Data form wizard and DataAdapter wizard) have definitely made our life as a database programmer a little easier. We have to remember one thing however: ADO.NET by default uses disconnected data and we have to specifically write update code if we want to make changes to the underlying table. Instead of changing the data in the table every time, this feature allows us to make all the changes to the table with just one stroke.
