c# - Can I execute a stored procedure many time in a for loop? -

i have web service executes stored procedure. web service function returns string[]. need call web service many times.

for optimization reasons, thought adding function web service executes stored procedure many times, in for loop. way web service called once instead of several times.

1-is thinking correct ?

2-below code, part specific problem described above.

3-is not possible using loop ?

my problem: if use code call stored procedure once, works, more (for loop iterates second time), catch block accessed.

if can explain me why happening and/or suggest solution/workaround appreciate.

    try     {         (int = 0; < number; i++)         {             connection.open();             cmd = new sqlcommand();             //sqltransaction transaction;             transaction = connection.begintransaction();             cmd.transaction = transaction;             cmd.connection = connection;              cmd.parameters.clear();             cmd.commandtext = "insertmsg";             cmd.commandtype = commandtype.storedprocedure;             cmd.parameters.add("@id", sqldbtype.varchar).value = ids[i];             cmd.parameters.add("@name", sqldbtype.varchar).value = names[i];             cmd.parameters.add("@age", sqldbtype.datetime).value = age;             cmd.executenonquery();             data[i] = ids[i];             transaction.commit();         }          connection.close();         return data;     }     catch (sqlexception ex)     {         transaction.rollback();         data[0] = "error";         return data;     } } 

the issues appears open , close statements. close outside loop, change like

try             {             connection.open();             transaction = connection.begintransaction();            (int = 0; < number; i++)         {              cmd = new sqlcommand();             //sqltransaction transaction;             cmd.transaction = transaction;             cmd.connection = connection;                  cmd.parameters.clear();                 cmd.commandtext = "insertmsg";                 cmd.commandtype = commandtype.storedprocedure;                 cmd.parameters.add("@id", sqldbtype.varchar).value = ids[i];                 cmd.parameters.add("@name", sqldbtype.varchar).value = names[i];                 cmd.parameters.add("@age", sqldbtype.datetime).value = age;                 cmd.executenonquery();                 data[i] =  ids[i];                 transaction.commit();                    }           connection.close();                    return data;         }             catch (sqlexception ex)             {                  transaction.rollback();                 data[0]="error";                 return data;             } 

closing connection should inside block, better use using statement instead. if possible looping , transaction inside stored procedure faster.