Last post May 31, 2017 11:07 AM by PatriceSc
May 31, 2017 10:55 AM|maheshvishnu|LINK
I am looping through each row from excel and validating the row data against database.
My question is do we need to open and close connection for every row or open connection validate all rows and close the connection?
In my research i noticed people recommending both approaches. Suggest me which is the best and why
May 31, 2017 11:05 AM|kaushalparik27|LINK
It really depends on your what you are focused on. I would suggest to open a single connection, open the connection and write a loop with a single connection and then close once done. But, my answer would be different if every loop count may have lengthy
process/execution, then I would suggest to have connection inside the loop. Another aspect, if you are looking for something like maintain Transaction (effect all or nothing in case of error) then you should opt single connection for loop. Ideal situation
should be you open the connection as late as possible, and you dispose as soon as possible.
May 31, 2017 11:07 AM|PatriceSc|LINK
Strictly speaking opening and closing the connection again an again should never be a problem with "connection pooling" that handles that for you behing the scene (the connection is not really closed but kept ready to be quickly reused and closed later if
not reused for some time so opening/closing connections is almost "free").
If you have an easy way to open the connection before the loop and close after the loop, you could try to see if it makes a difference. I don't expect to see a significant difference here.
Keeping the connection opened for too long is never good (and sometimes even worse you see one that tries to explicitely keep a connection open for each single user when not mistakenly for all users).