Fundamentally, almost all business applications need some type of access to data stored in a database. They need access to this data quickly, and in a number of different formats. This is where ADO.NET comes into the picture. In this series of two articles, Vaijayantee explains exactly what ADO.NET is, including discussions on scalability, architecture, etc. She also shows us how to use ADO.NET with VB.NET and Visual Studio.NET.
All You Need To Know About ADO.NET: Part 1/2 - Data binding in Windows application (Page 2 of 5 )
There are several ways to use ADO.NET in a Windows application. One of the easiest is by using a data form. Let's now see how to use a data form to access data from Authors table.
Using the Data Form Wizard The data form wizard is a handy tool. It helps you create data bound controls on windows form or web pages. The wizard itself writes code for loading the controls with data or updating the data.
Running the Data Form Wizard is like adding an item to a project, but here, we have to keep in mind that a Data Form can be added to a project that supports forms. The task of creating Data Forms can be accomplished with following few steps:
1. Start a new project. Select Add New Item from File menu.
2. Select Data Form from the dialog box.
3. The wizard opens up automatically with a welcome screen. Just press next and it starts working.
4. On the next page of the wizard, you have to select an existing DataSet or create a new one. Here, we'll create a new one and use the Pubs database for that. The next few steps will explain how to do that. Enter "DSet1" for your dataset's name and select "New Connection" from the next screen.
5. This opens up the Data Link dialog box. You can connect to the server and set a link to the required database here.
6. Once the link is established, the Data Form wizard takes over again showing tables from the database. Select the required table (authors ) and add it to the "Selected Items" by clicking on the ">" button:
7. From the "choose tables and columns to display on the form" dialog box, select the fields that you need. The Data Form Wizard is about to complete its job. It will now show the final screen asking us if we want all the rows to be displayed at a time in the DataGrid control, or a single row at a time, bound to controls. Let's select all the rows in a grid control for this example.
The wizard now generates the DataForm and adds it to the project. The form contains a button for loading the data in the grid, and also buttons for canceling all and updating. Code for the click events of these buttons is written by the wizard making our lives easier.
To run the project you either have to write some appropriate code or make the DataForm a StartUp object. You can see the DataForm in action once you click the "Load" button.
Similarly, if you had selected individual records, the wizard will ask if you need controls for editing and navigation. If you select these controls, then it will generate the form using controls for every field and create the navigation and other buttons for you.
Navigation does not update the data An important thing to note here is unlike the previous versions of data access objects, ADO.NET uses disconnected data. Older versions used to update the data once we made changes and navigated through the data, but now the navigation doesn't update the data to the underlying SQL table unless we click the update button manually.
Using the DataAdapter Configuration Wizard 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:
Generate SQL statements for Insert/Update/Delete.
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.
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
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.