About Me

Having 12 years experience in Microsoft technologies.Since more than 7 years working in SharePoint technologies. Expert in providing consultation for SharePoint projects. Hands on with development and administration.

Monday 30 January 2017

Bulk insert records to Oracle database using Oracle.ManagedDataAccess.dll from Visual Studio

Bulk insert records to Oracle table for .NET applications
  1. Down load latest ODP.net package from oracle site. You should download the correct version of package based on your visual studio version
  2. You can check the link for the right version
  3. After install you can find the Oracle.ManagedDataAccess.dll @
    D:\xxxxxxxxx\product\12.1.0\client_1\odp.net\managed\common\Oracle.ManagedDataAccess.dll
  4. Add the dll as a reference to your project solution in Visual studio.
  5. using Oracle.ManagedDataAccess.Client;
  6. Declare connection string for oracle –
    String ConnectionString = "host;port;service_name;useraccount;password”;
    String query = “insert into table values()”;
     
    Replace your data source details here
using (Connection = new OracleConnection(ConnectionString))

                {
                                Connection.Open();

using (OracleCommand command = new OracleCommand(query, Connection)) // create the oracle sql command

                                {
//define transaction and assign it to oracle command

OracleTransaction transaction = Connection.BeginTransaction(System.Data.IsolationLevel.ReadCommitted);

                                command.Transaction = transaction;
                                //add parameters to command
                                command.Parameters.Add(new OracleParameter("LOAD_ID", Load_ID));

                                //add more parameters based on your requirement

                                //assign oracle command execute to integer to know how many records have been

//processed

int result = oraCommand.ExecuteNonQuery();

if (result == records count())

{                //if both are equal, which means the bulk insert successfully

//inserted all rows retuned from query
}

No comments:

Post a Comment