InterSystems IRIS provides a complete application development environment for building sophisticated data- and analytics-intensive applications that connect data and application silos. It is designed to work with all of the common development technologies in an open, standards-based fashion and supports both server-side and client-side programming.

InterSystems IRIS supports server-side application development with both Python and InterSystems ObjectScript. InterSystems IRIS also supports client-side development using many popular development technologies, including Java, C#/.NET, Node.js, Python, and ObjectScript.

The purpose of this article will be to focus on client-side development using a popular environment, the .NET development environment.

The ADO.NET Managed Provider, NET Native SDK, XEP API, and the Entity Framework Provider are a set of powerful APIs that combine to cover your bases regarding client-side InterSystems IRIS data platform development by leveraging the .NET framework.

ADO.NET Managed Provider

The ADO.NET Managed Provider is the InterSystems implementation of the ADO.NET data access interface, which will enable connection to IRIS from your .NET application, enabling the use of SQL queries to access data. The other three APIs use this underlying connection protocol.

.NET Native SDK

The .NET Native SDK will provide direct access to InterSystems IRIS objects, globals, and ObjectScript functionality, such as running classes and routines. Directly accessing globals, the fundamental storage structure for data in IRIS, can speed up data retrieval for your .NET application.

XEP API

The XEP API will facilitate high-speed access to InterSystems objects. This is most useful when working with high throughput objects with low to medium complexity.

The Entity Framework Provider and Object Relational Mapping (ORM)

The Entity Framework Provider is the InterSystems implementation of the Entity Framework, the object-relational mapping for ADO.NET.

• What is Object-Relational Mapping or ORM?
  • A technique that lets you query and manipulate data from a database using an object-oriented paradigm. These techniques are often implemented as libraries, such as the SQLAlchemy library for Python.
  • If you're drawing a blank thinking of the equivalent library that implements ORM in IRIS, you should be. IRIS can be treated as a relational database (you can use SQL queries to query data stored in IRIS), so there is no need for a library implementing ORM techniques when ORM is built into the platform itself.

.NET developers can leverage any of these APIs alone or in conjunction with the stipulation of requiring the InterSystems.Data.IRISClient.dll assembly file to be referenced in their .NET project. Each API has its pros and cons, but a measured use of each one's capabilities provides a balanced approach to developing on the InterSystems IRIS data platform with the .NET Framework.

In recent versions of IRIS, a powerful new data loading command has been introduced to SQL: LOAD DATA. This feature has been highly optimized to import data into IRIS extremely fast, allowing hundreds of gigabytes of data to be inserted in seconds instead of hours or days. 

This is a very exciting improvement. However, a big problem in the data loading experience still exists. Namely, the time and hassle it takes to:

1. Define the schema for the table in which you want to load data.
2. Figure out the syntax for the LOAD DATA command.

I've developed a user interface that invisibly handles the CREATE TABLE step and then generates the syntax for LOAD DATA, all in a handy wizard!

At least in my case -- although I've been in the database business for decades -- I only import data a few times a year. Therefore, my CREATE TABLE skills get rusty, and it's really nice to have a tool take care of that for me. And this tool doesn't just handle syntax. It also inspects the input CSV file using a utility from the SQL Utilities library from @Benjamin De Boe to figure out the data types and their length. Then it asks a few more questions to define the syntax of the required LOAD DATA command. The app can run it, or you can just copy the syntax and tweak it yourself. 

Here's a walkthrough.

Step 1: Install the app and review the CSV file

After following the instructions to install the solution, you will have an Angular app published as a CSP application and a backend ObjectScript application that serves as the API to interface with the database.

Take a look at my sample data set (using the Rainbow CSV extension in VS Code). It has a mix of numeric, text and empty columns.

Step 2: Go to the app

You will probably find the app at http://localhost:52773/csp/dataloadapp/index.html if you use the default IRIS port and web application name.

Step 3: Specify the CSV file location

Step 4: Specify the CSV file's format

LOAD DATA needs to know some things like the column delimiter character and where to start in the file. 

Step 5: Define a destination table name, with the schema name as well

Step 6: Fine tune the field names and data types

Most of this will be filled in for you, and should be pretty accurate, but you will probably want to adjust some names or field lengths.

And that's it! Press "Load CSV" and the client-side app will make a call to the server to run a CREATE TABLE SQL command, then run LOAD DATA with the syntax shown in the black box on the right. Going into the Management Portal (or any other SQL client), you can see I now have the CSV file loaded into IRIS.

I must apologize in advance that there isn't much error checking yet, but this is open source so if you find this tool useful, join me in improving it on GitHub.