< XSLT - Transforming XML 2 | Main | C++ .NET and Using ADO.NET 2 >


 

 

C++ .NET And Using ADO.NET 1

 

 

What we have in this page?

  1. What is in this module?

  2. What Is ADO.NET?

  3. ADO.NET Data Providers

  4. .NET Framework Data Providers

  5. Core Objects of .NET Framework Data Providers

  6. The .NET Framework Data Provider for SQL Server

  7. The .NET Framework Data Provider for OLE DB

  8. The .NET Framework Data Provider for ODBC

  9. The .NET Framework Data Provider for Oracle

-----------------------Next---------------------------------------------

  1. Choosing a .NET Framework Data Provider

  2. ADO.NET Namespaces

  3. ADO.NET Assemblies

  4. Creating a Connected Application

  5. Connecting to a Database

  6. Creating and Executing a Command

  7. Executing a Command That Modifies Data

  8. Executing Queries and Processing the Results

  9. Creating a Disconnected Application

  10. DataSet Class

  11. DataSet Members

  12. Creating the Form

  13. A Very Quick Reference

 

What is in this module?

 

  • Connect to a Microsoft Access database.

  • Execute SQL statements to query the database.

  • Execute SQL statements to update the database.

  • Create disconnected applications, which use a DataSet to cache tables in memory.

  • Create a report displaying data from the database.

 

ADO.NET is the data access API from Microsoft for the Microsoft Windows .NET Framework 1.1. ADO.NET has been optimized to work with .NET to enable distributed applications and services to exchange data easily and reliably. ADO.NET offers two distinct programming models, depending on the type of application you need to build:

 

  • If your application can obtain and maintain a direct connection to a data source, you can use classes such as OleDbConnection, OleDbCommand, and OleDbDataReader to exchange data with the data source. These classes are superficially similar to previous versions of ADO, but you’ll find many differences when you look into the details.

  • If your application is disconnected from a data source, you can use a DataSet to represent an in-memory cache of data from the data source. DataSet is the most important new class in ADO.NET because it enables you to freely pass data between applications and services residing on different tiers in your architecture.

 

In this module, you will learn how to use ADO.NET to connect to a data source, execute queries, and perform database update operations. You will also learn how to use a DataSet in a disconnected application. You will see how to fill a DataSet with data from a database and display the data in a DataGrid. ADO.NET provides access to any kind of relational database. The examples in this module try to describe on how to use Microsoft Access.

 

What Is ADO.NET?

 

ADO.NET is a strategic API from Microsoft for data access in the modern era of distributed, Internet-based applications. ADO.NET contains a set of interfaces and classes that enable you to work with data from a wide range of databases, including Microsoft SQL Server, Oracle, Sybase, Access, and so on.

 

ADO.NET Data Providers

 

ADO.NET uses the concept of a data provider to provide efficient access to different types of databases. Each data provider includes classes to connect to a particular type of database. The .NET Framework includes several data providers, as discussed in the following section.

 

.NET Framework Data Providers

 

A .NET Framework data provider is used for connecting to a database, executing commands, and retrieving results. Those results are either processed directly, placed in an ADO.NET DataSet in order to be exposed to the user in an ad hoc manner, combined with data from multiple sources, or remoted between tiers. .NET Framework data providers are lightweight, creating a minimal layer between the data source and your code, increasing performance without sacrificing functionality. The following table lists the .NET Framework data providers that are included in the .NET Framework.

 

.NET Framework data provider

Description

.NET Framework Data Provider for SQL Server

Provides data access for Microsoft SQL Server version 7.0 or later. Uses the System.Data.SqlClient (System::Data::SqlClient) namespace.

.NET Framework Data Provider for OLE DB

For data sources exposed using OLE DB. Uses the System.Data.OleDb (System::Data::OleDb) namespace.

.NET Framework Data Provider for ODBC

For data sources exposed using ODBC. Uses the System.Data.Odbc (System::Data::Odbc) namespace.

.NET Framework Data Provider for Oracle

For Oracle data sources. The .NET Framework Data Provider for Oracle supports Oracle client software version 8.1.7 and later, and uses the System.Data.OracleClient (System::Data::OracleClient) namespace.

 

Table 1

 

Note: C++ .NET namespace is in the bracket.

 

