title: Runtime dynamic SQL Server database access by different connection strings
body: <p>I've searched the stackoverflow for a long time and didn't find a solution fit my situation, so I asked here.</p>
  
  <p>I have a single asp.net website, and need the web app to access different SQL Server database by the subdomain name.</p>
  
  <p>According to the url request subdomian to determine the access the different database.</p>
  
  <blockquote>
    <ol>
    <li>prj1.test.com  prj1--->use the prj1_DB</li>
    <li>prj2.test.com  prj2 use the prj2_DB</li>
    </ol>
  </blockquote>
  
  <p>I couldn't find a better practice to solve the issue.</p>
  
  <p>My intuition solution:</p>
  
  <p>when the url request coming, get the url subdomain, get the subdomain's db connection string stored in the main db, passing the connection string to the DAL to get the data.</p>
  
  <p><strong>Index.aspx.cs</strong></p>
  
  <pre><code>DataTable  dt = ProjectObject.GetProjectIndexNotice(new object[] { 0, CurrentProject.DbConnectionString });  
  </code></pre>
  
  <p><strong>ProjectObject.cs</strong></p>
  
  <pre><code>public static DataTable GetProjectIndexNotice(object[] param)
  {
     ProjectDLC obj = new ProjectDLC();
     return obj.GetProjectIndexNotice(param);
  } 
  </code></pre>
  
  <p><strong>ProjectDAL.cs</strong></p>
  
  <pre><code>public DataTable GetProjectIndexNotice(object[] param)
  {
     return base.GetDataTableFromDatabase(param, "NEMP_GetProjectIndexNotice");
  } 
  </code></pre>
  
  <p><strong>DALBase.cs</strong></p>
  
  <pre><code>DataBase db = new Microsoft.Practices.EnterpriseLibrary.Data.Sql.SqlDatabase(CurrentProject.DbConnectionString);
  </code></pre>
  
  <p>I want to find a better way to solve this problem.</p>
  
  <p>The solution I using above is: </p>
  
  <ul>
  <li>get the dbConnectionString from main DB,</li>
  <li>passing it over the <strong>Index.page ->BusinessObject Layer -->DAL Layer</strong></li>
  </ul>
  
  <p>It's so bad that passing the DB connection string from UI page to the DAL layer.</p>
  
  <p>Any ideas?</p>
  
  <p><strong>Update 1:</strong>
  What I really want is: don't pass the db connection string from UI to DAL layer.</p>
  
  <p>I want to find a solution that don't do pass the db connstr from UI to DAL linearly. </p>
  
  <p>Is there some pattern in asp.net to share some variable for UI layer and DAL Layer?</p>
  
  <p><strong>Update 2:</strong>
  if I stored the project db info in a xml file or in the main db, it looks like this
  
  </p>
  
  <p>it's a key-value part for the project. here is the question, I get the values all in the main DB or a xml file. <strong>How I get the key when I need to access the DB in DAL layer</strong>?</p>
  
  <p>in the DAL layer, <strong>how to get the correct key</strong> for the currnet url request?</p>
  
  <p>it's back to the above, pass the key from UI to DAL. that's I want to avoid.</p>
  
  <p><strong>the real problem is, I can get the key from url request in the UI layer, and I can get the value for that key in the DAL layer. but there is a gap between the two layers, How to conquer this gap?</strong></p>