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:
- Create an ADO.Connection, supply the OLEDB Simple Provider and the ProgID of the C# custom implementation as Data Source;
- Create a ADO.RecordSet, set the ActiveConnection property to that created in step (1);
- 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