The .NET Framework Data Provider for ODBC and the .NET Framework Data Provider for Oracle were not originally included in the .NET Framework version 1.0. If you require the .NET Framework Data Provider for ODBC or the .NET Framework Data Provider for Oracle, and are using .NET Framework 1.0, you can download these data providers from the Data Access and Storage Developer Center. The namespace for the downloaded .NET Framework Data Provider for ODBC is Microsoft.Data.Odbc. The namespace for the downloaded .NET Framework Data Provider for Oracle is System.Data.OracleClient.

 

 

 

 

Core Objects of .NET Framework Data Providers

 

The following table outlines the four core objects that make up a .NET Framework data provider.

 

Object

Description

Connection

Establishes a connection to a specific data source. The base class for all Connection objects is the DbConnection class.

Command

Executes a command against a data source. Exposes Parameters and can execute within the scope of a Transaction from a Connection. The base class for all Command objects is the DbCommand class.

DataReader

Reads a forward-only, read-only stream of data from a data source. The base class for all DataReader objects is the DbDataReader class.

DataAdapter

Populates a DataSet and resolves updates with the data source. The base class for all DataAdapter objects is the DbDataAdapter class.

 

Table 2

 

In addition to the core classes listed in the table above, a .NET Framework data provider also contains the classes listed in the following table.

 

Object

Description

Transaction

Enables you to enlist commands in transactions at the data source. The base class for all Transaction objects is the DbTransaction class.

CommandBuilder

A helper object that will automatically generate command properties of a DataAdapter or will derive parameter information from a stored procedure and populate the Parameters collection of a Command object. The base class for all CommandBuilder objects is the DbCommandBuilder class.

ConnectionStringBuilder

A helper object that provides a simple way to create and manage the contents of connection strings used by the Connection objects. The base class for all ConnectionStringBuilder objects is the DbConnectionStringBuilder class.

Parameter

Defines input, output, and return value parameters for commands and stored procedures. The base class for all Parameter objects is the DbParameter class.

Exception

Returned when an error is encountered at the data source. For an error encountered at the client, .NET Framework data providers throw a .NET Framework exception. The base class for all Exception objects is the DbException class.

Error

Exposes the information from a warning or error returned by a data source.

ClientPermission

Provided for .NET Framework data provider code access security attributes. The base class for all ClientPermission objects is the DBDataPermission class.

 

Table 3

 

The .NET Framework Data Provider for SQL Server

 

The .NET Framework Data Provider for SQL Server uses its own protocol to communicate with SQL Server. It is lightweight and performs well because it is optimized to access a SQL Server directly without adding an OLE DB or Open Database Connectivity (ODBC) layer. The following illustration contrasts the .NET Framework Data Provider for SQL Server with the .NET Framework Data Provider for OLE DB.

 

 

 

 

----------------------------------------------------------------------

 

The .NET Framework Data Provider for OLE DB communicates to an OLE DB data source through both the OLE DB Service component, which provides connection pooling, and transaction services, and the OLE DB Provider for the data source. The .NET Framework Data Provider for ODBC has a similar architecture to the .NET Framework Data Provider for OLE DB; for example, it calls into an ODBC Service Component.

To use the .NET Framework Data Provider for SQL Server, you must have access to SQL Server 7.0 or later. The .NET Framework Data Provider for SQL Server classes are located in the System.Data.SqlClient namespace. For earlier versions of SQL Server, use the .NET Framework Data Provider for OLE DB with the SQL Server OLE DB Provider (SQLOLEDB).

The .NET Framework Data Provider for SQL Server supports both local and distributed transactions. For distributed transactions, the .NET Framework Data Provider for SQL Server, by default, automatically enlists in a transaction and obtains transaction details from Windows Component Services or System.Transactions. The following code example shows how to include the System.Data.SqlClient namespace in your applications.

  1. Visual Basic: Imports System.Data.SqlClient

  2. C#: using System.Data.SqlClient;

  3. C++: using System::Data::SqlClient;

The .NET Framework Data Provider for OLE DB

 

The .NET Framework Data Provider for OLE DB uses native OLE DB through COM interop to enable data access. The .NET Framework Data Provider for OLE DB supports both local and distributed transactions. For distributed transactions, the .NET Framework Data Provider for OLE DB, by default, automatically enlists in a transaction and obtains transaction details from Windows 2000 Component Services. The following table shows the providers that have been tested with ADO.NET.

 

