OLEDB Simple Provider C# Custom Implementation

I am delighted to be able to say that I have succeeded in getting a C# Custom Implementation of a Data Source for use with the OLEDB Simple Provider. The code below demonstrates how to access in-memory arrays via ADO Recordsets for use in VBA clients..

This posts follows on from both the OLEDB Simple Provider OSP Toolkit Documentation post and the OLEDB Simple Provider - C++ Sample Step Thru post; together these form the investigation and documentation posts. I have been playing detective with this technology and have stepped through an old C++ sample, Googling for details as to how it works and accumulating documentation links before they fade forever. These have now given fruit in this post which shows how to achieve the same in C#.

The OLEDB Simple Provider

Writing a full OLEDB Provider is very hard, I've had some problems compiling even the free ATL/OLEDB sample that comes with Visual Studio 2017. A full OLEDB Provider requires the full implementation of a great many COM interfaces. Active Template Library (ATL) goes some way to give C++ default implementations of these interfaces but nevertheless OLEDB is complicated. As OLEDB is being replaced by .NET technologies, OLEDB is not worth hugely investing in.

So it is excellent news to know that there is the Simple Provider which provides a framework which a developer can customise by implementing only two (at minimum) interfaces, DataSource and OLEDBSimpleProvider. This is like being given a base class from which to inherit and override a minimum number of methods. Timesaver. Lifesaver.

C# Assembly Source Code

The following source code is for a .NET Framework Assembly (I built against .NET Framework 4.6.1) , register for COM interop and run Visual Studio under Administrator rights so it can access the registry.

Hard coded demonstration tables.

So I wanted to focus on the two interfaces that require implementing, DataSource and OLEDBSimpleProvider. To that end the code below does not connect to a database for its data, or open a file or read from a block of cells from an Excel worksheet. Instead to keep things simple, it initializes an in-memory array which is the case below is hard coded.

Clearly, the array could be dynamic as part of your own application. It should also be obvious that you can write your own code to open a file acting as a database.

The code below is also usefully annotated with links to Microsoft Docs.

C# Project requires references to COM libraries MSDATASRC and MSDAOSP

To make the code below compile you will need to add COM references to two separate type libraries. I give the file locations here below, though they may differ for your installation. You can also find them by looking through a VBA Tools->References dialog for there descriptions also given below.

C:\Windows\SysWOW64\msdatsrc.tlb - MSDATASRC - Microsoft Data Source Interfaces for ActiveX Data Binding Type Library
C:\Windows\SysWOW64\simpdata.tlb - MSDAOSP   - Microsoft OLE DB Simple Provider 1.5 Library

