docs.intersystems.com
Home / Using the InterSystems Managed Provider for .NET / Using ADO.NET Managed Provider Classes

Using the InterSystems Managed Provider for .NET
Using ADO.NET Managed Provider Classes
Previous section           Next section
InterSystems: The power behind what matters   
Search:  


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


Previous section           Next section
View this book as PDF   |  Download all PDFs
Copyright © 1997-2019 InterSystems Corporation, Cambridge, MA
Content Date/Time: 2019-04-23 13:43:20