[BEGINNER] Sorry for my English. I've been working for a new company for a short time and I consider myself a novice in BDD, since apart from school I haven't practiced since.

[ENVIRONMENT] I am developing in C# an existing software that I need to optimize. With a big constraint, it is not to modify the bases since many customers already use them and will not want to change their server. In general if he uses ORACLE it is because he also uses it for other software.

The software used System.Data.OracleClient, but the latter is obsolete, I started implementing Oracle.ManagedDataAccess.Client. I can communicate, read and write data with the database, but I can't send a large amount of data quickly.

On site we have a server with a remote base which is therefore with Oracle, or MySQL (what interests me for the moment is Oracle) And there are local workstations that use an SQLite database and must be able to operate without the remote database.

By default everything is saved locally and I have synchronizations to do if the remote database is accessible. It is therefore possible to have a large amount of data to transfer. (there are measurement records)

[FOR MY TESTS] I recovered one of the customer databases in order to do tests with real data and one of the tables with more than 650,000 rows. Transferring this line by line takes a lot of time, even if I don't make disconnection connections on each line of course. I wanted to try to send parameter blocks, but I can't do it at the moment.

[MY RESEARCH] I keep looking, but what I found was either using paid DLLs or I didn't understand their use.

[MY CODE] For the moment I left on it, really in test:

        public void testOracle()
        {
            try
            {
                if (Connection == null)
                {
                    Connection = OracleConnection();
                }
                string commandString = "INSERT INTO GRAPHE (ID, MESUREE_ID, DATE_MESURE, POINT_GRAPHE, D0, D1) VALUES (:IDp, :MESUREE_IDp, to_timestamp( :DATE_MESUREp ,'DD/MM/RR HH24:MI:SSXFF'), :POINT_GRAPHEp, :D0p, :D1p)";
                int _ID = 1;
                int _MESUREE_ID = 9624;
                string _DATE_MESURE = "16/12/ 08 00:00:00,000000000";
                int _POINT_GRAPHE = 1229421394;
                int[] _D0 = 0;
                int[] _D1 = 0;

                using (OracleCommand command = new OracleCommand(commandString, Connection))
                {

                    using (var transaction = Connection.BeginTransaction())
                    {
                        for (int i = 0; i < _ID.Length; i  )
                        {
                            command.Parameters.Add("IDp", OracleDbType.Decimal).Value = _ID;
                            command.Parameters.Add("MESUREE_IDp", OracleDbType.Decimal).Value = _MESUREE_ID];
                            command.Parameters.Add("DATE_MESUREp", OracleDbType.Varchar2).Value = _DATE_MESURE[i];
                            command.Parameters.Add("POINT_GRAPHEp", OracleDbType.Decimal).Value = _POINT_GRAPHE[i];
                            command.Parameters.Add("DOS10p", OracleDbType.Decimal).Value = _D0[i];
                            command.Parameters.Add("DOS07p", OracleDbType.Decimal).Value = _D1[i];
                            command.ExecuteNonQuery();
                        }
                        transaction.Commit();
                    }
                }
            }
            catch (Exception ex)
            {

            }
            Connection.Close();
        }


        public OracleConnection OracleConnection()
        {
            string serveur_name = "192.168.0.1";
            string database_name = "oracle.dev";
            string user_name = "name";
            string password = "pass";
            string oraclePort = "1521";
            OracleConnection _con = null;
            try
            {
                string connectionString = "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST="   serveur_name   ")(PORT="   oraclePort   ")) (CONNECT_DATA=(SERVICE_NAME="   database_name   "))); User Id="   user_name   ";Password="   password   ";";
                _con = new OracleConnection(connectionString);
                try
                {
                    _con.Open();
                }
                catch (Exception e)
                {

                }
            }
            catch (Exception e)
            {
      
            }
            return _con;
        }
    }```

CodePudding user response：

then you can create a DataTable and do bulk insert

using (var bulkCopy = new OracleBulkCopy(yourConnectionString, OracleBulkCopyOptions.UseInternalTransaction))
{
   bulkCopy.DestinationTableName = "GRAPHE";
   bulkCopy.WriteToServer(dataTable);
}

You can also use the BatchSize property as per your requirement.