Best way for SqlConnection inside a loop
c#, sql-server
Solution
I would build the connection outside of the loop. If it were something that were running forever my answer would be different, for instance if you were running a web service I would suggest that you open connections for each request (that's super different, but I'm just giving an example). But opening a connection is a resource-intensive task, and it'll just be easier to work off of a single one in a case like this. It really depends on what you're focused on here, as a couple other people have said, but I think a single connection sounds perfectly acceptable. And as I see another commenter said, you could then use transactions to wrap it all up, if that would help you at all.
The rest of this stuff isn't meant to answer your question directly, I just think it's worth thinking about given your situation.
You might want to look into the SQL `MERGE` statement, though. It'd be much faster if you could send more than one record to SQL at a time. You'd still, of course, have to save your images separately, but this could at least make the SQL aspect faster. Of course you can also do that with the `INSERT` statement, but that depends on whether every single new record you're passing is actually new, or if it'll violate unique keys.
You could also look into the TPL Dataflow because it has some useful classes for doing multiple operations concurrently and in batches that might make your insert way faster as well. Remember, though, that a single connection is sometimes not the best at handling concurrent requests. But if you were splitting your data into sets of 100 (remember that 2100 is the maximum number of query parameters per `SqlCommand`, so just take that divided by however many you need and subtract a margin of error), I'd probably say just go with creating the connection for each one.
I know this is way more than what you asked for, but I work with situations like this a lot so I thought I'd share some of what I've picked up along the way.
Problem
I am processing about 24k records totally per day in a windows service, I insert the rows into a database and then I save image compressed in a directory. Generally, what is the best way for this? ``` using (SqlConnection cnn = new SqlConnection()) { foreach (var item in collection) { //do stuff SaveImage(item.filename,item.img); } } ``` or ``` foreach (var item in collection) { using (SqlConnection cnn = new SqlConnection()) { //do stuff } SaveImage(item.filename, item.img); } ``` Thanks