닷넷에서 접근해보는 InterSystems의 IRIS Data Platform 데이터베이스

처음 듣는 데이터베이스입니다. ^^ 아래의 경로를 통해 community 버전을 다운로드 신청할 수 있고,

InterSystems IRIS Community Edition Download Registration
; http://download.intersystems.com/download/register.csp

InterSystems IRIS Data Platform 2020.2 > Multi-model Data Access > InterSystems SQL Reference > SQL Commands > CREATE TABLE
; https://docs.intersystems.com/irislatest/csp/docbook/DocBook.UI.Page.cls?KEY=RSQL_createtable

CREATE TABLE Employee (
     EMPID INT NOT NULL,
     NAME   CHAR(100) NOT NULL,
     AGE  INT,
     CONSTRAINT EMPLOYEEPK PRIMARY KEY (EMPID))

C:\InterSystems\IRIS\dev\dotnet> tree
Folder PATH listing
Volume serial number is D089-B7C9
C:.
└─bin
    ├─Core21
    │  └─Gateway
    │      └─runtimes
    │          └─win
    │              └─lib
    │                  └─netstandard2.0
    ├─CorePackages
    ├─v2.0.50727
    ├─v4.0.30319
    └─v4.5

using InterSystems.Data.IRISClient;
using System;
using System.Data;

namespace ConsoleApp1
{
    class Program
    {
        static void Main(string[] args)
        {
            TestDB();
        }

        static void TestDB()
        {

            // Connecting to the InterSystems Database
            // https://docs.intersystems.com/irislatest/csp/docbook/Doc.View.cls?KEY=BNET_connect#BNET_connect_connect

            using (IRISConnection sqlConnection = new IRISConnection())
            {
                // 기본 포트: 51773
                sqlConnection.ConnectionString = "Server=192.168.0.11; Port=51773; Namespace=USER;Password=SYS;User ID=_SYSTEM;";

                sqlConnection.Open();

                // Create
                IRISCommand insertCommand = new IRISCommand();
                insertCommand.Connection = sqlConnection;
                insertCommand.CommandText = "INSERT INTO Employee(ID, EMPID, NAME, AGE) VALUES (?, ?, ?, ?)";

                insertCommand.Parameters.Add("ID", IRISDbType.BigInt);
                insertCommand.Parameters.Add("EMPID", IRISDbType.Int);
                insertCommand.Parameters.Add("NAME", IRISDbType.NVarChar, 100);
                insertCommand.Parameters.Add("AGE", IRISDbType.Int);

                string nameValue = "Name" + Guid.NewGuid().ToString();
                insertCommand.Parameters[1].Value = (int)DateTime.Now.Ticks;
                insertCommand.Parameters[2].Value = nameValue;
                insertCommand.Parameters[3].Value = 10;

                int affected = insertCommand.ExecuteNonQuery();
                Console.WriteLine("[INSERT] # of affected row: " + affected);

                // Update
                IRISCommand updateCommand = new IRISCommand();
                updateCommand.Connection = sqlConnection;
                updateCommand.CommandText = "UPDATE Employee SET AGE=? WHERE NAME=?";

                updateCommand.Parameters.Add("AGE", IRISDbType.Int);
                updateCommand.Parameters.Add("NAME", IRISDbType.NVarChar, 100);

                updateCommand.Parameters[0].Value = 20;
                updateCommand.Parameters[1].Value = nameValue;

                affected = updateCommand.ExecuteNonQuery();
                Console.WriteLine("[UPDATE] # of affected row: " + affected);

                // Select - ExecuteScalar
                IRISCommand selectCommand = new IRISCommand();
                selectCommand.Connection = sqlConnection;
                selectCommand.CommandText = "SELECT count(*) FROM Employee";

                object result = selectCommand.ExecuteScalar();
                Console.WriteLine("[SELECT] # of records: " + result);

                // Select - DataTable
                DataSet ds = new DataSet();
                IRISDataAdapter da = new IRISDataAdapter("SELECT * FROM Employee", sqlConnection);
                da.Fill(ds, "mytable");

                DataTable dt = ds.Tables["mytable"];
                foreach (DataRow dr in dt.Rows)
                {
                    Console.WriteLine(string.Format("Name = {0}, Age = {1}", dr["NAME"], dr["AGE"]));
                }

                // Delete
                IRISCommand deleteCommand = new IRISCommand();
                deleteCommand.Connection = sqlConnection;
                deleteCommand.CommandText = "DELETE FROM Employee WHERE NAME=?";

                deleteCommand.Parameters.Add("NAME", IRISDbType.NVarChar, 100);
                deleteCommand.Parameters[0].Value = nameValue;

                affected = deleteCommand.ExecuteNonQuery();
                Console.WriteLine("[DELETE] # of affected row: " + affected);
            }
        }

    }
}

internal CachedPrepare GetStatement(string sqlText, IRISCommand cmd)
{
    CachedPrepare prepare2;
    this.rwl.EnterReadLock();
    try
    {
        object obj2 = this.sqlToId[sqlText]; // 수행된 적이 없는 쿼리는 obj2 == null
        CachedPrepare prepare = (CachedPrepare) base[obj2]; // System.ArgumentNullException 예외 발생
        if (prepare != null)
        {
            if (prepare.ownedBy == null)
            {
                prepare.ownedBy = cmd;
                this.unownedCnt--;
                prepare.refCount++;
            }
            else
            {
                return null;
            }
        }
        prepare2 = prepare;
    }
    catch (Exception)
    {
        prepare2 = null;
    }
    finally
    {
        this.rwl.ExitReadLock();
    }
    return prepare2;
}

System.ArgumentNullException
  HResult=0x80004003
  Message=Key cannot be null.
Parameter name: key
  Source=mscorlib
  StackTrace:
   at System.Collections.Hashtable.get_Item(Object key)

  This exception was originally thrown at this call stack:
    System.Collections.Hashtable.this[object].get(object)

Using Cache ADO.NET Managed Provider Classes
; https://cedocs.intersystems.com/latest/csp/docbook/Doc.View.cls?KEY=GBMP_ado#GBMP_ado_intro

Installing Cache
; http://www.hardhats.org/projects/New/InstallCache.html#download-and-install-cach%C3%A9-on-microsoft-windows