Connection Class Information storage & retrieval

Posted on 2006-05-19
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
    LVL 25

    Expert Comment

    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

    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

    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

    Thank you!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    In one of my recent projects, I was working with IP cameras, I need to take some pictures from the camera and do some processing on it. The first step, was to retrieve the image from camera into Image object. So that it can be displayed or …
    Extention Methods in C# 3.0 by Ivo Stoykov C# 3.0 offers extension methods. They allow extending existing classes without changing the class's source code or relying on inheritance. These are static methods invoked as instance method. This…
    It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
    In this sixth video of the Xpdf series, we discuss and demonstrate the PDFtoPNG utility, which converts a multi-page PDF file to separate color, grayscale, or monochrome PNG files, creating one PNG file for each page in the PDF. It does this via a c…

    761 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

    Need Help in Real-Time?

    Connect with top rated Experts

    8 Experts available now in Live!

    Get 1:1 Help Now