To close or not to close connection in database

Question

I work with Windows-Mobile and Windows-CE using SqlCE and I dont know what better to do.

To open connection when the program open, run any query's... update...delete database and close the connection after the program close?

Or open connection run any query's..update...delete database and close the connection immediately?

Lurlenelurline

Answer 1

Nice. The answers are all over the place. Here's what I know from experience and interacting with the SQL Compact team:

1. Closing the connection flushes the changes you've made, otherwise the engine waits for the flush period before doing it. It's a good idea to close the connection when you're done using it to ensure that your changes actually go to the store. A power loss after a write and before a flush will lose data.
2. There is no official connection pool, but opening the first connection is expensive (i.e. slow), all others are quick. The recommendation I got from the team is to actually create a connection when the app starts up and just leave it open. You don't actually need to use it, but keeping it open keeps a lot of connection info cached so that subsequent connections to the same store are quick.

So the answer, actually, is both.

Edit

For those interested, a good example of how this works can be seen in the OpenNETCF ORM library. The library, by default, creates a "maintenance" connection that remains open and is used for doing things like schema queries. All other data operations use their own connection. You also have to option to configure the library to reuse a single connection for the life of the Store, or to use a new connection every time it touches the store. Perfomance and behavior has always been best in all of my projects using the default (which is why I made it the default).

Seise

Thanks for the info. Just started using your ORM library. :-) – Inoperable

I have had problems with the connection open time slow for MS Charts. As you recommended, I opened a first connection and left it open. All other connections were then opened/closed in the correct manner with these times. Timespan with no connection left open: 00:00:00.0477849 Slight delay with Chart redraw. Timespan with connection left open as per ctacke recommendation: 00:00:00.0007839 Chart redraws instantly. Brilliant! – Eastward

Answer 2

Always keep a connection open for the lifetime of your Windows Mobile app. Opening a SQL Server Compact database is a costly operation.

Luddite

Answer 3

You should close your connection each time you have completed an sql transaction to free connection ports. Always a good practice to avoid security breach.

Calzada

Can you explain said "security breach"? SQL Compact is hosted in-process so there are no "connection ports". – Seise

Answer 4

Connection establishment is a slow operation, so, creating and closing it can slow down the application. On the opposite hand, if you have a lot of clients, the connection pool will be filled very quickly and other clients won't be able to connect.

Wring

Answer 5

There are already some conflicting answers here.

To be honest, I'm not enirely sure how WinCE deals with connections. I don't think there is a ConnectionPool.

But the general pattern in .NET is to keep connections open as short as possible. This improves reliability and prevents resource leaks. Make sure you know about the using (var conn = ...) { ... } pattern.

So I would say: go with your second option, and only keep connections longer if you really experience a performance problem, and if opening the connection is the cause. I don't think it will be with SqlCE

Kurtiskurtosis

Answer 6

On a single-user platform such as wince, there's no harm in keeping the connection open, and you may get better performance.

Pelayo

Answer 7

If worry about data lost because you are not calling Close() frequently, you can execute your code within a transaction that commits changes to disk immediately:

using (SqlCeTransaction transaction = this.connection.BeginTransaction())
{
    using (SqlCeCommand command = new SqlCeCommand(query, connection))
    {
        command.Transaction = transaction;
        command.ExecuteNonQuery();
    }
    transaction.Commit(CommitMode.Immediate);
}

Of course, there is still some performance lost when using CommitMode.Immediate too frequently.

Postmeridian