ADO.NET Programmer's Reference - Chapter 16 - COM Interoperability

Title ADO.NET Programmer's Reference Authors Dushan Bilbija, Paul Dickinson, Fabio Claudio Ferracchiati, Jeffrey Hasan, Naveen Kohli, John McTanish, Matt Milner, Jan D Narkiewicz, Adil Rehan, Jon D Reid Publisher Wrox Published September 2001 ISBN 186100558X Price US 39.99 Pages 700

COM Interoperability

What is COMInteroperability (Interop)? Why is it important? These are some of the basic questions that might come to mind when Interop is mentioned. And if you are developing a data access layer, another question pops into mind. How does this affect our components when we have ADO.NET to take care of all the needs as far as data access is concerned? In this chapter, we will arrive at some answers to these questions, and gain an understanding of how to use Interop in our applications.

Interop enables the interoperation of managed code with unmanaged code, and vice versa.

The code that runs under the Common Language Runtime (CLR) is managed code, and the code that runs outside CLR is unmanaged code. For example, all the components (assemblies) that are created using the .NET framework are managed code, and all the components that are developed with Win32 API are unmanaged code.

The.NET framework introduces a completely new model for application development and deployment. We no longer have to think about the choice of programming language because every language generates the same metadata in Microsoft Intermediate Language (MSIL) format. At the end of the day it does not matter how the metadata was generated, it will all be Just In Time (JIT) compiled to same native (for example x86) code.

If we closely study all the evolutionary changes that every developer will have to take into consideration, it may not be very incorrect to say that we will have to unlearn all the old concepts and start from square one. Does this mean that all the old applications and components that you have been using for quite some time on the server side or middle tier will stop working in .NET framework, or cannot be used in the .NET enabled applications? Absolutely not. Our efforts and investment in the old and tested components will not be wasted at all. This is where Interop comes into play.

COM Interop allows us to utilize existing COM (Component Object Model) components in our .NET applications without needing to modify the COM components in any way. As .NET gains acceptance we will hear a lot about Interop in the .NET framework. This chapter will first give a brief overview of Interop and then demonstrate the ways in which it can be used to access classic ADO components, and exploit facilities not yet developed for ADO.NET to leverage your .NET data access components.

Interop enables managed code to call unmanaged code transparently, and for unmanaged code to call managed code. In practice, this means that we do not need to rewrite unmanaged components to operate with .NET applications, and that we can develop managed components to work with unmanaged components. All we need to do, is make use of some utilities and tools provided with the .NET SDK to integrate existing applications with managed applications. We will look at these tools and utilities later in the chapter.

In an ideal world, we would probably want to covert all existing unmanaged code to managed code. In many cases this would be impractical, given certain design constraints, however, and – more importantly – the time framework in which to roll out the new application. Sometimes it is not possible to convert the unmanaged code to managed code at all: for example, if the source code is not available. Also, there may be a particular type of operation that we wish to implement: for instance, utilizing a recordset with a server side cursor, which cannot be achieved with ADO.NET. In both these types of situations, Interop comes to the rescue by enabling the use of classic ADO technology. Also, there are some other technologies, complementing ADO, such as Microsoft ActiveX Data Objects Extensions for Data Definition Language and Security ( ADOX), Jet Engine and Replication Objects (JRO) and ADO for Multi-Dimensional Data (ADOMD) that are not supported by ADO.NET. Interop is the only way to integrate these technologies into a managed application. Therefore, Interop is going to play a very important role when migrating existing applications to .NET.

This chapter will not discuss how to use ADO, ADOX, JRO, and ADOMD data access technologies. If you wish to learn more about them, please consult Professional ADO 2.5 Programming (Wrox Press, ISBN 1-861002-75-0) and ADO 2.6 Programmer's Reference (Wrox Press, ISBN 1-861004-63-X).

Garbage Collection

All the managed objects are garbage collected, meaning that when a particular object is not required anymore, and there is no one holding any reference to it, the garbage collector (GC) releases memory associated with it from the managed memory heap. We are however, talking about Interop with unmanaged code. We know that there is no GC for code running outside the CLR. So what happens to the managed components that are used by the unmanaged components? What releases the references for unmanaged COM components when they are used inside the managed code?

The .NET framework performs the following operations for the interoperation of managed and unmanaged code:

• When a managed object is marshaled out of CLR to the unmanaged code, a COM Callable Wrapper (CCW) is created

