Source Code Example - Database programming

[neeraj]

You may find the following snippets useful if you want to connect to databases and run queries. The following uses MS SQL Server LocalDB which is an inprocess database engine allowing up to 10 GB database size. Documentation says that the database files can be placed on a network drive and multiple processes from different computers can connect to it.

      TEST1;⎕USING;SQL;CMD;con;cmd
 ⎕USING←'System.Data.SqlClient,System.Data.dll'
 ⍝ ⎕USING,←⊂'System.IO'
 con←⎕NEW SqlConnection
 con.ConnectionString←'Data Source=(LocalDB)\v11.0;AttachDbFilename=C:\DB\SampleDatabase.mdf;Integrated Security=True'
 con.Open
 cmd←con.CreateCommand
 cmd.CommandText←'Use master'
 cmd.ExecuteNonQuery

      TEST2;⎕USING;SQL;CMD;con;cmd;result;reader;n
 ⎕USING←'System.Data.SqlClient,System.Data.dll'
 ⍝ ⎕USING,←⊂'System.IO'
 con←⎕NEW SqlConnection
 con.ConnectionString←'Data Source=(LocalDB)\v11.0;AttachDbFilename=C:\DB\AdventureWorks2012_Data.mdf;Integrated Security=True'
 con.Open
 cmd←con.CreateCommand
 cmd.CommandText←'SELECT COUNT(*) FROM sysobjects'
 ⎕←result←cmd.ExecuteScalar
 cmd.CommandText←'SELECT * FROM sysobjects where type=''U'''
 reader←cmd.ExecuteReader ⍬
 n←reader.FieldCount
 ⎕←reader.GetName¨¯1+⍳n
 :While reader.Read
     reader[¯1+⍳n]
 :EndWhile
 reader.Close