I am sure both of them would work. But I am wondering, should I keep the connection open till the datatable loop is done? Or open an connection and close it for each time of the loop to keep the connection short?
foreach (DataRow row in dt.Rows)
{
using(SqlConnection con = new SqlConnection(conString))
{
// do things
}
}
OR:
using(SqlConnection con = new SqlConnection(conString))
{
foreach (DataRow row in dt.Rows)
{
// do things
}
}
There is no real advantage to "keeping the connection short" since you still need the connection and use it across the same time window in either case.
The downside to opening/closing repeatedly is the penalty due to overhead of opening a connection, resulting in a lot of needless churn.
Besides, there is a good chance that the ADO connection pool would keep the connection open anyway, so you probably aren't gaining anything.
If this is a batch process (not in an OLTP environment), then there is nothing wrong with long-lived connections, as long as the code cleans up properly when it is done, and the sooner you get it done, the sooner you aren't tying resources.
The generally accepted stance is that opening a connection is "expensive" in terms of resources and all of the operations you're going to conduct against a data source should happen in the scope of one connection. There are a few exceptions to that idea, but generally speaking, you want to stick with a single connection instance.
It really depends on what "// do things" is doing, but assuming it is something trivial you should go with the latter, open it, do what you need to, then close it.
Your code will almost certainly run faster if you avoid recreating the SqlConnection every time through the loop. Keeping the connection short shouldn't be a concern if you're connecting to a database that allows simultaneous connections (pretty much any client-server database). The only reason I could see for reconnecting every row is if you are performing updates on a file-based database like SQLite, and even then I think it only locks the database file while performing the update, not for the duration of the connection.
