First Look: ADO.NET and InterSystems Products
This First Look guide explains how to connect to InterSystems IRIS™ via the InterSystems ADO.NET Managed Provider. Once you have completed this guide, you will have configured a Visual Studio project to use the InterSystems.Data.IRISClient.dll
assembly, established an ADO.NET connection to InterSystems IRIS, run several SQL statements from your .NET application, and confirmed the effects of these statements in the InterSystems IRIS System Management Portal.
To give you a taste of the ADO.NET Managed Provider without bogging you down in details, we’ve kept this exploration simple. These activities are designed to only use the default settings and features, so that you can acquaint yourself with the fundamentals of the feature without having to deal with details that are off-topic or overly complicated. When you bring ADO.NET to your production systems, there may be things you will need to do differently. Be sure not to confuse this exploration of ADO.NET with the real thing! The sources provided at the end of this document will give you a good idea of what is involved in using ADO.NET in production.
To browse all of the First Looks, including others that can be performed on a free Community Edition instance as described below, see InterSystems First Looks
ADO.NET is a data access technology from the Microsoft .NET Framework that provides access to data sources. It is used to establish database connectivity and provides a standard, reliable way for .NET Framework programmers to connect to many types of data sources or perform operations on them with SQL. Connecting to InterSystems IRIS via the ADO.NET Managed Provider is simple, especially if you’ve used ADO.NET before. Establishing an ADO.NET connection to InterSystems IRIS from a .NET application allows you to run SQL commands against InterSystems IRIS databases from your .NET application.
If you’re new to InterSystems IRIS but familiar with .NET and SQL, you can use your existing expertise right away to help you become familiar with the database platform. You can test ADO.NET connections and SQL commands in a development environment with just a few lines of code.
InterSystems IRIS is a fully compliant implementation of the ADO.NET specification. The InterSystems ADO.NET Managed Provider provides easy relational access to data. It processes ADO.NET method calls from applications and submits SQL requests to InterSystems IRIS. It then returns results to the calling application in this case, your .NET application.
Connecting to InterSystems IRIS via ADO.NET is a very straightforward process.
In order to use InterSystems IRIS ADO.NET capability, you must first add the InterSystems.Data.IRISClient.dll
assembly as a dependency to your Visual Studio project. After confirming a few settings, use our sample code to establish an ADO.NET connection to InterSystems IRIS and to execute SQL queries. Note that the InterSystems.Data.IRISClient.dll
assembly is implemented using .NET managed code throughout, making it easy to deploy within a .NET environment. It is thread-safe and can be used within multithreaded .NET applications.
We have developed a brief demo that shows you how to work with ADO.NET and InterSystems IRIS.
To run this demo you’ll need a Windows 10 system running version 4.5 of the Microsoft .NET Framework and Visual Studio (in this demo, we use Visual Studio Community 2017), and an instance of InterSystems IRIS, which you can select from among the following:
Deploy InterSystems IRIS in a container
You can deploy a free instance of InterSystems IRIS Community Edition in two ways:
Provision a cloud node
hosting a running InterSystems IRIS Community Edition container on the Google Cloud Platform, Microsoft Azure, or Amazon Web Services public cloud platforms.
Pull the container image
from the Docker Store and deploy an InterSystems IRIS Community Edition container on the system of your choice.
Deploy InterSystems IRIS on the web
The InterSystems Labs web page
lets you easily create your own demo instance of InterSystems IRIS Community Edition, accessible on the web. Your InterSystems Labs instance includes an integrated IDE and plenty of samples to work with, and you can connect your own IDE.
Install InterSystems IRIS on your system
If you are an InterSystems customer, you can install and license a development instance of InterSystems IRIS on your local machine or on another on your network; for instructions, see InterSystems IRIS Basics: Installation
. Install with Normal security settings.
Select the file and click Add
At this point, you are ready to connect to InterSystems IRIS from your .NET application. The connection string for the InterSystems ADO.NET Managed Provider is made up of key/value pairs that define the connection properties. The connection string syntax
Update this information in the code that follows after you paste it into Visual Studio. You can set namespace
to the predefined namespace USER
, as shown, or to another namespace you have created in your installed instance.
static void Main(string args)
String host = "<host>";
String port = "<port>";
String username = "<username>";
String password = "<password>";
String Namespace = "USER";
IRISConnection IRISConnect = new IRISConnection();
IRISConnect.ConnectionString = "Server = " + host
+ "; Port = " + port + "; Namespace = " + Namespace
+ "; Password = " + password + "; User ID = " + username;
String sqlStatement1 = "CREATE TABLE People(ID int, FirstName varchar(255), LastName varchar(255))";
String sqlStatement2 = "INSERT INTO People VALUES (1, 'John', 'Smith')";
String sqlStatement3 = "INSERT INTO People VALUES (2, 'Jane', 'Doe')";
String queryString = "SELECT * FROM People";
IRISCommand cmd1 = new IRISCommand(sqlStatement1, IRISConnect);
IRISCommand cmd2 = new IRISCommand(sqlStatement2, IRISConnect);
IRISCommand cmd3 = new IRISCommand(sqlStatement3, IRISConnect);
IRISCommand cmd4 = new IRISCommand(queryString, IRISConnect);
//ExecuteNonQuery() is used for CREATE, INSERT, UPDATE, and DELETE SQL Statements
//ExecuteReader() is used for SELECT
IRISDataReader Reader = cmd4.ExecuteReader();
Console.WriteLine("Printing out contents of SELECT query: ");
Console.WriteLine(Reader.GetValue(0).ToString() + ", " + Reader.GetValue(1).ToString() + ", " \
Console.WriteLine("Press any key to continue...");
Run the code by clicking the Start
button, or by pressing F5.
If the connection and queries have completed successfully, you should see a console window containing the results of the SELECT query.
Next, confirm your results in the Management Portal, using the following procedure.
If you are not in the namespace you specified in the code, switch to it (click Switch
next to the Namespace:
indicator at the top of the page).
ID, FirstName, LastName
. The page should display the contents of the People table created in the sample code.
To learn more about ADO.NET, SQL, and InterSystems IRIS, see:
Content Date/Time: 2019-04-23 13:43:20