CLR refresher

[stanl]

Just want to confirm this is the correct approach:  I am working to connect to an Oracle database without the Oracle client installed, so no listener or TNSNames.ora file.  This can be accomplished with the ODP.Net assembly from Oracle.  To get data you cannot use a connection or command but the dataadapter object can place sql returns into a .net dataset with the proper connect string.  I am able to do this in Powershell with

Add-Type -Path "C:\ODAC\odp.net4\odp.net\managed\common\Oracle.ManagedDataAccess.dll"

My question is if I wanted to use the WB CLR would my script have

ObjectClrOption("AppBase", "C:\ODAC\odp.net4\odp.net\managed\common")
ObjectClrOption("use","Oracle.ManagedDataAccess.dll")

in order to then replicate the rest of the Powershell code?  May seem a dumb question, but I have to compile a script at home then bring to work on a USB, so it helps to get it right the first time.

[td]

Your code as displayed is the proper approach to loading a non-GAC assembly into the WinBatch app domain. 

[stanl]

Thanks Tony.  The not so good news is when I ran a compiled script (I made 3 versions since I was also using ObjectClrOption("use","System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089") )  and received errors either 'DLL could not be found' ; 'Runtime already loaded' , 'unable to load assembly or one of its dependencies'

Good news is I just called the code from an existing EXE I compiled that loaded Powershell assemblies then ran PS code.. and no problems.  There is also an unmanaged dll in ODC.NET if that might be a deal breaker.

[td]

 I have used non-GAC assemblies like sqlite among others and have never had any issues.  May be a version conflict of some kind.  Newer versions of WinBatch have the "useany" option  that can resolve version issues.  Of course, it could be a completely different issue but it would require more information to diagnose. 

That said,  since you have something working, no reason to pursue it any further.

[stanl]

That said,  since you have something working, no reason to pursue it any further.

Working but not the most desirable. I have read threads of others in C#, PS having issues with the managed dll. So I will try the code with the unmanaged dll.  The Powershell solution can produce a grid or output in a number of ways, except one - passing the dataset or datatable back to WB, whereas if I used the CLR I would have the additional functionality.

But what is super nice (as I had others run the exe from their desktops with no Oracle stuff installed, no admin rights) is that the dll works to return Oracle data from a remote network server, the key being the code never opens a connection object but uses only the dataadapter and dataset.

Our credit cards got hacked again but expect new ones by today or tomorrow and will renew my compiler and maybe we can pick up this discussion with the latest version.

[td]

I failed to notice that you included the assembly's file extension in the "use" option.  That is generally a no-no.  In most but not all cases the CLR will cough up some kind of error when you use a file name instead of an assembly name to load an assembly into the app domain.   The assembly name is generally just the file name without the extension.

I apologize for not noticing that immediately.  It has been on the hectic side around here for the last couple of months so maintaining focus has been a bit of a challenge.

[stanl]

Understood. I removed the extension and the dll loads but access to the adapter object generated a real strange error (see attached)

[td]

Without getting into WIL implementation details, that error is sometimes but not always the result of a class or base class constructor not being accessible via reflection.  Generally, in well written classes you can still create an instance of the class by using a default constructor that does not require parameters.  You can then set the values you wanted to use in the constructor via class properties.   

Another possibility is that one of the parameters is not being typed correctly and the wrong constructor is being called by the CLR.  Generally, this would result in no matching constructor found error but if there is a private constructor that matches the signature, you could get the COM error.

The third possibility is that the assembly is written in such a manor that you are unable to use it in WinBatch.  WinBatch needs to work the way it does so nothing could be done about that.

You could try a bit of experimentation or consult the assembly's documentation about constructors and properties, if you wish to pursue this further.  You seem to have a knack for finding the odd assembly duck.

[stanl]

Probably Oracle... super proprietary.  My base PS code is only 8 lines and the tests I am doing use the Out-Gridview.  If I change to .csv I can have the WB script look for the file then manipulate accordingly. Time would not be of any essence.