I am working on an ASP.NET MVC application that uses Linq to SQL to connect to one of approximately 2000 databases. We noticed in our profiling tools that the application spends a lot of time connecting to databases, and I suspect that this is partially due to fragmentation of the connection pool, as described here: http://msdn.microsoft.com/en-us/library /8xx3tyca(v=vs.110).aspx
Many Internet service providers host multiple websites on a single server. They can use a single database to validate authentication forms, and then open a connection to a specific database for that user or group of users. Connection to authentication database is combined and used by everyone. However, there is a separate pool of connections to each database, which increase the number of connections to the server.
There is a relatively simple way to avoid this side effect without compromising security when connecting to SQL Server. Instead of connecting to a separate database for each user or group, connect to the same database on the server, and then execute Transaction-SQL USE to modify the desired database.
I'm trying to implement this solution in Linq to Sql, so we have fewer open connections, and most likely it will be the connection available in the pool when we need it. To do this, I need to change the database every time Linq to Sql tries to run a query. Is there a way to do this without reorganizing the entire application? Currently, we simply create a single data context for each request, and this data context can open and close many connections. Each time it opens a connection, I need to specify which database to use.
- SqlConnection , DbConnection. Open() . , , , :
System.InvalidOperationException:
, DbTransaction , SqlConnection, , . , , DbCommand SqlConnection. , DbCommand, DbCommand, .
, , , , . , , ?
-:
public class ScaledSqlConnection : DbConnection
{
private string _dbName;
private SqlConnection _sc;
public override void Open()
{
_sc.Open();
if (this._dbName != null)
this.ChangeDatabase(this._dbName);
}
protected override DbTransaction BeginDbTransaction(IsolationLevel isolationLevel)
{
return new ScaledSqlTransaction(this, _sc.BeginTransaction(isolationLevel));
}
protected override DbCommand CreateDbCommand()
{
return new ScaledSqlCommand(_sc.CreateCommand(), this);
}
}
public class ScaledSqlTransaction : DbTransaction
{
private SqlTransaction _sqlTransaction = null;
private ScaledSqlConnection _conn = null;
protected override DbConnection DbConnection
{
get { return _conn; }
}
}
public class ScaledSqlCommand : DbCommand
{
private SqlCommand _cmd;
private ScaledSqlConnection _conn;
private ScaledSqlTransaction _transaction;
public ScaledSqlCommand(SqlCommand cmd, ScaledSqlConnection conn)
{
this._cmd = cmd;
this._conn = conn;
}
protected override DbConnection DbConnection
{
get
{
return _conn;
}
set
{
if (value is ScaledSqlConnection)
_conn = (ScaledSqlConnection)value;
else
throw new Exception("Only ScaledSqlConnections can be connections here.");
}
}
protected override DbTransaction DbTransaction
{
get
{
if (_transaction == null && _cmd.Transaction != null)
_transaction = new ScaledSqlTransaction(this._conn, _cmd.Transaction);
return _transaction;
}
set
{
if (value == null)
{
_transaction = null;
_cmd.Transaction = null;
}
else
{
if (value is ScaledSqlTransaction)
_transaction = (ScaledSqlTransaction)value;
else
throw new Exception("Don't set the transaction of a ScaledDbCommand with " + value.ToString());
}
}
}
}
}