• When an unmanaged object is referenced in the managed code, a Runtime Callable Wrapper (RCW) is created

These wrapper objects act as the conduits between managed and unmanaged execution engines. These objects act as the proxies that marshal the calls between the managed and unmanaged objects.

When a COM client calls into a managed object, the runtime creates a new managed object and a CCW for that object. The object's lifetime is managed by this CCW. When the COM client calls AddRef on this managed object, the CCW holds the reference count for it. Therefore, to the COM client, this managed object appears exactly like a regular COM component. When the Release method is called on the object, CCW decrements the reference count on it. When there are no outstanding references to this managed object (in other words the reference count drops down to zero), the CCW releases the managed object in the CLR. In the next cycle of garbage collection, this released managed object gets removed from the managed heap. The COM clients using the managed objects should follow the classic COM rules for reference counting, and should make a Release call for every AddRef call. If the unmanaged code forgets to release the managed code, the CCW will keep on holding to the object, and the garbage collector will never collect it.

When the managed code creates an instance of unmanaged COM components, the CLR creates the RCW for this component and maintains the reference count for it. Every time a new object of this component is created, the reference count is incremented on this RCW. When all of the referenced object are no longer needed, the RCW releases the reference to the COM object and during next garbage collection cycle this wrapper (RCW) gets collected.

Error Handling

Before we dig deep into the use of Interop for data access components, there is one important aspect that we should look at. It is the way error handling mechanism works in Interop. In the existing COM components, the error conditions are communicated to the caller via HRESULT codes. If the call succeeds, the components return S_OK, otherwise an error code (E_FAIL, or some custom code) is returned to the caller.

The managed code takes a different approach. The errors are reported by means of exceptions. If a component wants to report a failure condition to the caller, it throws an exception. This exception can be one of the .NET framework provided exception classes (for example, InvalidCastException, InvalidOperationException, and so on) or it can be a user-defined exception (a class derived from System.Exception class). When a COM component returns an error, the Interop layer converts it to one of the framework-defined classes and stores the HRESULT code into HResult property of the System.Exception class object. When a managed code throws an exception, the Interop layer converts it to an HRESULT value.

Therefore, if our managed code is calling method of unmanaged code, then we should guard our implementation by providing exception handlers for the errors that will be returned. On the other side of spectrum, if we are implementing a managed code that will be used by unmanaged code, we should store the HRESULT code in the HResult property of the exception class before throwing the exception to the caller.

Tools For Interop

Managed code, running in CLR, is not capable, by itself, of crossing the CLR boundary to make calls into unmanaged components. The managed code has no knowledge of the data types defined by the unmanaged code. The .NET framework is all about data types, so it needs some mechanism to refer to the data types defined in unmanaged code. The COM components define their types in the type library, which could be present in a separate file (.TLB file) or embedded as a resource in the DLL or EXE file. The .NET component's meta-data is contained in the assembly file. The .NET framework provides the tools to convert between CLR and COM data type information.

TLBIMP (Type Library Importer)

This is a command line tool that can be used to convert the co classes and interfaces information, contained in a COM component's type library, into the .NET metadata information. The following example shows the simplest use of this tool to convert the ADO type library, embedded in msado15.dll, to the .NET metadata, defining the corresponding CRL types:

C:\>tlbimp msado15.dll /out:ClassicADO.dll

The first argument is the name of the file that contains the COM type library. This file can be a DLL, EXE, OLB, OCX or TLB file. The second argument is optional parameter that can be used to fine-tune the output from the tool. In this case, I have used the /out parameter, which specifies the name of the output file (ClassicADO.dll in this case). The detailed explanation of each optional parameter is out of scope of this book. We can call this tool with a /? option to see all the options supported, and a brief explanation of each one of them. The following screen short shows the list of options generated by the /? Parameter:

For detailed explanation of these optional parameters, refer to the .NET Framework Tools section in the .NET Framework SDK Documentation.

The tool converts the entire type library into the .NET metadata assembly. It cannot be used for partial conversion of the type library. Once we have created the assembly describing the metadata, we can discard the source type library file. The Interop operation does not depend on the source file anymore. The MANIFEST created in the assembly saves the GUID of the co class as GuidAttribute. The CLR creates the instance of COM object using this information from the registry.

We can create the unmanaged objects in your managed applications simply by calling new operator. There is no CoCreateInstance and no QueryInterface call. The RCW takes care of all these steps behind the scene. It calls the CoCreateInstance method by using the GUID stored in the manifest of the metadata assembly generated for the COM object.

