How to use ADO RecordSet objects in Visual C++ 2005 or in Visual C++ .NET (816158)

SUMMARY

This article describes how to create a console application that uses COM Interop to create an earlier version of an ADO RecordSet object, to convert the RecordSet object to an ADO.NET DataSet object, and then to display the record count.

back to the top

Requirements

The following list outlines the recommended hardware, software, network infrastructure, and service packs that you need:

• Microsoft Windows 2000 Professional, Microsoft Windows 2000 Server, Microsoft Windows Server 2003, or Microsoft Windows XP
• The Microsoft .NET Framework

This article assumes that you are familiar with the following topics:

• A general familiarity with Microsoft ADO and Microsoft ADO.NET

back to the top

Use COM Components from Visual Studio .NET

1. Start Visual Studio .NET or Visual Studio 2005.
2. On the File menu, point to New, and then click Project.
3. In Visual Studio .NET 2002, click Visual C++ Projects under Project Types, and then click Managed C++ Application under Templates.
   
   In Visual Studio .NET 2003, click Visual C++ Projects under Project Types, and then click Console Application (.NET) under Templates.
   
   Note In Visual Stuio 2005, click Visual C++ under Project Types, and then click Console Application under Templates.
4. Type Q816158 in the Name text box, type C:\ in the Location text box, and then click OK.
5. When the project is created, make sure that Solution Explorer is visible. If it is not visible, press the CTRL + ALT + L key combination.
6. Before you add code to the main() function, add a reference to the Component Object Model (COM) component that you will use. In Solution Explorer, right-click References under Q816158, and then click Add Reference. For additional information about adding references in Visual C++ .NET 2002, click the following article number to view the article in the Microsoft Knowledge Base:

   310674 HOW TO: Add References to a Managed Visual C++ Project

7. On the COM tab in the Add Reference dialog box, click Microsoft ActiveX Data Objects 2.5 Library, and then click Select. Your selection appears under Selected Components. Click OK. ADODB appears under References in the Q816158 project.
   
   Note In Visual Studio 2005, you do not have to click Select.
8. In Solution Explorer, right-click References under Q816158, and then click Add Reference.
9. On the .NET tab in the Add Reference dialog box, click the System.Data.dll component and the System.Xml.dll component. Click Select. The components appear under Selected Components. Click OK. System.Data and System.Xml appear under References in the Q816158 project.
   
   Note In Visual Studio 2005, you do not have to click Select.
10. If the Q816158.cpp file is not open in the editor window double-click Q816158.cpp in Solution Explorer. Now that you have a reference to an earlier version of an ADO component, its full capabilities are at your disposal. Additionally, Visual Studio .NET provides full Microsoft IntelliSense support for COM objects.
11. Add the following namespaces to the Q816158.cpp file:

    using namespace System::Data;
    using namespace System::Data::OleDb;

    Note The System.Data namespace consists mostly of the classes that constitute the ADO.NET architecture. You can build components that efficiently manage data from multiple data sources with the ADO.NET architecture. In a disconnected scenario (such as the Internet), ADO.NET provides the tools to request, to update, and to reconcile data in multiple-tier systems.
    
    You can access a data source to use in conjunction with a DataSet with the System.Data.OleDb namespace (the OLE DB .NET Data Provider).
12. The first few lines of code create and open a connection. Replace the existing code in the main() function with the following code:

    ADODB::ConnectionClass* cnn = new ADODB::ConnectionClass();
    cnn->ConnectionString = S"provider=SQLOLEDB.1;Data Source=<your SQL server>;Initial Catalog=Northwind;User Id=<username>;Password=<password>"; 
    cnn->Open(S"",S"",S"",-1);

    Note Set the values for Data Source, User ID, and Password in the ConnectionString as appropriate for your environment .
13. Create an instance of an ADO RecordSet object. Set the cursor and the lock properties. To do this, add the following code to the existing code:

    ADODB::RecordsetClass *rs = new ADODB::RecordsetClass();
    rs->CursorLocation = ADODB::CursorLocationEnum::adUseClient;
    

    Note This code will be familiar to you if you have experience with earlier versions of ADO. The difference is that you are now working with it in .NET.
14. Open a RecordSet object by passing in the following SQL statement that was created for this specific case, and pass in the connection object:

    rs->Open(S"select * from Products", cnn,ADODB::CursorTypeEnum::adOpenStatic,ADODB::LockTypeEnum::adLockBatchOptimistic,-1);

15. Disconnect the RecordSet object, and then close the connection as follows:

    rs->ActiveConnection = NULL;
    cnn->Close();
    

16. To make the RecordSet object fully usable in a .NET application, convert the RecordSet to an ADO.NET DataSet object by using the OleDbDataAdapter class:

    OleDbDataAdapter *da = new OleDbDataAdapter();
    
    DataSet *ds = new DataSet();
    da->Fill(ds,rs,S"products");
    

17. Add the last two lines of code as follows to write the total number of rows in the DataSet object to the console:

    Console::WriteLine(S"There are {0} total products.", ds->Tables->get_Item(0)->Rows->Count.ToString());
    Console::ReadLine();
    

back to the top

Complete Code Listing

#using <mscorlib.dll>

using namespace System;
using namespace System::Data;
using namespace System::Data::OleDb;

int _tmain()
{
	try
	{
		ADODB::ConnectionClass* cnn = new ADODB::ConnectionClass();
		cnn->ConnectionString = S"provider=SQLOLEDB.1;Data Source=<your SQL server>;Initial Catalog=Northwind;User Id=<username>;Password=<password>"; 
		cnn->Open(S"",S"",S"",-1);

		ADODB::RecordsetClass *rs = new ADODB::RecordsetClass();
		rs->CursorLocation = ADODB::CursorLocationEnum::adUseClient;
		rs->Open(S"select * from Products", cnn,ADODB::CursorTypeEnum::adOpenStatic,ADODB::LockTypeEnum::adLockBatchOptimistic,-1);
				
		rs->ActiveConnection = NULL;
		cnn->Close();

		OleDbDataAdapter *da = new OleDbDataAdapter();

		DataSet *ds = new DataSet();
		da->Fill(ds,rs,S"products");

		Console::WriteLine(S"There are {0} total products.", ds->Tables->get_Item(0)->Rows->Count.ToString());
		Console::ReadLine();
	}
	catch(Exception *ex)
	{
		Console::WriteLine(ex->Message );
	}
	return 0;
}

Note You must add the common language runtime support compiler option (/clr:oldSyntax) in Visual C++ 2005 to successfully compile the previous code sample. To add the common language runtime support compiler option in Visual C++ 2005, follow these steps:

1. Click Project, and then click <ProjectName> Properties.
   
   Note <ProjectName> is a placeholder for the name of the project.
2. Expand Configuration Properties, and then click General.
3. Click to select Common Language Runtime Support, Old Syntax (/clr:oldSyntax) in the Common Language Runtime support project setting in the right pane, click Apply, and then click OK.

For more information about the common language runtime support compiler option, visit the following Microsoft Web site:back to the top

Verify That It Works

1. Press F5 to run the application in debug mode.
2. After a brief pause you see the following:There are 77 total products.
3. Press ENTER to quit the console application and to return to Visual Studio .NET.

back to the top

Troubleshooting

You may have to modify the connection string to run this application (specifically the server name). Although provider=sqloledb is not required for .NET applications, in this case you must have it because .NET uses ODBC for earlier versions of ADO.

back to the top