Returning Datatable to R from C#/.NET

Question

I'm trying to call a .NET DLL file from R and return a Datatable.

This Q&A shows how to call .NET from R and that works, but I can't find any documentation on how to return data (apart from simple values like a string):

.NET Method:

public DataTable GetDatatable(string parameters)
{
    var dt = new DataTable();
    dt.Columns.Add("Dates", typeof(DateTime));
    dt.Columns.Add("Strings", typeof(String));
    dt.Columns.Add("Number", typeof(Double));

    DataRow dr = dt.NewRow();
    dr[0] = new DateTime(2014, 1, 23);
    dr[1] = "test";
    dr[2] = 123.45;

    dt.Rows.Add(dr);
    return dt;
}

Inside R:

> library(rClr)
> clrLoadAssembly('C:/Dev/Sandbox/XYZ/bin/Debug/XYZ2R.dll')
> myObj <- clrNew('XYZ2R.DAL,XYZ2R')
> res = clrCall(myObj,'GetDatatable', "parameter")
> res

An object of class "cobjRef"
Slot "clrobj":
<pointer: 0x0027f218>

Slot "clrtype":
[1] "System.Data.DataTable"

res[0,1]
Error in res[0, 1] : object of type 'S4' is not subsettable

From what I've read it appears I would need to pass in 'by ref' arguments to a params, for example:

public void Test(string parameters, params List<string>[] data)

Though I am not sure and there isn't any examples of calling .NET methods from R and returning lists. There are a few articles that discuss the opposite.

How can I do this?

score 2 · Accepted Answer · edited Feb 26 '19 at 07:49

With the currently released package, and using your test method, the following retrieves the items correctly

library(rClr)
clrLoadAssembly('c:/tmp/DataTableTest/bin/Debug/DataTableTest.dll')
obj <- clrNew('DataTableTest.Class1')
dataTable <- clrCall(obj, 'GetDatatable', '')
clrReflect(dataTable)
dv <- clrGet(dataTable, 'DefaultView')
clrReflect(dv)
getTableItem <- function(view, rowInd, colInd) {
  dataRowView <- clrCall(view, 'get_Item', as.integer(rowInd))
  clrCall(dataRowView, 'get_Item', as.integer(colInd))
}
getTableItem(dv, 0L, 0L)
getTableItem(dv, 0L, 1L)
getTableItem(dv, 0L, 2L)

Over the next few months I'll be working on some syntactic improvements; I take note that DataTable and related classes are a natural sibling to data frames, and will try to offer a more concise syntax for it. Feel free to log detailed feature request at ~~https://rclr.codeplex.com~~ https://github.com/jmp75/rClr

Thanks very much for this, I agree a Datatable to Dataframe is a good idea and a more concise syntax would be good. I will follow your developments. Thanks again. — Jeremy Thompson, Apr 08 '14 at 00:40

score 1 · Answer 2 · edited Dec 10 '21 at 08:29

To inspect methods and properties of .NET objects from R is easy by using the clrReflect method:

> clrReflect(dataTable)
$Methods
[1] "AcceptChanges" "add_ColumnChanged" "add_ColumnChanging" "add_Disposed" "add_Initialized" "add_RowChanged"
[7] "add_RowChanging" "add_RowDeleted" "add_RowDeleting" "add_TableCleared" "add_TableClearing" "add_TableNewRow"
[13] "BeginInit" "BeginLoadData" "Clear" "Clone" "Compute" "Copy"
[19] "CreateDataReader" "Dispose" "EndInit" "EndLoadData" "Equals" "get_CaseSensitive"
[25] "get_ChildRelations" "get_Columns" "get_Constraints" "get_Container" "get_DataSet" "get_DefaultView"
[31] "get_DesignMode" "get_DisplayExpression" "get_ExtendedProperties" "get_HasErrors" "get_IsInitialized" "get_Locale"
[37] "get_MinimumCapacity" "get_Namespace" "get_ParentRelations" "get_Prefix" "get_PrimaryKey" "get_RemotingFormat"
[43] "get_Rows" "get_Site" "get_TableName" "GetChanges" "GetChanges" "GetErrors"
[49] "GetHashCode" "GetObjectData" "GetService" "GetType" "ImportRow" "Load"
[55] "Load" "Load" "LoadDataRow" "LoadDataRow" "Merge" "Merge"
[61] "Merge" "NewRow" "ReadXml" "ReadXml" "ReadXml" "ReadXml"
[67] "ReadXmlSchema" "ReadXmlSchema" "ReadXmlSchema" "ReadXmlSchema" "RejectChanges" "remove_ColumnChanged"
[73] "remove_ColumnChanging" "remove_Disposed" "remove_Initialized" "remove_RowChanged" "remove_RowChanging" "remove_RowDeleted"
[79] "remove_RowDeleting" "remove_TableCleared" "remove_TableClearing" "remove_TableNewRow" "Reset" "Select"
[85] "Select" "Select" "Select" "set_CaseSensitive" "set_DisplayExpression" "set_Locale"
[91] "set_MinimumCapacity" "set_Namespace" "set_Prefix" "set_PrimaryKey" "set_RemotingFormat" "set_Site"
[97] "set_TableName" "ToString" "WriteXml" "WriteXml" "WriteXml" "WriteXml"
[103] "WriteXml" "WriteXml" "WriteXml" "WriteXml" "WriteXml" "WriteXml"
[109] "WriteXml" "WriteXml" "WriteXml" "WriteXml" "WriteXml" "WriteXml"
[115] "WriteXmlSchema" "WriteXmlSchema" "WriteXmlSchema" "WriteXmlSchema" "WriteXmlSchema" "WriteXmlSchema"
[121] "WriteXmlSchema" "WriteXmlSchema"

