Oracle CRUD Tutorials in C#: A Step-by-Step Guide

Oracle Database is a powerful, enterprise-grade relational database management system, and C# is a versatile programming language. In this tutorial, we’ll explore the basics of performing CRUD (Create, Read, Update, Delete) operations in Oracle using C#. We’ll cover the following steps:

Setting Up Your Environment:
- Installing Oracle Database.
- Setting up your C# development environment.
Connecting to Oracle:
- Creating a connection to your Oracle Database.
Creating a Table:
- Writing C# code to create a table in your Oracle Database.
Inserting Data:
- Demonstrating how to insert data into the table.
Querying Data:
- Retrieving data from the table.
Updating Data:
- Modifying existing records in the table.
Deleting Data:
- Deleting records from the table.

1. Setting Up Your Environment

Installing Oracle Database

Download and install Oracle Database from the official Oracle website.

Setting Up Your C# Development Environment

Install Visual Studio or Visual Studio Code, and ensure you have the .NET SDK installed.

2. Connecting to Oracle

To connect to your Oracle Database from a C# application, you can use the Oracle Data Provider for .NET (Oracle.ManagedDataAccess.Client). Install it using NuGet Package Manager or the .NET CLI:

dotnet add package Oracle.ManagedDataAccess

Now, let’s create a connection to your Oracle Database:

using System;
using Oracle.ManagedDataAccess.Client;

class Program
{
    static void Main()
    {
        string connectionString = "User Id=myuser;Password=mypassword;Data Source=mydatasource;";
        OracleConnection connection = new OracleConnection(connectionString);

        try
        {
            connection.Open();
            Console.WriteLine("Connected to Oracle Database!");
        }
        catch (Exception ex)
        {
            Console.WriteLine($"Error: {ex.Message}");
        }
        finally
        {
            connection.Close();
        }
    }
}

Replace myuser, mypassword, and mydatasource with your Oracle Database credentials and data source.

3. Creating a Table

Let’s create a simple users table in your Oracle Database:

string createTableSql = "CREATE TABLE users (" +
    "id NUMBER(10) PRIMARY KEY," +
    "name VARCHAR2(255) NOT NULL," +
    "email VARCHAR2(255) NOT NULL)";
OracleCommand createTableCommand = new OracleCommand(createTableSql, connection);

try
{
    connection.Open();
    createTableCommand.ExecuteNonQuery();
    Console.WriteLine("Table created!");
}
catch (Exception ex)
{
    Console.WriteLine($"Error: {ex.Message}");
}
finally
{
    connection.Close();
}

This code creates a table named users with columns id, name, and email.

4. Inserting Data

Now, let’s insert a user into the users table:

string insertSql = "INSERT INTO users (id, name, email) VALUES (:id, :name, :email)";
OracleCommand insertCommand = new OracleCommand(insertSql, connection);

// Parameters
insertCommand.Parameters.Add(":id", OracleDbType.Int32).Value = 1;
insertCommand.Parameters.Add(":name", OracleDbType.Varchar2).Value = "John Doe";
insertCommand.Parameters.Add(":email", OracleDbType.Varchar2).Value = "[email protected]";

try
{
    connection.Open();
    int rowsAffected = insertCommand.ExecuteNonQuery();
    Console.WriteLine($"Inserted {rowsAffected} row(s)!");
}
catch (Exception ex)
{
    Console.WriteLine($"Error: {ex.Message}");
}
finally
{
    connection.Close();
}

This code inserts a user with the ID 1, name “John Doe,” and email “[email protected]” into the users table.

5. Querying Data

Let’s retrieve data from the users table:

string query = "SELECT * FROM users";
OracleCommand queryCommand = new OracleCommand(query, connection);

try
{
    connection.Open();
    OracleDataReader reader = queryCommand.ExecuteReader();
    while (reader.Read())
    {
        Console.WriteLine($"ID: {reader["id"]}, Name: {reader["name"]}, Email: {reader["email"]}");
    }
}
catch (Exception ex)
{
    Console.WriteLine($"Error: {ex.Message}");
}
finally
{
    connection.Close();
}

This code queries and displays all records in the users table.

6. Updating Data

Let’s update a user’s email address:

string updateSql = "UPDATE users SET email = :newEmail WHERE name = :name";
OracleCommand updateCommand = new OracleCommand(updateSql, connection);

// Parameters
updateCommand.Parameters.Add(":newEmail", OracleDbType.Varchar2).Value = "[email protected]";
updateCommand.Parameters.Add(":name", OracleDbType.Varchar2).Value = "John Doe";

try
{
    connection.Open();
    int rowsAffected = updateCommand.ExecuteNonQuery();
    Console.WriteLine($"Updated {rowsAffected} row(s)!");
}
catch (Exception ex)
{
    Console.WriteLine($"Error: {ex.Message}");
}
finally
{
    connection.Close();
}

This code updates the email address of the user with the name “John Doe” in the users table.

7. Deleting Data

Let’s delete a user from the users table:

string deleteSql = "DELETE FROM users WHERE name = :name";
OracleCommand deleteCommand = new OracleCommand(deleteSql, connection);

// Parameter
deleteCommand.Parameters.Add(":name", OracleDbType.Varchar2).Value = "John Doe";

try
{
    connection.Open();
    int rowsAffected = deleteCommand.ExecuteNonQuery();
    Console.WriteLine($"Deleted {rowsAffected} row(s)!");
}
catch (Exception ex)
{
    Console.WriteLine($"Error: {ex.Message}");
}
finally
{
    connection.Close();
}

This code deletes the user with the name “John Doe” from the users table.

With these CRUD operations, you have a solid foundation for working with Oracle Database in your C# applications. Feel free to extend and adapt these examples to meet the requirements of your specific project. Oracle Database’s scalability and reliability make it a great choice for enterprise-level applications.