c# - stuck calling oracle procedure multiple times -

any way can speed up. have call stored procedure each time latest value oracle , update. last bottleneck, can called thousands of times. converted other inserts array binding bulk inserts.

c# call oracle

for (int = 0; < r.receiptkey.count(); i++) {          ld.receiptlinenumber.add(asngetnextavailablereceiptlinenumber(r.receiptkey[i])); } 

c# oracle

 public string asngetnextavailablereceiptlinenumber(string myreceiptkey)     {         using (oraclecommand cmd = new oraclecommand())         {             oracleconnection conn;             conn = new oracleconnection(connectionstringoracle);             cmd.commandtext = "bppack.getnextreceiptlinenumber";             cmd.commandtype = commandtype.storedprocedure;             cmd.connection = conn;              cmd.parameters.add(new oracleparameter("ireceiptkey", oracledbtype.varchar2, 10));             cmd.parameters.add(new oracleparameter("oretvalue", oracledbtype.varchar2, 5)).direction = parameterdirection.output;             cmd.parameters["ireceiptkey"].value = myreceiptkey;             conn.open();             try             {                 cmd.executenonquery();             }             catch (exception e9)             {                 return "error" + e9.message;             }             return cmd.parameters["oretvalue"].value.tostring();         }     } 

the plsql

  procedure getnextreceiptlinenumber(    ireceiptkey in varchar2,    oretvalue out varchar2)       xreceiptlinenumber varchar2 (5);     xreceiptlinecount  number (10);   begin      xreceiptlinenumber :='';      xreceiptlinecount  := 0;      select max (receiptlinenumber)     xreceiptlinenumber      receiptdetail     receiptkey        = ireceiptkey;    if (xreceiptlinenumber null)     xreceiptlinecount    := 0;   else     xreceiptlinecount := to_number (ltrim (xreceiptlinenumber, '0'));   end if;   xreceiptlinecount := xreceiptlinecount + 1;   oretvalue         := lpad (xreceiptlinecount, 5, '0');   end getnextreceiptlinenumber; 

do not connect on each procedure call - separate connection code , procedure call code.

---

procedure can situation of getting duplicates (imagine happens if 2 session call same key @ same time - use sequences instead of max if possible.