Database connection object with application-wide scope


Yet another connection object with application-wide scope . . .

Until recently I was working with VBA on Microsoft Access Database Projects (ADP).  Since moving to C# and ADO.NET, I've missed some of the convenience in the MS Access VBA Editor.  One of those really nice things was the CurrentProject object which gave application-wide access to a connection object.  I decided to create this functionality for use in my C# ADO.NET programs.

Basically, it consists of a class with static properties and very little code.  Begin by creating a new class for your existing project.  I called mine "CurrentProject.cs". Then fill in the code as shown below.  Once done, you can reference "CurrentProject.Connection" and "CurrentProject.ConnectionString" from anywhere in your forms or classes (Example: "SqlCommand cmd = CurrentProject.Connection.CreateCommand("commandtextstring");")

Hope someone finds this useful . . .

-Terry

/////////////////// Code Begins /////////////////////

using System;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Text;
using System.Windows.Forms;

namespace myDatabaseClient
{
    public sealed class CurrentProject
    {
        private static SqlConnection m_dbconnection;
        private static string m_connectionstring;

        //Properties
        public static SqlConnection Connection
        {
            get
            {
                if (m_dbconnection==null) { SetUpConnection(); }
                return m_dbconnection;
            }
        }

        public static string ConnectionString
        {
            get
            {
            if (m_connectionstring + "" == "") { SetConnectionString();}
            return m_connectionstring;
            }
        }

 //Since this project is for a customer in a different city,
 //the connection string will be different for him.
 //This method checks to see who is logged in to the
 //local computer.  If it is me, then it uses the
 //connection string for my internal network.  If it
 //is not me, then it assumes that the customer is
 //logged in on his network, so it provides a connection
 //string for his server;
        private static void SetConnectionString()
        {
            if (Environment.UserName == "mahert")
            {        //it's me, so use this string
                m_connectionstring =
                @"Data Source = SERVER01;" +
                "Network Library=DBMSSOCN;" +
                "Initial Catalog=Northwind;" +
                "Integrated Security=true;";
            }
            else
            {     //its not me so use the other string.
                m_connectionstring =
                @"Data Source = DBSERVER;" +
                 "Network Library=DBMSSOCN;" +
                 "Initial Catalog=Northwind;" +
                 "User ID=sa;" +
                 "Password=******;";
            }
            return;
        }

        //if this is the first time through, set up the connection now
        private static void SetUpConnection()
        {  
            //first make sure we have a connection string;
            if (m_connectionstring + "" == "") { SetConnectionString(); }

            //then check to see if the connection exists
            if (m_dbconnection == null)
            {
                try
                {
                    m_dbconnection = new SqlConnection(m_connectionstring);
                }
                catch (Exception exc)
                {
                    MessageBox.Show("CurrentProject:SetUpConnection: " + exc.Message);

                }
            }
        }
    }
}


Similar Articles