Openning and Closing DB Connection with every DB Access !! Problem ?

tarekahf posted on Monday, May 05, 2008

After I worked a while with CSLA Samples, and got a basic understanding of this great framework, I prepared a sample application for use within our environment, by re-writing small existing programs using CSLA.NET, and already use small parts of CSLA with one live application.

I passed the sample applications and all related material to my colleagues, and they started using them too.

One of my colleague, he is an experienced Java Developer with 10 years of experience. He has some concerns about the techniques applied in the Sample Applications for Opening and Closing Database Connections. He told me that in order to have more efficient code, the connection must be opened once, used as much as possible, and close it when it is no longer needed. Meaning, the life span of the connection must pass through multiple calls from the client to the web server.

In other words, we need to implement some kind of connection pooling, probably within the scope single user session, not across all sessions, or something like that.

According to what I remember, this kind of work is already taken care of by the Server, but I am not sure about the details.

My question:

1. Do we have to follow this method: Open Connection ... Do the Work.... Close the Connection, in a single call to the server ?

2. Is there any method provided by CSLA to help in that regard ?

3. Do we "the programmer" have to worry about connection pooling stuff and ensure efficient use/reuse of connections ?

Any comments/feedback will be greatly appreciated.

Tarek.

ajj3085 replied on Monday, May 05, 2008

In Clsa 3.5, there's a ConnectionManager or something like that, which you can use to ensure only one connection is opened and reused until the operations are complete.

Prior to Csla 3.5, you can open a connection and store it in the ApplicationContext.LocalContext.  Your instances can look there to see if there's an existing connection and use it, or create a new one and place it in the context. 

Connection pooling occurs automatically if you're using Ado.Net, provided the connection strings are identical.  So if that's the case, you won't need worry as much about opening and closing connections, because in reality the connection won't really be closed, just put back onto the pool. 

Of course if you're doing things within a transaction, you CAN'T open and close connections.  Also, if you intend to use TransactionScope transactions, you need to make sure you only open the connection once to avoid the transactions being promoted to a DTC transaction.

HTH
Andy

JoeFallon1 replied on Monday, May 05, 2008

When a web server accesses a database using the same credentials it automatically sets up and uses a connection pool. You can configure the size of this pool if the default is not big enough. This means that when CSLA code opens a connection, uses it and closes it that it really gets a connection from this pool. If there is no open connection then one is created and opened for you. When CSLA calls, "close connection" the connection is actually returned to the pool and is kept open by the web server. So the overhead of creating and opening connections is managed for you by the connection pool. Thus there is no need to handle this by yourself in your code.

Joe

 

ajj3085 replied on Monday, May 05, 2008

Joe,  that's a feature of Ado.net and has nothing to do with whether or not the code is run on a web server.

tarekahf replied on Monday, May 05, 2008

So, the connections used in CSLA .NET Sample Applications are ADO.NET ?

I always thought that I should never worry about connection re-use because this is handled else where for me. Even when I was involved in developing a huge application using ASP and SQL Server about 6 years ago (in my previous work), we had the same concern. I remember that we did a research, and based on the results, we decided not to worry about this issue, because once you enable pooling, it is taken care for you automatically.

Connection Pooling can even be defined on the ODBC Level in Administrative Tools in Control panel. But I do not really know much about the technical details in that regards.

So, I can say that it is a good practice to open the connection use it, and close it as soon as I am done with it.

Tarek.

Curelom replied on Monday, May 05, 2008

In general, it is good practice to open the connection, use it, then immediately free it.  This allows other threads to use the connection so that the server doesn't have to open as many connections for db access.  I've heard of people opening a connection and storing it in the session on the server.  It seems that Java developers are more likely to do this.  This would NOT be a scalable solution.  It would require at least one database connection per application/user.  When you get lots of users, you system comes to a crawl.

If you are doing a lot of database work all at once, it's good to keep the connection open until all the work is done.  You don't need to open and close the connection for each operation.