Alternative to executing SQL in a loop to update multiple rows?

[zachattack05]

I have a checklistbox that I have been working on and I need to commit the changes made to those checkstates to a sql table, removing rows where the checkbox is not checked and adding rows where the checkbox is checked (assuming the checkstate actually changed).

Instead of doing a foreach loop for each item in the checklistbox and if the checkstate changed, executing a sql statement, is it possible to pass other objects to sql and have it act on that object? Like a DataTable or something that is serialized or something that I can pass to a sproc?

I don't necessarily mind using the loop and honestly it's not a screen people would be using much so I doubt it would cause much network traffic anyway, but who knows...I'd rather do it right the first time than to have it cause problems later and not understanding why.

Any suggestions?

[thines01]

Looping for the update is pretty standard.
There are ways of sending minimal updates, but you really need to know your data (and the table structure).

One of the better tried-and-true techniques is to use stored-procedures where you are sending the procedure name and the parameters.
The next best is to prepare-and-parameterize your query, so (on a sophisticated database), you're still just sending a dynamic procedure name and parameters.

[zachattack05]

Hmmm, looping it is! Hehe, if it's standard to do that, then I don't see any point in going out of my way to modify a procedure for something that will probably rarely occur. It's just not worth it.

Thanks for the tip!