title: Linq to Sql: Change Database for each connection
body: <p>I'm working on an ASP.NET MVC application which uses Linq to SQL to connect to one of about 2000 databases.  We've noticed in our profiling tools that the application spends a lot of time making connections to the databases, and I suspect this is partly due to connection pool fragmentation as described here:  <a href="http://msdn.microsoft.com/en-us/library/8xx3tyca(v=vs.110).aspx" rel="nofollow noreferrer">http://msdn.microsoft.com/en-us/library/8xx3tyca(v=vs.110).aspx</a></p>
  <blockquote>
  <p>Many Internet service providers host several Web sites on a single
  server. They may use a single database to confirm a Forms
  authentication login and then open a connection to a specific database
  for that user or group of users. The connection to the authentication
  database is pooled and used by everyone. However, there is a separate
  pool of connections to each database, which increase the number of
  connections to the server.</p>
  <p>There is a relatively simple way to avoid this
  side effect without compromising security when you connect 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 the
  Transact-SQL USE statement to change to the desired database.</p>
  </blockquote>
  <p>I am trying to implement this solution in Linq to Sql so we have fewer open connections, and so there is more likely to be a connection available in the pool when we need one.  To do that I need to change the database each time Linq to Sql attempts to run a query.  Is there any way to accomplish this without refactoring the entire application?  Currently we just create a single data context per request, and that data context may open and close many connections.  Each time it opens the connection, I'd need to tell it which database to use.</p>
  <p>My current solution is more or less like <a href="http://social.msdn.microsoft.com/Forums/en-US/e27cefc5-66d9-4ee0-8f44-6d9ff81c5e1f/custom-idbconnection-with-datacontext" rel="nofollow noreferrer">this one</a> - it wraps a SqlConnection object inside a class that inherits from DbConnection.  This allows me to override the Open() method and change the database whenever a connection is opened.  It works OK for most scenarios, but in a request that makes many updates, I get this error:</p>
  <blockquote>
  <p>System.InvalidOperationException: Transaction does not match
  connection</p>
  </blockquote>
  <p>My thought was that I would then wrap a DbTransaction object in a similar way to what I did with SqlConnection, and ensure that its connection property would point back to the wrapped connection object.  That fixed the error above, but introduced a new one where a DbCommand was unable to cast my wrapped connection to a SqlConnection object.  So then I wrapped DbCommand too, and now I get new and exciting errors about the transaction of the DbCommand object being uninitialized.</p>
  <p>In short, I feel like I'm chasing specific errors rather than really understanding what's going on in-depth.  Am I on the right track with this wrapping strategy, or is there a better solution I'm missing?</p>
  <p>Here are the more interesting parts of my three wrapper classes:</p>
  <pre><code>public class ScaledSqlConnection : DbConnection
  {
      private string _dbName;
      private SqlConnection _sc;
      public override void Open()
      {
          //open the connection, change the database to the one that was passed in
          _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(&quot;Only ScaledSqlConnections can be connections here.&quot;);
          }
      }
  
      protected override DbTransaction DbTransaction
      {
          get
          {
              if (_transaction == null &amp;&amp; _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(&quot;Don't set the transaction of a ScaledDbCommand with &quot; + value.ToString());
              }
          }
      }
  }
  </code></pre>
  <p>}</p>