TLBEXP (Type Library Exporter)

This command line tool is the counterpart of the TLBIMP tool. It converts the metadata contained in a managed assembly into a type library. The following example shows the use of this tool:

C:\>tlbexp myassembly.dll /out:unmancode.dll

Like TLBIMP, this tool also supports a few optional parameters. The above example uses the /out parameter to specify the name of the output file. The detailed explanation of each optional parameter is out of scope of this book. We can call this tool with the /? option to see all the options supported and a brief explanation of each one of them. The following screen short shows the list of options generated by the /? Parameter:

For detailed explanations of these optional parameters, refer to the .NET Framework Tools section in the .NET Framework SDK Documentation.

Visual Studio.NET

We can directly add references to an already registered COM component's type library using Visual Studio 7.0. It will generate the assembly containing the metadata information corresponding to the types contained in the type library. It is like using TLBIMP tool where the IDE defines the optional parameter.

We can add reference to the type library by selecting COM tab on the Add Reference dialog box. This will bring up the list of all type libraries registered on the system. Select the one that you want to add the reference to. These two steps will create the assembly and add the reference to the project. The following screen shots show the addition of reference for ADO 2.7 type library in a project:

 

The type library has been added with the namespace ADODB.

TypeLibConverter Class

This class, defined in System.Runtime.InteropServices namespace, provides a programmatic way of creating the metadata information from a COM type library. This class has two methods, ConvertTypeLibToAssembly and ConvertAssemblyToTypeLib that do the conversion of types. We will not be discussing these methods in this book. For more details, refer to this class in .NET Framework Documentation.

After the assembly has been created using one of the above tools, we need to refer to this assembly when compiling the source code. Depending on the programming language used, we can refer to the namespace of this assembly as shown below:

VB.NET:

Imports ClassicADO

C#:

using ClassicADO;

If you are using Visual Studio 7.0, then you don't have to do any manual steps to add reference to the assembly containing the metadata information. For command line compilation of the code, use the /r option to add the reference to the DLL.

VB.NET:

vbc /r:ClassicADO.dll Foo.vb

C#:

csc /r:ClassicADO.dll Foo.cs

ADO in Managed Applications

In the last few years, ADO technology has come a long way. A lot of hard work and investment has been poured into developing robust, scalable, and reliable components that use this data access technology. The .NET data access class, OleDbDataAdapter, takes this into account and provides the Fill method, which takes an ADO Recordset object as its input parameter and populates the DataSet object with the supplied data. For more details, refer to the Fill method of the OleDbDataAdapter class in the DataAdapter chapter.

This is one approach that we can use to employ ADO Recordset objects in our managed application. We can use the TlbImp tool to import the type library from existing COM objects, that have methods and properties returning the Recordset objects.

Consider a case of a typical COM component, for example, Shop.dll, which has a method returning a Recordset of inventory records. First, we would use TlbImp to import the type library into a .NET assembly:

C:\>tlbimp /out:ShopObjects.dll Shop.dll

This imports the type library to ShopObjects.dll. Next, add reference to this managed assembly into a managed application:

vbc /r:ShopObjects.dll /r:ClassicADO.dll ShopApplication.vb

 

csc /r:ShopObjects.dll /r:ClassicADO.dll ShopApplication.cs

 

Then in our managed assembly, create this unmanaged component as a regular CLR object using the new operator.

Creating .NET Metadata For ADO

The first step for using classic ADO objects in our managed application is to establish a reference to the metadata file, describing all the interfaces exposed by it. By default, the ADO library file, msado15.dll, is present in the following folder:

<System Drive>\Program Files\Common Files\System\Ado

We would use the TLBIMP tool to generate the .NET compatible assembly that we will refer to from managed code:

In the above example, we have called TLBIMP from root directory, while the msado15.dll file is located in that folder. The reason we are able to do is, because we have specified the folder location of ADO files in the path variable of the system environment. Otherwise, it would be necessary that the TLBIMP tool be invoked from the same folder as the source file. Although the source file is not in the root folder, the output will be created in the root folder (C:/) and then copied to any folder from where we want to refer this metadata assembly. All the examples in the subsequent sections in this chapter invoke the TLBIMP tool from the root folder using the same approach of setting system environment's path variable.

This operation will generate a .NET assembly ClassicADO.dll containing the metadata for the ADO library with the namespaceClassicADO. We can provide a fully qualified name for all the ADO objects scoped within this namespace such as ClassicADO.Connection, ClassicADO.Command, and ClassicADO.Recordset:

