check database connection/open

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

All, I want to check wether db connection/open or not, if not it will post db
connection error to user in message box

The code is like:

Dim objConnection As New SqlConnection _
("Server=server1; database=db1;user
id=user1;password=whatever")
Dim objDataAdapter As New SqlDataAdapter()
Dim objDataSet

objDataAdapter.SelectCommand = New SqlCommand()
objDataAdapter.SelectCommand.Connection = objConnection
cmdtext = "Select parameter from table1"

objDataAdapter.SelectCommand.CommandText = cmdtext
objDataAdapter.SelectCommand.CommandType = CommandType.Text

objConnection.Open()
objDataAdapter.Fill(objDataSet, "table1")
objConnection.Close()

So if connection problem, it will post error masseage like below

MessageBox.Show("Ths is Database connenction issue", "DataBase Connection
problem")

My question is which code line is used to check if db connection/open or
not, it is objConnection.Open()? if it is, how to check? Thanks
 
martin1 said:
All, I want to check wether db connection/open or not, if not it will post db
connection error to user in message box

The code is like:

Dim objConnection As New SqlConnection _
("Server=server1; database=db1;user
id=user1;password=whatever")
Dim objDataAdapter As New SqlDataAdapter()
Dim objDataSet

objDataAdapter.SelectCommand = New SqlCommand()
objDataAdapter.SelectCommand.Connection = objConnection
cmdtext = "Select parameter from table1"

objDataAdapter.SelectCommand.CommandText = cmdtext
objDataAdapter.SelectCommand.CommandType = CommandType.Text

objConnection.Open()
objDataAdapter.Fill(objDataSet, "table1")
objConnection.Close()

So if connection problem, it will post error masseage like below

MessageBox.Show("Ths is Database connenction issue", "DataBase Connection
problem")

My question is which code line is used to check if db connection/open or
not, it is objConnection.Open()? if it is, how to check? Thanks
Click to expand...

Seems like all you need to do is put the .Fill method call inside of a
Try/Catch block.
 
Connections have the "state" property, which you can check.

-tom

martin1 ha scritto:
 
Martin,

In this construction you don't need to open and close the connection. The
dataadapter does that intrensic.

Cor
 
Hello martin1,

Three points:
1. The user doesn't give a flying rats ass whether or not your connection
is already open or not. So there's no point in telling them.

2. The connection.State property will tell you about the connections current
state (imagine that). So in accordance with point #1.. check the state and
if it's doing something naughty.. then correct it and move on. You should
only bother the user if the world is coming to an end.

3. You should always call connection.Open and .Close even if the DataAdapter
does this work for you. It mokes the code more readable and you are less
likely to forget when you use other methods for reading the data which do
not perform this functionality for you.

-Boo
 
GhostInAk
3. You should always call connection.Open and .Close even if the
DataAdapter does this work for you. It mokes the code more readable and
you are less likely to forget when you use other methods for reading the
data which do not perform this functionality for you.
Click to expand...
I assume that this is a very personal opinion from you. I find this a
beginners approach, that should be avoided as soon as possible.

Cor
 
Hello Cor Ligthert [MVP],

Well, when MS changes the way the .Fill method works so it doesn't automatically
open the connection.. you al will be cryin and I'll be sittin in Maui sippin
Rum n Coke.

-Boo
 
Hi GhostInAK

but... if we wanted to foresee all the things that ms might
change in the future, our programs would definitely grow without
limits and eventually get stack :)

I do use in several occasion a boolean function a call
"OpenConnectionIfNecessary" (opening an open connection
would rise error). But I apply it to cases where it is expected
the connection to be open (not here for instance).

Anyway, the idea of Rum n Coke at Maui it's not bad :)

-t

GhostInAK ha scritto:
 
GhostInAk,

See the answer from Thomasso, Microsoft will never create these breaking
changes if they know this, and this is an obvious one.

However, but let us take the not imaginable situation as well, that
Microsoft would stop checking in the dataadapter if the connection is
already open. Who is than sittin in Maui sipping Rum n Coke. Definitely not
you.

:-)

Cor
 
Hello Cor Ligthert [MVP],

You are most likely correct in that MS will not change the behaviour of the
DataAdapter.Fill method. However.. Seeing code that does something with
a connection whose state you are not in direct control of at design time
leaves one to guess at state. Whereas if instead you implicitly set the
state (both open and close) before doing something with the connection, the
guessing (at least as far as connection state is concerned) is eliminated.
It's just good technique to make sure you are in complete control of your
environment.

-Boo
 
Boo,

In my opinion can there be in a good written program be not more guessing
than that

dim C as integer = 1 + 1' C will be 2.

It is not something as this

dim c as string = 1 + 1 'while some users thinks that this will give 11
while it will give used in this way still forever be 2.

The dataadapter has a very fine mechanisme that opens and closes if that is
not done implicitly.

But just my opinion.

Cor
 
Cor and Boo ... ,

Thank you, this is very good discussion.

I tried to check connection property, if it is not 1 ( not sure) which means
connection failed, then close application. I use try-catch and put
connection.open() in the try block and if conenction failed and close app
using me.close in catch block, but the app window did Not colse and still
point to someline in debug mode. any more advice?
 
Martin,

In my idea should your code so be written that you don't have to check if a
connection is open.

Very thight opening and closing the connection will help for that (as long
as it is not Jet).

You can even do
Using myconnection
do whatever you need with that
End using
(Be aware that this dispose the connection what is in some framework 2.0
situation not always wanted.)

Or you can do
Try
Conn.open
Try
do what you want
Catch execptions
'what is an handling error
end Try
Catch execptions
'what is an connection error
Finally
Conn.close
End try

I hope this helps,

Cor
 
doh,

Sometimes my fingers are typing something else than I mean.

Read for Framework 2.0 -> generated datasources in winform VS 2005

Cor
 
Back
Top