locked
.Net opening and clossing database connections RRS feed

  • Question

  • User733978539 posted

    I currently designed the data access portion of our framework so that every time a business object needed to interact with the database it would have to open a connection, invoke the data access layer (to execute the query), and then close the connection. Then if it needed to run in a transaction it would open the connection, begin the transaction, invoke the data access layer (to execute the query) and then commit the transaction, close the transaction, and finally close the connection.

    I did it this way with the mindset of open late and close early...but what if I needed to call other BO's to submit data in a single transaction? Is there a better way to handle opening and closing connections as well as working with transactions?

    I'm a rookie at architecting applications so I hope I'm not doing this wrong...any help is appreciated.

    Thursday, April 1, 2010 8:32 PM

Answers

  • User-525215917 posted

    Your solution is correct and your application should perform well if you have done no mistakes on other parts of it. Keeping connections busy as short time as possible is good because it increases their reuse.

    Connections that are not in use are put to connection pool. If request comes and connection is requested then the first check is made to connection pool. If there is free connection then this connection is returned. Otherwise new connection will be created. As connections are expensive objects to create then connection pool is excellent solution to minimize the number of connections created during your application run.

    So, if you shorten the time you use your connection you also increase the probability that you need less connection instances to serve more visitors.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, April 2, 2010 2:41 AM

All replies

  • User-1636183269 posted

    in vb we use module.vb.net in which we write sql connection open and close in single point and it is access everywhere. 

    Similar you can create common class and project to call database transation. So when multiple BO call then it will  begin transaction by calling and all BO operation complete commit.

    One important thing if it is dependent on each other like customer->Address then such scenario we prefer to use SP so we can handle this in one transaction instead of call again again.

    Thursday, April 1, 2010 9:35 PM
  • User-826317797 posted

    Open late and close early is a good policy too follow.  You might want to look at what LINQ to SQL of Entity Framework has to offer.  These frameworks have the abilities to Lazy Load, work within transactions, etc.  For example, LINQ 2 SQL supports three Transactions models. 

    Friday, April 2, 2010 1:19 AM
  • User2077524089 posted

    Hi,

    If you are not using any transaction in ur application then close all the connections in finally block...

    While using Transactions open the transaction in Immediate line ater Try block and then close the transaction in Finally

    if at all any error occurs then close the transaction and dbconnection in Catch block itself...

    Ex:

    try

    {trans=dbcon.begintransaction();

    tran.commit();

    }

     catch(sqlexception ex)

    {  if(tran!=null) trans.close();dbcon.closeconnection();

    }

    finall

    {  if(tran!=null) trans.close();dbcon.closedbconnection();}

     

    Try this

    Friday, April 2, 2010 1:33 AM
  • User-525215917 posted

    Your solution is correct and your application should perform well if you have done no mistakes on other parts of it. Keeping connections busy as short time as possible is good because it increases their reuse.

    Connections that are not in use are put to connection pool. If request comes and connection is requested then the first check is made to connection pool. If there is free connection then this connection is returned. Otherwise new connection will be created. As connections are expensive objects to create then connection pool is excellent solution to minimize the number of connections created during your application run.

    So, if you shorten the time you use your connection you also increase the probability that you need less connection instances to serve more visitors.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, April 2, 2010 2:41 AM