Bring the data module to work with --- pass-Store-use a connection inside a reusable data module

Question

I want to seperate the data module of a site into an assembly ( a single dll file ) , What is the best way to get-store and pass the ConnectionString of the site in dealing with the web application . Inside the data assembly I made a static class named ConnectionManager . It has a property named DatabaseConnectionName , that I want to pass and store the connection namewhich is inside Web.Config file . In this strategy I decided to get the name and make the connection in the load time of the website at the Global.asax file and stroe that in the property I mentioned earlier ( DatabaseConnectionName ) . But , This is just the strategy that I used , I dont know what is the common pattern for doing this job .

Parts of code : ===================================== [ ------------ Global.asax ------------ ]

the code in the site that makes the Data module accessible for the site
    void Application_Start(object sender, EventArgs e) 
    {
         OurCompany.Data.ConnectionManager.DatabaseConnectionName = "MasterConnection";
    }

[ ------------ ConnectionManager Class ------------ ] this is in the data module apart from the site

public static class ConnectionManager
{

    public static SqlConnection  GetMasterConnection()
    {
        string connectionString = ConfigurationManager.ConnectionStrings[**DatabaseConnectionName**].ConnectionString;
        SqlConnection conn;     
        //conn.Open();
        conn = new SqlConnection(connectionString);
        return  conn;
    }

    private static string **databaseConnectionName**;
    public static string DatabaseConnectionName
    {
        get
        {
            return databaseConnectionName;
        }
        set
        {
            databaseConnectionName = value;
        }
    }

== END ===========================================================

--- Questions are : ---

1. Where to store the connection ? ( here was a property inside the ConnectionManager Class , theCompany.Data.ConnectionManager.DatabaseConnectionName )

2. When make this connection ? ( here was at Global.asax Application Load time )

3. Which method is best for storing such information : SessionState or ViewState or a simple property

4. Is this strategy good ? Do you know any better way or the common pattern for this ?

Thanks for any information - MHM -

Answer 1

A few thoughts...

1. You shouldn't be storing and hanging onto an open database connection. Open the connection, do your database operations, then close immediately. Apply the rule of acquire late, release early.

2. See point 1.

3. Don't store database connections in session state. See point 1 again. If you mean the connection string, then just read it from the configuration manager when you need it. It's already cached for you, don't re-invent or wrap the wheel.

4. I suggest you take a look at the patterns and practices enterprise library which abstracts away many of the common patterns of managing data access:

http://www.codeplex.com/entlib

HTH
Kev