3

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
Mike Chris
  • 89
  • 6
  • 1
    What is `SQAPL`? – Dai Jul 12 '23 at 20:35
  • 1
    And what are the funny boxes in your code block? – Dale K Jul 12 '23 at 20:55
  • 1
    And what language are you writing in, and why do you think it can use .NET components? – David Browne - Microsoft Jul 12 '23 at 20:59
  • 1
    Dyalog APL is very good at using .NET components. See https://docs.dyalog.com/latest/dotNET%20Core%20Interface%20Guide.pdf – xpqz Jul 12 '23 at 21:35
  • 1
    Have you tried [reading the friendly manual](https://docs.dyalog.com/latest/SQL%20Interface%20Guide.pdf)? _It is possible to connect to databases for which there is no pre-defined data source – see the description of the SQA.Connect function, in particular the DriverOptions parameter._ The implication being that you can pass in ODBC connection properties like `Server`. – AlwaysLearning Jul 12 '23 at 21:39
  • 1
    @AlwaysLearning That's the SQAPL OP *doesn't* want to use. – Adám Jul 13 '23 at 07:19
  • 1
    To a Dyalog APL user, this question is perfectly clear, with almost all the detail one could wish for. @MikeChris Can you add Dyalog and .NET versions to your post? Type `]version` into the session to get the info. – Adám Jul 13 '23 at 07:21
  • 1
    @DaleK Dyalog APL is full of funny symbols, inlcuding `⎕`, called "quad". It is correctly rendered as a box. – xpqz Jul 13 '23 at 08:05
  • 1
    You probably don't need/want to tag sql server... you're just attracting experts who cannot help. Dyalog and Apl seem the only relevant tags. – Dale K Jul 13 '23 at 08:21
  • 1
    @Adám I was disproving OP's comment, _SQAPL also needs DSN on client machine if I am not mistaken._ – AlwaysLearning Jul 13 '23 at 10:08

2 Answers2

4

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.

Adám
  • 6,573
  • 20
  • 37
  • Thanks a lot Adam, Success !!! So the SQLClient Namespace is in System.Data.dll I finally got the DataTable. – Mike Chris Jul 13 '23 at 09:39
4

With the Help of Adám, this is the code I used to get the DataTable from MS SQL Server.

⎕USING←'System.Data.SqlClient,System.Data.dll'
⎕USING←'System.Data,System.Data.dll'
c←⎕NEW SqlClient.SqlConnection (⊂'Server= 1.1.1.2;Database=dbName;User 
Id=sa;Password=sdsdedf;')
sqlcmd ← ⎕NEW SqlClient.SqlCommand
sqlAddapter ← ⎕NEW SqlClient.SqlDataAdapter
sqlAddapter.FillLoadOption ← LoadOption.OverwriteChanges
sqlcmd.CommandTimeout←2400
sqlcmd.Connection←c
sqlcmd.CommandType←  CommandType.Text
sqlcmd.CommandText←  'Select top(10) * from Companies'
sqlAddapter.SelectCommand←sqlcmd
tmpDataset ← ⎕NEW DataSet 
sqlAddapter.SelectCommand←sqlcmd
sqlAddapter.Fill tmpDataset 'Table'
sqlAddapter.Dispose
tblCompanies ← tmpDataset.Tables[0]
Adám
  • 6,573
  • 20
  • 37
Mike Chris
  • 89
  • 6