ODP.Net Oracle Bulk Copy

OracleBulkCopy Class

The ODP.NET Bulk Copy feature helps you to bulk upload or copy large amount of data into an oracle table from another data source.  Even though the target of bulk copy is restricted to oracle database table, data source is not limited to oracle database. It can be tables from same or another database or data from any datasource which is loaded to a DataTable instance or read with an IDataReader instance. The ODP.NET Bulk Copy feature uses a direct path load approach, which is similar to, but not the same as Oracle SQL*Loader. Using direct path load is faster than the conventional loading using conventional SQL INSERT statements.

Requirements 

Namespace: Oracle.DataAccess.Client

Assembly: Oracle.DataAccess.dll

OracleBulkCopy Properties

BatchSize :  Specifies the number of rows to be sent as a batch to the database. The default value is zero, which indicating the entire set of rows are sent in a single batch.

•If BatchSize> 0 and the UseInternalTransaction bulk copy option is specified, each batch of the bulk copy operation occurs within a transaction.

•If BatchSize> 0 and the UseInternalTransaction option is not specified, rows are sent to the database in batches of size BatchSize, but no transaction-related action is taken.

BulkCopyOptions :  Specifies the OracleBulkCopyOptions enumeration value that determines the behavior of the bulk copy operation

BulkCopyTimeout :  Specifies the number of seconds allowed for the bulk copy operation to complete before it is aborted. The default value is 30 seconds.

If BatchSize>0, rows that were sent to the database in the previous batches remain committed. The rows that are processed in the current batch are not sent to the database.

 If BatchSize=0, no rows are sent to the database.

ColumnMappings : Specifies the column mappings between the data source and destination table.

The ColumnMappings collection is unnecessary if the data source and the destination table have the same number of columns, and the ordinal position of each source column matches the ordinal position of the corresponding destination column. However, if the column counts differ, or the ordinal positions are not consistent, the ColumnMappings collection must be used to ensure that data is copied into the correct columns.

Connection :  Specifies the OracleConnection object that the Oracle database uses to perform the bulk copy operation

DestinationTableName : specifies the database table that the data is loaded into.

NotifyAfter :  Defines the number of rows to be processed before a notification event is generated. The default value for this property is zero, to specify that no notifications events are to be generated.This property can be retrieved in user interface components to display the progress of a bulk copy operation.

OracleBulkCopy Public Methods

Close :  Closes the OracleBulkCopy instance

Dispose :  Releases any resources or memory allocated by the object

WriteToServer :  This method copies all rows from the supplied data source  to a destination table specified by the DestinationTableName property of the OracleBulkCopy object.  The data source can be passed as argument of type DataRow array, DataTable, IDataReader, or OracleRefCursor.

OracleBulkCopy Events

OracleRowsCopied : Triggered every time the number of rows specified by the OracleBulkCopy.NotifyAfter property has been processed. To cancel the operation from this event, use the Abort property of OracleRowsCopiedEventArgs class

 OracleBulkCopy Class in Object Browser

 

Restrictions on Oracle Bulk Copy of a Single Partition

•The table that contains the partition cannot have any global indexes defined on it.

•The tables that the partition is a member of cannot have referential and check constraints enabled.

•Enabled triggers are not allowed.

Enabled Constraints

During an Oracle bulk copy, the following constraints are automatically enabled by default:

•NOT NULL

•UNIQUE

•PRIMARY KEY (unique-constraints on not-null columns)

Disabled Constraints

During an Oracle bulk copy, the following constraints are automatically disabled by default:

•CHECK constraints

•Referential constraints (FOREIGN KEY)

Database Insert Triggers

Table insert triggers are disabled when a direct path load begins. After the rows are loaded and indexes rebuilt, any triggers that were disabled are automatically re-enabled. 

Field Defaults

Default column specifications defined in the database are not available with direct path loading.

Dot Net Source Code Sample

For inserting bulk data into the Oracle database we need use ODP.NET (Oracle Data Provider for .NET). Install ODP.NET and add a reference to the Oracle.DataAccess.dllto the project. 

 

Sample 1:

In this sample I am trying to load data from excel file to oracle table. Oracle table have same column names as in the excel.There are more than 2 Lakh records in my excel file. Loading takes 3 to 4 minutes.
 

publicboolReadExcelUpdateTable(stringfileName, stringoracleConn)

{

try

{

stringxlsxConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0 Xml;HDR=Yes;IMEX=1;';";

xlsxConn = string.Format(xlsxConn, HttpContext.Current.Server.MapPath(fileName)).Trim();

//Create Connection to Excel Workbook

using (OleDbConnection connection = newOleDbConnection(xlsxConn))

                {

OleDbCommand command = newOleDbCommand("SELECT * FROM [PackingDataSheet$]", connection);

connection.Open();

//Create DbDataReader to Data Worksheet

using (OleDbDataReaderdr = command.ExecuteReader())

                    {

//Bulk Copy to Database

using (OracleBulkCopybulkCopy = newOracleBulkCopy(oracleConn))

                        {

bulkCopy.DestinationTableName = "TB_PACKING_LIST";

bulkCopy.WriteToServer(dr);

bulkCopy.Close();

                        }

                    }

                }

returntrue;

            }

catch (Exception ex)

            {

returnfalse;

            }

} 

 

