Access to Read[] method on .Net object incorrectly flagged as syntax error, and changes needed to call NETLink functionality in a package.

Question

Mathematica can access .net objects easily via NETLink. Works well to custom assemblies.

However, I have run into a problem when trying to use Read() method on a SqlCeResultSet object returned by System.Data.SqlServerCe.dll.

Read() method is documented here for MS SQL Compact Edition (v3.5 SP2)

https://msdn.microsoft.com/en-us/library/system.data.sqlserverce.sqlcedatareader.read(v=vs.100).aspx

Below a simple script to demonstrate the problem. You will notice that in the function GetAllGuids[] the .nb editor will place a red carat on the results@Read[] and flag an error saying the Read command needs more than zero parameters (which is true for Mathematica builtin Read[] method, but not true for the referenced Read() method on the .Net Object which has no parameters.). Workbench will also flag the error in the .m file (see below re package).

The code will run correctly even though the editor flags the line as in error.

Needs["NETLink`"];
InstallNET[];
sqlceAssembly = 
  LoadNETAssembly[
"C:\\Program Files\\Microsoft SQL Server Compact Edition\\v3.5\\Desktop\\System.Data.SqlServerCe.dll"];
LoadNETType["System.Data.SqlServerCe.ResultSetOptions", sqlceAssembly];
LoadNETType["System.Data.SqlDbType"];

OpenSDFConnection[filename_] := Module[{connectionString},
Print["Open SDF file: ", filename];
(* Open a connection to the database *)
connectionString = "Data Source='" <> filename <> "'; Password='XXX'; Encrypt = FALSE; File Mode=Read Only; SSCE:Temp File Directory='C:\\Temp';";
connection = NETNew[{"System.Data.SqlServerCe.SqlCeConnection", sqlceAssembly}, connectionString];
connection@Open[];


(* Construct commands to query the database *)
cmdGetAllMeasurementIds = connection@CreateCommand[];
cmdGetAllMeasurementIds@CommandText = "SELECT DISTINCT " <> FIELDMEASID <> " FROM " <> TABLEDATA;

(* Return the filename *)
filename
]


GetAllGuids[] := Module[{results},
results = cmdGetAllMeasurementIds@ExecuteResultSet[ResultSetOptions`None];
Reap[
  While[results@Read[],
    Sow[results@GetGuid[0]@ToString[]]
  ]
][[2, 1]]
]

SdfFilename = FileNameJoin[{NotebookDirectory[], "pt2.sdf"}];
OpenSDFConnection[SdfFilename];
guids = GetAllGuids[] // TableForm

I wanted to move the database access functions to a Mathematica package to hide the implementation details and facilitate easier source control (diff, merge etc. of .m easier than .nb)

The call to a OpenSDFConnection[fn_] in a package appears to work, but Mathematica refuses to run the GetAllGuids[] method, returning the error Read::argt: Read called with 0 arguments; 1 or 2 arguments are expected.

Mathematica appears to apply parsing rules for the built in Read[] method, not the required rules for the referenced .Net Object's Read().

Is this a known issue? Am I doing something incorrect? (I am a newbie to Mathematica)

I will keep the DB script in the .nb as a work-a-round for now as I need to keep moving forward.

I am interested in suggestions how it might be put into a Mathematica package, without coding a custom .Net Assembly to act as a thin facade to System.Data.SqlServerCe.dll

Answer 1

As pointed out by @Szabolcs, the syntax highlighting in the editors is misleading and can be ignored.

To answer the question how to get the .Net assembly methods to run in a package, prefix NETLink methods in a package with the namespace. i.e. use

NETLink`InstallNET[];

rather than

InstallNET[];

See LoadNETAssembly[], LoadNETType[] and NETNew[] in following example.

(* Wolfram Language package *)
(* ::Package:: *)

BeginPackage["VasomonDatabase`"]

OpenSDFConnection::usage = "OpenSDFConnection[sdfFilename_]" 
GetAllGuids::usage = "GetAllGuids[] returns all guids in database"

Begin["`Private`"]

TABLEDATA = "Data";
TABLEPROP = "Properties";
FIELDMEASID = "MeasID";
FIELDPROPNAME = "PropName";
FIELDPROPVAL = "PropVal";
FIELDDATANAME = "DataName";
FIELDDATAVAL = "DataVal";


GetAllGuids[] := Module[{results},
results = cmdGetAllMeasurementIds@ExecuteResultSet[ResultSetOptions`None];
 Reap[
   While[results@Read[],
    Sow[results@GetGuid[0]@ToString[]]
    ]
   ][[2, 1]]
 ]

OpenSDFConnection[sdfFilename_] := Module[{connectionString},
    (* Activate NETLink if not yet loaded *)
    Needs["NETLink`"];
    NETLink`InstallNET[];
    (* get instance of SQL CE assembly and required types *)
    sqlceAssembly = NETLink`LoadNETAssembly["C:\\Program Files\\Microsoft SQL Server Compact Edition\\v3.5\\Desktop\\System.Data.SqlServerCe.dll"];
    NETLink`LoadNETType["System.Data.SqlServerCe.ResultSetOptions", sqlceAssembly];
    NETLink`LoadNETType["System.Data.SqlDbType"];

    connectionString = "Data Source='" <> sdfFilename <> "'; Password='NIBPLOGGER'; Encrypt = FALSE; File Mode=Read Only; SSCE:Temp File Directory='C:\\Temp';";
    connection = NETLink`NETNew[{"System.Data.SqlServerCe.SqlCeConnection", sqlceAssembly}, connectionString];

    (* Open a connection to the database *)
    connection@Open[];

    (* Construct commands to query the database *)
    cmdGetAllMeasurementIds = connection@CreateCommand[];
    cmdGetAllMeasurementIds@CommandText = "SELECT DISTINCT " <> FIELDMEASID <> " FROM " <> TABLEDATA;

    cmdGetMeasurementProperties = connection@CreateCommand[];
    cmdGetMeasurementProperties@CommandText = "SELECT " <> FIELDPROPNAME <> "," <> FIELDPROPVAL <> " FROM " <> TABLEPROP <> " WHERE " <> FIELDMEASID <> " = @id";
    prmGetMeasurementPropertiesId = NETLink`NETNew[{"System.Data.SqlServerCe.SqlCeParameter", sqlceAssembly}, "@id", SqlDbType`UniqueIdentifier];
    cmdGetMeasurementProperties@Parameters@Add[prmGetMeasurementPropertiesId];

    cmdGetMeasurementData = connection@CreateCommand[];
    cmdGetMeasurementData@CommandText = "SELECT " <> FIELDDATANAME <> "," <> FIELDDATAVAL <> " FROM " <> TABLEDATA <> " WHERE " <> FIELDMEASID <> " = @id";
    prmGetMeasurementDataId = NETLink`NETNew[{"System.Data.SqlServerCe.SqlCeParameter", sqlceAssembly}, "@id", SqlDbType`UniqueIdentifier];
    cmdGetMeasurementData@Parameters@Add[prmGetMeasurementDataId];

    (* Return the filename *)
    sdfFilename
  ]

End[]


EndPackage[]

You can then use the package simply from a notebook by supplying the path to the sdf file (example here for simplistic database schema with a GUID in first column)

SdfFilename = FileNameJoin[{NotebookDirectory[], "pt2.sdf"}];
OpenSDFConnection[SdfFilename];
guids = GetAllGuids[] 

Hopefully future versions of the editors will avoid the syntax highlighting errors for Methods with the same name as builtin functions. Thanks for the help.