Reference to the ADO Assembly

To compile the ADO assembly generated by the TLBIMP tool into the managed code, we would add a reference as follows:

VB.NET:

vbc /t:library /r:ClassicADO.dll /out:EmployeeDB.dll EmployeeDB.vb

C#:

csc /t:library /r:ClassicADO.dll /out:EmployeeDB.dll EmployeeDB.cs

Using a Recordset to Populate a DataSet

The following example shows the use of ADO objects in a managed assembly. This example requires use of server side cursor support, which is not provided by ADO.NET. It opens an ADO Connection by providing a connection string and then calls the Execute method to get the Recordset object. It then uses this Recordset object to populate the DataSet using the Fill method of the OleDbDataAdapter class. This approach can also be used to get a Recordset with a server side cursor and pass it to the unmanaged code.

VB.NET:

Dim strQuery As String = "SELECT * FROM Employees WHERE EmployeeID = 1"
Dim strConn As String = "Provider=SQLOLEDB; Data Source = SYNCMONK" & _
                "; Initial Catalog = Northwind; User ID = sa; Pwd ="
                    
' Create a new instance ADO Connection object
Dim dbConn As New Connection ()
dbConn.CursorLocation = CursorLocationEnum.adUseClient

' Open the connection with specified connection string.
dbConn.Open (strConn, "", "", (int)ConnectModeEnum.adModeUnknown)

' Execute SQL query to get the emploee record.
Object recsAffected = Null
Dim rs Recordset = dbConn.Execute (strQuery, recsAffected,
                     CType (CommandTypeEnum.adCmdText, Integer))
               
Dim dtSet As New DataSet ()
Dim dtAdapter As New OleDbDataAdapter ()
dtAdapter.Fill (dtSet, rs, "Employee")
rs.Close ()

C#:

string strQuery = "SELECT * FROM Employees WHERE EmployeeID = 1";
string strConn = "Provider=SQLOLEDB; Data Source = Northwind";
strConn += "; Initial Catalog = Northwind";
strConn += "; User ID = sa; Pwd = ;";
                    
// Create a new instance ADO Connection object
Connection dbConn = new Connection ();
dbConn.CursorLocation = CursorLocationEnum.adUseServer;
// Open the connection with specified connection string.
dbConn.Open (strConn, "", "", (int)ConnectModeEnum.adModeUnknown);

// Execute SQL query to get the employee record.
object recsAffected = null;
_Recordset rs = dbConn.Execute (strQuery, out recsAffected, (int)CommandTypeEnum.adCmdText);

DataSet dtSet = new DataSet ();
OleDbDataAdapter dtAdapter = new OleDbDataAdapter ();
dtAdapter.Fill (dtSet, rs, "Employee");
rs.Close ();

Note that the second parameter to the Execute method call on the ADO Connection has been passed as a System.Object and not as a VARIANT. The reason is that VARIANT is an unmanaged data type and Interop marshals this as a System.Object.

Whenever we are not clear about what data types to use for the unmanaged method parameters, it is a good idea to use the ILDASM tool and take a look at the IL Code generated for the method. IL Code generated by the TLBIMP can provide complete information about the type of parameters that need to be passed into a method call, and what kind of output it will return. The IL code acts like documentation for the imported unmanaged objects.

ADOX In Managed Applications

ADO.NET provides a framework for manipulating existing data sources. Like classic ADO, it does not provide direct support for creating new data source objects, such as physical data files, maintaining users, and groups and working with the permission objects. ADO provides the Microsoft ActiveX Data Objects Extension for Data Definition Language and Security (ADOX) model, an extension library, to accomplish these tasks. But in ADO.NET, there is no direct or indirect support for these tasks. This means that we must rely on Interopto make calls into the ADOX library and perform these operations. Although ADO.NET provides managed providers for SQL Server (version 7.0 and higher), OLE DB providers, and native ODBC drivers, the use of ADOX through Interop is still limited by the ADOX features supported by the particular OLE DB provider. Of the OLE DB providers currently available, Microsoft Jet has complete support for ADOX. The OLE DB providers for SQL Server, Oracle, DB2, and others, provide a very limited implementation of ADOX.

Creating .NET Metadata For ADOX

The first step for using ADOX in a managed app is to establish a reference to the metadata file describing all the interfaces exposed by it. The ADOX library file name is msadox.dll, which is present in the following folder on your machine.