Driver

Provider

SQLOLEDB

Microsoft OLE DB Provider for SQL Server

MSDAORA

Microsoft OLE DB Provider for Oracle

Microsoft.Jet.OLEDB.4.0

OLE DB Provider for Microsoft Jet

 

Table 4

 

Using an Access (Jet) database as a data source for multithreaded applications, such as ASP.NET applications, is not recommended. If you must use Access as a data source for an ASP.NET application, and are unable to use an alternative such as SQL Server or MSDE, be aware that ASP.NET applications connecting to an Access database can encounter connection problems most commonly related to security permissions. For help troubleshooting connection problems using ASP.NET and an Access database, see article Q316675, "PRB: Cannot Connect to Access Database from ASP.NET" in the Microsoft Knowledge Base.

The .NET Framework Data Provider for OLE DB does not support OLE DB version 2.5 interfaces. OLE DB Providers that require support for OLE DB 2.5 interfaces will not function properly with the .NET Framework Data Provider for OLE DB. This includes the Microsoft OLE DB Provider for Exchange and the Microsoft OLE DB Provider for Internet Publishing.

The .NET Framework Data Provider for OLE DB does not work with the OLE DB Provider for ODBC (MSDASQL). To access an ODBC data source using ADO.NET, use the .NET Framework Data Provider for ODBC. .NET Framework Data Provider for OLE DB classes are located in the System.Data.OleDb namespace. The following code example shows how to include the System.Data.OleDb namespace in your applications.

  1. Visual Basic: Imports System.Data.OleDb

  2. C#: using System.Data.OleDb;

  3. C++: using System::Data::OleDb

The .NET Framework Data Provider for OLE DB requires MDAC 2.6 or later, and MDAC 2.8 Service Pack 1 (SP1) is recommended. You can download MDAC 2.8 SP1 from the Data Access and Storage Developer Center.

 

 

 

 

The .NET Framework Data Provider for ODBC

 

The .NET Framework Data Provider for ODBC uses the native ODBC Driver Manager (DM) to enable data access. The ODBC data provider supports both local and distributed transactions. For distributed transactions, the ODBC data provider, by default, automatically enlists in a transaction and obtains transaction details from Windows 2000 Component Services. The following table shows the ODBC drivers tested with ADO.NET.

 

Driver

SQL Server.

Microsoft ODBC for Oracle.

Microsoft Access Driver (*.mdb).

 

Table 5

 

.NET Framework Data Provider for ODBC classes are located in the System.Data.Odbc namespace. The following code example shows how to include the System.Data.Odbc namespace in your applications.

  1. Visual Basic: Imports System.Data.Odbc

  2. C#: using System.Data.Odbc;

  3. C++: using System::Data::Odbc;

The .NET Framework Data Provider for ODBC requires MDAC 2.6 or later, and MDAC 2.8 SP1 is recommended. You can download MDAC 2.8 SP1 from the Data Access and Storage Developer Center.

 

The .NET Framework Data Provider for Oracle

 

The .NET Framework Data Provider for Oracle enables data access to Oracle data sources through Oracle client connectivity software. The data provider supports Oracle client software version 8.1.7 or later. The data provider supports both local and distributed transactions. The .NET Framework Data Provider for Oracle requires Oracle client software (8.1.7 or later) on the system before you can connect to an Oracle data source.

.NET Framework Data Provider for Oracle classes are located in the System.Data.OracleClient namespace and are contained in the System.Data.OracleClient.dll assembly. You must reference both the System.Data.dll and the System.Data.OracleClient.dll when compiling an application that uses the data provider. The following code example shows how to include the System.Data.OracleClient namespace in your applications.

Visual Basic:

 

Imports System.Data

Imports System.Data.OracleClient

 

C#:

 

using System.Data;

using System.Data.OracleClient;

 

C++:

 

using System::Data;

using System::Data::OracleClient;

Keep in mind that in C++ the dot (.) is replaced with the scope operator (::).

 

 

Part 1 | Part 2 | Part 3 | Part 4 | Part 5

 

 


< XSLT - Transforming XML 2 | Main | C++ .NET and Using ADO.NET 2 >