Using Caché ADO.NET Managed Provider Classes
The Caché ADO.NET Managed Provider allows your .NET projects to access Caché databases with fully compliant versions of generic ADO.NET Managed Provider classes such as Connection, Command, CommandBuilder, DataReader, and DataAdapter. The following classes are Caché-specific implementations of the standard ADO.NET Managed Provider classes:
-
CacheConnection — Represents the connection between your application and the databases in a specified Caché namespace. See “Connecting to the Caché Database” for a detailed description of how to use CacheConnection.
-
CacheCommand — Encapsulates an SQL statement or stored procedure to be executed against databases in the namespace specified by a CacheConnection.
-
CacheCommandBuilder — Automatically generates SQL commands that reconcile a Caché database with changes made by objects that encapsulate a single-table query.
-
CacheDataReader — Provides the means to fetch the resultset specified by a CacheCommand. A CacheDataReader object provides quick forward-only access to the resultset, but is not designed for random access.
-
CacheDataAdapter — Encapsulates a resultset that is mapped to data in the namespace specified by a CacheConnection. It is used to fill an ADO.NET DataSet and to update the Caché database, providing an effective random access connection to the resultset.
This chapter gives some concrete examples of code using Caché ADO.NET Managed Provider classes. The following subjects are discussed:
-
Introduction to ADO.NET Managed Provider Classes — provides a simple demonstration of how Caché ADO.NET Managed Provider classes are used.
-
Using CacheCommand and CacheDataReader — demonstrates how to execute a simple read-only query.
-
Using SQL Queries with CacheParameter — demonstrates passing a parameter to a query.
-
Using CacheDataAdapter and CacheCommandBuilder — changing and updating data.
-
Using Transactions — demonstrates how to commit or rollback transactions.
Although the examples in this chapter use only SQL statements to access Caché data, it is also possible to access database instances as objects rather than rows in a relational database (as described in “Using the Caché Object Binding for .NET”). Both types of access can be used in the same program.
The examples presented in this chapter are fragments from samples provided in the bookdemos project (see “The Caché .NET Sample Programs” for details). It is assumed that you are familiar with standard coding practices, so the fragments omit error trapping (try/catch) statements and other code that is not directly relevant to the examples. For complete, working versions of the code examples, see the main code file, SampleCode.cs, located in <Cache-install-dir>\dev\dotnet\samples\bookdemos (see “Caché Installation Directory” in the Caché Installation Guide for the location of <Cache-install-dir> on your system).
Introduction to ADO.NET Managed Provider Classes
A project using the Caché 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.CacheClient;
using InterSystems.Data.CacheTypes;
namespace TinySpace {
class TinyProvider {
[STAThread]
static void Main(string[] args) {
CacheConnection CacheConnect = new CacheConnection();
CacheConnect.ConnectionString = "Server = localhost; "
+ "Port = 1972; " + "Namespace = SAMPLES; "
+ "Password = SYS; " + "User ID = _SYSTEM;";
CacheConnect.Open();
string SQLtext = "SELECT * FROM Sample.Person WHERE ID = 1";
CacheCommand Command = new CacheCommand(SQLtext, CacheConnect);
CacheDataReader Reader = Command.ExecuteReader();
while (Reader.Read()) {
Console.WriteLine("TinyProvider output: \r\n "
+ Reader[Reader.GetOrdinal("ID")] + ": "
+ Reader[Reader.GetOrdinal("Name")]);
};
Reader.Close();
Command.Dispose();
CacheConnect.Close();
} // end Main()
} // end class TinyProvider
}
This project contains the following important features:
-
The Using statements provide access to the CacheClient assembly:
using InterSystems.Data.CacheClient; using InterSystems.Data.CacheTypes;
-
The CacheConnection object is used to create and open a connection to the Caché SAMPLES namespace:
CacheConnection CacheConnect = new CacheConnection(); CacheConnect.ConnectionString = "Server = localhost; " + "Port = 1972; " + "Namespace = SAMPLES; " + "Password = SYS; " + "User ID = _SYSTEM;"; CacheConnect.Open();
-
The CacheCommand object uses the CacheConnection 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"; CacheCommand Command = new CacheCommand(SQLtext, CacheConnect);
-
The CacheDataReader object is used to access the data items in the row:
CacheDataReader Reader = Command.ExecuteReader(); while (Reader.Read()) { Console.WriteLine("TinyProvider output: \r\n " + Reader[Reader.GetOrdinal("ID")] + ": " + Reader[Reader.GetOrdinal("Name")]); };
Using CacheCommand and CacheDataReader
Simple read-only queries can be performed using only CacheCommand and CacheDataReader. Like all database transactions, such queries also require an open CacheConnection object.
In this example, an SQL query string is passed to a new CacheCommand object, which will use the existing connection:
string SQLtext = "SELECT * FROM Sample.Person WHERE ID < 10";
CacheCommand Command = new CacheCommand(SQLtext, CacheConnect);
Results of the query are returned in a CacheDataReader object. Properties are accessed by referring to the names of columns specified in the SQL statement.
CacheDataReader 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 CacheDataReader 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");
}
For a working example, see the ADO_1_CommandReader() method in the bookdemos sample program (see “The Caché .NET Sample Programs”).
Using SQL Queries with CacheParameter
The CacheParameter 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 CacheParameter value:
string SQLtext =
"SELECT ID, Name, DOB, SSN "
+ "FROM Sample.Person "
+ "WHERE Name %STARTSWITH ?"
+ "ORDER BY Name";
CacheCommand Command = new CacheCommand(SQLtext, CacheConnect);
The parameter value is set to get all rows where Name starts with A, and the parameter is passed to the CacheCommand object:
CacheParameter Name_param =
new CacheParameter("Name_col", CacheDbType.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 CacheCommand.Prepare() to validate the syntax for the SQL statement against the Cache Server.
A CacheDataReader object can access the resulting data stream just as it did in the previous example:
CacheDataReader 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");
};
For a working example, see the ADO_2_Parameter() method in the bookdemos sample program (see “The Caché .NET Sample Programs”).
The CacheCommand, CacheParameter, and CacheDataReader classes are also used to execute a Caché Query method from a proxy object. See “Using Caché Queries” for details.
Using CacheDataAdapter and CacheCommandBuilder
The CacheCommand and CacheDataReader classes are inadequate when your application requires anything more than sequential, read-only data access. In such cases, the CacheDataAdapter and CacheCommandBuilder 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 Caché database.
The CacheDataAdapter constructor accepts an SQL command and a CacheConnection object as parameters, just like a CacheCommand. 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 ";
CacheDataAdapter Adapter = new CacheDataAdapter(SQLtext, CacheConnect);
Adapter.TableMappings.Add("Table", "Person");
A CacheCommandBuilder 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 Caché database:
CacheCommandBuilder Builder = new CacheCommandBuilder(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 Caché 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 CacheCommandBuilder code to update the Caché database with the current data in the DataSet object's Person table.
Adapter.Update(DataSet, "Person");
For a working example, see the ADO_3_AdapterBuilder() method in the bookdemos sample program (see The Caché .NET Sample Programs).
Using Transactions
The Transaction class is used to specify an SQL transaction (see “Transaction Processing” in Using Caché SQL for an overview of how to use transactions with Caché). In the following example, transaction Trans will fail and be rolled back if SSN is not unique.
CacheTransaction Trans =
CacheConnect.BeginTransaction(System.Data.IsolationLevel.ReadCommitted);
try {
string SQLtext = "INSERT into Sample.Person(Name, SSN) Values(?,?)";
CacheCommand Command = new CacheCommand(SQLtext, CacheConnect, Trans);
CacheParameter Name_param =
new CacheParameter("name", CacheDbType.NVarChar);
Name_param.Value = "Rowe, Richard";
Command.Parameters.Add(Name_param);
CacheParameter SSN_param =
new CacheParameter("ssn", CacheDbType.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);
}
For a working example, see the ADO_4_Transaction() method in the bookdemos sample program (see “The Caché .NET Sample Programs”).