asked on

Connection Class Information storage & retrieval

Hi Guys,
I am working on a connection Class and trying to design a wrapper for all DB activities. There are several SQL Server instances and many DB's on those that I need to dinamically grand access to. I would like to consider several alternatives about how to store and retrieve connection parameters.
Do I define them as Class constants like Server1, Server2, Server1.AccountsDB, Server2.TransactionsDB...etc, or employ a DB for that, say set up 2 tables with one-to-many between them where the first would hold the server names and the second the server names and db names? I even saw some use the Registry for that.

Thanks,
Serge

dstanley9

One of the best utils I've used was a utility that stored invormation about databases indexed by a key. It stored the server, database, and a username and password that you could retrieve via a web service. For example, if you wanted to connect to the Transactions database, you'd have a function like:

public struct ConnectionInfo
{
string Server;
string Database;
string Username;
string Password;
bool UseWindowsAuth;
}

public ConnectionInfo GetConnectionInfo(string dbName)
{
// query the database to get the parameters for key "dbName"
}

THis way, the dbName can be completely independent of the server and database names.

You could even add a method to return an OLEDB or ODBC connection string based on the server type (very handy if your shop has a mix of servers like SQL, SyBase, Oracle, etc.)

This is VERY handy in failover scenario as all you have to do is change the server name to fail over to a secondary server.

Autofreak

ASKER

I am not sure I understand.
1. Do you suggest storing my server/database info in a DB?
2. I am not familiar with the web service feature. Could you elaboratem on that?
3. Could you be more specific? I am providing you with a concrete example.

Ex. This table below - myDBSettings, contains all databases I currently use with their respective servers.

table : myDBSettings, server: AdminServer

ServerName DatabaseName

Server1 DB1
Server1 DB2
Server2 DB1
Server2 DB2

Thank you,
Serge

ASKER CERTIFIED SOLUTION

dstanley9

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Autofreak

ASKER

Excellent!
Thank you!
Serge

Connection Class Information storage &amp; retrieval

Connection Class Information storage & retrieval