$Fields
character(0)

$Properties
[1] "CaseSensitive" "ChildRelations" "Columns" "Constraints" "Container" "DataSet" "DefaultView" "DesignMode"
[9] "DisplayExpression" "ExtendedProperties" "HasErrors" "IsInitialized" "Locale" "MinimumCapacity" "Namespace" "ParentRelations"
[17] "Prefix" "PrimaryKey" "RemotingFormat" "Rows" "Site" "TableName"

> clrReflect(dv)
$Methods
[1] "add_Disposed" "add_Initialized" "add_ListChanged" "AddNew" "BeginInit" "CopyTo" "Delete" "Dispose"
[9] "EndInit" "Equals" "Equals" "Find" "Find" "FindRows" "FindRows" "get_AllowDelete"
[17] "get_AllowEdit" "get_AllowNew" "get_ApplyDefaultSort" "get_Container" "get_Count" "get_DataViewManager" "get_DesignMode" "get_IsInitialized"
[25] "get_Item" "get_RowFilter" "get_RowStateFilter" "get_Site" "get_Sort" "get_Table" "GetEnumerator" "GetHashCode"
[33] "GetService" "GetType" "remove_Disposed" "remove_Initialized" "remove_ListChanged" "set_AllowDelete" "set_AllowEdit" "set_AllowNew"
[41] "set_ApplyDefaultSort" "set_RowFilter" "set_RowStateFilter" "set_Site" "set_Sort" "set_Table" "ToString" "ToTable"
[49] "ToTable" "ToTable" "ToTable"

$Fields
character(0)

$Properties
[1] "AllowDelete" "AllowEdit" "AllowNew" "ApplyDefaultSort" "Container" "Count" "DataViewManager" "DesignMode" "IsInitialized" "Item" "RowFilter" "RowStateFilter" "Site" "Sort" "Table"

To call methods use clrCall(dotNetObject, 'MethodName')

To see properties use clrGet(dotNetObject,'PropertyName')

For example let's work out the number of Rows in the DataTable returned from the GetDatatable method:

> clrCall(dv,'get_Count')
[1] 2

Now let's work out the number of columns:

> clrGet(dataTable,'Columns')
An object of class "cobjRef"
Slot "clrobj":
<pointer: 0x0ee0f920>

Slot "clrtype":
[1] "System.Data.DataColumnCollection"

> cols <- clrGet(dataTable,'Columns')
> clrReflect(cols)
$Methods
[1] "Add" "Add" "Add" "Add" "Add" "add_CollectionChanged"
[7] "AddRange" "CanRemove" "Clear" "Contains" "CopyTo" "CopyTo"
[13] "Equals" "get_Count" "get_IsReadOnly" "get_IsSynchronized" "get_Item" "get_Item"
[19] "get_SyncRoot" "GetEnumerator" "GetHashCode" "GetType" "IndexOf" "IndexOf"
[25] "Remove" "Remove" "remove_CollectionChanged" "RemoveAt" "ToString"

$Fields
character(0)

$Properties
[1] "Count" "IsReadOnly" "IsSynchronized" "Item" "Item" "SyncRoot"

> clrGet(cols,'Count')
[1] 26

Returning Datatable to R from C#/.NET

2 Answers2