Visual Basic .NET Data Processing Capabilities

As an Access database developer, you will probably spend an exceptional amount of time working with ADO.NET ”the .NET Framework data component. This section gives special focus to this topic from an introductory perspective. As with other sections of this chapter, attention is drawn to features that have special importance for Access developers. Chapters 7 through 9 will explore ADO.NET in much more depth. However, this section should prepare you for those chapters as well as for the Jump Start example application that closes Chapter 2.

Why Move to ADO.NET?

ADO.NET is, loosely speaking, an extension of the ADO technology introduced slightly before Access 2000. Since ADO is actually a new data model relative to the earlier Remote Data Objects (RDO) and DAO technologies, you may be wondering why we need yet another set of objects for accessing and manipulating data.

There are a couple of legitimate answers to the question, why move to ADO.NET? First, the .NET Framework is built from the ground up to be an integrated cross-language development platform that uses a common run-time engine for all languages supported by .NET. The former data object models (DAO, ADO, and RDO) were designed before the introduction of the .NET Framework. ADO.NET retains selected ADO concepts, but ADO.NET integrates tightly with the .NET Framework in a way that ADO cannot without breaking its backward compatibility. Second, ADO.NET was designed with the Web in mind, particularly for its disconnected data management capabilities. ADO.NET supports only disconnected data manipulation (namely, tasks such as updates, inserts , and deletes). Scalability increases dramatically with disconnected data access and data manipulation and is particularly beneficial for Web applications where the number of users can grow rapidly from an already large base.

If you previously made the move from DAO to ADO, you will have a running start on learning ADO.NET. ADO was a strategic database technology because it offered integrated programming for Access, other databases, and Web applications. ADO.NET maintains those benefits while adding significantly to performance and scalability. By not requiring, or even allowing, a constant connection for datasets, ADO.NET enables a database to serve many more users. In addition, ADO.NET works with data in memory, which can dramatically speed performance.

Another important reason for using ADO.NET is that it is smart about XML. You can readily persist ADO.NET data as XML documents and later read the persisted data to repopulate ADO.NET data structures. In addition, ADO.NET integrates tightly with XML Web services ”a powerful technology for sharing data over a Web in XML format.

ADO.NET Data Providers

In order to begin an ADO.NET application, you must pick an appropriate data provider for the data source with which you want to work. The .NET Framework installs with two .NET data providers ”OLE DB .NET and SQL Server ” and a third is available from the Microsoft downloads site ”ODBC .NET. At the time this chapter was written, the ODBC .NET Data Provider was available from the following URL:

http://msdn.microsoft.com/downloads/default.asp?URL=/downloads/sample.asp?url=/MSDN-FILES/027/001/668/msdncompositedoc.xml

When you are connecting to an Access 2000 or Access 2002 database, you should use the OLE DB .NET Data Provider. As the data provider s name implies, it actually works with the native OLE DB Data Provider. (You may recall using this provider with ADO.) In fact, the OLE DB .NET Data Provider runs the native OLE DB Data Provider through the COM Interop assembly. In addition to Access databases, you can link to Oracle and SQL Server data sources with the OLE DB .NET Data Provider. You should not use the OLE DB .NET Data Provider for any other data sources.

Note  

COM was the development technology for exposing functionality and hosting applications. The .NET Framework will eventually supplant COM. In the interim, it will be necessary to run COM applications, such as the OLE DB Data Provider, with .NET applications. The COM Interop assembly facilitates this objective by mapping COM object members to equivalent members in the .NET Framework.

Since SQL Server is Microsoft s enterprise database, the .NET Framework ships with the SQL Server .NET Data Provider that is especially optimized for use with SQL Server 7 and later. This data provider does not invoke a COM Interop assembly because it communicates directly with SQL Server data sources. If your application uses a version prior to SQL Server 7, you should use the OLE DB .NET Data Provider instead of the SQL Server .NET Data Provider.

The ODBC .NET Data Provider is the third .NET data provider. After the download, the data provider becomes an add-on component to the .NET Framework SDK. Your applications can use this data provider with any ODBC data source. Microsoft Knowledge Base article Q310985 provides insight on the use of this data provider, including sample code demonstrating its use with Visual Basic .NET.

