Getting SQL Connection fragmentation, different way to connect to DB's

We have multiple DB servers. On one of the servers we have a master config table that holds instructions as to what DB server and DataBase name an Agency is supposed to use.

Currently each Database always has 2 connections on them, even if they're not being used (which we are fixing). However, we're trying to find a way to make it so our connections are not all over the place, and relieve some of the stress on our DB Servers.

After a lot of research we found some articles saying to do all connections to a central location, and then Change which database we're using through the SQLConnection object. Which seems a bit roundabout, but could work.

So I'm wondering what others do in this situation?

The current path for this is:

-User Logs in -System access ConfigTable to find out which database user is going to connect to. -System loads the Agency connection settings into memory (SEssion) for that user. -Every request now directly hits that users database.

Is there a mo开发者_开发技巧re efficient way of doing this?

Open connections late, and close them early.

For example:

string result;
using (var con = new SqlConnection(...))
{
    con.Open();
    var com = con.CreateCommand();
    com.CommandText = "select 'hello world'";
    result = com.ExecuteScalar();
}

The Windows OS will make sure to efficiently pool and reuse connections. And since you're only using connections when you need them, there are no idle connections lying around.

EDIT: Windows only caches connection strings that are literally the same, so if you use Initial Catalog=<dbname> in the connection string, that could hurt performance by requiring 500+ "connection pools" for one server.

So if you have 4 servers with a lot of databases, make sure you only use 4 connection strings. After connecting, switch database with:

com.CommandText = "use <dbname>";
com.ExecuteNonQuery();

Or query with a three-part name like:

select * from <dbname>.dbo.YourTable