Problem calling SqlConnection.Open() on a Timer callback

[Skip Key]

While writing a windows service, I've run into a fairly strange problem. I
have a worker proc that gets fired every n seconds using
System.Threading.Timer. This works fine, unless I open an SqlConnection on
it. Just as soon as I do so, the proc fires that time, and never again.
The connection is valid, I can do SQL commands on in with no problems, but
my timer goes away.


Here's a fairly simple console sample that shows the problem. Basically, it
fires up a timer callback every 5 seconds, so you can see it's being called,
then when you hit a keystroke it will call SqlConnect.Open() and
SqlConnect.Close() exactly once.

Ideas, anyone?

using System;
using System.Threading;
using System.Data.SqlClient;

namespace ThreadPoolTest
{
public class Startup
{
public static int nOpen = 0;

public static void Main()
{
StartupThreads();

Console.WriteLine( "Press Enter to open SQL connection next
time" );
Console.ReadLine();

Interlocked.Increment( ref nOpen );

Console.WriteLine( "Press Enter to Exit" );
Console.ReadLine();
}

public static void StartupThreads()
{
TimerCallback tc = new TimerCallback( DBFileTimerProc );
Timer t = new Timer( tc, null, 5000, 5000 );
}

static void DBFileTimerProc( object state )
{
Console.WriteLine( "In TimerProc" );
ThreadPool.QueueUserWorkItem( new WaitCallback( DBFileWorkProc ) );
}

static void DBFileWorkProc( object state )
{
Console.WriteLine( "In WorkProc" );
if (nOpen > 0)
{
string strConnect = "Persist Security Info=False;Integrated
Security=SSPI;database=Northwind;server=localhost";

using (SqlConnection conn = new SqlConnection( strConnect ))
{
Console.WriteLine("Opening connection" );
conn.Open();
conn.Close();
Console.WriteLine("connection closed" );
}
}
}
}
}

 

[Richard Blewett [DevelopMentor]]

Firstly, thankyou for the fantastic sample that allowed me to work on this.

OK, the problem is in the StartUpThreads method. Here you create the timer, but you assign it to a local variable. The problem is the timer is getting garbage collected. If you move it out to a member variable everything works fine.

So why did it work without opening the connection? because opening the connection causes a whole slew of allocation and this causes a GC, without it a GC just wasn't being triggered.

Regards

Richard Blewett - DevelopMentor
http://www.dotnetconsult.co.uk/weblog
http://www.dotnetconsult.co.uk

While writing a windows service, I've run into a fairly strange problem. I
have a worker proc that gets fired every n seconds using
System.Threading.Timer. This works fine, unless I open an SqlConnection on
it. Just as soon as I do so, the proc fires that time, and never again.
The connection is valid, I can do SQL commands on in with no problems, but
my timer goes away.


Here's a fairly simple console sample that shows the problem. Basically, it
fires up a timer callback every 5 seconds, so you can see it's being called,
then when you hit a keystroke it will call SqlConnect.Open() and
SqlConnect.Close() exactly once.

Ideas, anyone?