CODEWITHSUNDEEP
ado.netdatabase-connectionasp.net-mvc-5
Mohammed Suez
Mohammed Suez

Reputation: 159

C# MVC5 classic ADO.NET when to open connection

I'm using MVC5 with classic ADO.NET objects such as sqldatareader and sqldataadapter and sqlconnection and so on.... My controllers are creating a connection while initializing because I need to send the request object to the class holding the sqlconnection for something irrelevant to the question so my controller has an override void

protected override void Initialize(RequestContext requestContext)
    {
        base.Initialize(requestContext);
        db = new db(Request);
        db.Connect();
    }

Where db is my class and the method (connect) will create the sqlconnection object and open a connection...

and to close the connection I used the controller's dispose method as follows 

protected override void Dispose(bool disposing)
    {
        base.Dispose(disposing);
        if (db != null)
        {
            db.Close();
            db = null;
        }
    }

and everything works fine then at one moment I got a weird server error (can't connect to db) please notice that my host is smarterasp.net

I can connect to database remotely using my home computer and I can connect to the web host as well so the problem is between my webhost and my database host, or between my application and my database host...

or it could be something related to the connection pooling even though the server error doesn't give me any details or stack trace(hens error is not inside my app thread)....

and I've fixed the problem by opening (remote iis) tab of smarterasp.net's control panel and clicked on (fix ACL) which I have no idea what it does but it fixed my problem.... temporarily :( unfortunately the problem reoccurred many times after that

so my question is in short format is it good practice to open the connection while I'm initializing the controller and close it while the controller disposing???

and what do you think the error reason is??

finally I want to apologize if the question wasn't clear enough because English is not my first language (obviously)....

thanks a lot

Upvotes: 1

Views: 971

Answers (1)

William Xifaras
William Xifaras

Reputation: 5312

so my question is in short format is it good practice to open the connection while I'm initializing the controller and close it while the controller disposing???

I do not think that is a good approach. You shouldn't open / close database connections and / or access the database from your controller. The controller should be as "thin" as possible. Additionally - the connection should be kept open for as short a period of time as possible and let ADO.NET connection pooling handle the details for you.

I also recommend wrapping your connection in a using block as it will implicitly call the close method:

using (SqlConnection connection = new SqlConnection(
           connectionString))
{
    SqlCommand command = new SqlCommand(queryString, connection);
    command.Connection.Open();
    command.ExecuteNonQuery();
}

You did not say what the exact error is. At first glance, you aren't even checking to see if the connection is open before you try closing it. You should check the connection state before you try to explicitly close it and this should happen outside of the controller. Though I recommend that you wrap your SqlConnection in a using block (mentioned above).

EDIT

I read your comment. You are trying to manage the connection within the context of the controller's lifecycle and I suspect this is your issue.

If you were using Entity Framework (or possibly an another ORM), an IoC with "per-request lifestyle" - then the IoC container would properly dispose your context (connection) at the end of each request, and serve a new instance at each new one.

Perhaps this an option you can explore if you want to manage your database connection this way.

Upvotes: 1

Related Questions