Asp.net MVC3, open connection

Question

I open Database connection in my controller and pass the opened connection to my Model for using that connection.

but since 2nd time calling to the controller(Ajax), it return an error message that say 'Connection must be open.' I reloaded page, and call the controller again then it works well. but for the 2nd time calling, it keep return that error message.

My controller has the code to open DB Connection, so I believed, it should open database for every processing(Calling). and the (open) code is in using{} block so after using it, it should be closed automatically.

but I think I'm doing wrong :(

here is my code,

public JsonResult myControllerMethod() // Action Controller
{
    using (AdsConnection conn = new AdsConnection(connString))
    {
    conn.Open();
    AdsTransaction txn = conn.BeginTransaction(); // Begin Transaction
    try
    {
        MyModel mo = new MyModel();
        mo.doProcess(conn); // pass connection to model
        txn.Commit();
        return Json(new { success = true });
    }
    catch (Exception e)
    {
        txn.Rollback();
        return Json(new { success = false, message = e.Message });
    }
    }
}

MyModel

public void doProcess(AdsConncection conn){ // Model
    try{
        ..
        //AdsCommand select, update, delete and insert....
        ..
    }catch(Exception e){
        throw e;
    }
}

Anybody know, what I am doing wrong? please advice me.

Thanks

score 0 · Accepted Answer · answered Sep 25 '12 at 20:52

0

You shouldn't pass a database connection to your view - your view should accept an object (as a model) that contains "solid" data. A view should have no logic of its own, and a Model's logic should only be concerned with modifying internal state, such as performing validation (not verification) or some internal data transformation. The task of populating a Model from a database should be done by the controller or a separate mapping class (or by some kind of "Populate" method of the model, but this method should be called and contained within the controller's action method).

That said, the problem you're experiencing is because you're using using() blocks. A using() block will always call the IDisposable.Dispose() method of its subject object, in this case a DB connection. SqlConnection.Dispose calls its .Close method, which is why the connection object is closed whenever the method returns.

answered Sep 25 '12 at 20:52

Dai

141,631
28
261
374

Thank you for your answer, but I'm not passing DB Connection to View. It pass to Model (Mvc). and you mean, I can not use the using block in my action controller? – Expert wanna be Sep 25 '12 at 20:58
Sorry - I didn't read your post through (so most of my reply is moot). What happens when you step-through debugging in your controller method? You should be able to see when your connection is closed. – Dai Sep 25 '12 at 21:03
Also, can you post the stack trace of the exception? – Dai Sep 25 '12 at 21:03
Ohhh, I got it, I am so sorry, it was miss typo :( – Expert wanna be Sep 25 '12 at 21:11

Asp.net MVC3, open connection

1 Answers1