ADO.NET Architecture

There are two main elements to the ADO.NET architecture. First, the .NET data provider implements classes for connecting to and querying a database. Second, the .NET data providers transfer data to and from a database and a local DataSet object. The users of your ADO.NET application will typically interact with the local dataset object. When appropriate, your application can convey inserts, updates, and deletes performed against the local dataset to the data source to which your .NET data provider connects.

The four classes that a .NET data provider implements are the Connection , Command , DataReader , and DataAdapter classes. The names for the classes change slightly from one provider to the next . There are also minor differences in how the classes offered by different providers perform their functionality, but all .NET data providers make available substantially the same four classes.

The name of the Connection class implemented by the OLE DB .NET Data Provider is OleDbConnection . This class resides in the System.Data.OleDb namespace. In fact, all the classes implemented by the OLE DB .NET Data Provider reside in this namespace. To open a connection to an Access database file, you need a connection string designating the OLE DB provider and pointing at the database file. It is good practice to declare and instantiate the connection object with a single statement using the New keyword. The following statement demonstrates the syntax for declaring and instantiating a new Connection object ( cnn1 ) based on the OleDbConnection class; the sample also opens the connection.

 Dim cnn1 As System.Data.OleDb.OleDbConnection = New _ System.Data.OleDb.OleDbConnection _ ("Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=C:\myDB.mdb;") cnn1.Open() 

If you program ADO, you will notice the striking similarity to ADO syntax. In spite of garbage collection, you should close the connection object as soon as you no longer need it. Invoke the object s Close or Dispose method to achieve this result.

After you create a connection to a database, you can execute commands against the database to perform data access, data manipulation, and data definition tasks. The name of the Command class implemented by the OLE DB .NET Data Provider is OleDbCommand . It resides in the same namespace as the OleDbConnection class. Use parameters to permit run-time specification of how a command operates. Command objects can pass data to other objects, such as DataReaders and DataAdapters.

The OLE DB .NET Data Provider implements the DataReader object with the OleDbDataReader class in the System.Data.OleDb namespace. Instances of this class provide read-only, forward-only access to a data source specified by a Connection object and a Command object. When all you need to do is pass through some data, such as instructions to populate a list box, instances of the DataReader class provide high performance. One reason for the high performance of DataReader objects is that they maintain an open connection to a data source. You should close the DataReader and its associated connection as soon as your application no longer needs them. This frees the database to accommodate more requests from other users.

The DataAdapter class acts as a bridge between an Access database or other external data source providing data to an ADO.NET application and a local DataSet object. Users of a form in a Windows application do not interact directly with the connected data source as is common with ADO applications coded with Visual Basic or VBA. Instead, the form in a Windows application points at the local DataSet object. This object is disconnected from the data source until a DataAdapte r object either fills the local dataset or updates the data source based on inserts, updates, and deletes to a local dataset. The name of the DataAdapter class from the OLE DB .NET Data Provider is OleDbDataAdapter; it resides in the System.Data.OleDb namespace.

The DataSet class is in the System.Data namespace. You can think of DataSet objects as in-memory databases that contain one or more tables. When a dataset has more than one table, you can specify relationships between tables. You can also designate foreign key constraints in a table that point at another table to define the column(s) linking two tables. You can populate and pass revisions from the tables in a dataset to the tables in a database via a DataAdapter object based on the DataAdapter class from any of the three .NET data providers.

The DataSet class is an exceptionally rich class that offers advantages for many data processing scenarios. Because a dataset can represent a whole database structure in memory, you can denote a data structure such as the Northwind database with multiple tables and many relationships. Within the DataSet class are collections for the tables and relationships in a dataset instance. Any individual table within a dataset can have a data row collection for representing values in the table and a data column collection for representing structure.

Perhaps the most important aspect of a dataset is that it is disconnected from its source. This feature increases the ability of the database source for a dataset to serve more users. That s because the ADO.NET architecture causes users to connect to a database only for a brief moment to either populate the dataset from the database source or to update the database source based on changes made to the dataset. Since Access databases tend to be bound by the number of concurrent users, this ADO.NET disconnected dataset feature can help to extend the life of Access databases that are currently experiencing performance degradation because of too many concurrent users.