C# - With Oracle

Performing CRUD (Create, Read, Update, Delete) operations with Oracle in C# involves connecting to an Oracle database, executing SQL commands, and processing results. Here’s a step-by-step guide for setting up and performing CRUD operations with Oracle using C#.

1. Set Up Oracle Database

Ensure you have Oracle Database installed and running. Use Oracle SQL Developer or another Oracle client to manage your database and create tables if needed.

2. Install Oracle Data Provider for .NET (ODP.NET)

To connect to Oracle from C#, you need Oracle Data Provider for .NET. The package Oracle.ManagedDataAccess can be installed via NuGet.

Using Visual Studio:

Right-click on your project in Solution Explorer.
Choose "Manage NuGet Packages."
Search for Oracle.ManagedDataAccess and install it.

Using .NET CLI:

bash
dotnet add package Oracle.ManagedDataAccess

3. Create a Basic C# Application for CRUD Operations

Below are examples of how to perform CRUD operations using C# and Oracle.

Create (Insert Data)

csharp
using Oracle.ManagedDataAccess.Client;
using System;

class Program
{
    static void Main()
    {
        string connectionString = "User Id=your_username;Password=your_password;Data Source=your_data_source;";

        using (OracleConnection connection = new OracleConnection(connectionString))
        {
            connection.Open();

            string insertQuery = "INSERT INTO YourTable (Column1, Column2) VALUES (:Value1, :Value2)";
            using (OracleCommand command = new OracleCommand(insertQuery, connection))
            {
                command.Parameters.Add(new OracleParameter("Value1", "SampleValue1"));
                command.Parameters.Add(new OracleParameter("Value2", "SampleValue2"));

                int rowsAffected = command.ExecuteNonQuery();
                Console.WriteLine($"{rowsAffected} row(s) inserted.");
            }
        }
    }
}

Read (Select Data)

csharp
using Oracle.ManagedDataAccess.Client;
using System;

class Program
{
    static void Main()
    {
        string connectionString = "User Id=your_username;Password=your_password;Data Source=your_data_source;";

        using (OracleConnection connection = new OracleConnection(connectionString))
        {
            connection.Open();

            string selectQuery = "SELECT * FROM YourTable";
            using (OracleCommand command = new OracleCommand(selectQuery, connection))
            {
                using (OracleDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        string column1Value = reader["Column1"].ToString();
                        string column2Value = reader["Column2"].ToString();
                        Console.WriteLine($"Column1: {column1Value}, Column2: {column2Value}");
                    }
                }
            }
        }
    }
}

Update (Modify Data)

csharp
using Oracle.ManagedDataAccess.Client;
using System;

class Program
{
    static void Main()
    {
        string connectionString = "User Id=your_username;Password=your_password;Data Source=your_data_source;";

        using (OracleConnection connection = new OracleConnection(connectionString))
        {
            connection.Open();

            string updateQuery = "UPDATE YourTable SET Column1 = :NewValue WHERE Column2 = :Condition";
            using (OracleCommand command = new OracleCommand(updateQuery, connection))
            {
                command.Parameters.Add(new OracleParameter("NewValue", "UpdatedValue"));
                command.Parameters.Add(new OracleParameter("Condition", "ConditionValue"));

                int rowsAffected = command.ExecuteNonQuery();
                Console.WriteLine($"{rowsAffected} row(s) updated.");
            }
        }
    }
}

Delete (Remove Data)

csharp
using Oracle.ManagedDataAccess.Client;
using System;

class Program
{
    static void Main()
    {
        string connectionString = "User Id=your_username;Password=your_password;Data Source=your_data_source;";

        using (OracleConnection connection = new OracleConnection(connectionString))
        {
            connection.Open();

            string deleteQuery = "DELETE FROM YourTable WHERE Column1 = :Condition";
            using (OracleCommand command = new OracleCommand(deleteQuery, connection))
            {
                command.Parameters.Add(new OracleParameter("Condition", "ValueToDelete"));

                int rowsAffected = command.ExecuteNonQuery();
                Console.WriteLine($"{rowsAffected} row(s) deleted.");
            }
        }
    }
}

4. Handling Exceptions

Proper exception handling ensures that your application can gracefully handle errors. Use try-catch blocks to handle exceptions.

csharp
using Oracle.ManagedDataAccess.Client;
using System;

class Program
{
    static void Main()
    {
        string connectionString = "User Id=your_username;Password=your_password;Data Source=your_data_source;";

        try
        {
            using (OracleConnection connection = new OracleConnection(connectionString))
            {
                connection.Open();

                // Perform CRUD operations here

                Console.WriteLine("Operation completed successfully.");
            }
        }
        catch (OracleException ex)
        {
            Console.WriteLine($"Oracle Error: {ex.Message}");
        }
        catch (Exception ex)
        {
            Console.WriteLine($"Error: {ex.Message}");
        }
    }
}

5. Parameterization

Always use parameterized queries to avoid SQL injection and handle user inputs safely.

techmore.in