Is it OK to re-create many SQL connections (SQL 2008)
- by Mr. Flibble
When performing many inserts into a database I would usually have code like this:
using (var connection = new SqlConnection(connStr))
{
connection.Open();
foreach (var item in items)
{
var cmd = new SqlCommand("INSERT ...")
cmd.ExecuteNonQuery();
}
}
I now want to shard the database and therefore need to choose the connection string based on the item being inserted. This would make my code run more like this
foreach (var item in items)
{
connStr = GetConnectionString(item);
using (var connection = new SqlConnection(connStr))
{
connection.Open();
var cmd = new SqlCommand("INSERT ...")
cmd.ExecuteNonQuery();
}
}
Which basically means it's creating a new connection to the database for each item. Will this work or will recreating connections for each insert cause terrible overhead?
