ADO.NET for Beginners

• Download AcesssForm - 291 KB

Introduction

The purpose of this article is to give the beginning developer an understanding of using Visual Basic to create a Windows Forms application that resides on top of a database. Anyone who has ever developed applications using Visual Basic knows that there is a rich set of controls that can be dragged and dropped onto a Windows Form that functions as a user interface. ADO.NET is for working with database information and involves displaying, modifying, and searching for database content using a combination of program code and Windows Forms controls. Microsoft Visual Studio 2005 was specifically designed to create applications that provide access to a wide variety of data sources. Ever since .NET 2.0, Visual Studio 2005 has been able to take advantage of partial classes in order for the IDE to generate code. To avoid the complexities of querying a database directly, we will create an application that can access data from a database rather than having the user have access to the database directly. Often in a work environment, a user might face restrictions in database access, or the worker might just not have that type of particular training to deal with some complex queries.

To develop an application that has logic on its user interface in order to access data from a data store or a database, we will use the Data Source Configuration Wizard to establish a connection to a Microsoft Access database. Because I chose to use an Access database, the data provider will be from the System.Data.OleDb namespace. ADO.NET begins by managing the connection to the database no matter what type of database or other data source you want to connect to. A connection includes the process of locating the database, either using a file system or a network protocol. It begins the connection for the application to speak with the database. ADO.NET manages both the requests by and the responses to the application when the application makes a request to retrieve data from the database.

In ADO.NET, various objects are used to retrieve and modify information in a database. In the following example, we will use the Data Source Configuration Wizard to establish a connection to a Microsoft Access database. As said, ADO.NET manages the connection to this database as it would manage a connection to any other database no matter what type of database or other data source you want to connect to. The single most important part of this first step is to understand that establishing a connection actually specifies information about the database and creates something for other controls and components to bind to. Thus a connection involves the process of locating the database by using either a file system or a network protocol to begin to speak with the Access database. This begins data binding data to user interface controls. Next, the Data Sources Configuration Wizard creates a DataSet object, which is a representation of one or more tables you work with in your program. Note that you do not manipulate the actual data from the permanent Access file, but you manipulate a copy of the data -- you are working with an in-memory copy of the data. When you make changes, such as adding records (rows), deleting records, or modifying them, you are working with an in-memory copy of that data. After the changes have been made, they are copied to the permanent database. In ADO.NET terminology, a DataAdapter is the object that makes the connection to the database to fill the DataSet.

Afterwards, it connects back to the database to update the data there based on the modifications performed while the DataSet held the data. This means that the DataSet has no knowledge of the source of the data, as the managed data provider has the specific information. The role of the managed provider is to connect, fill, and persist the DataSet to and from the database. Below are the managed providers:

.NET Framework Data Providers

• System.Data.OleDb
• System.Data.SqlClient
• System.Data.Odbc
• System.Data.Oracle

These data providers that derive from the System.Data (ADO.NET) namespace provide four basic objects:

• Connections
• Commands
• DataReaders
• DataSets
• DataAdapters

Next, the wizard displays a screen that helps you establish a connection to your Access database by building a statement called a connection string. The connection string contains the information that Visual Studio needs to open and extract information from a database file. Note that as long as the connection is open, your application is able to speak with the database. We choose the Microsoft Access database file from the data sources collection and we choose the .NET Framework Data Provider for OLE DB from the data provider choices. When doing this, you will have to pull the Access file that you have downloaded from the directory that you extracted the project files to. After we test the connection to succeed, we click OK to close the Add Connection dialog box. We save the default connection string and are now prompted to select the subset of database objects we ant to use for this particular project.

Recall a connection is made that specifies information about the database and creates something for other controls and components. This enables binding data onto the Windows Form. Databinding is a feature of your user interface controls that allow them to retrieve information from a database to display that information to the end user. Some controls might only display one field of data, so we will use the DataGrid control, which allows us to display multiple fields of entire rows, as well as multiple rows of data within a grid. Other controls will allow us to edit the data according to an end user's purpose. The Data Sources Configuration Wizard also adds an XML schema file to our project and associates a table adapter and a data navigator with the dataset to handle retrieving data from the database, posting changes, and moving from one record to another. You can then bind information in the dataset to controls on a form by using the Data Sources window or the DataBindings property settings.

We begin by starting a new project and calling the Windows Form AccessData. The new form appears on the IDE. We then go to the Data menu and choose the Add New Data Source command. The Data Source Configuration Wizard appears on the development environment. We then click the database icon.

The items you select in the dialog box are referred to within the project as database objects. Database objects can include tables of fields and records, database views, Stored Procedures, functions, and other items unique to your database. The collective term for all of the database objects you select is a DataSet. The DataSet is called StudentDataSet because that is the name of the Access database file. It can easily be changed to DepartmentDataSet. We click Finish to complete and close the Data Source Configuration Wizard.

Now if we look at Solution Explorer, we see a new file named StudentDataSet.xsd. This file is an XML schema file. ADO.NET uses the XSD file to define the internal structure of the dataset that ADO.NET will use internally to manage a copy of the data that is pulled from our database so that you can work with that data in the computer's memory. In other words, you can add a record to the dataset, you can delete records from the dataset, and modify records in the dataset; once changes have been committed to the in-memory copy of the dataset, the data will be copied into the permanent database. You click the schema file in the Solution Explorer to see a visual representation of the tables, fields, and data adapter commands related to your new dataset in a visual called the DataSet Designer. There is a one to one correlation between the items in the permanent database and in the xsd file's visual representation. You want to drag and drop a control onto the Windows Forms. The open data sources container will show a drop down box that provides a data grid control view. You select the datagrid choice from that drop down box, and then drag the entire database onto the form.

Press the F7 key to build the solution, and then click the Start Without Debugging choice. Note that when you drag and drop the database onto the form, you create an instance of an object called StudentDataSet. Below the form in the designer tray, you should see the StudentDataSet, DeparmentBindingSource, DepartmentTableAdapter, and DepartmentBindingNavigator. These controls correspond to the data controls as seen in the toolbox. When we run the application, you have a fully operational application with fields that can be edited.

At this point, it is worth noting the Visual Studio 2005 databinding features. You know by now that StudentDataSet is an in-memory storage area for the data that we have pulled from the database. Should you decide to make changes to the in-memory storage area called StudentDataSet, you can then propagate those changes back to the database. Recall that the DataAdapter is the object that connects to the database to fill the DataSet. This is how the data gets from the database to the DataSet. As soon as the form loads, it requests that the data be loaded. This brings us to the DepartmentsTableAdpater. A table adapter knows how to retrieve data from a table, and then it knows how to put information back into a table, or it might know how to delete data from a table. But it knows how to communicate with the underlying database in order to make the changes identical to the changes that were made with the student dataset in memory. The binding source object functions to sync up the data that is in the DataSet with the data that is currently displayed on the form. This object coordinates the data that is in the DataSet with the data that is in the fields. Finally, the binding navigator is a reference to the toolbar to help us navigate through the records, or to create a new record. This object therefore communicates with the binding source object.