Refractor code with SQL bulk insert {ADO.NET, ODP.NET}

Hi I use SQL bulk insert to Oracle 10g Db. I use ODP.NET because I need insert 20.000 - 40.000 rows in table. Long time a use LINQ to SQL (for oracle devart http://www.devart.com/linqconnect/). Now I must use "ADO.NET" object but my code look horrible. Can you help me and give my ideas how to refractor it? I would like to find compromise between readability of code and performacne.

Sorry for my english

    private void InitArrays(int size)
    {
        _id = new string[size];
        _lm = new DateTime[size];
        _priceWithoutDiscount = new decimal[size];
        _priceWithDiscount = new decimal[size];
        _talkTime = new int[size];
        _type = new string[size];
        _voiceNetwork = new string[size];
        _callNo = new string[size];
        _callDate = new DateTime[size];
        _callType = new string[size];
        _surname = new string[size];
        _name = new string[size];
        _no = new string[size];
    }

    private void PrepareArrays(IList<Call> calls)
    {
        InitArrays(calls.Count);

        Parallel.For(0, calls.Count, i =>
        {
            _id[i] = IdGenerator.GenerateGuidForCall(calls[i]);
            _no[i] = calls[i].Number;
            _name[i] = calls[i].Name;
            _surname[i] = calls[i].Surname;
            _callType[i] = calls[i].CallType;
            _callDate[i] = calls[i].Dt;
            _callNo[i] = calls[i].CallingNumber;
        开发者_JAVA百科    _voiceNetwork[i] = calls[i].VoiceNetwork;
            _type[i] = calls[i].Type;
            _talkTime[i] = calls[i].TalkTimeInSec;
            _priceWithDiscount[i] = (decimal)calls[i].PriceWithDiscount;
            _priceWithoutDiscount[i] = (decimal)calls[i].PriceWithoutDiscount;
            _lm[i] = DateTime.Now;

        });
    }


    public void InsertCalls(IList<Call> calls)
    {
        PrepareArrays(calls);

        string sql = "insert into r_calls (ID, NO, NAME, SURNAME, CALL_TYPE, CALL_DATE, CALL_NO, VOICE_NETWORK,"
                     +"TYPE, TALK_TIME,PRICE_WITH_DISCOUNT, PRICE_WITHOUT_DISCOUNT, LM_MODIFIED) "
                     + "values (:id, :no, :name, :surname, :callType, :callDate, :callNo, :voiceNetwork, :type,"
                     +" :talkTime, :priceWithDiscount, :priceWithoutDiscount, :lm)";


        var cnn = new OracleConnection(GenerateConnectionString());
        cnn.Open();
        OracleCommand cmd = cnn.CreateCommand();
        cmd.CommandText = sql;
        cmd.CommandType = CommandType.Text;
        cmd.BindByName = true;

        // To use ArrayBinding, we need to set ArrayBindCount
        cmd.ArrayBindCount = _id.Count();

        // Instead of single values pass arrays of values as parameters
        cmd.Parameters.Add(":id", OracleDbType.Varchar2, 
                            _id, ParameterDirection.Input);

        cmd.Parameters.Add(":no", OracleDbType.Varchar2,
                            _no, ParameterDirection.Input);

        cmd.Parameters.Add(":name", OracleDbType.Varchar2,
                            _name, ParameterDirection.Input);

        cmd.Parameters.Add(":surname", OracleDbType.Varchar2,
                            _surname, ParameterDirection.Input);

        cmd.Parameters.Add(":callType", OracleDbType.Varchar2,
                            _callType, ParameterDirection.Input);

        cmd.Parameters.Add(":callDate", OracleDbType.Date,
                            _callDate, ParameterDirection.Input);

        cmd.Parameters.Add(":callNo", OracleDbType.Varchar2,
                            _callNo, ParameterDirection.Input);

        cmd.Parameters.Add(":voiceNetwork", OracleDbType.Varchar2,
                            _voiceNetwork, ParameterDirection.Input);

        cmd.Parameters.Add(":type", OracleDbType.Varchar2,
                            _type, ParameterDirection.Input);

        cmd.Parameters.Add(":talkTime", OracleDbType.Decimal,
                            _talkTime, ParameterDirection.Input);

        cmd.Parameters.Add(":priceWithDiscount", OracleDbType.Decimal,
                            _priceWithDiscount, ParameterDirection.Input);

        cmd.Parameters.Add(":priceWithoutDiscount", OracleDbType.Decimal,
                            _priceWithoutDiscount, ParameterDirection.Input);

        cmd.Parameters.Add(":lm", OracleDbType.Date,
                        _lm, ParameterDirection.Input);


        cmd.ExecuteNonQuery();
        cnn.Close();

    }

---

This may not be the whole solution, but first i would try avoiding hardcoding of the parameter names. I will get the param names from the table schema itself and then submit data over it. A bit of refactoring this is. I wrote an article on this, check it out: http://www.codeproject.com/KB/TipsnTricks/StoredProcSchemaSaving.aspx

---

This looks good to me, but I'd suggest to run StyleCop over it.

Additionally you could use another formatting for the SQL statements:

string sqlStatement = @"
insert into TABLE
( COL1,  COL2,  COL3) VALUES
(:COL1, :COL2, :COL3)
";

Also use using for the connection and the command. It implements IDisposable and thus can be wrapped in a using block.

using(var connection = this.GetSomeConnection())
using(var command = this.InitTheCommandSomehow(connection))
{
    // here your code can safely throw exceptions etc.
    // and the connection will still be closed

    ...

    cmd.Parameters.Add(
        "id",
        OracleDbType.Varchar2,
        _id,
        ParameterDirection.Input);

    ...

}