DYALOG APL, Connect to SQL Server using .NET and load a DataTable with data using SqlConnection

Question

DYALOG APL, Connect to MS SQL Server using .NET and load a DataTable with data using SqlConnection.

I do not want to use SQAPL.
I want to use DataTable as they can be used as data sources to GUI components, and want to use DataTable methods and libraries I have experience with.
SQAPL also needs DSN on client machine if I am not mistaken.

I want to connect to SQL Server using .NET and load a DataTable with data, using the SqlConnection class.

I do not mind using an older version .Net such as 4.5

      ]version
 Dyalog  18.2.45405 64-bit Unicode, BuildID 8ebc0eba            
 OS      Windows 10 or Windows Server 2016 (10.0.19045) 64-bit  
 Link    3.0.19                                                 
 SALT    2.9                                                    
 UCMD    2.51                                                   
 .NET    .NET Framework 4.8.9166.0                              
 WS      18.2  

      ⎕USING←'' 'System.Data.SqlClient'
      sqlConnection1 ← ⎕NEW SqlConnection VarConnectionString
VALUE ERROR: Undefined name: SqlConnection

Answer 1

With .NET Framework (4.8) it is very straightforward:

      ⎕USING←'System.Data.SqlClient,System.Data.dll'
      c←⎕NEW SqlConnection (⊂'Server=myServerName,myPortNumber;Database=myDataBase;User Id=myUsername;Password=myPassword;')
      c.⎕NL -3 ⍝ Methodlist
BeginTransaction  ChangeDatabase  ChangePassword  ClearAllPools  ClearPool  Close  CreateCommand  CreateObjRef  Dispose EnlistDistributedTransaction  EnlistTransaction  Equals  GetHashCode  GetLifetimeService  GetSchema  GetType  InitializeLifetimeService  Open  OpenAsync  ReferenceEquals  RegisterColumnEncryptionKeyStoreProviders  ResetStatistics  RetrieveStatistics  ToString

---

SQAPL also needs DSN on client machine if I am not mistaken.

SQAPL does NOT require defining a DSN; you can provide a connect string instead.