Sample 2:

  

Now  I am trying to load data from excel file to oracle table. Oracle table don’t have same column names as in the excel. But have same number of columns and ordinal position’s also same. First,read excel data into DataTable and try to insert to oracle table using OracleBulkCopy. I am sharing my code below.

privateboolDoBulkCopy(stringconnString)

{

DataTabledtExcelData = newDataTable();

dtExcelData = ReadSheetToDatatable();

BulkUploadPackingData(connString, dtExcelData);

returntrue;

}

privateDataTableReadSheetToDatatable()

{

//Excel sheet column header : PACKING_ID, PACKING_NO, NAME, DESCRIPTION, PACKING_DATE, PACKING_ITEM_NO, PACKING_UOM, PACKING_QTY, DELIVERY_DATE

DataTabledtExcelData = newDataTable("PackingDataSheet");

dtExcelData.Columns.Add(newDataColumn("PACKING_ID", System.Type.GetType("System.String"))); //System.Int64

dtExcelData.Columns.Add(newDataColumn("PACKING_NO", System.Type.GetType("System.String")));

dtExcelData.Columns.Add(newDataColumn("NAME", System.Type.GetType("System.String")));

dtExcelData.Columns.Add(newDataColumn("DESCRIPTION", System.Type.GetType("System.String")));

dtExcelData.Columns.Add(newDataColumn("PACKING_DATE", System.Type.GetType("System.String"))); //System.DateTime

dtExcelData.Columns.Add(newDataColumn("PACKING_ITEM_NO", System.Type.GetType("System.String")));

dtExcelData.Columns.Add(newDataColumn("PACKING_UOM", System.Type.GetType("System.String")));

dtExcelData.Columns.Add(newDataColumn("PACKING_QTY", System.Type.GetType("System.String")));   //System.Decimal

dtExcelData.Columns.Add(newDataColumn("DELIVERY_DATE", System.Type.GetType("System.String")));  //System.DateTime

//"Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=""Excel 8.0;HDR=Yes"";Data Source={0}"  // for .xls

using (OleDbConnectionoleDBcon = newOleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0 Xml;HDR=Yes;IMEX=1;'")) //.xlsx

            {

string query = string.Format("SELECT * FROM [PackingDataSheet$]");

OleDbDataAdapterdataAdapter = newOleDbDataAdapter();

dataAdapter.SelectCommand = newOleDbCommand(query, oleDBcon);

dataAdapter.Fill(dtExcelData);

returndtExcelData;

            }

}

privateboolBulkUploadPackingData(stringconnString, DataTabledtExcelData)

{

try

            {

using (OracleConnection connection = newOracleConnection(connString))

                {

connection.Open();

//using (OracleBulkCopybulkCopy = new OracleBulkCopy(connection, OracleBulkCopyOptions.UseInternalTransaction))

using (OracleBulkCopybulkCopy = newOracleBulkCopy(connection, OracleBulkCopyOptions.Default))

                    {

bulkCopy.DestinationTableName = "TB_PACKING_LIST";

bulkCopy.BatchSize = 25000;

//bulkCopy.BulkCopyTimeout = 20000;

bulkCopy.ColumnMappings.Add(newOracleBulkCopyColumnMapping("PACKING_ID", "PKG_ID"));

bulkCopy.ColumnMappings.Add(newOracleBulkCopyColumnMapping("PACKING_NO", "PKG_NO"));

bulkCopy.ColumnMappings.Add(newOracleBulkCopyColumnMapping("NAME", "PKG_NAME"));

bulkCopy.ColumnMappings.Add(newOracleBulkCopyColumnMapping("DESCRIPTION", "PKG_DESC"));

bulkCopy.ColumnMappings.Add(newOracleBulkCopyColumnMapping("PACKING_DATE", "PKG_DATE"));

bulkCopy.ColumnMappings.Add(newOracleBulkCopyColumnMapping("PACKING_ITEM_NO", "PKG_ITEM_NO"));

bulkCopy.ColumnMappings.Add(newOracleBulkCopyColumnMapping("PACKING_UOM", "PKG_UOM"));

bulkCopy.ColumnMappings.Add(newOracleBulkCopyColumnMapping("PACKING_QTY", "PKG_QTY"));

bulkCopy.ColumnMappings.Add(newOracleBulkCopyColumnMapping("DELIVERY_DATE", "PKG_DEL_DATE"));

bulkCopy.WriteToServer(dtExcelData);

//bulkCopy.OracleRowsCopied += new OracleRowsCopiedEventHandler(bulkCopy_OracleRowsCopied);

bulkCopy.Close();

                    }

returntrue;

                }

            }

catch (Exception ex)

            {

returnfalse;

            }

}