COM and .Net Interop (A C# Sybase Sql Interpreter)

An introduction to .Net Interoperation Services. We develop a COM object providing access to Sybase databases for .Net applications.

Prologue

This document arose when I began to seriously think about connecting to Sybase databases from C# applications. My first attempt was to use the ODBC layer. It was immediately clear that this approach was not going to work. It was about 100 times slower than the usual CT-Library/C++ solution. The framework comes equipped with native, managed drivers for Oracle and Microsoft SQL Server, but not for Sybase. This is not surprising since as far as I know, this database is not widely used outside of the financial district in the United States. As such this article may have a limited audience outside Wall St. So, what can we do? There is no question about it, COM is an entrenched technology. Like most programmers using Microsoft technology, we have used and developed COM objects over the years. With the Interoperation services available in .Net an intriguing solution came to mind. Build a COM object that would expose a subset of the Sybase CT-Library API, and use it to access the Sybase database from .Net/C#. We'll begin with a tiny introduction to .Net Interoperation.

Introduction to .Net Interop

.Net interoperation is the mechanism by which managed code can expose and be exposed to unmanaged code. There are two types of interaction between .Net applications and unmannaged COM code. The first is the ability of .Net clients to call methods implemented by COM interfaces. This is made possible by the Runtime Callable Wrapper (RCW). The other part of the equation is the ability of calling .Net methods from COM code. This is made possible by the COM Callable Wrapper (CCW). There are two main sub-topics that involve RCWs. The first uses the PInvoke service to access the Win32 API. The second uses the type library importer tlbimp.exe to access custom COM code. The "hello world" example of RCW is the Win32 API MessageBox example:


      using System;
      using System.Runtime.InteropServices;
      namespace iopmb
      {
        class Win32Wrapper
        {
          [DllImport("user32.dll", EntryPoint="MessageBoxW", CharSet=CharSet.Auto, ExactSpelling=true)]
          public static extern int MsgBox (int hwnd, string txt, string cap, int type);
        }

        class Class1
        {
          [STAThread]
          static void Main(string[] args)
          {
            Win32Wrapper.MsgBox(0, "Hello Narendra!", "C# MsgBox", 0);
          }
        }
      }

The DllImport attribute is used by the InteropServices to determine where and how to invoke the Win32 call. The argument ExactSpelling indicates that the EntryPoint parameter is the exact name of the method to call. No overhead is incured by the runtime matching the correct method in the specified dll. Note the non-optional "static extern" method decoration. The program produces the message dialog box:

RCW seeks, as much as possible, to hide COM details from .Net clients. The CLR uses its Garbase Collector to manage object lifetimes, but COM uses a counting method, involving the classic AddRef() and Release methods of the IUnknown interface. The RCW handles this counting for us. What's involved in calling custom COM methods is the subject of the main example, the construction of a COM object to access Sybase databases. The following is a basic RCW diagram.

An unmannaged COM Sybase Driver

This section assumes a knowledge of COM and ATL programming, as well as a thorough understanding of Sybase CT-Library programming. I used an inproc server for the COM object since we'll be dealing with potentially large amounts of data. The approach is to start with a standard ATL/COM dll project and build on it. We need only construct a single interface and expose all our CT-Lib methods in it. For this simple program I only implement three CT-Lib calls, ct_connect(), ct_send(), and ct_fetch(). These I expose as co_connect(), co_send() and co_fetch() in the Isyb interface we construct.


      // The Isyb COM interface generated by the ATL IDE.
      interface Isyb : IUnknown
      {
        [helpstring("method co_connect")] HRESULT co_connect(BSTR user,   BSTR passwd,
                                                             BSTR dbsrvr, BSTR host);
        [helpstring("method co_fetch")] HRESULT co_fetch(BSTR* row, BSTR* heading);
        [helpstring("method co_send")] HRESULT co_send(BSTR sqlstr, [out, retval]int* ret);
      };

These are the most important parts of the CT-Lib API, as far as connecting and doing a query. The co_connect() method does the standard CT-Lib connection to a database: Allocate context and connection objects, apply the properties and call ct_connect(). The co_send() method is also straightforward: Setup a command structure with the required query and call ct_send(). I pass the return code back to the caller via an int pointer:

STDMETHODIMP Csyb::co_send(BSTR sqlstr, int* ret) ...

The final API, co_fetch() is a little more involved since I combined the main CT-Lib API's used to fetch results in this one COM call. In CT-Lib, two loops are used to collect result sets. The outer loop controlled by ct_results() and the inner loop by ct_fetch(). Note, I am only concerned with ct_results() of type CS_ROW_RESULT, there are many others. I also separate the heading of the result set from the actual result set into two BSTR return types.

STDMETHODIMP Csyb::co_fetch(BSTR* row, BSTR* heading) ...

This way a caller may disregard the heading if necessary. A code fragment follows, see the zip file for the complete code.


      STDMETHODIMP Csyb::co_fetch(BSTR* row, BSTR* heading)
      {
        USES_CONVERSION;
        while ((ct_results (cmd, &res_type)) == CS_SUCCEED)
        {
          if (res_type == CS_ROW_RESULT)

          //  *** CODE DELETED  ***

          retcode = CS_SUCCEED;
          while (retcode == CS_SUCCEED)
          {
            retcode = ct_fetch(cmd, CS_UNUSED, CS_UNUSED, CS_UNUSED, &rows_read);
            if (retcode == CS_SUCCEED)
            {
              for (i = 0; i < numcols; i++)
              {
                br += d[i].val;
                br += L"\t";
              }
              br += L"\n";
          }  // inner while
        } // outer while

        //  *** CODE DELETED  ***

      }

Build the project to create the sybcom.dll library.

A C# Sybase Sql Interpreter

In order to run this program, you must have both access to a Sybase server and a correctly configured client environment. If you can log into your Sybase database from isql then you should be fine. First open the Visual Studio .Net Command Prompt and goto the directory containing the newly created sybcom.dll library. Invoke the type library importer with the following:

tlbimp.exe symcom.dll /out:sybcomassembly.dll

We just generated a .Net assembly. It contains all the information necessary to correctly call our COM interface methods. Open the new assembly with ILDasm and take a look at the manifest. Notice how the methods of the interface are mapped, see the diagram highlighted in grey.

The parameters with [in] attributes are mapped as C# in parameters, the [out] attribute parameters are C# references and the [out, retval] attribute is a return value.

To call these methods from C# (.Net) we simply reference the assembly and instiantiate the COM object. Then we are free to call the COM interface methods as any other C# method. Notice how output parameters are passed as ref, and the [out, retval] parameter is mapped to a return value automatically.


				...
      syb = new sybClass();
      syb.co_connect(muser, mpasswd, mserver, mhost);
				...
      if (sc.syb.co_send(sqltxt.Text) == 0)
      {
        string row_data = null;
        string heading_data = null;
        sc.syb.co_fetch(ref row_data, ref heading_data);
      }
				...

The Sql Interpreter is very simple. It consists of two paneled RichTextBoxes separated by a splitter. Enter sql queries in the top box and see the results in the bottom box.

Download sybseinterop.zip

Conclusion

I must stress that the COM object presented here for accessing Sybase databases is not production quality. In particular there is little in the way of error checking. Also, it only provides access to returned row result sets, not return parameters, or stored procedure return codes. However it is very fast since it uses the same method a C++/CT-Lib programmer would use. Finally there is a commercially available, managed Sybase driver from DataDirect, it is also very fast. You may also want to consider that option.

References

ATL Developer's Guide - Tom Armstrong (The best COM book I have read)

Begining ATL COM - Richard Grimes

Professional DCOM Programming - Richard Grimes

Essential COM - Don Box

Open Client Client-Library/C Reference Manual - Sybase Corp.

The excellent Interop articles on this and other sites.

December 22, 2002