Click here to Skip to main content
15,867,453 members
Articles / Programming Languages / C#
Article

ADO Data Access through COM Interop in .NET

Rate me:
Please Sign up or sign in to vote.
4.83/5 (19 votes)
25 Mar 20027 min read 199.9K   1.7K   41   21
The article explains how to make use of COM objects from .NET

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.


Image 1

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
C#
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.

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

Image 2  

C#
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.

C#
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

C#
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.

C#
DataSet objDS = new DataSet();
After this we create an OleDbDataAdapter object,
C#
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
C#
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,
C#
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
C#
dataGrid1.DataSource=objDS.DefaultViewManager;
Now you can see the results displayed in the grid.

Image 3

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here


Written By
Web Developer
India India
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
Generalnice Pin
Pranay Rana23-Dec-10 1:03
professionalPranay Rana23-Dec-10 1:03 
GeneralPaging in ADO Recordset Pin
kavirajan27-Aug-07 4:13
kavirajan27-Aug-07 4:13 
GeneralOptional parameters in COM Pin
Anonymous13-Oct-05 1:06
Anonymous13-Oct-05 1:06 
GeneralRe: Optional parameters in COM Pin
JohnAtJenisys18-Nov-10 2:38
JohnAtJenisys18-Nov-10 2:38 
GeneralOpening a recordset from an persisted xml Pin
Anonymous9-Sep-05 0:41
Anonymous9-Sep-05 0:41 
Generaldo update Pin
evoseven20-Jul-04 2:49
evoseven20-Jul-04 2:49 
GeneralPrepare ADO recordset in .NET for VB6 Pin
Motoneige10-Sep-03 6:47
Motoneige10-Sep-03 6:47 
GeneralRe: Prepare ADO recordset in .NET for VB6 Pin
Manuel Quelhas11-Jun-08 23:02
Manuel Quelhas11-Jun-08 23:02 
QuestionHow about the other way around? Pin
Anonymous2-Oct-02 11:00
Anonymous2-Oct-02 11:00 
AnswerRe: How about the other way around? Pin
Kannan Kalyanaraman2-Oct-02 18:53
Kannan Kalyanaraman2-Oct-02 18:53 
GeneralRe: How about the other way around? Pin
Anonymous15-Oct-02 4:12
Anonymous15-Oct-02 4:12 
GeneralGr8 Job mann Pin
Kirthivasan B31-Mar-02 18:27
Kirthivasan B31-Mar-02 18:27 
GeneralGood show Kannan !! Pin
Jagadeesh.P26-Mar-02 20:40
Jagadeesh.P26-Mar-02 20:40 
GeneralGreat Work Kannan Pin
Abraham T C26-Mar-02 20:08
Abraham T C26-Mar-02 20:08 
GeneralJust gave you a 5. Pin
Nish Nishant26-Mar-02 14:44
sitebuilderNish Nishant26-Mar-02 14:44 
GeneralRe: Just gave you a 5. Pin
Kannan Kalyanaraman27-Mar-02 16:58
Kannan Kalyanaraman27-Mar-02 16:58 
GeneralRe: Just gave you a 5. Pin
Nish Nishant27-Mar-02 18:22
sitebuilderNish Nishant27-Mar-02 18:22 
GeneralRe: Just gave you a 5. Pin
16-Apr-02 21:51
suss16-Apr-02 21:51 
GeneralWell done Kannan! Pin
Nish Nishant26-Mar-02 14:43
sitebuilderNish Nishant26-Mar-02 14:43 
GeneralRe: Well done Kannan! Pin
Kannan Kalyanaraman26-Mar-02 20:08
Kannan Kalyanaraman26-Mar-02 20:08 
Thanks Nish.

regards
Kannan
GeneralRe: Well done Kannan! Pin
Kannan Kalyanaraman26-Mar-02 20:03
Kannan Kalyanaraman26-Mar-02 20:03 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.