You must Sign In to post a response.
  • Category: ASP.NET

    How to use OracleBulkCopy?

    How to use OracleBulkCopy?

    I have tried it using Oracle.DataAccess.Client,
    but the "OracleBulkCopy" class is not available in Oracle.DataAccess.Client
    namespace.

    Any help on this is appreciated!
  • #666212
    For oracle bulk upload first you need ODP.NET Classes that Inherit from ADO.NET 2.0 Base Classes

    Connection String for oracle

    <connectionStrings>
    <add name="Publications" providerName="Oracle.DataAccess.Client"
    connectionString="User Id=scott;Password=tiger;Data Source=inst1" />
    </connectionStrings>


    SqlDataReader reader = null;
    SqlConnection sourceConnection = null;
    SqlCommand commandSourceData = null;
    StringBuilder query = new StringBuilder();
    try
    {
    string sourceConnectionString = GetSourceDbConnectionString();
    using (sourceConnection = new SqlConnection(sourceConnectionString))
    {
    sourceConnection.Open();
    query = GetQuery(startRow, endRow);
    using (commandSourceData = new SqlCommand(query.ToString(), sourceConnection))
    {
    commandSourceData.CommandTimeout = 180;
    using (reader = commandSourceData.ExecuteReader())
    {
    string destinationConnectionString = GetDestinationDbConnectionString();
    using (OracleConnection destinationConnection = new OracleConnection(destinationConnectionString))
    {
    destinationConnection.Open();
    using (OracleBulkCopy bulkCopy = new OracleBulkCopy(destinationConnection))
    {
    bulkCopy.DestinationTableName = TABLE_CIS;
    bulkCopy.BatchSize = 100000;
    bulkCopy.BulkCopyTimeout = 20000;
    bulkCopy.WriteToServer(reader);
    bulkCopy.OracleRowsCopied += new OracleRowsCopiedEventHandler(bulkCopy_OracleRowsCopied);
    bulkCopy.Close();
    bulkCopy.Dispose();
    destinationConnection.Dispose();
    }
    }
    }
    }
    }
    }
    catch (Exception ex)
    {
    throw ex;
    }
    finally
    {
    reader.Close();
    reader.Dispose();
    sourceConnection.Dispose();
    commandSourceData.Dispose();
    }

  • #666226
    Hi,
    An OracleBulkCopy object copies data into an Oracle table from another data source.
    It can be declared in c# as follows:
    public sealed class OracleBulkCopy : IDisposable

    Regards,
    Prachi.

  • #666246

    The OracleBulkCopy class is part of Oracle.DataAccess.Client version 11.1.6.20 and newer, which you can get from http://www.oracle.com/technology/software/tech/windows/odpnet/index.html .

    Thanks & Regards
    Anil Kumar Pandey
    Microsoft MVP, DNS MVM


  • This thread is locked for new responses. Please post your comments and questions as a separate thread.
    If required, refer to the URL of this page in your new post.