How to connect an Oracle Data Source by using ADO.NET

Step 1: Provide a connection string

To connect to a database, you must provide a connection string to identify the database. The following list describes several common parameters of connection strings.

• Persist Security Info: if is false, the data source does not return security-sensitive if the connection is open.
• User ID and Password: The data source login and password to use if you are not using integrated security.
• Integrated Security or Trusted_Connection: if true, the data source uses the current account credentials for authentication, if false, you must specify the User ID and Password in the connection string.
• Data Source: The name or network address of the data source instance.
• Initial Catalog or Database: the name of the database.
• Connection Timeout: The length of time in seconds to wait for a connection to the server before the data source terminates the attempt and returns an error. The default timeout is 15 seconds.

Fig 1. Provide a connection string.

Step 2: Retrieve a connection string from an application configuration file.

You can either store connection strings in an application configuration file or you can hard-code them directly in your application. If you store connection strings in the configuration file, you can modify them easily, without having to edit the source code and recompile the application. Each connection string that is stored in an application configuration file has its own assigned name. In an application, you can access a connection string by its programmatic name.

Fig 2. Create and return an OracleConnection object.

Step 3: Handle connection events

ADO.NET connection objects have two events that you can use to retrieve informational messages from a data source or to determine if the state of a connection has changed.

• InfoMessage: Occurs when a data source returns an informational message. Informational messages are messages from a data source that do not result in an exception being thrown.
• StateChage: Occurs when a connection changes from the closed state to the open state or from the open state to the closed state.

If an error occurs at the data source, the data provider throws an exception. However, if the data source returns an informational message, the data provider raises an InfoMessage event instead.

Step 4: Handle connection exceptions

The .NET Data provider for Oracle throws an OracleException when it encounters an error or warning generated by an Oracle database. OracleException objects have a Code property that gets the code portion of the error as an integer and message property that gets a description of the error.

Fig 3. Step 3 and 4: Handle connection events and exceptions, you can see the example below.

The following code show how to test each step in C# code.

Fig 4. The code to test our classes.

Fig 5. The program wrote the events in the log.