<System Drive>\Program Files\Common Files\System\Ado

Use the TlbImp tool to generate the .NET compatible assembly that we will refer to from managed code:

This operation will generate a .NET assembly ADOX.dll containing the metadata for the ADOX library with the ADOX namespace. We can provide fully qualified names for all the ADOX model objects scoped within this namespace, such as ADOX.Catlog and ADOX.Table:

Reference to the ADOX Assembly

We will need to add reference to the ADOX assembly, generated by the TLBIMP tool, to compile it with our managed code. The following example adds a reference to ADOX.dll to generate a managed component EmployeeDB.dll.

VB.NET:

vbc /t:library /r:ADOX.dll /out:EmployeeDB.dll EmployeeDB.vb

C#:

csc /t:library /r:ADOX.dll /out:EmployeeDB.dll EmployeeDB.cs

Creating a Microsoft Access Database File

The Catalog object in ADOX can be used to create a new Access database by calling the Create method.

The following code shows the use of the ADOX metadata assembly in a managed assembly to create a new database. First, it creates a new instance of the Catalog object simply by calling New operator. It instantiates a Catalog object, and then the Create method is called on it with a connection string. The connection string specifies that Jet4.0 OLE DB provider will be used to create the new Microsoft Access Database file:

VB.NET:

Option Explicit On
Option Strict On
Imports System
Imports ADOX

Namespace ADOX_Interop
 Public Class ADOX_EmployeeDB

 Public Function CreateEmployeeDB () As Boolean

    Try
        Dim strConn As String = "Provider=Microsoft.JET.OLEDB.4.0;" & _
                         "Data Source = C:\\EmployeeDB.mdb"
        ' Create instance of Catalog object.    
        Dim dbCatalog As New Catalog ()
        ' Call Create method to create mdb file.
        dbCatalog.Create (strConn)
    Catch ex As System.Exception
        Console.WriteLine (ex.Message)
        Return False
    End Try
    Return True
  End Function
 End Class
End Namespace

C#:

using System;
using ADOX;

namespace ADOX_Interop
{
  public class ADOX_EmployeeDB
  {
    public ADOX_EmployeeDB (){}
    public void CreateEmployeeDB ()
    {
      try
      {
       string strConn="Provider=Microsoft.JET.OLEDB.4.0;";
         strConn += "Data Source = C:\\EmployeeDB.mdb";
       // Create instance of Catalog object.
      Catalog dbCatalog = new Catalog ();
       //Call Create method to create mdb file.
         dbCatalog.Create (strConn);
     }
     catch (System.Exception ex)
     {
        Console.WriteLine (ex.Message);
        Return false;
     }
     return true;
     }
   }
}

Creating Tables in an Access Database

The Catalog object in ADOX exposes Tablesobject. The Tables object exposes the Columns, Index, Keys, and Properties collections. We can use these objects to append a new table to the database.

The following code sample shows how we can use a Catalog object to create a Microsoft Access 2000 database .mdb file. First, the Catalog object is created to which the table will be added. Then a new instance of the Table object is created. This is the object to which all the data columns will be added. Then new data columns are appended to the Columns collection of the Table. The Column object exposes properties like Name, Attributes, DefinedSize, NumericScale, Precision, and Type. For the sake of brevity, in this example only three properties have been specified for each column, name, data type and the size. After adding the columns to the collection, the table is appended to Tables collection of the catalog by calling Append method. Finally, a primary key index is added to the Indexes collection of the table. The Index object exposes properties like Name, Unique, PrimaryKey, and so on, that can be used to control its action.

VB.NET:

Dim strConn As String = "Provider=Microsoft.JET.OLEDB.4.0;" & _
                  "Data Source = C:\\EmployeeDB.mdb"
' Create instance of Catalog object             
Dim dbCatalog As New Catalog ()
dbCatalog.Create (strConn)

' Create instance of Table object
Dim dtTable As New Table ()
With dtTable
     .Name = "Address"
     .Columns.Append ("AddressID", ADOX.DataTypeEnum.adInteger, 4)
     .Columns.Append ("Street", ADOX.DataTypeEnum.adVarWChar, 128)
     .Columns.Append ("City", ADOX.DataTypeEnum.adVarWChar, 128)
     .Columns.Append ("State", ADOX.DataTypeEnum.adVarWChar, 128)
     .Columns.Append ("Zip", ADOX.DataTypeEnum.adVarWChar, 128)
     .Columns.Append ("Country", ADOX.DataTypeEnum.adVarWChar, 128)
