Advice on the best way to update a database

Hi,

Apologies if this has already been asked, but I couldnt find a thread that asked exactly what I wanted.

Im making an administration panel for a site where you can change various settings, options, and categories that data can fall into. When editing, adding or deleting a record i COULD make a trip to the database every time, but this feels very inefficient as I understand that establishing the connection is usually the biggest performance hit when querying a database.

An alternative plan is for me to simply record the changes made in the panel and have a "save" button. When this is clicked, ONE database connection would be opened and all the data would be saved/updated/deleted as necessary. However, this would involve several "for" loops while the connection is open.

The question is; which method would you recommend and why? And does having several "for" loops while the connection is open nullify the advantage gained by only opening one connection?

Any advice would be very much appreciated.

Thank you

It does not matter which way you choose -- you should choose based only on what makes the most sense for your app (ie, what interface to the users is most intuitive, what coding style is going to be most effective and maintainable).

While it is true that getting a connection is expensive, assuming you're use .Net then your app will take advantage of connection pooling which automatically reuses connections if at all possible. This is done for you -- you don't have to do anything to take advantage of itas long as you take care to properly close your connections when you're done. Seehttp://www.15seconds.com/issue/040830.htm andhttp://msdn2.microsoft.com/en-us/library/8xx3tyca(vs.71).aspx

|||

Thank you.

I didnt realise connection pooling worked quite like that. Very useful thing to know...