Release 9.2.0.4
Part Number B10961-01
 Contents |
 Index |
| Oracle® Data Provider for .NET Developer's Guide Release 9.2.0.4 Part Number B10961-01 |
|
Oracle.DataAccess.Client Namespace, 4 of 30
An OracleConnection object represents a connection to an Oracle database.
Object
MarshalByRefObject
Component
OracleConnection
// C# public sealed class OracleConnection : Component, IDbConnection, ICloneable
All public static methods are thread-safe, although instance methods do not guarantee thread safety.
// C# // Uses connection to create and return an OracleCommand object. ... string ConStr = "User Id=myschema;Password=mypassword;" + "Data Source=oracle;"; OracleConnection con = new OracleConnection(ConStr); con.Open(); OracleCommand cmd = con.CreateCommand(); cmd.CommandText = "insert into mytable values (99, 'foo')"; cmd.CommandType = CommandType.Text; cmd.ExecuteNonQuery(); ...
Namespace: Oracle.DataAccess.Client
Assembly: Oracle.DataAccess.dll
OracleConnection members are listed in the following tables:
OracleConnection constructors are listed in Table 4-19.
| Constructor | Description |
|---|---|
|
Instantiates a new instance of the |
OracleConnection static methods are listed in Table 4-20.
| Methods | Description |
|---|---|
|
|
Inherited from |
OracleConnection properties are listed in Table 4-21
OracleConnection public methods are listed in Table 4-22.
OracleConnection events are listed in Table 4-23.
OracleConnection event delegates are listed in Table 4-24.
| Event Delegate Name | Description |
|---|---|
|
An event delegate that handles the |
|
|
An event delegate that handles the |
|
|
An event delegate that handles the |
OracleConnection constructors instantiate new instances of the OracleConnection class.
This constructor instantiates a new instance of the OracleConnection class using default property values.
This constructor instantiates a new instance of the OracleConnection class with the provided connection string.
This constructor instantiates a new instance of the OracleConnection class using default property values.
// C# public OracleConnection();
The properties for OracleConnection are set to the following default values:
ConnectionString = empty string
ConnectionTimeout = 15
DataSource = empty string
ServerVersion = empty string
This constructor instantiates a new instance of the OracleConnection class with the provided connection string.
// C# public OracleConnection(String connectionString);
The ConnectionString property is set to the supplied connectionString. The ConnectionString property is parsed and an exception is thrown if it contains invalid connection string attributes or attribute values.
The properties of the OracleConnection object default to the following values unless they are set by the connection string:
ConnectionString = empty string
ConnectionTimeout = 15
DataSource = empty string
ServerVersion = empty string
OracleConnection static methods are listed in Table 4-25.
| Methods | Description |
|---|---|
|
|
Inherited from |
OracleConnection properties are listed in Table 4-26
This property specifies connection information used to connect to an Oracle database.
// C# public string ConnectionString{get; set;}
If the connection string is supplied through the constructor, this property is set to that string.
IDbConnection
ArgumentException - An invalid syntax is specified for the connection string.
InvalidOperationException - ConnectionString is being set while the connection is open.
The default value is an empty string.
ConnectionString must be a string of attribute name and value pairings, separated by a semi-colon, for example:
// C# OracleConnection con = new OracleConnection(); con.ConnectionString = "User Id=MYSCHEMA;Password=MYPASSWORD;" + "Data Source=Oracle";
If the ConnectionString is not in a proper format, an exception is thrown. All spaces are ignored unless they are within double quotes.
When the ConnectionString property is set, the OracleConnection object immediately parses the string for errors. An ArgumentException is thrown if the ConnectionString contains invalid attributes or invalid values. Attribute values for User Id, Password, Proxy User Id, Proxy Password, and Data Source (if provided) are not validated until the Open method is called.
The connection must be closed to set the ConnectionString property. When the ConnectionString property is reset, all previously set values are reinitialized to their default values before the new values are applied.
The Oracle database supports case-sensitive user names. To connect as a user whose name is of mixed case, for example, "MySchema", the User Id attribute value must be surrounded by double quotes, as follows:
// C# OracleConnection con = new OracleConnection(); con.ConnectionString = "User Id=\"MySchema\";Password=MYPASSWORD;" + "Data Source=Oracle";
However, if the Oracle user name is all upper case, the User Id connection string attribute can be set to that user name without the use of the double quotes since User Ids that are not doubled-quoted are converted to all upper case when connecting. Single quotes are not supported.
|
See Also:
"Example" for a complete example |
If a connection string attribute is set more than once, the last setting takes effect and no exceptions are thrown.
Boolean connection string attributes can be set to either true, false, yes, or no.
Supported connection string attributes:
Table 4-27 lists the supported connection string attributes.
This code example shows that the case of the User Id attribute value is not preserved unless it is surrounded by double quotes. The example also demonstrates when connection pools are created and when connections are drawn from the connection pool.
// C# // Assume users "MYSCHEMA"and "MySchema" exist in the database ... OracleConnection con1 = new OracleConnection(); con1.ConnectionString = "User Id=myschema;Password=mypassword;" + "Data Source=oracle;"; con1.Open(); // Attempts to connect as "MYSCHEMA/MYPASSWORD" // A new connection is created; A new Connection Pool X is created con1.Dispose(); // Connection is placed back into Pool X OracleConnection con2 = new OracleConnection(); con2.ConnectionString = "User Id=MySchema;Password=MyPassword;" + "Data Source=oracle;"; con2.Open(); // Attempts to connect as "MYSCHEMA/MYPASSWORD" A connection is // obtained from Pool X; A new connection is NOT created. con2.Dispose(); // Connection is placed back into Pool X OracleConnection con3 = new OracleConnection(); con3.ConnectionString = "User Id=\"MYSCHEMA\";Password=MYPASSWORD;" + "Data Source=oracle;"; con3.Open(); // Attempts to connect as "MYSCHEMA/MYPASSWORD" A connection is // obtained from Pool X; A new connection is NOT created. con3.Dispose(); // Connection is placed back into Pool X OracleConnection con4 = new OracleConnection(); con4.ConnectionString = "User Id=\"MySchema\";Password=mypassword;" + "Data Source=oracle;"; con4.Open(); // Attempts to connect as "MySchema/MYPASSWORD" // A new connection is created; A new Connection Pool Y is created con4.Dispose(); // Connection is placed back into Pool Y OracleConnection con5 = new OracleConnection(); con5.ConnectionString = "User Id=MySchema;Password=mypassword;" + "Data Source=oracle; "; con5.Open(); // Attempts to connect as "MYSCHEMA/MYPASSWORD" // A connection is obtained from Connection Pool X // Extra spaces in the connection string do not force creation // of a new pool con5.Dispose(); // Connection is placed back into Pool X OracleConnection con6 = new OracleConnection(); con6.ConnectionString = "User Id=MySchema;Password=mypassword;" + "Data Source=oracle;Pooling=true;"; con6.Open(); // Attempts to connect as "MYSCHEMA/MYPASSWORD" // A connection is obtained from Connection Pool X. "Pooling=true" // in the connection string does not force creation of a new pool // since the initial connection was established using the default // value of "Pooling=true". Note that even if the connection // string had "POOLING=Yes", a new connection pool will not be // created since they both enable pooling. The same rule applies // to other connection string attributes as well. con6.Dispose(); // Connection is placed back into Pool X ...
This property specifies the maximum amount of time that the Open() method can take to obtain a pooled connection before terminating the request.
// C# public int ConnectionTimeout {get;}
The maximum time allowed for a pooled connection request, in seconds.
IDbConnection
The default value is 15.
Setting this property to 0 allows the pooled connection request to wait for a free connection without a time limit. The timeout takes effect only for pooled connection requests and not for new connection requests.
This property specifies the Oracle Net Service Name (formerly known as TNS alias) that identifies an Oracle database instance.
// C# public string DataSource {get;}
The Oracle Net Service Name.
The default value of this property is an empty string
This property specifies the version number of the Oracle database to which the OracleConnection has established a connection.
// C# public string ServerVersion {get;}
The version of the Oracle database, for example "9.2.0.1.0."
InvalidOperationException - The connection is closed.
The default is an empty string.
This property specifies the current state of the connection.
// C# public ConnectionState State {get;}
The ConnectionState of the connection.
IDbConnection
ODP.NET supports ConnectionState.Closed and ConnectionState.Open for this property. The default value is ConnectionState.Closed.
OracleConnection public methods are listed in Table 4-28.
BeginTransaction methods begin local transactions.
This method begins a local transaction.
This method begins a local transaction with the specified isolation level.
This method begins a local transaction.
// C# public OracleTransaction BeginTransaction();
An OracleTransaction object representing the new transaction.
IDbConnection
InvalidOperationException - A transaction has already been started.
The transaction is created with its isolation level set to its default value of IsolationLevel.ReadCommitted. All further operations related to the transaction must be performed on the returned OracleTransaction object.
This method begins a local transaction with the specified isolation level.
// C# public OracleTransaction BeginTransaction(IsolationLevel isolationLevel);
An OracleTransaction object representing the new transaction.
IDbConnection
InvalidOperationException - A transaction has already been started.
ArgumentException - The isolationLevel specified is invalid.
The following two isolation levels are supported:
Requesting other isolation levels causes an exception.
// C# // Starts a transaction and inserts one record. If insert fails, rolls back // the transaction. Otherwise, commits the transaction. ... string ConStr = "User Id=myschema;Password=mypassword;" + "Data Source=oracle;"; OracleConnection con = new OracleConnection(ConStr); con.Open(); //Create an OracleCommand object using the connection object OracleCommand cmd = new OracleCommand("", con); // Start a transaction OracleTransaction txn = con.BeginTransaction(IsolationLevel.ReadCommitted); try { cmd.CommandText = "insert into mytable values (99, 'foo')"; cmd.CommandType = CommandType.Text; cmd.ExecuteNonQuery(); txn.Commit(); Console.WriteLine("Both records are inserted into the database table."); } catch(Exception e) { txn.Rollback(); Console.WriteLine("Neither record was inserted into the database table."); } ...
This method creates a copy of an OracleConnection object.
// C# public object Clone();
An OracleConnection object.
ICloneable
The cloned object has the same property values as that of the object being cloned.
// C# ... OracleConnection con = new OracleConnection(ConStr); con.Open(); ... //Need a proper casting for the return value when cloned OracleConnection con_cloned = (OracleConnection) con.Clone(); ...
This method closes the connection to the database.
// C# public void Close();
IDbConnection
Performs the following:
The connection can be reopened using Open().
This method creates and returns an OracleCommand object associated with the OracleConnection object.
// C# public OracleCommand CreateCommand();
The OracleCommand object.
IDbConnection
// C# // Uses connection to create and return an OracleCommand object. ... string ConStr = "User Id=myschema;Password=mypassword;" + "Data Source=oracle;"; OracleConnection con = new OracleConnection(ConStr); con.Open(); OracleCommand cmd = Con.CreateCommand(); cmd.CommandText = "insert into mytable values (99, 'foo')"; cmd.CommandType = CommandType.Text; cmd.ExecuteNonQuery(); ...
GetSessionInfo returns or refreshes an OracleGlobalization object that represents the globalization settings of the session.
This method returns a new instance of the OracleGlobalization object that represents the globalization settings of the session.
This method refreshes the provided OracleGlobalization object with the globalization settings of the session.
This method returns a new instance of the OracleGlobalization object that represents the globalization settings of the session.
// C# public OracleGlobalization GetSessionInfo();
The newly created OracleGlobalization object.
// C# // Retrieves the session globalization info and prints the language name. // Then sets new territory, language, and timestamp format into the session // globalization info in the connection object. ... string ConStr = "User Id=myschema;Password=mypassword;" + "Data Source=oracle;"; OracleConnection con = new OracleConnection(ConStr); con.Open(); ... //Get session info from connection object OracleGlobalization ogi = con.GetSessionInfo(); //Print the language name Console.WriteLine(ogi.Language); //Update session info oraGlob.Territory = "JAPAN"; ogi.Language = "JAPANESE"; ogi.TimeStampFormat = "HH.MI.SSXFF AM YYYY-MM-DD"; //Set session info into connection object con.SetSessionInfo(ogi); ...
This method refreshes the provided OracleGlobalization object with the globalization settings of the session.
// C# public void GetSessionInfo(OracleGlobalization oraGlob);
This method opens a connection to an Oracle database.
// C# public void Open();
IDbConnection
ObjectDisposedException - The object is already disposed.
InvalidOperationException - The connection is already opened or the connection string is null or empty.
The connection is obtained from the pool if connection pooling is enabled. Otherwise, a new connection is established.
It is possible that the pool does not contain any unused connections when the Open() method is invoked. In this case, a new connection is established.
If no connections are available within the specified connection timeout value, when the Max Pool Size is reached, an OracleException is thrown.
This method opens a new connection with the new password.
// C# public void OpenWithNewPassword(string newPassword);
This method uses the ConnectionString property settings to establish a new connection. The old password must be provided in the connection string as the Password attribute value.
This method can only be called on an OracleConnection in the closed state.
This method alters the session's globalization settings with all the property values specified in the provided OracleGlobalization object.
// C# public void SetSessionInfo(OracleGlobalization oraGlob);
An OracleGlobalization object.
Calling this method is equivalent to calling an ALTER SESSION SQL on the session.
// C# // Retrieves the session globalization info and prints the language name. // Then sets new territory, language, and timestamp format into the session // globalization info in the connection object. ... string ConStr = "User Id=myschema;Password=mypassword;" + "Data Source=oracle;"; OracleConnection con = new OracleConnection(ConStr); con.Open(); //Create an OracleGlobalization object OracleGlobalization ogi; //Get session info using the second overloaded method con.GetSessionInfo(ogi); //Print the language name Console.WriteLine(ogi.Language); //Update session globalization info oraGlob.Territory = "JAPAN"; ogi.Language = "JAPANESE"; ogi.TimeStampFormat = "HH.MI.SSXFF AM YYYY-MM-DD"; //Set session globalization info into connection object con.SetSessionInfo(ogi);
OracleConnection events are listed in Table 4-29.
This event is triggered when an Oracle failover occurs.
// C# public event OracleFailoverEventHandler Failover;
The event handler receives an OracleFailoverEventArgs object which exposes the following properties containing information about the event.
This event is triggered for any message or warning sent by the database.
// C# public event OracleInfoMessageEventHandler InfoMessage;
The event handler receives an OracleInfoMessageEventArgs object which exposes the following properties containing information about the event.
Errors
The collection of errors generated by the data source.
Message
The error text generated by the data source.
Source
The name of the object that generated the error.
In order to respond to warnings and messages from the database, the client should create an OracleInfoMessageEventHandler delegate to listen to this event.
This event is triggered when the connection state changes.
// C# public event StateChangeEventHandler StateChange;
The event handler receives a StateChangeEventArgs object which exposes the following properties containing information about the event.
The StateChange event is raised after a connection changes state, whenever an explicit call is made to Open, Close or Dispose.
OracleConnection event delegates are listed in Table 4-30.
| Event Delegate Name | Description |
|---|---|
|
An event delegate that handles the |
|
|
An event delegate that handles the |
|
|
An event delegate that handles the |
This event delegate handles the Failover event.
This event delegate handles the InfoMessage event.
This event delegate handles the StateChange event.
|
See Also:
|
|
 Copyright © 2002, 2003 Oracle Corporation. All Rights Reserved. |
|