Phil's Notes

Inserting Multiple Records into Oracle with OleDB and Binding in C#

c#oracle

While there is a way to do this with an Oracle driver, but in case you have highly restricted access to your PC and can’t look at or configure the LDAP settings, you can get by using OleDB.

First, be sure to include this statement up top:

using System.Data.OleDb;

In the example below, we are adding 10 new usernames to a table that are named BOT01 – BOT10. It’s a pretty basic example that you can edit to suit your needs.

string connectionString = "Provider=OraOLEDB.Oracle;Data Source=<your_data_source>;User ID=<user_id>;Password=<password>;";
string insertStatement = $@"INSERT INTO <your_table>
                            (username, effective_date)
                            VALUES
                            (:username, to_date('1-JAN-2019', 'DD-MON-YYYY')";
 
try
{
    using (OleDbConnection connection = new OleDbConnection(connectionString))
    {
        connection.Open();
        using (OleDbCommand command = new OleDbCommand(insertStatement, connection))
        {
            // For single inserts, you can just do this:
            // command.Parameters.Add(new OleDbParameters("username", "<your_username>"));
            // command.ExecuteNonQuery();
 
            // Otherwise for multiple inserts
            command.Parameters.Add(new OleDbParameters("username", OleDbType.VarChar));
            for (int i = 1; i <= 10; i++)
            {
                string botUsername = $"BOT{i.ToString("D2")}";
                Console.WriteLine($"Inserting {botUsername} record...");
                command.Parameters["username"].Value = botUsername;
                command.ExecuteNonQuery();
            }
        }
    }
}
catch (Exception ex)
{
    Console.WriteLine($"Exception: {ex.Message}");
}
Console.WriteLine("Done");
Console.ReadLine();