Connection Class Information storage & retrieval

Posted on 2006-05-19
Medium Priority
Last Modified: 2010-04-16
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.

Question by:Autofreak
  • 2
  • 2
LVL 25

Expert Comment

ID: 16718734
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.  

Author Comment

ID: 16720706
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,

LVL 25

Accepted Solution

dstanley9 earned 500 total points
ID: 16720809
Yes, that's exactly what I was suggesting.  THat eliminates any hard-coding of database info (other than SQL and/or stored procedure names).

You don't have to use a web service; we did just so it could be used across several applications.  Web Serivces in general is too big a subject to go through here, buy you can start here:


Here's the schema I used:

dbName      Server      Database      Username      Password      useWindowsAuth
Payroll      sql01      payrolldb      readonly      readonly      0
Clients      sql02      clients      <null>      <null>      1

I would query the database for the key "Payroll" for example, and I would get back the server, DB, username, and password.

This way, if you ever switch database servers for a particular database, you just change the server column and all clients would automatically point to the new server.


Author Comment

ID: 16720924
Thank you!

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article introduced a TextBox that supports transparent background.   Introduction TextBox is the most widely used control component in GUI design. Most GUI controls do not support transparent background and more or less do not have the…
We all know that functional code is the leg that any good program stands on when it comes right down to it, however, if your program lacks a good user interface your product may not have the appeal needed to keep your customers happy. This issue can…
Loops Section Overview
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question