End With

' Append the table to catalog.
dbCatalog.Tables.Append (CType(dtTable, Object))

' Create the primary key column with unique values.
Dim primKeyIdx As New Index ()
With primKeyIdx
     .Name = "UniqueAddrID"
     .Unique = True
     .PrimaryKey = True
     .Columns.Append ("AddressID", ADOX.DataTypeEnum.adInteger, 4)
End With
' Append the primary index to table.
dtTable.Indexes.Append (CType (primKeyIdx, Object), Nothing)

C#:

string strConn = "Provider=Microsoft.JET.OLEDB.4.0;" +
            "Data Source = J:\\NetProjects\\EmployeeDB.mdb";
// Create instance of Catalog object.                  
Catalog dbCatalog = new Catalog ();
dbCatalog.Create (strConn);

// Create instance of Table object.                    
Table dtTable = new Table ();



dtTable.Name = "Address";
dtTable.Columns.Append ("AddressID", ADOX.DataTypeEnum.adInteger, 4);
dtTable.Columns.Append ("Street", ADOX.DataTypeEnum.adVarWChar, 128);
dtTable.Columns.Append ("City", ADOX.DataTypeEnum.adVarWChar, 128);
dtTable.Columns.Append ("State", ADOX.DataTypeEnum.adVarWChar, 128);
dtTable.Columns.Append ("Zip", ADOX.DataTypeEnum.adVarWChar, 128);
dtTable.Columns.Append ("Country", ADOX.DataTypeEnum.adVarWChar, 128);

// Append the table to catalog.
dbCatalog.Tables.Append ((object)dtTable);

// Create the primary key column with unique values.
Index primKeyIdx = new Index ();
primKeyIdx.Name = "UniqueAddrID";
primKeyIdx.Unique = true;
primKeyIdx.PrimaryKey = true;
primKeyIdx.Columns.Append ("AddressID", ADOX.DataTypeEnum.adInteger, 4);

// Append the index to table.
dtTable.Indexes.Append ((object)primKeyIdx, null);

We can use ADOX objects through Interop to improve the functionality of the managed applications. Before using any of the ADOX features it is important to read the documentation for that particular OLE DB managed provider to check what support is available and what is not. For example, using the OLE DB managed provider for SQL Server, we cannot create a new table in the database as we would have using the Create method of the Catalog object in classic ADO.

The following example shows how to use ADO and ADOX objects to create a new data table in a SQL Server database.

VB.NET:

Dim strConn As String = "Provider=SQLOLEDB;" & _
 "Data Source = DOTNET; Database = FashionHouse;" & _
 "User ID = foo; Pwd = ;"
' Create instance of Catalog object.
Dim dbCatalog As New Catalog ()
' Create ADO Connection object
Dim dbConn As New Connection ()
dbConn.Open (strConn)
dbCatalog.ActiveConnection = CType (dbConn, Object)

' Create instance of Table object.
Dim dtTable As New Table ()
With dtTable
     .Name = "Categories"
     .Columns.Append ("CategoryID", ADOX.DataTypeEnum.adInteger, 4)
     .Columns.Append ("Name", ADOX.DataTypeEnum.adVarWChar, 64)
     .Columns.Append ("Descr", ADOX.DataTypeEnum.adVarWChar, 128)
End With

' Append the table to catalog.
dbCatalog.Tables.Append (CType(dtTable, Object))

JRO In Managed Applications

ADO.NET does not provide direct support for features such as compacting a database, database replication, replica synchronization, setting passwords, and encryption on databases, and so on. These features are available in the classic ADO model through Jet and Replication Objects (JRO). An application can provide all these features by using Interop to access JRO objects.

Creating .NET Metadata For JRO

To establish a reference to the metadata file, describing all the interfaces exposed by the JRO library, we would use the TlbImp tool on msjro.dll to create a .NET enabled assembly. This library file resides, by default, in the following folder on your machine:

<System Drive>\Program Files\Common Files\System\Ado

This operation will generate a .NET assembly JRO.dll containing the metadata for the JRO library with JRO namespace. We can provide a fully qualified name for all the JRO model objects scoped within this namespace, such as JRO.Replica and JRO.Filter:

Reference to the JRO Assembly

To compile the JRO assembly generated by the TLBIMP tool into the managed code, we would add a reference, as follows:

VB.NET:

