When Entity Framework Connection Gets Open & Closed

Question

I have an asp.net web forms application it uses EF for all database activities. When page loads I have to fetch lot of data from different tables. I have a DataAccessor class where I am having a member variable of Entity Framework DbContext (MyDBEntities). See class definition below.

public class DataAccessor
{
    public static DataAccessor Instance = new DataAccessor();
    private MyDBEntities dbEntities = new MyDBEntities();

    private DataAccessor() 
    {
    }

    public FetchTable_1_Data()
    {
        return dbEntities.Table1.Where( x => x.Id = something).List();
    }

    public FetchTable_2_Data()
    {
         return dbEntities.Table2.Where( x => x.Id = something).List();
    }

    public FetchTable_n_Data()
    {
         return dbEntities.TableN.Where( x => x.Id = something).List();
    }

}

Using data accessor as below in page load

Page_Load()
{
   Repeater1.DataSource = DataAccessor.Instance.FetchTable_1_Data();
   Repeater1.DataBind();

   Repeater2.DataSource = DataAccessor.Instance.FetchTable_2_Data();
   Repeater2.DataBind();
}

My Questions are,

1. When DB connection is getting open in my case
2. When DB connection getting closed?
3. Do I need to use using(MyDBEntities dbEntities = new MyDBEntities()) instead of using member variable
4. If i would need to use as question #3, do I need to open connection using "using" statement in each fetch methods?

My database connection is broken sometimes and system performance is getting degrade, i suspect my usage of EF. Can someone advice me how to use EF?

Some more questions,

1. How connection pooling is working with EF?

   connectionString="metadata=res:///ReviewsDb.csdl|res:///ReviewsDb.ssdl|res://*/MyDb.msl;provider=System.Data.SqlClient;provider connection string="data source=SERVER;initial catalog=MyDB;persist security info=True;user id=sa;password=mypwd;multipleactiveresultsets=True;application name=EntityFramework"" providerName="System.Data.EntityClient" />

2. I am using above connection string, do I have connection pool with above connection string?

3. How can I configure connection in EF

Appreciate any help on these questions. thank you friends

Answer 1

You dataaccessor is a static member.

Stay away from using static since it may survive between page accesses. It is not hard to imagine what damage that could cause.

I have got database errors from the previous page access when I was doing that. I had to scratch my head a lot.

Also use == when comparing instead of =.

You don´t have to use using.

I would actually advise you to have a data accessor that your Page_Load will create with something like var accessor = new DataAccesssor(); instead of using your approach with a private constructor.

Now it will be clear for you which lifetime your MyDbEntities instance will have. It will be scoped to your Page_Load function.