Using the ADO.NET Managed Provider
ADO.NET needs no introduction for experienced .NET database developers, but it can be useful even if you only use it occasionally for small utility applications. This section is a quick overview of ADO.NET that demonstrates how to do simple database queries and work with the results.
The InterSystems ADO.NET Managed Provider allows your .NET projects to access InterSystems databases with fully compliant versions of generic ADO.NET Managed Provider classes such as Connection, Command, CommandBuilder, DataReader, and DataAdapter. See “Connecting Your Application to InterSystems IRISOpens in a new tab” for a complete description of how to connect your .NET application with InterSystems IRIS. The following classes are InterSystems-specific implementations of the standard ADO.NET Managed Provider classes:
-
IRISConnection — Represents the connection between your application and the databases in a specified InterSystems namespace. See “Connecting to the InterSystems Database” for more information on how to use IRISConnection.
-
IRISCommand — Encapsulates an SQL statement or stored procedure to be executed against databases in the namespace specified by a IRISConnection.
-
IRISCommandBuilder — Automatically generates SQL commands that reconcile a database with changes made by objects that encapsulate a single-table query.
-
IRISDataReader — Provides the means to fetch the result set specified by a IRISCommand. A IRISDataReader object provides quick forward-only access to the result set, but is not designed for random access.
-
IRISDataAdapter — Encapsulates a result set that is mapped to data in the namespace specified by a IRISConnection. It is used to fill an ADO.NET DataSet and to update the database, providing an effective random access connection to the resultset.
This chapter gives some concrete examples of code using InterSystems ADO.NET Managed Provider classes. The following subjects are discussed:
-
Introduction to ADO.NET Managed Provider Classes — provides a simple demonstration of how InterSystems ADO.NET Managed Provider classes are used.
-
Using IRISCommand and IRISDataReader — demonstrates how to execute a simple read-only query.
-
Using SQL Queries with IRISParameter — demonstrates passing a parameter to a query.
-
Using IRISDataAdapter and IRISCommandBuilder — changing and updating data.
-
Using Transactions — demonstrates how to commit or rollback transactions.
Introduction to ADO.NET Managed Provider Classes
A project using the InterSystems implementations of ADO.NET Managed Provider classes can be quite simple. Here is a complete, working console program that opens and reads an item from the Sample.Person database:
using System;
using InterSystems.Data.IRISClient;
namespace TinySpace {
class TinyProvider {
[STAThread]
static void Main(string[] args) {
string connectionString = "Server = localhost; Port = 51783; " +
"Namespace = USER; Password = SYS; User ID = _SYSTEM;";
using IRISConnection conn = new IRISConnection(connectionString);
conn.Open();
using IRISCommand command = conn.CreateCommand();
command.CommandText = "SELECT * FROM Sample.Person WHERE ID = 1";
IRISDataReader reader = command.ExecuteReader();
while (reader.Read()) {
Console.WriteLine($"TinyProvider output:\r\n " +
$"{reader[reader.GetOrdinal("ID")]}: {reader[reader.GetOrdinal("Name")]}");
}
reader.Close();
} // end Main()
} // end class TinyProvider
}
This project contains the following important features:
-
The Using statements provide access to the IRISClient assembly. A namespace must be declared for the client code:
using InterSystems.Data.IRISClient; namespace TinySpace {
-
The IRISConnection conn object is used to create and open a connection to the USER namespace. The conn object is created with a using declaration to ensure that it will always be properly closed and disposed:
string connectionString = "Server = localhost; Port = 51783; " + "Namespace = USER; Password = SYS; User ID = _SYSTEM;"; using IRISConnection conn = new IRISConnection(connectionString); conn.Open();
-
The IRISCommand command object uses the IRISConnection object and an SQL statement to open the instance of Sample.Person that has an ID equal to 1.
using IRISCommand command = conn.CreateCommand(); command.CommandText = "SELECT * FROM Sample.Person WHERE ID = 1";
-
The IRISDataReader object is used to access the data items in the row:
IRISDataReader reader = command.ExecuteReader(); while (reader.Read()) { Console.WriteLine($"TinyProvider output:\r\n " + $"{reader[reader.GetOrdinal("ID")]}: {reader[reader.GetOrdinal("Name")]}"); } reader.Close();
Using IRISCommand and IRISDataReader
Simple read-only queries can be performed using IRISCommand and IRISDataReader. Like all database transactions, such queries also require an open IRISConnection object.
In this example, an SQL query string is passed to a new IRISCommand object, which will use the existing connection:
string SQLtext = "SELECT * FROM Sample.Person WHERE ID < 10";
IRISCommand Command = new IRISCommand(SQLtext, Conn);
Results of the query are returned in a IRISDataReader object. Properties are accessed by referring to the names of columns specified in the SQL statement.
IRISDataReader reader = Command.ExecuteReader();
while (reader.Read()) {
Console.WriteLine(
reader[reader.GetOrdinal("ID")] + "\t"
+ reader[reader.GetOrdinal("Name")] + "\r\n\t"
+ reader[reader.GetOrdinal("Home_City")] + " "
+ reader[reader.GetOrdinal("Home_State")] + "\r\n");
};
The same report could be generated using column numbers instead of names. Since IRISDataReader objects can only read forward, the only way to return to beginning of the data stream is to close the reader and reopen it by executing the query again.
reader.Close();
reader = Command.ExecuteReader();
while (reader.Read()) {
Console.WriteLine(
reader[0] + "\t"
+ reader[4] + "\r\n\t"
+ reader[7] + " "
+ reader[8] + "\n");
}
Using SQL Queries with IRISParameter
The IRISParameter object is required for more complex SQL queries. The following example selects data from all rows where Name starts with a string specified by the IRISParameter value:
string SQLtext =
"SELECT ID, Name, DOB, SSN "
+ "FROM Sample.Person "
+ "WHERE Name %STARTSWITH ?"
+ "ORDER BY Name";
IRISCommand Command = new IRISCommand(SQLtext, Conn);
The parameter value is set to get all rows where Name starts with A, and the parameter is passed to the IRISCommand object:
IRISParameter Name_param =
new IRISParameter("Name_col", IRISDbType.NVarChar);
Name_param.Value = "A";
Command.Parameters.Add(Name_param);
Be default, the SQL statement is not validated before being executed on the Server, since this would require two calls to the Server for each query. If validation is desirable, call IRISCommand.Prepare() to validate the syntax for the SQL statement against the server.
A IRISDataReader object can access the resulting data stream just as it did in the previous example:
IRISDataReader reader = Command.ExecuteReader();
while (reader.Read()) {
Console.WriteLine(
reader[reader.GetOrdinal("ID")] + "\t"
+ reader[reader.GetOrdinal("Name")] + "\r\n\t"
+ reader[reader.GetOrdinal("DOB")] + " "
+ reader[reader.GetOrdinal("SSN")] + "\r\n");
};
Using IRISDataAdapter and IRISCommandBuilder
The IRISCommand and IRISDataReader classes are inadequate when your application requires anything more than sequential, read-only data access. In such cases, the IRISDataAdapter and IRISCommandBuilder classes can provide full random read/write access. The following example uses these classes to get a set of Sample.Person rows, read and change one of the rows, delete a row and add a new one, and then save the changes to the database.
The IRISDataAdapter constructor accepts an SQL command and a IRISConnection object as parameters, just like a IRISCommand. In this example, the resultset will contain data from all Sample.Person rows where Name starts with A or B. The Adapter object will map the resultset to a table named Person:
string SQLtext =
" SELECT ID, Name, SSN "
+ " FROM Sample.Person "
+ " WHERE Name < 'C' "
+ " ORDER BY Name ";
IRISDataAdapter Adapter = new IRISDataAdapter(SQLtext, Conn);
Adapter.TableMappings.Add("Table", "Person");
A IRISCommandBuilder object is created for the Adapter object. When changes are made to the data mapped by the Adapter object, Adapter can use SQL statements generated by Builder to update corresponding items in the database:
IRISCommandBuilder Builder = new IRISCommandBuilder(Adapter);
An ADO DataSet object is created and filled by Adapter. It contains only one table, which is used to define the PersonTable object.
System.Data.DataSet DataSet = new System.Data.DataSet();
Adapter.Fill(DataSet);
System.Data.DataTable PersonTable = DataSet.Tables["Person"];
A simple foreach command can be used to read each row in PersonTable. In this example, we save Name in the first row and change it to "Fudd, Elmer". When the data is printed, all names will be in alphabetical order except the first, which now starts with F. After the data has been printed, the first Name is reset to its original value. Both changes were made only to the data in DataSet. The original data in the database has not yet been touched.
if (PersonTable.Rows.Count > 0) {
System.Data.DataRow FirstPerson = PersonTable.Rows[0];
string OldName = FirstPerson["Name"].ToString();
FirstPerson["Name"] = "Fudd, Elmer";
foreach (System.Data.DataRow PersonRow in PersonTable.Rows) {
Console.WriteLine("\t"
+ PersonRow["ID"] + ":\t"
+ PersonRow["Name"] + "\t"
+ PersonRow["SSN"]);
}
FirstPerson["Name"] = OldName;
}
The following code marks the first row for deletion, and then creates and adds a new row. Once again, these changes are made only to the DataSet object.
FirstPerson.Delete();
System.Data.DataRow NewPerson = PersonTable.NewRow();
NewPerson["Name"] = "Budd, Billy";
NewPerson["SSN"] = "555-65-4321";
PersonTable.Rows.Add(NewPerson);
Finally, the Update() method is called. Adapter now uses the IRISCommandBuilder code to update the database with the current data in the DataSet object's Person table.
Adapter.Update(DataSet, "Person");
Using Transactions
The Transaction class is used to specify an SQL transaction (see “Transaction Processing” in Using InterSystems SQL for an overview of how to use transactions). In the following example, transaction Trans will fail and be rolled back if SSN is not unique.
IRISTransaction Trans =
Conn.BeginTransaction(System.Data.IsolationLevel.ReadCommitted);
try {
string SQLtext = "INSERT into Sample.Person(Name, SSN) Values(?,?)";
IRISCommand Command = new IRISCommand(SQLtext, Conn, Trans);
IRISParameter Name_param =
new IRISParameter("name", IRISDbType.NVarChar);
Name_param.Value = "Rowe, Richard";
Command.Parameters.Add(Name_param);
IRISParameter SSN_param =
new IRISParameter("ssn", IRISDbType.NVarChar);
SSN_param.Value = "234-56-3454";
Command.Parameters.Add(SSN_param);
int rows = Command.ExecuteNonQuery();
Trans.Commit();
Console.WriteLine("Added record for " + SSN_param.Value.ToString());
}
catch (Exception eInsert) {
Trans.Rollback();
WriteErrorMessage("TransFail", eInsert);
}