vbc /t:library /r:JRO.dll /out:EmployeeDB.dll EmployeeDB.vb

C#:

csc /t:library /r:JRO.dll /out:EmployeeDB.dll EmployeeDB.cs

Replicating an Access Database

The following example shows how a JRO Replica object can be used to create a full replication of a Microsoft Access database.

VB.NET:

Dim strConn As String = "Provider=Microsoft.JET.OLEDB.4.0;" & _
                        "Data Source = C:\\EmployeeDB.mdb"
Dim strRep As String = "J:\\ EmployeeDB_Rep.mdb"
'Create an instance of Replica object
Dim dbRep As New Replica ()
dbRep.MakeReplicable ("J:\\NetProjects\\EmployeeDB.mdb", True)

' Call CreateReplica method to create replica of Employee database
dbRep.CreateReplica (strRep, "Replica of Employee Database", _
                     ReplicaTypeEnum.jrRepTypeFull)

C#:

string strRep = "C:\\EmployeeDB_Rep.mdb";               
// Create an instance of Replica object
Replica dbRep = new Replica ();
dbRep.MakeReplicable ("C:\\EmployeeDB.mdb", true);
// Call CreateReplica method to create replica of Employee database
dbRep.CreateReplica (strRep, "Replica of Employee Database",
                     ReplicaTypeEnum.jrRepTypeFull,
                VisibilityEnum.jrRepVisibilityGlobal, -1,
                UpdatabilityEnum.jrRepUpdFull);

Please note that all parameters for the CreateReplica method (ReplicaType, Visibility, Priority and Updatability) must be specified in C#, whereas in VB they are optional. Does this mean that the managed code generated for VB is different than that generated for C#? Before I answer this question, lets look at the method signature generated by TLBIMP tool:

.method public hidebysig newslot virtual
    instance void  CreateReplica(
           [in] string  marshal( bstr) replicaName,
                [in] string  marshal( bstr) description,
                [in][opt] valuetype JRO.ReplicaTypeEnum ReplicaType,
                [in][opt] valuetype JRO.VisibilityEnum Visibility,
                [in][opt] int32 Priority,
                [in][opt] valuetype JRO.UpdatabilityEnum updatability
          ) runtime managed internalcall
{
  .custom instance void
  [mscorlib]System.Runtime.InteropServices.DispIdAttribute::.ctor(int32)
     = ( 01 00 0E 00 02 60 00 00 )                         // .....`..
  .param [3] = int32(0x00000002)
  .param [4] = int32(0x00000001)
  .param [5] = int32(0xFFFFFFFF)
  .param [6] = int32(0x00000000)
  .override JRO.IReplica::CreateReplica
}

Pay attention to the last four parameters of the CreateReplica method. There are two attributes associated with each one of these. The first is [in] indicating that it is an INPUT parameter, and the second is [opt] indicating that it is an OPTIONAL parameter.

Visual Basic allows the missing parameters in the methods. In case of VB, the framework replaces the missing parameters with Type.Missing object that indicates that if there are any default values specified for these parameters, use those values. The C# language specifications do not allow the use of missing parameters. You will have to specify the values for these optional parameters either explicitly or provide the Type.Missing objects for these values.

The Type.Missing class is defined in the System.Reflection namespace.

ADOMD In Managed Applications

.NET provides no support for manipulating On Line Analytical Processing (OLAP) server data. Classic ADO provided this support through the companion ADOMD library. Once again, through Interop, we can incorporate ADOMD features into our managed applications.

Creating .NET Metadata For ADOMD

To establish a reference to the metadata file, describing all the interfaces exposed by ADO library, use TlbImp tool on msado.dll to create a .NET enabled assembly. This file resides, by default, in the following folder on your machine:

<System Drive>\Program Files\Common Files\System\Ado

This operation will generate a .NET assembly – ADOMD.dll – containing the metadata for the ADOMD library with ADOMD namespace. We can provide fully qualified names for all the ADOMD objects scoped within this namespace, such as ADOMD.Catalog and ADOMD.CubeDef. These fully qualified names will be essential if you are using ADOX objects along with ADOMD objects, because there is name collision in two namespaces: for example, both the namespaces contain the Catalog object:

Reference to ADOMD Assembly

To compile the ADOMD assembly generated by the TlbImp tool into the managed code, add a reference as follows:

VB.NET:

vbc /t:library /r:ADOMD.dll /out:EmployeeDB.dll EmployeeDB.vb

