Working with SQL Connections

[ianbhenderson73]

Hi

This might sound like a really simple question, but I really hope someone out there can help me with something I can't seem to get my head around.

I'm in the process of writing a Windows Service. When the service starts, it logs that fact in a database table and then goes on to generate an email advising that the service has been started. So far, so good.

Once that step has been completed, the Service makes a series of calls to a configuration table in the back-end database which contains various settings that can be used to control the behaviour of the service. My issue at the moment is that I've been testing the service quite happily in my development environment, but when I roll it out to what will eventually become the production server, I'm getting a number of errors, one of which "ExecuteReader requires an open and available Connection. The connection's current state is connecting." suggests that the service is unable to retrieve one (or more) of the configuration settings because the connection hasn't opened quickly enough.

The code that I'm trying to use is this:

Dim sReturn As String = ""

'Open the connection if necessary
If cGlbDB.cmd.Connection.State = ConnectionState.Closed Then cGlbDB.cmd.Connection.Open()

'Execute the query and return the values
cGlbDB.reader = cGlbDB.cmd.ExecuteReader

While cGlbDB.reader.Read
    If Len(sReturn) > 0 Then sReturn &= ";"
    sReturn &= LTrim(RTrim(cGlbDB.reader("ConfigValue")))
End While

Return sReturn

For brevity, I haven't included the Try..Catch block which starts before the comment and finishes after the Return statement. My problem is this: If the code that's trying to Open the Connection isn't able to do so instantly, this means that the Connection is (presumably) sitting in a state of "Connecting...". Is there a graceful way of coding this so that the application waits until the Connection is fully open before proceeding with the ExecuteReader etc.? The only way I can think of to do it is with a Do..Loop Until clause, with nothing in the actual Do section, but I wondered if there was a nicer way of doing it.

TIA

[Shaggy Hiker]

Remove that do...loop idea from your head as the first step. That's a busy wait, or spin wait, and it's bad news. It works, it just has a serious cost in power consumption, because the CPU will be totally pegged while the loop is running.

One thing that comes to mind is: Do you know about connection pooling? You may or may not have encountered that. With modern .NET, when you close a connection, it doesn't fully close, but hangs around in a connection pool in case you re-establish the connection. This keeps you from paying the full cost of a connection each time. So, given that, do you think it is likely that you are creating a brand new connection here, or drawing one from the pool? If this is the first connection to that DB, then it would be a brand new connection, but if you are connecting over and over, it would be a pooled connection. If it was likely to be a connection drawn from the pool, I'd be surprised that you are having any issues. I always create my connections on the fly using something like this:

Using cn As New Connection(myConnectionString)
 Using cmd As Command = cn.CreateCommand
  Try
    'Do stuff
  Catch
  
  End Try
 End Using
End Using

So, for every connection, I'm creating it right then. Generally, the programs are running on local machines, but not always. Sometimes the connection is to a SQL Server across a LAN. I've never had an issue with what you are seeing, which makes me think that perhaps this is the first use of a connection to that DB for the program, and that first use is creating a totally new connection, which may be taking longer. If that seems likely, you might just try adding a Thread.Sleep(100) after you open the connection. I have a program that connects across a LAN. That first connection isn't always fast enough, so I added a Thread.Sleep(5000), which is excessive, but this all happened on application startup which already took 20-30 seconds, so another 5 didn't matter. That problem appeared to be about the SQL Server listener not starting up until the first request, but that seems unlikely to be an issue if you are using terms like "production server". For that one, the connection would timeout, in which case I'd sleep for 5s, then connect again. That always worked, and likely would have worked with a shorter sleep time. If your DB doesn't have that spin up time (I only occasionally did), then that may not be the issue.

The other thought, though, is that there's something going wrong with the connection. After all, aside from that randomly occurring spin up issue, I have never run into any delay in creating a connection using code as shown above, regardless of whether the DB was located local or across a LAN. So, the fact that you are encountering some kind of lag suggests that the issue may not be solved with a simple pause.

You COULD test whether or not a pause would work with a spin wait, as you suggested, but this should ONLY be done for testing. It might show you how long a pause is needed (use a Stopwatch object to time how long the loop runs), but it might also show you that the connection eventually just fails.

[ianbhenderson73]

I tried your suggestion, but couldn't get it working successfully in a production environment. I have however managed to come up with a hack which (whilst not being in the slightest bit graceful) seems to do the trick.

Now when the service is started, in between generating out an email advising that the service has been started, and starting the main Timer object, I'm declaring a series of variables to hold the various configuration details that I'm interested in. I've tested this on both my development machine, and the live environment and am happy to say that it works.

[Shaggy Hiker]

What was my suggestion that you tried?

The solution sounds like an old standard practice for games: Getting items from memory is fast, so the more you can pre-compute ahead of time, the better the performance will be once the important works gets going. I mentioned that I was willing to re-try a connection after a 5 second pause because my application took so long to start. That time is spent doing pretty much what you describe. It's calculating, setting up, creating objects, instantiating things, and so forth. So, I wouldn't call that a hack in any way, if that's what you're doing.

The one thing that I would be concerned about, in your case, is the time it is taking to open the connection, unless you feel that there's a good reason for that. Like I mentioned, I've never had to worry about that whether connecting over a LAN or connecting local. The delay I had to deal with was caused by the SQL Server listener starting up. After that, all my connections were effectively instantaneous.