C# and Oracle Introduction

Oracle is a database that can be used in conjunction with C# to create desktop, web and console based applications. It can be used to store data, as well as configuration information. C# can be used to query the data stored within an Oracle database, as well as insert, update and delete the data.

Connecting to an Oracle Database

In order to access data within an Oracle database with C#, a connection to the database must first be established. To facilitate this, the package 'Oracle.ManagedDataAccess.Core' needs to be added to the project. This can be done in a number of different ways, depending on what Integrated Development Environment (IDE) is being used. Visual Studio incorporates NuGet Package Manager, which allows for packages to be searched for and installed. It also has an integrated package manager console, where this package can be added using the following command.

Install-Package Oracle.ManagedDataAccess.Core

For IDEs that don't have a built in package manager, Powershell can be used. The following command can be used to install the above mentioned package. Before running this command it is necessary to navigate to the folder where the project resides. It should be noted that this command will also work in Linux, through a Terminal window.

dotnet add package Oracle.ManagedDataAccess.Core

Once added, a ‘using‘ statement for the ‘Oracle.ManagedDataAccess.Client’ namespace needs to be included.

// Database variables.
string source = "localhost:1521/Demo";
string username = "DemoUN";
string password = "DemoPW";

// Database connection variable.
OracleConnection connect = new OracleConnection(
    "Data Source=" + source + ";" + 
    "User Id=" + username + ";" + 
    "Password=" + password);


    // Connect to database.

    // Message confirming successful database connection.
    Console.WriteLine("Database connection successful.");

catch (Exception e)

    // Message confirming unsuccessful database connection.
    Console.WriteLine("Database connection unsuccessful.");

    // Stop program execution.


In this example, a ‘try-catch’ block is used to catch any exceptions that may arise in connecting to the database. A ‘try-catch’ block can be used to handle any exceptions in a user friendly manner. The 'source' refers to the location of the database, as well as the Oracle SID, or System Identifier. The ‘System.Environment.Exit(1)’ statement, after the confirmation of an unsuccessful database connection, stops execution of the program completely, so no further statements are executed.

It should be noted that it isn’t necessary to have the source and database information in separate variables before using it in the database connection. It much depends on personal preference and whether the variables are going to be re-used elsewhere in the program. The database connection variable can be re-written as follows.

// Database connection variable.
OracleConnection connect = new OracleConnection(
    "Data Source=localhost:1521/Demo;" + 
    "User Id=DemoUN; Password=DemoPW");

Further Resources