Should I Open() And Close() My Sql Database Constantly Or Leave It Open?

I am creating an app that uses an SQL database for storing data. The way the app is designed it will be updated every 3 minutes or so with new data depending on user actions while

Solution 1:

I tried to leave a connection open once -- I used it to stuff a repeater or something -- can't remember now.

Later in the program, I had another need to use the connection -- I think I had it so that when a user clicked a parent item in the repeater, a detail div would pop-up with more information for that item. This generated an error -- something to the effect of 'cannot open() on an open connection'.

I think the error might have been avoidable another way (like checking to see if the connection I was trying to open was already open), but as I thought about it, I realized I'd have to make that a standard practice throughout my app, and that seemed like too much work, so I just made it a standard practice to always close my connections after each use.

Connections stay in a connection pool -- I'm no whiz on that -- but if was curious about performance, I guess I'd keep that in mind, in terms of what it costs to open a connection multiple times -- whatever your situation is requiring anyway.

Another thought is that your DB admin may be able to force-close all open connections, or the db may close for some other reason. If you're not the dba, you might give a thought to risk/benefit of depending on something that you don't have long-term control over like keeping the connection open.

Solution 2:

Three minutes is eternity on todays processors, even phone processors. I would close it and open it each time. It is better than possibly leaving handles hanging out in 'the ether'.

Solution 3:

Let's say you have several DB operations to do back to back in a single thread. I'd keep the DB open to do those actions. However close the DB after doing a chunk of work.

Solution 4:

I think the answer to this question also depends on what type of application is accessing the database.

If you re-query the database a lot In this case you can keep the database open.

Are there any other applications accessing the same database? If there is a risk for concurrency or blocking issues, it might be wise to close the database after finished reading/writing from/to it.

Have a look onthe following url

When to close db connection on android? Every time after your operation finished or after your app exit

Thanks Deepak

Solution 5:

if you ask me by everything i have read so far about android and in general it is better to close it right after you make the modifications.

What i find its best to do when you can is actually make and register a content provider. It has its prices but i believe you gain a lot more than you pay. You can do pretty much everything with a content provider and the notepad example on the android portal is great for learning how to implement it. it takes care of db synchronization and opening/closing it. so maybe look into that.