Skip to main content
Previous sectionNext section

ADO.NET Managed Provider for Occasional Users

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. 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 a detailed description of 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

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;
using InterSystems.Data.IRISTypes;

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

      IRISConnection Conn = new IRISConnection();
      Conn.ConnectionString = "Server = localhost; "
        + "Port = 51774; " + "Namespace = USER; "
        + "Password = SYS; " + "User ID = _SYSTEM;";
      Conn.Open();

      string SQLtext = "SELECT * FROM Sample.Person WHERE ID = 1";
      IRISCommand Command = new IRISCommand(SQLtext, Conn);
      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();
      Conn.Close();
    } // end Main()
  } // end class TinyProvider
}
Copy code to clipboard

This project contains the following important features:

  • The Using statements provide access to the IRISClient assembly:

      using InterSystems.Data.IRISClient;
      using InterSystems.Data.IRISTypes;
    Copy code to clipboard
  • The IRISConnection object is used to create and open a connection to the USER namespace:

      IRISConnection Conn = new IRISConnection();
      Conn.ConnectionString = "Server = localhost; "
        + "Port = 51774; " + "Namespace = USER; "
        + "Password = SYS; " + "User ID = _SYSTEM;";
      Conn.Open();
    Copy code to clipboard
  • The IRISCommand object uses the IRISConnection object and an SQL statement to open the instance of Sample.Person that has an ID equal to 1.

      string SQLtext = "SELECT * FROM Sample.Person WHERE ID = 1";
      IRISCommand Command = new IRISCommand(SQLtext, Conn);
    Copy code to clipboard
  • 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")]);
      };
    Copy code to clipboard

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, Conn);
Copy code to clipboard

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");
  };
Copy code to clipboard

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");
  }
Copy code to clipboard

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);
Copy code to clipboard

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);
Copy code to clipboard
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 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");
  };
Copy code to clipboard

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");
Copy code to clipboard

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);
Copy code to clipboard

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"];
Copy code to clipboard

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) {
      Display.WriteLine("\t"
        + PersonRow["ID"] + ":\t"
        + PersonRow["Name"] + "\t"
        + PersonRow["SSN"]);
    }
    FirstPerson["Name"] = OldName;
  }
Copy code to clipboard

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);
Copy code to clipboard

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");
Copy code to clipboard

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();
    Display.WriteLine("Added record for " + SSN_param.Value.ToString());
  }
  catch (Exception eInsert) {
    Trans.Rollback();
    WriteErrorMessage("TransFail", eInsert);
  }
Copy code to clipboard