< C++ .NET and Using ADO.NET 1 | Main | C++ .NET and Using ADO.NET 3 >


 

 

C++ .NET And Using ADO.NET 2

 

 

What we have in this page?

  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

 

 

 

 

 

Choosing a .NET Framework Data Provider

 

Depending on the design and data source for your application, your choice of .NET Framework data provider can improve the performance, capability, and integrity of your application. The following table discusses the advantages and limitations of each .NET Framework data provider.

 

 

Provider

Notes

.NET Framework Data Provider for SQL Server

Recommended for middle-tier applications using Microsoft SQL Server 7.0 or later.

Recommended for single-tier applications using Microsoft Database Engine (MSDE) or SQL Server 7.0 or later.

Recommended over use of the OLE DB Provider for SQL Server (SQLOLEDB) with the .NET Framework Data Provider for OLE DB.

For SQL Server 6.5 and earlier, you must use the OLE DB Provider for SQL Server with the .NET Framework Data Provider for OLE DB.

.NET Framework Data Provider for OLE DB

Recommended for middle-tier applications using SQL Server 6.5 or earlier.

For SQL Server 7.0 or later, the .NET Framework Data Provider for SQL Server is recommended.

Also recommended for single-tier applications using Microsoft Access databases. Use of an Access database for a middle-tier application is not recommended.

.NET Framework Data Provider for ODBC

Recommended for middle and single-tier applications using ODBC data sources.

.NET Framework Data Provider for Oracle

Recommended for middle and single-tier applications using Oracle data sources.

 

Table 6

 

ADO.NET Namespaces

 

The classes in ADO.NET are divided into the six namespaces shown in the following table.

 

Namespace

Description

System::Data

This is the core namespace in ADO.NET, and it contains generic classes that are used for any type of data source. For example, the DataSet class is defined in this namespace.

System::Data::Common

Defines common interfaces that are implemented appropriately by each data provider.

System::Data::SqlClient

Defines classes for the SQL Server data provider. These classes include SqlConnection, SqlCommand, SqlParameter, SqlDataReader, SqlTransaction, and SqlDataAdapter. (A data adapter is a special object that enables you to load and save data in a DataSet; you will learn more about data adapters later in this module.)

System::Data::OleDb

Defines classes for the OLE DB data provider. These classes include OleDbConnection, OleDbCommand, OleDbParameter, OleDbDataReader, OleDbTransaction, and OleDbDataAdapter.

System::Data::OracleClient

Defines classes for the Oracle data provider.

System::Data::Odbc

Defines classes for working directly with ODBC.

System::Data::SqlTypes

Defines classes that represent native SQL Server data types.

System::Xml

Defines XML-related classes. ADO.NET is tightly integrated with XML. For example, there is a class named XmlDataDocument in the System::Xml namespace that enables you to treat relational data as if it were an XML document. See XmlDataDocument in Microsoft Visual Studio/C++ .NET Help for details.

 

Table 7

 

ADO.NET Assemblies

 

The majority of ADO.NET classes are in the System::Data assembly (one notable exception is the XmlDataDocument class, which is in the System::Xml assembly). To use these assemblies, you will need to include the following statements in your application:

// This assembly contains ADO.NET classes

#using <System.Data.dll>

// This assembly contains XML classes,

// such as XmlDataDocument

#using <System.Xml.dll>

Note that if you are creating projects using Visual Studio .NET, the reference to System.Data.dll will be provided for you. Once you have imported these assemblies, you can import the required namespaces for your application. For example, in C++ .NET, if you need to access a SQL Server 2000 database, you need the following using statement:

using System::Data::SqlClient;

Creating a Connected Application

 

In the next few pages, you will create a managed C++ application that connects to a Microsoft Access database. You will use an OleDbConnection object to establish this connection. Once you are connected, you will create an OleDbCommand object to represent a SQL statement. You will then perform the following tasks:

 

  • Execute a statement that returns a single value. OleDbCommand has an ExecuteScalar method for this purpose.

  • Execute a statement that updates the database. OleDbCommand has an ExecuteNonQuery method to do this.

  • Execute a statement that queries the database. OleDbCommand has an ExecuteReader method to do this. ExecuteReader returns an OleDbDataReader object, which provides fast, forward-only access to the rows in the result set. You will use this OleDbDataReader object to process the result set.

 

Connecting to a Database

 

In this exercise, you will create a new application to perform all the operations described in the preceding section. The first step is to connect to the database. This exercise uses the Microsoft Northwind database. You may already have a copy if you have Microsoft Office installed on your PC. There is a SQL version of the Northwind sample database that can be downloaded from Microsoft site and also available at Visualcplusdotnet.com. The new version of the SQL database sample is AdventureWorks.

 

1.      Create a Visual C++ CLR Console Application project named ConnectedApplication.

 

Creating a Visual C++ CLR Console Application project named ConnectedApplication

 

2.      In the ConnectedApplication.cpp file, after the using namespace System; statement, add the following statements:

// Generic ADO.NET definitions

using namespace System::Data;

// Specific definitions for the OleDb data provider

using namespace System::Data::OleDb;

 

Adding a related database namespaces to the main project file

 

If you are using SQL Server 7 or later, you can use the SQL Server data provider instead. To do so, use the System::Data::SqlClient namespace and replace all the class names that begin OleDb with Sql. For example, replace OleDbException with SqlException.

 

3.      In the main() function, create an OleDbConnection object as follows:

// Create the connection

OleDbConnection ^ cnNwind = gcnew OleDbConnection();

 

Adding C++ .NET code to create an OleDbConnection

 

4.      The OleDbConnection object has a ConnectionString property, which enables you to specify the database you want to use. Set the ConnectionString property as follows:

 

// Set the connection string

cnNwind->ConnectionString =

    L" Provider=Microsoft.Jet.OLEDB.4.0; "

    L"Data Source=C:\\temp\\northwind.mdb";

Adding C++ .NET code to set the ConnectionString property

 

This connection string defines a connection to the Northwind database, using the Microsoft Jet database engine. You will need to edit the Data Source path to point to your copy of Northwind. If you are using SQL Server, you’ll need to provide a SQL Server connection string. You’ll also need to connect to a SQL Server version of the Northwind database.

 

5.      Open the database connection as follows:

try

{

  // Open the database

  cnNwind->Open();

  Console::WriteLine(L"Connected to database successfully!");

}

catch (OleDbException ^ pe)

{

  Console::Write(L"Error occurred: ");

  Console::WriteLine(pe->Message);

}

 

Adding C++ .NET source code to open database connection

 

Just about everything you do with databases can generate an exception. Therefore, you should always enclose your database code in a try and catch block, as shown in the preceding code.

 

 

 

 

6.      At the end of the main() function, close the database connection as follows:

// Close the connection

if (cnNwind->State != ConnectionState::Closed)

{

  cnNwind->Close();

}

Console::WriteLine(L"The database connection is closed...");

 

Adding c++ code to close database connection

 

The State property indicates the current state of the connection. The allowable values for this property are defined in the ConnectionState enumerated type, which is located in the System::Data namespace.

 

7.      Build your program, and fix any compiler errors. Run the program. If all is well, you’ll see the message shown in the following figure displayed on the console.

 

C++ .NET opening and closing database connection program output example

 

 

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

 

 


< C++ .NET and Using ADO.NET 1 | Main | C++ .NET and Using ADO.NET 3 >