How to manage connection string dynamically

Suraj Dubey

Hi,

I have a requirement like, developed an ASP.net web application. I want to host this application for different companies with seperate database, but website shall be hosted only one copy in IIS. so, based on user login, the application should connect different database server.

For Ex: if user "xxx" login with credential and belogs to "ABC" company and the database is "ABC", then ABC data need to display on the web page.

if user "XYZ" login with credential and belogs to "HGJ" company and the database is "HGJ", then HGJ data need to display on the web page.

Can any one give good solution for the same.

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Sachin Singh4y ago
Accepted answer
when the user logs in store the credential somewhere like session.
Then create a separate connection class as shown below
session["compnName"]="loggedInUserCompany"; // ex:- comp1
public IDbConnection GetDbConnection(string connectionString)
       {
           IDbConnection connection = null;

           switch (dbType)
           {
               case EDbConnectionTypes.Sql:
                   connection = new SqlConnection(connectionString);
                   break;
               default:
                   connection = null;
                   break;
           }

           connection.Open();
           return connection;
       }
now store all connection string in your config file and in the DataAcess Layer or wherever you use connection pass connection string based on user.
"Company1CS" value="constr1">
"Company2CS" value="constr1">
Data Access
public class SqlDAL
{
public SqlDAL(string compName)
{
string cs=string.Empty;
if(compName=="comp1")
{
cs=ConfigurationManager.ConnectionString["Company1CS"].ConnectionString;
}
else if(compName=="comp1")
{
cs=ConfigurationManager.ConnectionString["Company1CS"].ConnectionString;
}
}

public List GetAll()
{
using(SqlConnection con= new SQLConnection(cs))
{
// fetch record
}
}
use this DAL and while creating its object pass the company name.
SqlDAL obj= new SqlDAL (Session["compName"].ToString());
var products=obj.GetAll();
+1
Sachin Singh4y ago
In that case you can store all your connection string inside a database table or XML file separately, and can call it based on loggedIn user's Company.

you can also modify connection string on the fly. as shown below
public void ModifyConStr(string  DataSource, string InitialCatalog,string IntegratedSecurity)
{
Configuration config = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None);
config.ConnectionStrings.ConnectionStrings.Add(new ConnectionStringSettings("MyConnectionString",String.Format("DataSource={0};InitialCatalog={1};IntegratedSecurity={2}",DataSource, InitialCatalog, IntegratedSecurity)));
config.Save(ConfigurationSaveMode.Modified, true);
ConfigurationManager.RefreshSection("connectionStrings");

}
+1
Suraj Dubey4y ago
But what to do if 1000 or 5000 company are there my question is based on this sichuation.
+1
Asma Khalid4y ago
You need to create a proper Database structure to capture such events and proceed in your app accordingly.

Mark answer as accepted if helpful

Thanks & Regards,

Asma Khalid
+1