ADO Data Access through COM Interop in .NET

• Download demo project - 103 Kb

This is my first article submitted for CodeProject. I have always wanted to submit articles for CodeProject, but couldn�t do so for various reasons. Now somehow, I have managed to post this article. Hope you guys enjoy it as much as I enjoyed writing this one.

Introduction

This article explains how to access traditional COM objects from .NET

For demonstrating the interop we will first see how to display the DataLink Property Dialog which is exposed as a COM object through the Ole Db Service Component.

We will also see how to access traditional ADO Component from .NET

In .NET the preferred way of accessing data is through ADO.NET. However, if you would like to use ADO for some reason, this article explains how to do that. A possible scenario I could imagine is when you want to re-use existing COM components which return ADO recordsets. 

The article does not go into details of how the actual interop occurs i.e the internals. It requires a detailed understanding of COM and its associated intricacies. .NET adds its own set of abstractions/access mechanisms in the form of CCWs and RCWs for interop. 

This article uses C# as the implementation language. However, its pretty simple to do the same in Visual Basic.NET. Also the article requires that you have a Visual Studio.net Installation. .NET provides the facility for accessing COM components through the Interop layer. 

For knowing more about COM interop in .NET you can go thru this excellent article from http://www.codeproject.com/dotnet/cominterop.asp.

Calling the DataLink Properties Dialog

The following are the steps needed to call the DataLink Properties Dialog.

In order to invoke the Datalink Properties Dialog you need to convert the COM CoClasses and data structures in COM into something that is understandable by the .NET framework. This is typically a set of classes and structures.For more information you can refer to the InteropServices namespace documentation in MSDN.

If you are using Visual Studio.NET this process is extremely simple. You just need to perform the following steps.

• Create a new project, select Visual C# project and in the templates section, select Windows Application. Give a meaningful name, let it be DataLink.
  
  
• Clicking OK will generate a Windows Application with a Form.
  
  
• After this we need to refer to the COM Component for which we need to generate a assembly/class wrapper. If you have used VC++ this process is very similar to generating the class wrappers for COM objects. However, in .NET this is simply an interop assembly(a dll) and namespace. 
  
  
• Right click on the References section in the Solution Explorer and click on Add Reference.
  
  
• This will bring up the following dialog. Go to the COM tab.

Before we proceed further, we need to know the COM object which exposes DataLink properties dialog. This COM object is present in the oledb32.dll, which is usually present in the path \Program Files\Common Files\System\OLE DB\oledb32.dll.

You can locate this information by searching for the following entry in the above dialog,

Microsoft OLE DB Service Component 1.0 Type Library.

Once you have the entry selected add it by clicking on the select button. Since we would also like to perform data access through the traditional ADO we need to generate interop assembly for the same. Hence we select Microsoft ActiveX Data Objects 2.5 Library, once the references are added, Visual Studio .net automatically generates interop assembly wrappers for the same and creates the dlls in the project bin\debug(or release) directory.

By default the ADO Wrapper dll cannot be seen in the debug(or release) directory, however if you change the property Copy Local � true for the assembly reference, you should be able to see the dlls in the project�s debug (or release) directory. You can see the physical presence of the dlls by going to the project menu and selecting Show All Files option. This will let you view all the files present inside your project directory.

Now that we have generated class/assemblies for the COM objects, now we can start using them. 

As a first step we need to include the following namespace

using System.Runtime.InteropServices;
using MSDASC;
using ADODB;

The InteropServices namespace contains services required for accessing COM objects. The MSDASC namespace generated through the Visual Studio .net contains classes and methods for accessing the DataLink component.

The ADODB namespace as the name implies contains the classes for accessing ADO from .NET

DataLinksClass objDataLink=new DataLinksClass();
objDataLink.hWnd = this.Handle.ToInt32();
Connection obj = (Connection) objDataLink.PromptNew();

First we create a DataLink object by creating an instance of DataLinksClass.

Note in the above code that the hWnd parameter expects a parent window handle as an integer. The handle of the current windows form is accessed by this.Handle which returns a Systems.IntPtr which we convert to integer by calling the function ToInt32.

The function PromptNew brings up the DataLink Properties Dialog as shown below and returns an ADO Connection object. Since we have class wrappers generated for ADO we make use of the connection class directly by type-casting the return value to an ADO connection object.

The ConnectionString property of the ADO Connection object now contains the ConnectionString returned by the DataLink Property Dialog.

Now we have got the connection string, its time that we make use of this by connecting to the database. With the Connection object now we can open the connection.

Connection objConn = (Connection) objDataLink.PromptNew();

 

objConn.Open(objConn.ConnectionString, null,null,
            (int)ConnectModeEnum.adModeUnknown);

Object RecordsAffected = null;

// We assume that Northwind Access DB is selected in the DataLink 

// Property dialog


Recordset objRecordset = objConn.Execute("select * from products",
                                         out RecordsAffected,
                                         (int) CommandTypeEnum.adCmdText);

Note, In ADO the Open method of the Connection object allows optional arguments, so its possible to getaway with calling the method without any parameters. However, in the classes generated by Visual Studio.net it expects all the arguments.

I believe that there should be some option that would help generate assemblies/wrappers with support for optional arguments, if anyone knows how to do this please email me so that I would update this article. Hence the Open method, now has the following syntax.

Void Open(string ConnectionString,string UserID,string Password,int Options)

We know the ConnectionString, and for the rest of the arguments UserID and Password we pass null strings as arguments as we are working with a MS-Access database. However depending on the database you connect you might have to give values to these arguments, the last of the argument Options specifies an int value which helps the ADO provider in evaluating how to go about with the command type. For now we can pass adModeUnknown as an argument. We simply convert the enumeration to int by typecasting.

Now that the connection is open, we can execute a query on this connection, through which we can get the ADO Recordset. 
For this we call the Connection Object�s execute method,which has the following syntax

Recordset Execute(string CommandText,out object RecordsAffected,int Options)

The CommandText represents the query for which the recordset is returned. The second parameter is an interesting one where we pass an object.

The RecordsAffected parameter gives information about how many records where affected by the operation.Note that it doesn�t return the no of records from the query executed, but it returns the no of records affected by executing the query. We specify the out keyword because the function promises that it returns a value on that parameter, ie. The RecordsAffected would contain some information after the completion of Execute statement.

Since we are not interested in this value, we pass an object initialised to null and in the Options we specify the enum that it is a command text with enum value �adCmdText�

Now that we have executed, the recordset is returned, we will display the recordset in a grid format using the DataGrid control. For this the first step would be to create an instance of DataSet.

DataSet objDS = new DataSet();

After this we create an OleDbDataAdapter object,

OleDbDataAdapter objProducts = new OleDbDataAdapter();

Then we fill the Data Adapter with the recordset, for which we use the following overload of the Fill function in OleDbDataAdapter

public int Fill(DataSet dataSet,object ADODBRecordSet,string srcTable);

As you see in the above function Fill accepts a ADO recordset as one of its parameters. Obviously, needless to say, the redmontanians have given some thought on providing direct support for ADO recordsets. So our call becomes,

objProducts.Fill(objDS,objRecordset,"products");

Now that we have filled the data adapter, its time to pass on this information to the Grid for displaying the result. The datagrid control has a property called DataSource, which we initialise as follows

dataGrid1.DataSource=objDS.DefaultViewManager;

Now you can see the results displayed in the grid.