IngresConnection Class
The IngresConnection class represents an open connection to an Ingres database. This class requires a connection string to connect to a target server and database.
IMPORTANT! An application must Close( ) or Dispose( ) on the Connection object to return it to the connection pool for reuse by other applications.
IngresConnection Class Declaration
The IngresConnection class declaration method signature is:
C#: public sealed class IngresConnection : System.Data.Common.DbConnection, IDbConnection, IDisposable
VB.NET: NotInheritable Public Class IngresConnection
Inherits System.Data.Common.DbConnection
Implements IDbConnection, IDisposable
IngresConnection Class Example
IngresConnection conn = new IngresConnection(
“Host=myserver.mycompany.com;Database=mydatabase;” +
“User ID=myuid;Password=mypassword;”);
conn.Open( );
IngresConnection Class Properties
The IngresConnection class has the following properties:
IngresConnection Class Public Methods
The public methods for the IngresConnection class are:
IngresConnection Class Events
The events generated by the IngresConnection are:
IngresConnection Class Constructors
The constructors for the IngresConnection class are:
Connection String Keywords
Keywords for the ConnectionString property of the IngresConnection class are as follows.
Connection string keywords are case-insensitive. Certain keywords have synonyms. For example, keywords Server and Address are synonyms of Host. Spaces in values are retained. Values can be delimited by double quotes.
BlankDate
Specifies how an Ingresdate blank (empty) result value is to be returned to the application:
BlankDate=null
Returns a null value.
BlankDate=default
Returns the default (9999-12-31 23:59:59).
BlankDate=datetime
Returns a .NET DateTime value with the customized BlankDate value. This option lets you specify your own "magic" DateTime value to represent an Ingres blank date. The format of datetime must be yyyy-mm-dd hh:mm:ss [GMT], and the value must be a valid .NET DateTime and Ingresdate value.
If the GMT specification is included, the returned .NET DateTime value is adjusted for the local time zone and returned as a .NET DateTime with DateTimeKind.Local property. For example:
"BlankDate=2099-12-31 12:00:00 GMT;" returns 2099-12-31 12:00:00 GMT converted to local time.
Note: Be careful if the application does an INSERT or UPDATE of the Ingresdate column. If the column is to contain a blank Ingresdate value, the application must map the DBNull or .NET DateTime value back to the Ingresdate column value of blank date. Applications that specify their own BlankDate "magic" values should be consistent with a value. Failure to INSERT or UPDATE these mapped values properly can lead to an inconsistent, confusing mix of blank dates and various "magic" date values in the database.
Character Encoding
Specifies the .NET character encoding name (for example, windows-1252) used for conversions between Unicode and character data types. This keyword allows an alternate .NET character encoding to be specified as an override, or a valid .NET character encoding to be used if the data provider is unable to map the Data Access Server’s installation character set. A code page name can also be specified in “cp” format (for example, “cp1252”).
Compress
Specifies whether network communication blocks are compressed between the data provider and server:
true—(Default) Enables compression. Connection fails if compression is not possible with the server.
false—No compression.
The keyword "compression" is a synonym for "compress."
Connection Lifetime or Connect Lifetime
Specifies an interval, in seconds, that defines the lifetime of the connection. An inactive connection that has existed longer than the specified interval beyond when it was first opened is removed from the connection pool and is closed.
This parameter will increase the number of physical connections required over the life of the system, but allows the system administrator to release connections and system resources, or balance servers regardless of other pool parameters such as Min Pool Size.
Default: 0 (no checking)
Note: Inactive connections waiting for work for more than 60 seconds are automatically removed from the pool unless the user specifies a Min Pool Size. Removing inactive connections releases system resources to other processes in both the client and server.
Connection Timeout or Connect Timeout
Specifies the time, in seconds, to wait for an attempted connection to time out if the connection has not completed. Default is 15.
Cursor_mode
Specifies the default cursor concurrency mode, which determines the concurrency of cursors that are not explicitly assigned in the command text (for example, FOR UPDATE or FOR READONLY). Options are:
readonly—(Default) Provides non-updatable cursors for best performance
update—Provides updatable cursors
dbms—The DBMS Server assigns the cursor concurrency mode.
Database or DB
Specifies the name of the database being connected to. If a server is required, use the syntax dbname/server_class.
Date_alias
Specifies the alias for the DATE data type. This property is not used directly by the data provider but is sent to the DBMS and affects the processing of query text. Options are:
ansidate—Sets ANSIDATE as the alias (default if SendIngresDates is not set or is set to FALSE).
ingresdate—Sets INGRESDATE as the alias (default if SendIngresDates is set to TRUE).
Date_format or Date_fmt
Specifies the Ingres format for date literals. It corresponds to the Ingres environment variable II_DATE_FORMAT and is assigned the same values. This option is not used directly by the data provider, but is sent to the DBMS and affects the parsing of date literals in query text.
Dbms_user
Specifies the user name to be associated with the DBMS session. This name is equivalent to the Ingres –u flag, which can require administrator privileges.
Dbms_password
Specifies the DBMS password of the user, which is equivalent to the Ingres –P flag.
Decimal_char
Specifies the character that the DBMS Server will use to separate fractional and non-fractional parts of a number. It corresponds to the Ingres environment variable II_DECIMAL and is assigned the same values. This option is not used directly by the data provider, but is sent to the DBMS and affects the parsing and construction of numeric literals.
Decimal_char=',' specifies the comma (,) character.
Default: period (.) as in 12.34.
Encrypt
Specifies whether encryption is enabled for the connection:
false—(Default) Disables encryption.
true—Enables encryption. Connection fails if encryption is not possible with the server.
Encryption AES Key Size
Specifies the size of the AES key in bits. When client and server have different capabilities, the largest common key size will be used. Valid values are: 128, 192, or 256.
Default: 128
Encryption RSA Key Scope
Defines the scope where an RSA key is used. Options are:
process—(Default) Generates one RSA key in a process. Key is used for all connections originating from that process.
connection—Generates an RSA key for each connection. Key is used only for that connection.
Encryption RSA Key Size
Specifies the size of the RSA key in bits. Valid values are: 1024, 2048, 3072, 4096.
Default: 1024
Enlist
Specifies whether the IngresConnection in the transaction context is automatically enlisted if the creation thread is within a transaction context as established by System.EnterpriseServices.ServicedComponent.
Default: true
Group ID
Specifies the group identifier that has permissions for a group of users.
Host or Server or Address
Specifies the name of the target host server machine with the Data Access Server (DAS).
Multiple host names can be specified as a semicolon-separated list enclosed in parentheses with optional port lists:
Server=(hostname1[:port[,port]];hostname2[:port[,port]])
Example connection string:
Server=(myserver1;myserver2);db=mydatabase;
If a port ID is attached to a host name, any additional port IDs outside the list or specified in the PORT keyword are ignored. If a host name does not have an explicit port ID in the specification, then port IDs in the list that follows the PORT keyword are distributed to the hostname. For example:
Host=(myservera:II6;myserverb);Port=II8,II9;Database=mydb;
is equivalent to:
Host=(myservera:II6;myserverb):II8,II9;Database=mydb;
is equivalent to:
Host=(myservera:II6;myserverb:II8;myserverb:II9);Database=mydb;
To maintain performance, the connection string should specify a hostname and port that the DAS servers typically listen to.
Example:
Assume that four DAS servers are started and listening on symbolic ports II7, II8, II9, and II10. The following IngresConnection.ConnectionString will connect a .NET application to the Ingres database using one of the four ports:
Host=myserver;Port=II7,II8,II9,II10;UserID=myuserid;Pwd=mypwd;Database=mydb;
or
Host=myserver:II7,II8,II9,II10;UserID=myuserid;Pwd=mypwd;Database=mydb;
Identifier_Delimiter or IdentifierDelimiter or ID_Delimiter or IDDelimiter
Specifies the character recognized by the data provider to delimit an identifier. Options are:
dquote—(Default) Recognizes only double-quotes.
bracket—Recognizes both double-quotes and brackets. This keyword is for use with products such as Microsoft SQL Server Analysis Services (SSAS), which generate bracket-delimited identifiers in their processing of SQL table and column references.
Max Pool Size
Specifies the maximum number of connections that can be in the pool.
Default: 100
Min Pool Size
Specifies the minimum number of connections that can be in the pool.
Default: 0
Money_format or Money_fmt
Specifies the Ingres format for money literals. It corresponds to the Ingres environment variable II_MONEY_FORMAT and is assigned the same values. This option is not used directly by the data provider, but is sent to the DBMS and affects the processing of query text.
Money_precision or Money_prec
Specifies the precision of money data values. It corresponds to the Ingres environment variable II_MONEY_PREC and is assigned the same values. This option is not used directly by the data provider, but is sent to the DBMS and affects the processing of money values.
Password or PWD
Specifies the password to the database. This value may be case-sensitive depending on the target server.
Persist Security Info
Specifies whether password information from the connection string is returned in a get of the ConnectionString property:
false—(Default) Does not return password information.
true—Returns password information.
Pool Check Interval
Specifies the interval, in seconds, at which inactive connections in the pool that are waiting for work are checked to test if the session is still responsive. Non-responsive connections are closed and removed from the pool.
A value that is too low generates needless query traffic to the DBMS Server. A value that is too high delays detection of non-responsive servers. An interval of 300 seconds may be a reasonable starting value.
Default: 0 (no checking)
Pool Check Timeout
Specifies the time, in seconds, for an inactive connection in the pool to wait on a non-responsive server before timing out and disconnecting. This keyword has no effect if Pool Check Interval is 0.
Default: 5
Pooling
Enables or disables connection pooling:
true—(Default) Enables connection pooling.
false—Disables connection pooling.
Port
Specifies the port number on the target host server machine that the Data Access Server is listening to.
Multiple port numbers, separated by commas, can be specified, one for each configured DAS. For example, "Port=II7,II8,II9,II10;" can be specified if four DAS servers have been configured to listen on each respective port. The data provider attempts to connect to each port, in random order, until a successful connection is made.
Ingres default: II7
Vector default: VW7
PrefetchBlocks
Specifies the number of data blocks to be prefetched. By default, the rows that will fit in one communication data block are prefetched. When this property is set, the prefetch block count is multiplied by the default prefetch row count to determine the number of rows to be prefetched. Depending on the row size and unused space in the rows, the number of blocks retrieved may be different.
Default: 1 (no override to the default algorithm)
PrefetchStream
Specifies whether to return data as a high performance prefetch data stream from the result set when using a select loop (a SELECT query that is neither prepared with the PREPARE statement nor has parameters). Default: false (no data streaming of the result set).
Role ID
Specifies the role identifier that has associated privileges for the role.
Role Password or Role PWD
Specifies the role password associated with the Role ID.
SendIngresDates
Specifies whether .NET DateTime parameter data is sent as INGRESDATE data type rather than as ANSI date and time data type. Options are:
false—Sends as ANSI date and time type. (Default if Date_alias is not set or is set to ANSIDATE.)
true—Sends as INGRESDATE type. (Default if Date_alias is set to INGRESDATE.)
Timezone or TZ
Specifies the time zone associated with the client's location. Corresponds to the Ingres environment variable II_TIMEZONE_NAME and is assigned the same values. This information is not used directly by the data provider, but is sent to the DBMS and affects the processing of dates.
User ID or UID
Specifies the name of the authorized user connecting to the DBMS Server. This value may be case-sensitive depending on the target server.
Vnode_usage
Allows the .NET application to control the portions of the vnode information that are used to establish the connection to the remote DBMS server through the Data Access Server. Options are:
connect—(Default) Uses only the vnode connection information.
login—Uses both the vnode connection and login information.
User ID Options for the Data Provider
When the .NET client application is running on the same machine as the Data Access Server (DAS), and DBMS authentication is not enabled, the .NET Data Provider does not require a user ID and password to establish a DBMS connection. The .NET client process user ID is used to establish the connection when a user ID and password are not provided.
If DBMS authentication is enabled and the user password is required, then the user ID and password must be provided even if the connection is to the same machine.
If the target database name specification includes a VNODE name specification, the VNODE login information is used to access the DBMS machine. Optionally, a user ID and password can be provided and is handled as described next.
When the DAS and DBMS servers are on different machines, a VNODE name is required in the target database specification of the form vnodename::dbname. The VNODE provides the connection and (optionally) login information needed to establish the DBMS connection.
The connection string keyword Vnode_usage determines how the VNODE is used to access the DBMS. Vnode_usage also determines the context (DAS or DBMS) in which the application user ID/password is used. If the target database specification does not contain a VNODE name, the Vnode_usage specification is ignored.
When Vnode_usage is set to CONNECT, only global VNODE connection information is used to establish the DBMS connection. The application-provided user ID and password are used in the DBMS context to access the DBMS machine.
When Vnode_usage is set to LOGIN, both connection and login VNODE information is used to access the DBMS machine. The application-provided User ID and Password are used in the DAS context, allowing access to private and global VNODEs on the DAS server.
The .NET Data Provider supports IPv6 addressing. IPv6 addresses should be enclosed in brackets [ ] because of the different address format—for example: [fe80::127:dff:fe7c:fecc].
If a hostname is associated with multiple IP addresses, the data provider sequentially tries to connect to each IP address in the AddressList returned by System.Net.Dns.GetHostEntry until it achieves a successful socket connection or until it reaches the end of the list. If the connection to the first address is down, the data provider attempts a connection to the next entry in the AddressList. Although performance will suffer as each Exception from a failed connection is caught, this re-attempt allows a secondary IP (backup) for a connection to a server.
SendIngresDates Connection Keyword
The .NET Data Provider sends .NET DateTime parameter data to the Data Access Server (DAS) and the DBMS. The format of this DateTime parameter data sent by the provider depends on the level of support of the ANSI Date/Time data types.
In releases prior to Ingres 9.1 (also known as 2006 Release 2), the data is sent as a data type of INGRESDATE with a GMT timezone. Beginning with Ingres 9.1, the date/time data is sent with a data type of ANSI TIMESTAMP_WITH_TIMEZONE (TS_W_TZ) with a local datetime, local timezone, and microsecond data. This flexibility and adaptability to the capabilities of the DBMS is useful, but can have a side-effect when upgrading to new releases of Ingres.
When upgrading from a release of Ingres prior to 9.1, the behavior of the program can change under certain circumstances when datetime formerly sent as an INGRESDATE type is now sent as a TS_W_TZ type. Typically, no problem occurs because the TS_W_TZ data is converted as needed and any components of the data such as microseconds are discarded, depending on the target data type. In some cases, however, the change in data type can change the semantics of processing enough to become an issue.
For example, if datetime parameter data is sent to a query containing the the IFNULL( ? , ' ') function, the function may succeed or fail depending on the data type sent by the provider. If the provider sends the data as INGRESDATE type, then IFNULL(<ingresdate>, ' ' ) will execute correctly (returning the date or the empty string date if the parameter is null). Upon upgrade to Ingres 9.1 and later, the datetime parameter data is sent by the drivers as TIMESTAMP_WITH_TIMEZONE data type, and the IFNULL(<ts_w_tz>, ' ' ) will fail because the empty string date is not permitted in combination with ANSI TIMESTAMP_WITH_TIMEZONE.
The work-around for this problem is to wrap the INGRESDATE function around the parameter: IFNULL( INGRESDATE(?) , ' '). Changing every source reference in an application, however, may not be feasible.
Using the SendIngresDates=TRUE connection keyword tells the .NET Data Provider to send the date/time parameter data as INGRESDATE type and value (as if the server was pre-Ingres 9.1) to force the old behavior.
Caution! SendIngresDates=TRUE is intended to allow older applications to run with newer versions of Ingres. It should be used only in applications that access INGRESDATE columns only. Applications accessing ANSI Date/Time columns may experience other side effects of this feature due to loss of fractional second information and timezone format differences when the datetime parameter data is sent as INGRESDATE to an ANSI date/time column. Rather than using the SendIngresDates option, which is general in scope, we recommend using the IngresType.IngresDate parameter data type when sending .NET DateTime data that requires an INGRESDATE semantic context for the parameter.
IngresConnection.GetSchema Method
The IngresConnection.GetSchema method is used to return information on the schema of a database. The collection of metadata returned describes the tables, columns, and so on, that are defined in the database. The schema information is returned in the form of a .NET Framework DataTable.
GetSchema takes zero or more parameters. The first parameter is the collection name (for example: "Tables" or "Columns"). Optional restriction parameters in a String array ("restrictionValues") allow the returned metadata to be filtered to only those rows for a specified value, for example, only those columns for a specific table name. If GetSchema is invoked with no parameters or with a collection name of "MetaDataCollections" then a DataTable is returned that lists the collection names. Each row in the DataTable lists the collection name and number of restrictions supported for that collection.
The following collections are supported:
MetaDataCollection
DataSourceInformation
DataTypes
Restrictions
ReservedWords
Tables
Views
Columns
Indexes
Procedures
ProcedureParameters
1. Can be used as a restriction in the first entry of restrictionValues array.
2. Can be used as a restriction in the second entry of restrictionValues array.
3. Can be used as a restriction in the third entry of restrictionValues array.
4. Can be used as a restriction in the fourth entry of restrictionValues array.
Enlistment in Distributed Transactions
The .NET Data Provider supports enlistment in distributed transactions through the MS Distributed Transaction Coordinator (MSDTC) and the XA two-phase commit protocol.
Developers should be aware of MSDTC performance with distributed transactions and the lag time in communicating with all voters of the two-phase commit protocol. For performance reasons, distributed transactions should be used carefully. While the enlistment in a distributed transaction is not slow, it is not as fast as an enlistment in a local transaction.
To use the EnlistTransaction and EnlistDistributedTransaction methods in the .NET Data Provider, the administrator of the Windows machine must enable XA transactions through Component Services.
To enable XA support in Windows 7
Select Start, Control Panel, System and Security, Administrative Tools, Component Services, Computers, My Computer, Distributed Transaction Coordinator, (right-click) Local DTC, Properties, Security tab, Enable XA Transactions.
System.Transactions Programming Models
The .NET Framework System.Transactions namespace offers two programming models to the .NET application programmer to create a transaction. The .NET Data Provider supports both models:
• The explicit programming model allows the programmer to create, enlist into, and control the transaction manually.
• The implicit programming model allows .NET to automatically perform these operations. The implicit programming model is recommended as a best practice since there are fewer chances for programming errors and it frees the programmer from the details of managing enlistment in the System.Transactions.Transaction.
Implicit Automatic Enlistment using TransactionScope
The System.Transactions.TransactionScope class allows a .NET application to establish a transaction context. When a TransactionScope is instantiated, a current transaction context is established by .NET. Resource managers such as Ingres by default enlist in this ambient transaction. If an IngresConnection.Open() is issued by the application within the scope of this current transaction, and if the ConnectionString contains Enlist=yes (the default), then the IngresConnection automatically enlists the IngresConnection into the transaction.
Within the scope of the TransactionScope, the application calls the TransactionScope.Complete() method to indicate that the database unit of work should be committed. If the method is not called, the work is rolled back. When the TransactionScope is Disposed, updates to the Ingres database are committed or rolled back as directed by the .NET Transaction Manager. The Transaction Manager examines whether TransactionScope.Complete() method was called and issues the appropriate commit or rollback statements to Ingres.
Note: When an Ingres connection is enlisted in the .NET transaction, the commit or rollback to Ingres to commit/rollback the database changes occur when the TransactionScope is disposed, not when the IngresConnection is closed or disposed. Even though a IngresConection.Close() method has been called and the IngresConection instance has been disposed, the Ingres session remains active until the .NET TransactionScope is disposed and the .NET Transaction Manager issues the commit/rollback to the Ingres session.
The .NET application programmer can prevent automatic enlistment of the IngresConnection in the transaction context if the IngresConnection.ConnectionString includes the Enlist=No keyword/value pair.
The advantage to coding a "using" statement and TransactionScope is that if any of the database operations throws an exception, flow of control jumps out of the "using (TransactionScope)" block and a rollback of the transaction automatically occurs. The ease of programming and reliability of rollback or commit within the TransactionScope makes this model of enlistment a better programming practice.
TransactionScope Example
This example shows the enlistment of an Ingres database session in a distributed transaction managed by a using TransactionScope block. When the IngresConnection.Open( ) method is issued, the Ingres connection will enlist in the distributed transaction represented by the Transaction object within the TransactionScope. After the update, the TransactionScope is marked Complete(), and the updates to the Ingres database will be committed when the TransactionScope object is disposed.
static void TestEnlistTransactionImplicitSample(
string connstring)
{
using (TransactionScope scope = new TransactionScope())
{
using (IngresConnection conn1 =
new IngresConnection(connstring))
{
conn1.Open();
IngresCommand cmd = conn1.CreateCommand();
cmd.CommandText =
"update authors set au_id = '409-56-7008' " +
"where au_id = '409-56-7008'";
cmd.ExecuteNonQuery();
scope.Complete();
} // end using (IngresConnection)
} // end using (TransactionScope)
}
Explicit Enlistment by EnlistTransaction() Method
A .NET application can disable automatic transaction enlistment and manually enlist the Ingres connection in the transaction if desired. The application programmer can prevent automatic enlistment of the IngresConnection in the current transaction context if the IngresConnection.ConnectionString includes the Enlist=no or Enlist=false keyword/value pair. Later, the application can manually enlist the Ingres connection in the transaction by calling the IngresConnection.EnlistTransaction method. The .NET Transaction Manager will issue a commit to the Ingres transaction if the .NET System.Transactions.Transaction is marked complete before the Transaction object is disposed, else the Ingres transaction will be rollbacked.
Ingres Enlistment in a .NET Transaction
Whether the enlistment is automatic or manual, when an Ingres connection is enlisted in a .NET transaction, the .NET Data Provider participates as a resource manager within the transaction. The data provider works with the Microsoft Distributed Transaction Coordinator (MSDTC), Ingres DBMS Server, and the Ingres XA Distributed Transaction Processing (DTP) subsystem to allow the Ingres connection to participate in the distributed transaction on Windows with other Ingres or non-Ingres participants. The participants are polled in a vote to commit in a two-phase commit protocol (2PC). If the vote to commit is unanimous, all participants are directed to commit; otherwise, they are directed to roll back the database updates as an atomic unit of work.