Once compiled and registered (don't forget Visual Studio needs Administrator rights to register your assembly in the registry), then you can run the sample VBA code given below (page/scroll down).

using MSDAOSP;
using MSDATASRC;
using System;
using System.Runtime.InteropServices;
using System.Collections.Generic;
using System.Threading;

// Added COM Reference to C:\Windows\SysWOW64\msdatsrc.tlb - MSDATASRC - Microsoft Data Source Interfaces for ActiveX Data Binding Type Library
// Added COM Reference to C:\Windows\SysWOW64\simpdata.tlb - MSDAOSP   - Microsoft OLE DB Simple Provider 1.5 Library

namespace SimpleOLEDBProvider1

{
    [ClassInterface(ClassInterfaceType.None)]
    [ComDefaultInterface(typeof(MSDATASRC.DataSource))]
    [ComVisible(true)]
    public class MyDataSource : MSDATASRC.DataSource
    {
        
        List<MSDATASRC.DataSourceListener> _listListeners = new List<MSDATASRC.DataSourceListener>();
        Dictionary<string, MyCustomRecordset> _dataMembers = new Dictionary<string, MyCustomRecordset>();


        ~MyDataSource()
        {   // release all listeners
            while (_listListeners.Count > 0)
            {
                MSDATASRC.DataSourceListener head = _listListeners[0];
                head = null; // calls IUnknown::Release (hopefully)
                _listListeners.RemoveAt(0);
            }
        }

        void MSDATASRC.DataSource.addDataSourceListener(DataSourceListener pDSL)
        {   // https://docs.microsoft.com/en-us/previous-versions/windows/desktop/ms716931(v=vs.85)
            _listListeners.Add(pDSL);
        }

        dynamic MSDATASRC.DataSource.getDataMember(string bstrDM, ref Guid riid)
        {   // https://docs.microsoft.com/en-us/previous-versions/windows/desktop/ms724549(v=vs.85)

            MyCustomRecordset retval = null;

            if (_dataMembers.ContainsKey(bstrDM))
            {
                retval = _dataMembers[bstrDM];
            }
            else
            {
                retval = new MyCustomRecordset();
                retval.Initialize(bstrDM);
                _dataMembers.Add(bstrDM, retval);
            }

            return retval;
        }

        string MSDATASRC.DataSource.getDataMemberName(int lIndex)
        {   // https://docs.microsoft.com/en-us/previous-versions/windows/desktop/ms715947(v=vs.85)
            List<string> keys = new List<string>();
            keys.AddRange(_dataMembers.Keys);
            return keys[lIndex];
        }

        int MSDATASRC.DataSource.getDataMemberCount()
        {   // https://docs.microsoft.com/en-us/previous-versions/windows/desktop/ms721173(v=vs.85)
            return _dataMembers.Count;
        }

        void MSDATASRC.DataSource.removeDataSourceListener(DataSourceListener pDSL)
        {   // https://docs.microsoft.com/en-us/previous-versions/windows/desktop/ms724012(v=vs.85)
            _listListeners.Remove(pDSL);
        }
    }

    [ClassInterface(ClassInterfaceType.None)]
    [ComDefaultInterface(typeof(MSDAOSP.OLEDBSimpleProvider))]
    [ComVisible(true)]
    public class MyCustomRecordset : MSDAOSP.OLEDBSimpleProvider
    {
        private List<object[]> _tabularData = null;
        private string _bstrDM = null;
        private List<MSDAOSP.OLEDBSimpleProviderListener> _listListeners = new List<MSDAOSP.OLEDBSimpleProviderListener>();

        ~MyCustomRecordset()
        {
            while (_listListeners.Count > 0)
            {
                MSDAOSP.OLEDBSimpleProviderListener head = _listListeners[0];
                head = null; // calls IUnknown::Release (hopefully)
                _listListeners.RemoveAt(0);
            }
        }

        public void Initialize(string bstrDM)
        {
            //
            // we got some hard coded statrting tables for demonstration purposes only
            // in a real situation one would open a file or something
            //
            switch (bstrDM.ToLower())
            {
                case "customers":

                    _tabularData = new List<object[]>();
                    _tabularData.Add(new object[4] { "CustomerId", "CustomerName", "ContactName", "Country" });
                    _tabularData.Add(new object[4] { 1, "Big Corp", "Mandy", "USA" });
                    _tabularData.Add(new object[4] { 2, "Medium Corp", "Bob", "Canada" });
                    _tabularData.Add(new object[4] { 3, "Small Corp", "Jose", "Mexico" });

                    break;
                case "orders":

                    _tabularData = new List<object[]>();
                    _tabularData.Add(new object[3] { "OrderId", "CustomerId", "OrderDate" });
                    _tabularData.Add(new object[3] { 420, 2, new DateTime(2018, 10, 10) });
                    _tabularData.Add(new object[3] { 421, 3, new DateTime(2018, 10, 11) });
                    _tabularData.Add(new object[3] { 422, 1, new DateTime(2018, 10, 12) });
                    _tabularData.Add(new object[3] { 423, 2, new DateTime(2018, 10, 13) });

                    break;
                default:
                    // if unrecognised hand back default of colors
                    _tabularData = new List<object[]>();
                    _tabularData.Add(new object[2] { "ColorName", "ColorRGB" });
                    _tabularData.Add(new object[2] { "Red", "FF0000" });
                    _tabularData.Add(new object[2] { "Green", "00FF00" });
                    _tabularData.Add(new object[2] { "Blue", "0000FF" });
                    break;
            }

            _bstrDM = bstrDM;
        }

        void OLEDBSimpleProvider.addOLEDBSimpleProviderListener(OLEDBSimpleProviderListener pospIListener)
        {   // https://docs.microsoft.com/en-us/previous-versions/windows/desktop/ms713697(v=vs.85)
            _listListeners.Add(pospIListener);
        }

        int OLEDBSimpleProvider.deleteRows(int iRow, int cRows)
        {   // https://docs.microsoft.com/en-us/previous-versions/windows/desktop/ms713665(v=vs.85)

            // Notify our Listeners:
            foreach (OLEDBSimpleProviderListener listener in _listListeners)
                listener.aboutToDeleteRows(iRow, cRows);

            _tabularData.RemoveRange(iRow, cRows);

            // Notify our Listeners:
            foreach (OLEDBSimpleProviderListener listener in _listListeners)
                listener.deletedRows(iRow, cRows);

            return cRows;
        }

        int OLEDBSimpleProvider.find(int iRowStart, int iColumn, object val, OSPFIND findFlags, OSPCOMP compType)
        {   // https://docs.microsoft.com/en-us/previous-versions/windows/desktop/ms709764(v=vs.85)
            // not yet implemented
            return -1;
        }


        int OLEDBSimpleProvider.getColumnCount()
        {   // https://docs.microsoft.com/en-us/previous-versions/windows/desktop/ms715965(v=vs.85)

            object[] colHeaders = _tabularData[0];
            int totalColumnCount = colHeaders.Length;
            return totalColumnCount;
        }

        int OLEDBSimpleProvider.getEstimatedRows()
        {   // https://docs.microsoft.com/en-us/previous-versions/windows/desktop/ms713703(v=vs.85)
            // should not include row header

            int totalRowCount = _tabularData.Count;
            return (totalRowCount - 1);
        }

        string OLEDBSimpleProvider.getLocale()
        {   // https://docs.microsoft.com/en-us/previous-versions/windows/desktop/ms711216(v=vs.85)
            //return "en-us";
            var name = Thread.CurrentThread.CurrentCulture.Name;
            var locale = name.Split('-')[1];
            return locale;
        }

        int OLEDBSimpleProvider.getRowCount()
        {
            // https://docs.microsoft.com/en-us/previous-versions/windows/desktop/ms715931(v=vs.85)
            // should not include row header

            int totalRowCount = _tabularData.Count;
            return (totalRowCount - 1);
        }

        OSPRW OLEDBSimpleProvider.getRWStatus(int iRow, int iColumn)
        {
            // https://docs.microsoft.com/en-us/previous-versions/windows/desktop/ms709946(v=vs.85)
            return OSPRW.OSPRW_READWRITE;
        }

        dynamic OLEDBSimpleProvider.getVariant(int iRow, int iColumn, OSPFORMAT format)
        {
            // https://docs.microsoft.com/en-us/previous-versions/windows/desktop/ms725341(v=vs.85)

            if (iRow < _tabularData.Count)
            {
                object[] row = _tabularData[iRow];
                return row[iColumn - 1];  // columns start at 1 it seems
            }
            else
            {
                // oddly, when a record is removed we still get called for dead row
                // so until we diagnose and fix this bug return a null
                return null;
            }
        }

        int OLEDBSimpleProvider.insertRows(int iRow, int cRows)
        {   // https://docs.microsoft.com/en-us/previous-versions/windows/desktop/ms721207(v=vs.85)

            object[] colHeaders = _tabularData[0];
            int totalColumnCount = colHeaders.Length;

            object[] blankRow = new object[totalColumnCount];

            // Notify our Listeners:
            foreach (OLEDBSimpleProviderListener listener in _listListeners)
                listener.aboutToInsertRows(iRow, cRows);


            for (int i = 0; i < cRows; i++)
            {
                _tabularData.Insert(iRow, blankRow);
            }

            // Notify our Listeners:
            foreach (OLEDBSimpleProviderListener listener in _listListeners)
                listener.insertedRows(iRow, cRows);

            return cRows;
        }

        int OLEDBSimpleProvider.isAsync()
        {   // https://docs.microsoft.com/en-us/previous-versions/windows/desktop/ms723042(v=vs.85)
            return 0; // not async
        }


        void OLEDBSimpleProvider.removeOLEDBSimpleProviderListener(OLEDBSimpleProviderListener pospIListener)
        {   // https://docs.microsoft.com/en-us/previous-versions/windows/desktop/ms712993(v=vs.85)
            _listListeners.Remove(pospIListener);
        }

        void OLEDBSimpleProvider.setVariant(int iRow, int iColumn, OSPFORMAT format, object Var)
        {   // https://docs.microsoft.com/en-us/previous-versions/windows/desktop/ms714366(v=vs.85)

            // Notify our Listeners:
            foreach (OLEDBSimpleProviderListener listener in _listListeners)
                listener.aboutToChangeCell(iRow, iColumn);

            object[] row = _tabularData[iRow];
            row[iColumn - 1] = Var;

            _tabularData[iRow] = row;

            // Notify our Listeners:
            foreach (OLEDBSimpleProviderListener listener in _listListeners)
                listener.cellChanged(iRow, iColumn);

        }

        void OLEDBSimpleProvider.stopTransfer()
        {   // https://docs.microsoft.com/en-us/previous-versions/windows/desktop/ms722788(v=vs.85)
            // we do not handle async ops
        }
    }
}

VBA Client Code

So now you have compiled and registered above C# code now you can run client VBA code. You will need a reference to an ADO type library.

So to get an ADO recordset created by the C# custom implementation one does the following:

1. Create an ADO.Connection, supply the OLEDB Simple Provider and the ProgID of the C# custom implementation as Data Source;
2. Create a ADO.RecordSet, set the ActiveConnection property to that created in step (1);
3. Call the newly created recordSet's Open method supplying the table name.

In given example, the string passed into the RecordSet.Open method is a table name but one can write the code to parse and evaluate a more complicated expression.

Looking through the client code, you'll be able I have commented out some lines which call Recordset.Seek or Recordset.Sort methods because they are not supported and throw errors.

    'rsColors.Seek "Green"  '* throws error "Current provider does not support the necessary interface for Index functionality."
    'rsColors.Sort = "colorName" '* throws error "Current provider does not support the necessary interfaces for sorting or filtering."

But actually you can seek using the Recordset.Filter method. You can add and delete records happily, don't forget to call Recordset.Update. Code to add and delete records is given.

On the matter of sorting you could supply more text to the Recordset.Open method such as a 'Sort By <fieldName>' clause and change the implementation from being a List to a sortable collection class.

In fact you can supply so much more in the string passed to the Recordset.Open method. You could pass a whole SQL command just like other OLEDB providers but you will have to write your own SQL parsing code.

Also in the code below we take a snapshot of the recordset into a variant array. Snapshots are driven by the Recordset.GetRows method. This is also the same logic that drives the Range.CopyFromRecordset method which allows quick writing of a recordset to a block of cells (some commented out code for this is also given).

Enjoy!

Option Explicit

'* Tools->References
'ADODB      Microsoft ActiveX Data Objects 6.1 Library  C:\Program Files (x86)\Common Files\System\ado\msado15.dll

Sub TestCustomSimpleProvider()
    Dim vVarArray
    

    Dim oConn As ADODB.Connection
    Set oConn = New ADODB.Connection
    
    '*
    '* SimpleOLEDBProvider1.MyDataSource is progid of the custom simple provider
    '*
    oConn.Open "Provider=MSDAOSP;Data Source=SimpleOLEDBProvider1.MyDataSource"
    
    Dim rsColors As ADODB.Recordset
    Set rsColors = New ADODB.Recordset

    Set rsColors.ActiveConnection = oConn

    rsColors.Open "colors"
    
    '*
    '* what it doesn't do:
    '* (a) seeking on an index
    '* (b) sorting
    '*
    
    'rsColors.Seek "Green"  '* throws error "Current provider does not support the necessary interface for Index functionality."
    'rsColors.Sort = "colorName" '* throws error "Current provider does not support the necessary interfaces for sorting or filtering."
    
    '*
    '* what it does do:
    '* (1) filtering
    '* (2) adding
    '* (3) removing
    '*
    
    Debug.Assert rsColors.RecordCount = 3
    rsColors.Filter = "colorName='Green' or colorName='Red'"
    rsColors.Update
    Debug.Assert rsColors.RecordCount = 2
    
    rsColors.Filter = ""
    
    rsColors.AddNew
    rsColors!ColorName = "Yellow"
    rsColors!ColorRGB = "FFFF00"
    rsColors.Update
    
    Debug.Assert rsColors.RecordCount = 4
    
    rsColors.MoveFirst
    rsColors.Delete
    rsColors.Update
    
    Debug.Assert rsColors.RecordCount = 3
    
    
    rsColors.MoveFirst
    vVarArray = Application.WorksheetFunction.Transpose(rsColors.GetRows)
    Stop

    Dim rsCustomers As ADODB.Recordset
    Set rsCustomers = New ADODB.Recordset

    Set rsCustomers.ActiveConnection = oConn
    rsCustomers.Open "customers"

    vVarArray = Application.WorksheetFunction.Transpose(rsCustomers.GetRows)
    Stop
    
    Dim rsOrders As ADODB.Recordset
    Set rsOrders = New ADODB.Recordset
    
    Set rsOrders.ActiveConnection = oConn
    
    rsOrders.Open "Orders"
    vVarArray = Application.WorksheetFunction.Transpose(rsOrders.GetRows)

    '* uncomment the following to test writing whole recordset to worksheet in one go
    'Dim rng As Excel.Range
    'Set rng = ThisWorkbook.Worksheets.Item(1).Cells(1, 1)
    'rsOrders.MoveFirst
    'rng.CopyFromRecordset rsOrders
    
    Stop
End Sub

Links

• Excel Development Platform - OLEDB - C++ - Solved: ATL's OLEDB Provider sample crashes Excel (uncaught exception from msado15.dll)