Creating Database connection String for a multi-database N-tier Web App.

Jan 28, 2016 at 2:47 AM
I m developing a multi-tenancy N-tier web application. I want each tenant to have their own database but share same application code. I am using Microsoft Enterprise Library 5.0 for DAL and the System configuration and log4net for logging. So far so good, if it is a single database, it works great but I want the ability to connect to different tenant databases in real time. To fully implement this, I have created a User database to store all login privileges, tenants database names, and perhaps each tenant's db connection string. Now, when tenant A logs on the system, Tenant A login privileges are retrieved from User db then to tenant A db to fetch data needed. Below is base data access object method that implements common database connection functionality and lots DAL methods derive on it.

I would like to add a constructor that's utilizes a dynamic string parameter. The string Parameter will be the tenant db name queried from User db. I would really appreciate anyone who's accomplished this to share with how s/he did.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.Common;
using System.Data.Sql;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
using Crane_Infrastructure;
using Crane_Infrastructure.Exceptions;
using Crane_Infrastructure.ValueObjects;

namespace CranePMS_DataAccess
public class BaseDAO: BaseObject
   public BaseDAO() : base(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType) {

   protected BaseDAO(Type loggerClassType) : base(loggerClassType) {


   private Database db;

   private string DataSource = "Database = .\\SQLEXPRESS";
   private string _Server="(local)";
   private string DbName = "defaultdbname";
   private string UserName = "admin";
   private string Password="password";
   private string Security = "SSPI";

   // create DB connection goes to test.dll.config  

   protected Database Database

           Database DbConfig = DatabaseFactory.CreateDatabase();
           DbConnectionStringBuilder sb = DbConfig .DbProviderFactory.CreateConnectionStringBuilder();

           // Set the Connection String fields.

           sb["Database"] = DataSource;
           sb["Server"] = _Server;
           sb["Initial Catalog"] = DbName;
           sb["User ID"] = UserName;
           sb["Password"] = Password;
           sb["Integrated Security"] = Security;
           // sb["MultipleActiveResultSets"] = result;

           GenericDatabase newdatabase = new GenericDatabase(sb.ToString(), DbConfig.DbProviderFactory);
           if (db == null)
                   db = newdatabase;

               catch (System.Configuration.ConfigurationException ce)
                   LogError("Couldn't create the database object, caught a ConfigurationException", ce);
                   throw new DBException("Could not obtain a handle to the database", ce);
               catch (System.Reflection.TargetInvocationException tie)
                   LogError("Couldn't create the database object, caught a TargetInvocationException", tie);
                   throw new DBException("Could not obtain a handle to the database", tie);

           return db = newdatabase;



  // close db
   protected void CloseReader(IDataReader reader)


       }catch(Exception ex){

           LogWarn("Caught an exception in trying to close the reader, logging only.", ex);


   }//end CloseReader()

}// end DAO class{}
}// End namespace