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

Question

I have a web service that executes a stored procedure. My web service function returns string[]. Sometimes I need to call the Web service many times.

For optimization reasons, I thought about adding a function to my web service which executes the stored procedure many times, in a for loop. That way the web service is called only once instead of several times.

1-Is my thinking correct ?

2-Below is my code, only the part specific to the problem described above.

3-Is it just not possible to do this using a for loop ?

My Problem: If I only use this code to call the stored procedure once, it works, but as soon as it is more (for loop iterates second time), the catch block is accessed.

If you can explain to me why is this happening and/or suggest a solution/workaround I would really appreciate.

    try
    {
        for (int i = 0; i < 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;
    }
}

Answer 1

The issues appears to be with the open and close statements. Close is outside the for loop, change it like

try
            {
            connection.Open();
            transaction = connection.BeginTransaction();

          for (int i = 0; i < 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 the connection should be inside a finally block, better use a using statement instead. Also if possible do the looping and transaction inside the stored procedure which will be faster.