Connection state open even without a server or database?

  • Thread starter Thread starter Earl
  • Start date Start date
E

Earl

I test for a connection when I load a particular form, so as to prevent
other more ghastly exceptions further into the logic. However, I've noticed
that even with NO viable connection string, the connection object state
shows open. Anyone see something I'm overlooking here?

Here is the code:

Dim strSQLServer As New SqlConnection(strConn)
strSQLServer.Open()
Debug.WriteLine(strConn.ToString())
Debug.WriteLine(strSQLServer.State.ToString())

Here is the output:

data source=;initial catalog=;integrated security=SSPI;
Open
 
Well, you've called the open method of the connection, haven't you?

As long as the connection string you've passed to the connection's
constructor is a string (any string), the connection object will go into an
open state if you call the open method. Where you would most certainly
encounter exceptions though is when attempting to use the connection (a
command.executeNonQuery for example).

All of your ADO .NET code should be inside of Try...Catch blocks to catch
any of the ghastly-ness you refer to.
 
I was not aware that the connection object could "go into the open state"
with an invalid connection string. All of the ADO.Net code is already well
protected by Try-Catch, but I just don't want the user seeing an exception
like "xxx stored procedure not found..." When I open the main form, I launch
another form showing a dashboard of status information, and what I'd like to
do is make a check in the main form that the assigned server and database
are available before trying to load any further information. I'm still
looking for a cleaner method of doing this than just throwing an exception
the first time I *really* need the connection to the database.
 
I hear you, but it is quite common to deal with situations like yours by
putting your command actions in try...catches. And, with the ability to set
up multiple catches for different exceptions and different conditions on
exceptions, there is no reason why you couldn't have a single label on the
top of your form that starts out with no text and if, and only if, an
exception is found a *customized* message (not the exception's message could
be placed in that label. The user will see more generic messages and your
code will have the ability to react however you want it to.
 
Here's a good anaology to explain the various steps of connecting and doing
something to data in ADO.NET....

Declaring, instantiating and passing the connection string are like setting
up a phone number on speed dial. If you do it wrong, would you know about
it at this point? No, because you haven't attempted to *dial* the number
yet, just programmed it in.

Opening the connection is like picking up the phone prior to dialing.
Again, if you've done something wrong already, you wouldn't know it yet,
because you still haven't tried dialing the number. Opening the connection
gets you your "dial tone".

Performing a C.R.U.D. (create, read, update, delete) method via a command
object is like actually dialing the number. If you've improperly programmed
in the number into speed dial, you'll know about it here. Or, even if you
have put the number in correctly, you may ask a bad question (via your
CommandText) like "May I please speak to sirloin steak?", which doesn't make
sense and you'll get an error there as well.

Hope this helps.

-Scott
 
That's a good analogy, altho I had always thought that "picking up the
phone", thus "checking for a dial tone" would throw an exception.

One idea I had thought of was to set the string to Nothing if the server
and/or database settings did not return a string (I pull these out of an XML
file, allowing the user to point the connection wherever they like).
 
Earl said:
That's a good analogy, altho I had always thought that "picking up the
phone", thus "checking for a dial tone" would throw an exception.
Click to expand...

But, as you found out, you'll always get a dial tone. Even if the "number"
you want to call is "out of order".
One idea I had thought of was to set the string to Nothing if the server
and/or database settings did not return a string (I pull these out of an
XML file, allowing the user to point the connection wherever they like).
Click to expand...

That would just mean you get an exception a little earlier. But really, why
bother? If you are going to get an exception when you invoke your command,
why not just catch any and all exceptions there?
 
Back
Top