Call stored procedure in loop

I have gridview display a list of users. I have added a column for a check box. If the box is checked I move the users to another table.

I need to pass some parameter of or each row to a stored proc.

My question.

In the loop where I check if the checkbox is selected I need to call the stored procedure.

Currently I do an open and closed inside the loop.

What is best and most effficent method of doing this should I open and close the connection outside the loop and change the procs parameters as loop through.

 System.Data.SqlClient.SqlConnection conn =new System.Data.SqlClient.SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["connString"].ConnectionString); System.Data.SqlClient.SqlCommand commChk =new System.Data.SqlClient.SqlCommand("storedProc", conn); commChk.CommandType = System.Data.CommandType.StoredProcedure; commChk.Parameters.AddWithValue("@.mUID", ddMainUser.SelectedValue.ToString()); commChk.Parameters.AddWithValue("@.sUId", gvUsers.Rows[i].Cells[2].Text); commChk.Connection.Open(); commChk.ExecuteNonQuery(); conn.Close();

If so exactly how do I do this? How do I reset the parmaters for the proc?

I haven't done this before where I need to loop through passing parameter to the same proc.

thanks

If you are going to reuse the same SqlCommand object several times, you should call

commChk.Parameters.Clear();

to clear out the contents of the parameter collection before adding new values.

Hope this helps.

|||

I would do it like this:

build connection object

build command object (assigning the command to the connection)

Build command parameters w/ datatype.

open connection

begin loop

set command parameter values

execute command

end loop

close connection

in vb it would be:

Dim conn as new sqlconnection(ConfigurationManger...)

dim cmd as new sqlcommand("SELECT ...",conn)

cmd.Parameters.Add("@.param1",sqldbtype.int)

cmd.Parameters.Add("@.param2",sqldbtype.varchar)

conn.open

while ...

cmd.Parameters("@.param1").value= ...

cmd.Parameters("@.param2").value= ...

cmd.executenonquery

end while

conn.close

--

Notice very little actually takes place within the loop. I'm not creating whole objects, or creating connections, just setting a few values and executing the command. It also makes it easier to wrap up in a nice catch/try block or a transaction.