I have been working on creating a ticketing system for a client and they wanted to have a replica of the system install at a two location, each with its own database. In addition to this they want to be able to communicate with hosted database at different locations and generate report based on the data.
So I have been researching different approaches to take and found out the below described approach is the easiest.
- Create a table in the database to store the connection string details, I have the following items in the table.
- Connection Name
- Server Name
- Database Name
- Requires User Details ?
- Password (this is not really good, its better to ask the user for the password when required)
2. Create views for user to manage the connection strings.
- List, edit and create
3. Using the connection strings in querying.
I have created a view for reports and it has a drop-down list to select the connection string name. When the connection string name is selected, I can look up the database for the details of connection string and create a database connection string dynamically.