I am just doing some research at the moment into this and would like to know if it's possible.
I have an application where someone logs in with a username/ I would like to have a table with all the company id's stored and depending on which one they log in using it will use a connection string from the DB for that company.
I am fully aware of how to do it with the web.config but want to minimise the information kept here because potentially we are talking of around 1,000 connection strings.
Sure create a table or modify an existing table.
CREATE TABLE Connection
(
Id INT IDENTITY NOT NULL PRIMARY KEY
,ConnectionString VARCHAR(100) NOT NULL
);
INSERT INTO Connection (ConnectionString)
VALUES ('Server=myServerAddress;Database=myDataBase;Trusted_Connection=True;');
I would advise against storing passwords in the connection string when the contents of the table are unencrypted. Setup a domain account with Sql Server privileges to run your IIS app pool or Windows Service under.
const string Query = "SELECT ConnectionString FROM Connection WHERE Id = @Id";
public string GetConnectionString(int id)
{
using(var connection = GetConnection())
using(var command = new SqlCommand(Query, connection))
{
command.Parameters.AddWithValue("@Id", id);
connection.Open();
using(var reader = command.ExecuteReader())
{
if(reader.Read())
{
return Convert.ToString(reader["ConnectionString"]);
}
}
}
}
var connectionString = GetConnectionString(1);
using(var connection = new SqlConnection(connectionString))
{
//logic
}
Yes, this is possible.
Store the connection strings in your (main?) DB, retrieve them and use them when instantiating new DdConnections.
Most of the classes that inherit from DbConnection (SqlConnection, for example) have a constructor overload that takes a connection string.
.config to a central DB that contains all the other ones.