C#:

csc /t:library /r:ADOMD.dll /out:EmployeeDB.dll EmployeeDB.cs

Enumerating Cubes In The Catalog

The following example shows how ADOMD objects Catalog, CubeDefs, and CubeDef can be used along with an ADO Connection object to find all the cubes defined in the FoodMart data source provided with SQL Analytical Server 2000.

The code follows the following steps:

1. It creates a new instance of ADO connection by specifying the connection string. Notice that there is no InitialCatalog in the connection string. This is due to the fact that there is only one catalog per OLAP server.

2. The connection is opened with the server.

3. A new instance of Catalog object is created and the opened connection is set as its ActiveConnection property.

4. The CubeDefs property provides the collection of CubeDef objects in the database.

5. Each object in the CubeDefs collection is accessed to printout its name.

VB.NET:

Dim strConn As String = _
    "Provider=msolap; Data Source = localhost; " & _
    "Initial Catalog = FoodMart 2000; User ID=sa; Pwd="
Dim dbConn As New ClassicADO.Connection ()
dbConn.Open (strConn)
Dim dtCatalog As New ADOMD.Catalog ()
dtCatalog.ActiveConnection = CType (dbConn, Object)

Dim cubes As ADOMD.CubeDefs = dtCatalog.CubeDefs
Dim cube As ADOMD.CubeDef
For Each cube in cubes
    Console.WriteLine (cube.Name)
Next

C#:

string strConn = "Provider=msolap; Data Source = SULTAN;" +
    "Initial Catalog = FoodMart 2000; User ID =; Pwd=";
ClassicADO.Connection dbConn = new ClassicADO.Connection ();
dbConn.Open (strConn, "", "", (int)ConnectModeEnum.adModeUnknown);
ADOMD.Catalog dtCatalog = new ADOMD.Catalog ();
dtCatalog.ActiveConnection = (object)dbConn;

CubeDefs cubes = dtCatalog.CubeDefs;
Console.WriteLine ("Number of Cubes = {0}", cubes.Count.ToString ());
foreach (ADOMD.CubeDef cube in cubes)
{
    Console.WriteLine (cube.Name);
}

The output of the above example is as follows:

Number Of Cubes = 3
Sales
Warehouse
Warehouse and Sales

The above example can be extended to access the Dimension, Hierarchy, Level, and Member objects contained in the various CubeDef objects. The following C# code can be added to the above example to print out the information from Time dimension of WarehouseCubeDef:

ADOMD.CubeDef warehouse = cubes["Warehouse"];
ADOMD.Dimension timeDim = warehouse.Dimensions["Time"];
foreach (ADOMD.Level lvl in timeDim.Hierarchies[0].Levels)
{
    Console.WriteLine (lvl.Caption);
    foreach (ADOMD.Member mem in lvl.Members)
    {
     Console.WriteLine ("\t" + mem.Caption + "\t" + mem.UniqueName);
    }
}

The following screen shot shows the output of the above code.

Summary

In this chapter, we have seen how to use Interop to incorporate the features from our existing unmanaged components and other classic ADO objects. This is a very powerful mechanism provided by the .NET framework to ease the migration path from unmanaged components to managed assemblies.

There are some features that mandate the use of Interop on the existing platform. The most important of these are the transaction services. The MTS and COM+ infrastructure is COM based. Therefore, if we want to create a managed component that should be able to participate in the transactions, we will have to implement it in such a way that it can be registered as a COM component. The .NET components can be registered as native COM objects using the REGASM tool. Then the component can be installed in the COM+ explorer. Now the .NET component can participate in the transactions by using Interop capabilities. Calls have to cross the CLR boundary, therefore, performance will suffer. Therefore, Interop should only be used where there is no other .NET option available.

Copyright and Authorship Notice

This chapter is taken from ".NET Programmer's Reference" by Dushan Bilbija, Paul Dickinson, Fabio Claudio Ferracchiati, Jeffrey Hasan, Naveen Kohli, John McTanish, Matt Milner, Jan D Narkiewicz, Adil Rehan, Jon D Reid, published by Wrox Press Limited in September 2001; ISBN 186100558X; copyright © Wrox Press Limited 2001; all rights reserved.

No part of this chapter may be reproduced, stored in a retrieval system or transmitted in any form or by any means -- electronic, electrostatic, mechanical, photocopying, recording or otherwise -- without the prior written permission of the publisher, except in the case of brief quotations embodied in critical articles or reviews.