If a console program terminates, will the database connections used in the program still remain open?

Question

In Java and C#, they both have something like System.terminate(). If my program has open database connections, database readers, and database command variables, and I terminate my program in a catch clause, will database resources still remain in use? or will they be freed automatically since my entire program has just exited?

Normally, how should I handle such cases to make sure I always free database connections, whether through normal program termination or unexpected program termination? Any good practices?

Answer 1

Unless you specifically close the connection, they will remain open until a timeout hits.

I've found this out the hard way in C# a few times. Best practices dictate to shutdown/close resources you know you will no longer need. File I/O streams , DB connections , etc etc

Answer 2

Upon termination of a process, all associated resources (incl. memory, handles, connections, ...) will be freed up.

Normally, in C#, you'll use the Dispose pattern/using statement to control scarce resources.

Answer 3

It should be cleared on the next GC, but to be precise, in C# you can close the connection on finally block of the structured exception handling.

try {
  // open DB connection
} catch (Exception ex) {
  // deal with exception
} finally {
  // close and dispose connection
}

Answer 4

The general POSIX behaviour is that, when the program terminates, all file handles, network connections and so on are closed. Whether the other end does the right thing at that point is an open question, but if you're using any reasonably popular RDBMS, it will be fine.

Answer 5

In C#, implicit cleanup is done by the garbage collector if a finalizer is implemented in the object being garbage collected. Any cleanup of unmanaged resources, such as database connections, can be done in the Dispose method.

See this article for more information:

Implementing Finalize and Dispose to Clean Up Unmanaged Resources
http://msdn.microsoft.com/en-us/library/b1yfkh5e(VS.71).aspx

Answer 6

In C#, you will normally close connections immediately after using them, e.g.:

using(SqlConnection connection = ...)
{
   ... do something ...
} // connection disposed / closed here

However typically you will be using a connection pool, and all this does is returns the connection to the pool. So your process will still have active connections to the database server.

If you shut down cleanly, the connection pool will no doubt be cleaned up, and the actual connections to the database will be closed (you can verify this by looking at the open connections on the database server).

But there are situations (e.g. calling Environment.FailFast) where the application may crash without closing down the connections - in this case they'll eventually time out and be closed by the database server.

Answer 7

When a process terminates, all the file descriptors it has open should be released by the operating system. File descriptors include files and sockets, which will generally cover your database connections.

All that tells you is that when your client terminates its connections are closed. It does not tell you what the server does. Depending on how it is written, it is entirely possible that the server will continue to hold its connections open, expecting messages from the client that will never arrive, or even attempting to send data. These will probably timeout eventually, but this might not be well-planned. (It should be, for a decent RDBMS, but it might not be.) So depending on your RDBMS, you might need to take some steps to tell the server you are going down in order to tell it to release its resources.

Answer 8

If you are working with SQL Server you can have a look in sysprocesses or run sp_who2. I tested this on my machine and connections do get closed, i.e:

Console.Write("Opening connection");
Console.ReadLine();
SqlConnection connection = new SqlConnection(@"Data Source=.\SQLEXPRESS;Initial Catalog=SeniorMail;Integrated Security=SSPI;");
connection.Open();
SqlCommand command = new SqlCommand("SELECT count(*) from Account", connection);
Console.Write("Running sql");
Console.ReadLine();
int? count = command.ExecuteScalar() as int?;
Console.Write("Now I'll throw an exception");
Console.ReadLine();
int a = 0, b = 1, c = 0;

try
{
    c = b / a;
}
catch
{
    Environment.Exit(1);
}

I've checked sp_who2 either side of "Now I'll throw an exception", and I can see the connection has disappeared after the app exits.