Connecting 2 the DB after every transaction?

[udit99]

I recently came across this 3-tier sample application at PlanetSourceCode which connected 2 Access through ADO. On going through the code I found that the application connected to the Database(MS Access) everytime a transaction(INSERT\UPDA TE\DELETE\VIEW) was carried out and disconnected after it was done. While what I have been doing till now is Connect to the DB at the beginning of the application( in the Form Load of the MDI) and then keep it connected throughout until the application ends.

So what is the usual\preferred method ? Any special circumstances when option1 is chosen over option 2 ?

[Gaffer]

well I don't know what those very clever people are doing over in PlanetsourceCode, but I personally get all the database conectivity done as soon as the user starts the app, adn close it when the program shuts down.
I can see no advantage in opening for each DML statement - it's too much overhead..

Do you have a link to the code?

[swatty]

Why should you keep the connection open to the database.

If your app is running on different machines it will slow down the network traffic.
If you open your connection say in the morning app was started at 08:00 am and stays open , how can you be sure the connection is still valid without checking it.

When you develop in 3-tier and keep your connection open how would you do it without deleting the object connected to the database.

If you keep an instance of this object open for the whole time your app is running , why even bother to develop in 3-tier.

I always disconnect after the transactions are done, think it is the best way to keep track of your connections and destroying your objects yourself.

[Gaffer]

I think we all disconnect after all transactions are completed.

I think the issue here is that is was suggested that the database connection should be opened and closed for each SQL statement.

[Ecniv]

I personlly do that - I mean open for each transaction.

Example :
Form Opens
- open connection to grab data etc and fill list boxes
- close connection

Update Click
- open connection to send results
- close
- open for refreshing lists (as this is in its own code)
- close



I suppose you could send it a variable reference to the open connection - then if there isn't one it would open a new one - otherwise it would use the open one.

Something to think about next time...


Vince

[udit99]

Inference is that when develping normal Desktop Appz, connect on application start and disconnect on application end ( since there is no network b/width 2 worry abt), and when using 3-tier distributed architecture, connect and d/c b4 and after every transaction.

Right ?