Controller Refresh after 5 sec - Avoid Constructor Call

Question

I have following code for my Controller Class. I refresh my View from the controller. Every time Refresh takes place, my whole class constructor is called, I wanted to refresh only my Show function and its attached View. How to avoid this constructor getting called every time.

public class DriverController : Controller
{
    OdbcConnection DBConnection; //= new OdbcConnection("DSN=pitbuccx");
    OdbcConnection DBConnection2; // = new OdbcConnection("DSN=pitbuccx2");
    Query q;
    string myQuery;
    OdbcCommand cmd;
    OdbcCommand cmd2;
    OdbcDataReader DbReader;
    OdbcDataReader DbReader2;

    string LoggedInAgents;   
    string OnCalls;              
    string AgentsInReadyState;   
    string AgentsInNotReadyState;
    string AgentsInWrapup;       
    string ReservedAgents;       
    string CallsOffered;         
    string CallsAnswered;        
    string CallsAbandoned;       
    string CallsInQueue;         
    string LongestCallInQueue;   
    string AbandRate;            
    string ServiceLevelPct;      
    string ASA;                  
    string AHT;
    string LongestTalkDuration;  

    public DriverController()
    {
        OdbcConnection DBConnection = new OdbcConnection("DSN=pitbuccx01");
        DBConnection.Open();
        OdbcConnection DBConnection2 = new OdbcConnection("DSN=pitbuccx02");
        DBConnection2.Open();
        q = new Query();
        myQuery = q.getQuery();

        cmd = DBConnection.CreateCommand();
        cmd2 = DBConnection2.CreateCommand();
        cmd.CommandText = myQuery;
        cmd2.CommandText = myQuery;

        Console.WriteLine("This is Constructor");
    }

    public ActionResult Show()
    {
        DbReader = cmd.ExecuteReader();
        DbReader2 = cmd2.ExecuteReader();
        DbReader.Read();
        DbReader2.Read();

        string testData1;
        string testData2;
        //Checking Connection Data Validity.
        testData1 = DbReader["A"].ToString();
        testData2 = DbReader2["B"].ToString();

        Response.AddHeader("Refresh", "5");
        return View();
    }
}

 ~DriverController()
    {

        DbReader.Close();
        cmd.Dispose();
        DBConnection.Close();

        DbReader2.Close();
        cmd2.Dispose();
        DBConnection2.Close();
    }

_"How to avoid this constructor getting called every time."_ - you can't by default, and you shouldn't want to. Controllers get initialized to handle one request. Sounds like an XY problem. What's the problem with running the constructor every five seconds? You _could_ create your own controller factory, but again, you really don't want to. — CodeCaster, Sep 07 '16 at 09:36
There is no way to stop the constructor getting called every time. Anyway, it looks fairly simple code here so there's no performance hit really (unless `q.getQuery()` takes a lot of resource) — DavidG, Sep 07 '16 at 09:37
DavidG@ In Constructor "Connection.Open" is called. That is costly function, and I dnt want to Open it every 5 sec. Need to open it once and then fetch updated data every time — BASEER ULHASSAN, Sep 07 '16 at 09:48
Opening a connection is most certainly not a costly function and you should absolutely **always** open a connection just before you run a query and then close it immediately. — DavidG, Sep 07 '16 at 09:51
If I keep it as it is then my destructor (closing connections) is fine or should I place dispose functions in my Show method as well. Also, I hit Live DB so I thought Opening connection every time is Overhead — BASEER ULHASSAN, Sep 07 '16 at 09:53
Well of course nothing is free, but you should open and close the connection inside the `Show` action. — DavidG, Sep 07 '16 at 10:06
Can I create a Static class where I open connections, and only access those connections in Controller — BASEER ULHASSAN, Sep 07 '16 at 10:06

score 2 · Answer 1 · answered Sep 07 '16 at 09:42

2

You should not be doing this in your controller. By definition a controller is the director of web requests. At the minimum you need a domain type to create and release external resources like DB connections, and do queries against the data source. I can't even see where connections are being closed/disposed.

answered Sep 07 '16 at 09:42

DvS

1,025
6
11

Added close/dispose in Destructor. I need to open and close connection only ONCE in LIfetime – BASEER ULHASSAN Sep 07 '16 at 09:50
@BASEERULHASSAN - No, *no*, NO! Don't use just ONE connection throughout your application! Wrap it in a `using` block every time you need to access your DB... – Geoff James Sep 07 '16 at 09:54
Geoff@ Whats wrong for one connection only because same DB has to be accessed every 5 sec. Why not just utilize original created Connection/. – BASEER ULHASSAN Sep 07 '16 at 09:56
1

That's also not the right way to do it. You need to hook into the controller lifecycle, like overriding the Controller.EndExecute method to do cleanup. But you are over-concerned about the performance hit of creating database connections. The right way is to open and close connections as they are needed so other requests can get connections from the pool. – DvS Sep 07 '16 at 09:57
@BASEERULHASSAN See this: http://programmers.stackexchange.com/questions/142065/creating-database-connections-do-it-once-or-for-each-query – Geoff James Sep 07 '16 at 10:01

score 1 · Accepted Answer · edited May 23 '17 at 10:29

How to avoid controller instantiation for every request?

You can do that by implementing and registering your own controller factory, which instantiates a given controller only once and then stores it somewhere in the AppDomain so it stays alive for longer than the current request, for example in some static variable.

This question however reeks like an XY problem. You seem to be concerned with the relative cost of opening a database connection for every request.

Well good news, everybody! You're not the first to think about this, and you should not worry, because that has already been fixed long ago by the framework builders: .NET uses connection pooling.

In short, this means that you should not worry about opening database connections, because an earlier opened connection will be saved in the pool and returned to your application when it requests it.

Controller Refresh after 5 sec - Avoid Constructor Call

2 Answers2