How to update/add large amount of data into Oracle database from ASP.NET Core

Question

I am working on an ASP.NET Core MVC application written in C#, where I have 2 jQuery datatables in a .cshtml view. I want to add and update large amount of data from these 2 jQuery datatables to the Oracle database.

There is 2 stored procedures in the Oracle database. I want to call these 2 stored procedures to add and update the records into Oracle database. I want to insert data into a table in Oracle DB and update the data into another table in Oracle DB (2 different operations at a time).

Is there any bulk copy or bulk insert or bulk update in Oracle using C# code? In some posts, it says bulk copying/inserting is not a proper way to do this. Is that correct ?

OracleConnection con = new  OracleConnection(ConnectionString);
con.Open();

OracleCommand cmd = new OracleCommand(con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "procedure name";

cmd.ExecuteNonQuery();
 

I am getting time out exception if sending over 1000 records to Oracle. Is there any way to do it ?

Answer 1

Suppose you have a list of users

public class Person
{
    public int ID { get; set; }
    public string Name { get; set; }
}

You could use Bulk Insert with a list of Person objects (List) and bulk insert or update these people into your Oracle database, with Transactions

using Oracle.ManagedDataAccess.Client;
using System;
using System.Collections.Generic;
using System.Data;

public class OracleBulkOperations
{
    private readonly string connectionString = "your_connection_string";

    public void BulkInsert(List<Person> people)
    {
        using (var connection = new OracleConnection(connectionString))
        {
            connection.Open();
            using (var transaction = connection.BeginTransaction())
            {
                try
                {

                    // Bulk Insert using stored procedure
                    using (var cmdInsert = new OracleCommand("YourInsertStoredProcedure", connection))
                    {
                        cmdInsert.BindByName = true;
                        cmdInsert.CommandType = CommandType.StoredProcedure;

                        // Set the number of records being processed
                        cmdInsert.ArrayBindCount = people.Count;
    
                        // Bind arrays for insert
                        cmdInsert.Parameters.Add("p_ids", OracleDbType.Int32, people.Select(t=>t.ID).ToArray(), ParameterDirection.Input);
                        cmdInsert.Parameters.Add("p_names", OracleDbType.Varchar2, people.Select(t=>t.Name).ToArray(), ParameterDirection.Input);

                        // Execute bulk insert
                        cmdInsert.ExecuteNonQuery();
                    }

                  
                    // Commit the transaction
                    transaction.Commit();
                }
                catch (Exception ex)
                {
                    transaction.Rollback();
                    Console.WriteLine($"An error occurred: {ex.Message}");
                }
            }
        }
    }
}