Terminating database connection with GC.Collect

Question

0.00/5 (No votes)

See more:

I have a program which connects a user to a database via an application role as follows.

VB

ConnectionString = "WW"
con1.ConnectionString = connectionString
con1.Open()
cmd.Connection = con1
Call GetPermissions()
cmd.CommandText = "sp_setapprole"
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.AddWithValue("@rolename", "XX")
cmd.Parameters.AddWithValue("@password", "XX")
cmd.ExecuteNonQuery()

Between 13:30 and 14:00 I want to terminate the connection to the database then display a messagebox telling the user the GUI is unavailable.
The reason is to allow housekeeping jobs to run on the database.
I do so using this code.

VB

con1.Close()
GC.Collect()
MessageBox.Show("Database housekeeping job in progress.  GUI unavailable")
End

However when looking at the activity monitor on the database it seems as though the connection is only terminated after the application ends. Alternatively it may take a few minutes for GC.collect to work.
Therefore at the time that the message box appears the connection is still active.
How can I make sure the connection has terminated before the messagebox appears.

[Modified: added the pre tags for code formatting...pre tags are your friend...]

Posted 2-Nov-10 5:50am

AlHal2

Updated 2-Nov-10 5:58am

William Winner

v2

Add a Solution

Comments

William Winner 2-Nov-10 11:59am

Not sure I can answer your question, but I'm curious as to what you think keeping the connection open does...that's not the standard way to handle database operations.

4 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Simon_Whale · Answer 1 · 2010-11-02T07:08:00

By default ADO.net Connection object uses connection pooling

"ADO.NET 2.0 introduced two new methods to clear the pool: ClearAllPools and ClearPool. ClearAllPools clears the connection pools for a given provider, and ClearPool clears the connection pool that is associated with a specific connection. If there are connections being used at the time of the call, they are marked appropriately. When they are closed, they are discarded instead of being returned to the pool."

taken from this article MSDN: SQL Server Connection Pooling ADO.NET[^]

Dave Kreskowiak · Answer 2 · 2010-11-03T17:18:00

Solution 4

You do not need to call GC.Collect at all. Doing so screws with the internal tuning of the GC and can actually degrade the performance of your app.

Posted 3-Nov-10 17:18pm

Dave Kreskowiak

voloda2 · Answer 3 · 2010-11-02T06:04:00

Solution 1

There is an internal connection pool which holds connections to database opened for some time. So look for a way how to reinitialize this pool (if possible).

Posted 2-Nov-10 6:04am

voloda2

AlHal2 · Answer 4 · 2010-11-03T04:40:00

Solution 3

Thanks. I set pooling=false in the config file.

Posted 3-Nov-10 4:40am