docs.intersystems.com
Home  /  Application Development: Language Bindings and Gateways  /  Using the InterSystems Managed Provider for .NET  /  Using InterSystems IRIS ADO.NET Managed Provider Classes


Using the InterSystems Managed Provider for .NET
Using InterSystems IRIS ADO.NET Managed Provider Classes
[Back]  [Next] 
InterSystems: The power behind what matters   
Search:  


The InterSystems IRIS™ ADO.NET Managed Provider allows your .NET projects to access InterSystems IRIS databases with fully compliant versions of generic ADO.NET Managed Provider classes such as Connection, Command, CommandBuilder, DataReader, and DataAdapter. The following classes are InterSystems IRIS-specific implementations of the standard ADO.NET Managed Provider classes:
This chapter gives some concrete examples of code using InterSystems IRIS ADO.NET Managed Provider classes. The following subjects are discussed:
Introduction to ADO.NET Managed Provider Classes
A project using the InterSystems IRIS 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;
using InterSystems.Data.IrisTypes;

namespace TinySpace {
  class TinyProvider {
    [STAThread]
    static void Main(string[] args) {

      IrisConnection IrisConnect = new IrisConnection();
      IrisConnect.ConnectionString = "Server = localhost; "
        + "Port = 51773; " + "Namespace = USER; "
        + "Password = SYS; " + "User ID = _SYSTEM;";
      IrisConnect.Open();

      string SQLtext = "SELECT * FROM Sample.Person WHERE ID = 1";
      IrisCommand Command = new IrisCommand(SQLtext, IrisConnect);
      IrisDataReader Reader = Command.ExecuteReader();
      while (Reader.Read()) {
        Console.WriteLine("TinyProvider output: \r\n   "
          + Reader[Reader.GetOrdinal("ID")] + ": "
          + Reader[Reader.GetOrdinal("Name")]);
      };
      Reader.Close();
      Command.Dispose();
      IrisConnect.Close();
    } // end Main()
  } // end class TinyProvider
}
This project contains the following important features:
Using IrisCommand and IrisDataReader
Simple read-only queries can be performed using only 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, IrisConnect);
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()) {
    Display.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()) {
    Display.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, IrisConnect);
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);
Note:
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 InterSystems IRIS 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()) {
    Display.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 InterSystems IRIS 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, IrisConnect);
  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 InterSystems IRIS 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 InterSystems IRIS 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) {
      Display.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 InterSystems IRIS 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 with InterSystems IRIS). In the following example, transaction Trans will fail and be rolled back if SSN is not unique.
  IrisTransaction Trans =
    IrisConnect.BeginTransaction(System.Data.IsolationLevel.ReadCommitted);
  try {
    string SQLtext = "INSERT into Sample.Person(Name, SSN) Values(?,?)";
    IrisCommand Command = new IrisCommand(SQLtext, IrisConnect, 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();
    Display.WriteLine("Added record for " + SSN_param.Value.ToString());
  }
  catch (Exception eInsert) {
    Trans.Rollback();
    WriteErrorMessage("TransFail", eInsert);
  }