Overview of ADO.NET in Font

Creator Code39 in Font Overview of ADO.NET

ADO.NET is a programming model for processing data within .NET languages, such as Visual Basic 2005. While there are subtle differences between VWDE and VBE, you can take advantage of the ADO.NET programming model from Windows applications and web applications. The ADO.NET model is suitable for use with stand-alone data generated from within VBE or VWDE, file-based data, and data in databases such as SQL Server Express, Microsoft Access, and other OLE DB or ODBC-compatible data sources. This chapter drills down on the use of ADO.NET with SQL Server Express databases, and to a much lesser extent, OLE DB databases as represented by Microsoft Access. Both SQL Server Express and Microsoft Access permit you to work with stand-alone database files, but SQL Server Express also permits you to process data that is continuously managed by a database server.

ADO.NET offers a provider-based model for interacting with databases. While the different providers offer highly comparable programming interfaces, the whole idea behind provider-specific classes is that you can take advantage of the special features of each type of database with a provider tailored for it. There are four providers supplied along with VBE and VWDE. Each provider targets a different type of resource. The four provider classes are summarized in the following bullet points. .NET Provider for SQL Server works with SQL Server 7.0 + databases, including SQL Server Express. The objects for this provider reside in the System.Data.SqlClient namespace. .NET Provider for OLE DB works with any data source that you can connect with via OLE DB, such as Microsoft Access, SQL Server versions prior to SQL Server 7.0, and Oracle versions prior to 8.1.7. The objects for this provider reside in the System.Data.OleDb namespace.

.NET Provider for ODBC works with any data source that you can connect with via ODBC (which is just about every database), and selected file types, including .csv, .xls, and even .txt. The objects for this provider reside in the System.Data.Odbc namespace. .NET Provider for Oracle is optimized to work with Oracle databases running Oracle client software 8.1.7 or later. The objects for this provider reside in the System.Data.OracleClient namespace, which resides in the System.Data.OracleClient.dll assembly. Solutions using .NET Provider for Oracle must add references to the System.Data.dll and System.Data. OracleClient.dll assemblies. The providers deliver a set of ADO.NET objects that Visual Basic 2005 developers can program. The ADO.NET object model can connect VBE and VWDE solutions with databases. On one end, you can program with these objects in VBE and VWDE with Visual Basic 2005 (and other .NET languages). On the other end, the ADO.NET objects communicate with a type of database server. The topic of ADO.NET database providers has the potential to be very rich for those who require something other than the standard database providers. Two major themes characterize this richness. One ADO.NET 2.0 innovation is the introduction of provider-independent data access. At some escalation in the coding difficulty and abstraction level, provider-independent coding allows you to use one model to access different types of data sources. If you regularly work with different types of data sources, and optimized performance for each data source is not an issue, this approach may have some appeal. Second, multiple third parties offer highly optimized providers for specific data sources that are not provided with either VBE or VWDE. Some of the specialized providers are for databases, such as MySQL, Informix, Sybase, DB2, and Oracle.

There are a couple of significant ways to segment the ADO.NET classes. Some classes are tied to providers, which makes them specialized for one type of data source or another. For example, you cannot connect to a SQL Server data source with an instance of the OleDbConnection class. Another type of ADO.NET class is independent of the provider type. DataSet and DataTable classes are not tied to a specific data source. A single DataSet instance can contain multiple DataTable objects. Each of the DataTable objects can contain data from a different type of data source. Furthermore, it is not even necessary to populate a DataTable object from a remote data source because you can populate a DataTable with local data. These classes will be discussed further momentarily. Another way to divide ADO.NET classes is to see whether they permit forward-only access or disconnected access. Forward-only access is very rapid. When you use forward-only access to connect to a data source, you cannot modify the data source values. Disconnected access is not as fast as forward-only access, but it provides a richer set of features. One significant reason that disconnected access is slower is that it stores the data from a data source locally in one or more DataTable objects that can reside in a DataSet object. There are four key provider-specific ADO.NET classes. These classes are duplicated in each of the four standard data providers. The four classes complement one another in the type of functionality that they provide. The names that follow refer to the classes by their generic name, such as Connection or DataReader. However, remember that the names of the classes within a provider are specific to a class. Therefore, the name OleDbConnection references a Connection object from the System.Data.OleDb namespace, but the SqlConnection name applies to the name of a Connection object in the System.Data.SqlClient namespace. Connection class instances point at a data source from a client application. You will typically use a Connection object along with one or more other ADO.NET objects.