This article will explain how we can insert bulk amounts of data in an Oracle database using C#. Generally, what happens is, we insert records one-by-one into the database, but sometimes, we have requirements to insert numerous amounts of data into the database in one go. This means, rather than going through a one by one process which is a lengthy and time-consuming process, we prefer to insert whole data all at once into the database. So, today we will see two different ways using which we can insert bulk data into Oracle database table using C#.
For this demonstration, we will use SQL Developer Tools for managing our database related tasks and Visual Studio 2017 for creating console applications using C# where we can implement both approaches.
So, let's move to the demonstration and create a table in Oracle database "Test_Db" where we will insert the Bulk Data. Open SQL Developer Tool and create a table name as “BulkInsertTest” with following scripts. The table has three columns as Id, Name, Address.
CREATE TABLE "TEST_DB"."BULKINSERTTEST"
( "ID" NUMBER NOT NULL ENABLE,
"NAME" VARCHAR2(200 BYTE) NOT NULL ENABLE,
"ADDRESS" VARCHAR2(500 BYTE)
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE " TEST_DB_DATA" ;
Now we have Oracle table ready. Let’s move to Visual Studio 2017 where we will do a practical demonstration and see both ways of inserting bulk data. So, open Visual Studio with any version [For me, it's Visual Studio 2017] and create a Console Application with a name as "Bulk Insert" with Visual C#. So, we have application ready now.
While working with a database, we must provide the connection string into configuration file to access the database and its table. So, open App.Config inConsole Application and add a connection string inside <appSettings> tag as follows.
<appSettings>
<add key="connectionString"
value="data source=mukesh-pc:1531/mukesh;password=xxxxxxx;user id=mukesh;Connection Timeout=3600;Incr Pool Size=5;Decr Pool Size=2;"/>
</appSettings>
FIRST WAY TO INSERT BULK DATA
Now the database table is ready, and the connection string is also ready. Let’s right click on solution and click Manage NugGet Packages. It will open a window from where we can install new packages, update packages or see installed packages. Just go to Browse tab and search “Oracle.ManageDataAccess” and install the first one.
We require this package because of performing database operations between C# and Oracle databases.
![FIRST WAY TO INSERT BULK DATA]()
Now move to Program.cs class and add the following code to insert bulk data.
using Oracle.ManagedDataAccess.Client;
using System;
using System.Configuration;
using System.Data;
namespace BulkInsert
{
class Program
{
private readonly string connectionString = ConfigurationManager.AppSettings["connectionString"].ToString();
static void Main(string[] args)
{
Program objProgram = new Program();
DataTable dt = new DataTable();
dt.Columns.Add("ID");
dt.Columns.Add("NAME");
dt.Columns.Add("ADDRESS");
for (int i = 0; i < 200000; i++)
{
DataRow dr = dt.NewRow();
dr["ID"] = i;
dr["NAME"] = "Hello " + i;
dr["ADDRESS"] = "World " + i;
dt.Rows.Add(dr);
}
objProgram.SaveUsingOracleBulkCopy(dt);
}
public void SaveUsingOracleBulkCopy(DataTable dt)
{
try
{
using (var connection = new OracleConnection(connectionString))
{
connection.Open();
int[] ids = new int[dt.Rows.Count];
string[] names = new string[dt.Rows.Count];
string[] addresses = new string[dt.Rows.Count];
for (int j = 0; j < dt.Rows.Count; j++)
{
ids[j] = Convert.ToInt32(dt.Rows[j]["ID"]);
names[j] = Convert.ToString(dt.Rows[j]["NAME"]);
addresses[j] = Convert.ToString(dt.Rows[j]["ADDRESS"]);
}
OracleParameter id = new OracleParameter();
id.OracleDbType = OracleDbType.Int32;
id.Value = ids;
OracleParameter name = new OracleParameter();
name.OracleDbType = OracleDbType.Varchar2;
name.Value = names;
OracleParameter address = new OracleParameter();
address.OracleDbType = OracleDbType.Varchar2;
address.Value = addresses;
// create command and set properties
OracleCommand cmd = connection.CreateCommand();
cmd.CommandText = "INSERT INTO BULKINSERTTEST (ID, NAME, ADDRESS) VALUES (:1, :2, :3)";
cmd.ArrayBindCount = ids.Length;
cmd.Parameters.Add(id);
cmd.Parameters.Add(name);
cmd.Parameters.Add(address);
cmd.ExecuteNonQuery();
}
}
catch (Exception ex)
{
throw ex;
}
}
}
}
First, we have prepared data which needs to be inserted into the database. So, here we are going to create some dummy data using for loop and add it to a data table. So, create a DataTable with three columns as Id, Name, and Address. And add two lakhs record into it inside the Main method using a loop. Now create one method as SaveUsingOracleBulkCopy(DataTable dt) which takes one parameter as a data table. This method will perform a task to insert the data into the database.
Get the connection string and create a connection with Oracle database and open it. Once the connection is available we can perform database operations like insert, update etc. Before adding the data, we have to bind data with Oracle Parameter, so get the data from the data table and give it to Oracle parameter. And at the last, we will write oracle insert command and pass the arguments as a value and finally call the method inside the main method just after preparing the data for the data table.
Now run the code and check your database, you will find that two lakhs record has added inside the BulKInsertTest table.
SECOND WAY TO INSERT BULK DATA
Here we will write minimum code to perform bulk insertion of data into Oracle database where we will only pass the table name in which we want to insert the data and data table that have data. So, just going to use Oracle.DataAccess.dll for creating connection and performing the database operation using CSharp.
NOTE
I am using the same application for both examples. So, I just removed “Oracle.ManageDataAccess” from NuGet before proceeding with the second approach which we have added in the first approach.
NOTE
We can not directly use Oracle.DataAccess.dll, if we do, we will get the following error. Error: Could not load file or assembly 'Oracle.DataAccess, Version=2.122.1.0, Culture=neutral, PublicKeyToken=89b483f429c47342' or one of its dependencies.
To resolve the above error, before using Oracle.DataAccess.dll, we have to install Oracle Client Installation at your local system. You can download Oracle client from the following link. Go to the following link and select Oracle version 11 from the dropdown list and Accept the License Agreement and then click to Download.
Once it will be downloaded as a zip file, just extract the zip file. Once the zip file gets extracted than go to the folder and inside that there is a folder named Install. Now move to install folder, in this folder we have an executable file “oui.exe”. You must run it for installing the Oracle client.
Reference - How to install Oracle Client
Once Oracle client is installed then move to the solution and right click on solution and choose “Manage NuGet Packages”. From the NuGet, you have to search ODP.NET as follows.
![]()
And install the ODP.NET x86 version. Once it will be installed, give the reference of Oracle.DataAccess.dll from the ODP.NET installed package with your project folder. Once more you must move to Program.cs file and add following code into the class.
using System;
using System.Configuration;
using System.Data;
namespace BulkInsert
{
class Program
{
private readonly string connectionString = ConfigurationManager.AppSettings["connectionString"].ToString();
static void Main(string[] args)
{
Program objProgram = new Program();
DataTable dt = new DataTable();
dt.Columns.Add("ID");
dt.Columns.Add("NAME");
dt.Columns.Add("ADDRESS");
for (int i = 0; i < 200000; i++)
{
DataRow dr = dt.NewRow();
dr["ID"] = i;
dr["NAME"] = "Hello " + i;
dr["ADDRESS"] = "World " + i;
dt.Rows.Add(dr);
}
objProgram.SaveUsingOracleBulkCopy("BULKINSERTTEST", dt);
}
public void SaveUsingOracleBulkCopy(string destTableName, DataTable dt)
{
try
{
using (var connection = new Oracle.DataAccess.Client.OracleConnection(connectionString))
{
connection.Open();
using (var bulkCopy = new Oracle.DataAccess.Client.OracleBulkCopy(connection, Oracle.DataAccess.Client.OracleBulkCopyOptions.UseInternalTransaction))
{
bulkCopy.DestinationTableName = destTableName;
bulkCopy.BulkCopyTimeout = 600;
bulkCopy.WriteToServer(dt);
}
}
}
catch (Exception ex)
{
throw ex;
}
}
}
}
As you can see with the above code, we are using Oracle.DataAccess. It is because in this library we have an available method as “OracleBulkCopy” which inserts whole data in one go without writing a manual script for adding the record. We have to pass only table name and data. It will automatically connect with table and map the columns and finally insert the whole data into one go. For data preparing, we are using the same approach as we have used in the first approach.
Conclusion
So, today we have seen two different ways to insert bulk data into oracle database using C#.
I hope this post will help you. Please put your feedback using comments which helps me to improve myself for the next post. If you have any doubts please ask your doubts or queries in the comments section and If you like this post, please share it with your friends. Thanks.
Drop here!