check database connection/open

[martin1]

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=server 1; database=db1;us er
id=user1;passwo rd=whatever")
Dim objDataAdapter As New SqlDataAdapter( )
Dim objDataSet

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

objDataAdapter. SelectCommand.C ommandText = cmdtext
objDataAdapter. SelectCommand.C ommandType = CommandType.Tex t

objConnection.O pen()
objDataAdapter. Fill(objDataSet , "table1")
objConnection.C lose()

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.O pen()? if it is, how to check? Thanks

[zacks@construction-imaging.com]

Re: check database connection/open


martin1 wrote:

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=server 1; database=db1;us er
id=user1;passwo rd=whatever")
Dim objDataAdapter As New SqlDataAdapter( )
Dim objDataSet
>
objDataAdapter. SelectCommand = New SqlCommand()
objDataAdapter. SelectCommand.C onnection = objConnection
cmdtext = "Select parameter from table1"
>
objDataAdapter. SelectCommand.C ommandText = cmdtext
objDataAdapter. SelectCommand.C ommandType = CommandType.Tex t
>
objConnection.O pen()
objDataAdapter. Fill(objDataSet , "table1")
objConnection.C lose()
>
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.O pen()? if it is, how to check? Thanks

Seems like all you need to do is put the .Fill method call inside of a
Try/Catch block.

[tommaso.gastaldi@uniroma1.it]

Re: check database connection/open

Connections have the "state" property, which you can check.

-tom

martin1 ha scritto:

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=server 1; database=db1;us er
id=user1;passwo rd=whatever")
Dim objDataAdapter As New SqlDataAdapter( )
Dim objDataSet
>
objDataAdapter. SelectCommand = New SqlCommand()
objDataAdapter. SelectCommand.C onnection = objConnection
cmdtext = "Select parameter from table1"
>
objDataAdapter. SelectCommand.C ommandText = cmdtext
objDataAdapter. SelectCommand.C ommandType = CommandType.Tex t
>
objConnection.O pen()
objDataAdapter. Fill(objDataSet , "table1")
objConnection.C lose()
>
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.O pen()? if it is, how to check? Thanks

[martin1]

Re: check database connection/open

Thank you. Tom

it works

"tommaso.gastal di@uniroma1.it" wrote:

Connections have the "state" property, which you can check.
>
-tom
>
martin1 ha scritto:
>

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=server 1; database=db1;us er
id=user1;passwo rd=whatever")
Dim objDataAdapter As New SqlDataAdapter( )
Dim objDataSet

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

objDataAdapter. SelectCommand.C ommandText = cmdtext
objDataAdapter. SelectCommand.C ommandType = CommandType.Tex t

objConnection.O pen()
objDataAdapter. Fill(objDataSet , "table1")
objConnection.C lose()

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.O pen()? if it is, how to check? Thanks

>
>

[Cor Ligthert [MVP]]

Re: check database connection/open

Martin,

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

Cor

"martin1" <martin1@discus sions.microsoft .comschreef in bericht
news:0F21B492-45DF-4FDC-AC3A-5F323E793D5B@mi crosoft.com...

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=server 1; database=db1;us er
id=user1;passwo rd=whatever")
Dim objDataAdapter As New SqlDataAdapter( )
Dim objDataSet
>
objDataAdapter. SelectCommand = New SqlCommand()
objDataAdapter. SelectCommand.C onnection = objConnection
cmdtext = "Select parameter from table1"
>
objDataAdapter. SelectCommand.C ommandText = cmdtext
objDataAdapter. SelectCommand.C ommandType = CommandType.Tex t
>
objConnection.O pen()
objDataAdapter. Fill(objDataSet , "table1")
objConnection.C lose()
>
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.O pen()? if it is, how to check? Thanks
>
>
>
>
>
>

[GhostInAK]

Re: check database connection/open

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.Stat e 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

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=server 1; database=db1;us er
id=user1;passwo rd=whatever")
Dim objDataAdapter As New SqlDataAdapter( )
Dim objDataSet
objDataAdapter. SelectCommand = New SqlCommand()
objDataAdapter. SelectCommand.C onnection = objConnection cmdtext =
"Select parameter from table1"
>
objDataAdapter. SelectCommand.C ommandText = cmdtext
objDataAdapter. SelectCommand.C ommandType = CommandType.Tex t
>
objConnection.O pen()
objDataAdapter. Fill(objDataSet , "table1")
objConnection.C lose()
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.O pen()? if it is, how to check? Thanks
>

[Cor Ligthert [MVP]]

Re: check database connection/open

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.
>

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

[GhostInAK]

Re: check database connection/open

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

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.
>>

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
>

[tommaso.gastaldi@uniroma1.it]

Re: check database connection/open

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
"OpenConnection IfNecessary" (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:

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
>

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.
>

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

[Cor Ligthert [MVP]]

Re: check database connection/open

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

"GhostInAK" <ghostinak@gmai l.comschreef in bericht
news:c71747b429 1e68c87997223d6 ae4@news.micros oft.com...

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
>

>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.
>>>

>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
>>

>
>

[GhostInAK]

Re: check database connection/open

Hello Cor Ligthert [MVP],

You are most likely correct in that MS will not change the behaviour of the
DataAdapter.Fil l 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

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
>
"GhostInAK" <ghostinak@gmai l.comschreef in bericht
news:c71747b429 1e68c87997223d6 ae4@news.micros oft.com...
>

>Hello Cor Ligthert [MVP],
>>
>Well, when MS changes the way the .Fill method works so it doesn't
>automaticall y open the connection.. you al will be cryin and I'll be
>sittin in Maui sippin Rum n Coke.
>>
>-Boo
>>

>>GhostInAk
>>>
>>>3. You should always call connection.Open and .Close even if the
>>>DataAdapte r 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
>>>functionalit y for you.
>>>>
>>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
>>>

[Cor Ligthert [MVP]]

Re: check database connection/open

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


"GhostInAK" <ghostinak@gmai l.comschreef in bericht
news:be1391bffa 8e8c87a1b7eb3f9 a6@news.microso ft.com...

Hello Cor Ligthert [MVP],
>
You are most likely correct in that MS will not change the behaviour of
the DataAdapter.Fil l 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
>

>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
>>
>"GhostInAK" <ghostinak@gmai l.comschreef in bericht
>news:c71747b42 91e68c87997223d 6ae4@news.micro soft.com...
>>

>>Hello Cor Ligthert [MVP],
>>>
>>Well, when MS changes the way the .Fill method works so it doesn't
>>automatical ly open the connection.. you al will be cryin and I'll be
>>sittin in Maui sippin Rum n Coke.
>>>
>>-Boo
>>>
>>>GhostInAk
>>>>
>>>>3. You should always call connection.Open and .Close even if the
>>>>DataAdapt er 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
>>>>functionali ty for you.
>>>>>
>>>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
>>>>

>
>

[martin1]

Re: check database connection/open

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?


"Cor Ligthert [MVP]" wrote:

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
>
>
"GhostInAK" <ghostinak@gmai l.comschreef in bericht
news:be1391bffa 8e8c87a1b7eb3f9 a6@news.microso ft.com...

Hello Cor Ligthert [MVP],

You are most likely correct in that MS will not change the behaviour of
the DataAdapter.Fil l 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

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
>
"GhostInAK" <ghostinak@gmai l.comschreef in bericht
news:c71747b429 1e68c87997223d6 ae4@news.micros oft.com...
>
>Hello Cor Ligthert [MVP],
>>
>Well, when MS changes the way the .Fill method works so it doesn't
>automaticall y open the connection.. you al will be cryin and I'll be
>sittin in Maui sippin Rum n Coke.
>>
>-Boo
>>
>>GhostInAk
>>>
>>>3. You should always call connection.Open and .Close even if the
>>>DataAdapte r 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
>>>functionalit y for you.
>>>>
>>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
>>>

>
>
>

[Cor Ligthert [MVP]]

Re: check database connection/open

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


"martin1" <martin1@discus sions.microsoft .comschreef in bericht
news:93680815-206E-4FE1-923B-E798975226CF@mi crosoft.com...

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?
>
>
"Cor Ligthert [MVP]" wrote:
>

>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
>>
>>
>"GhostInAK" <ghostinak@gmai l.comschreef in bericht
>news:be1391bff a8e8c87a1b7eb3f 9a6@news.micros oft.com...

Hello Cor Ligthert [MVP],
>
You are most likely correct in that MS will not change the behaviour of
the DataAdapter.Fil l 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
>
>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
>>
>"GhostInAK" <ghostinak@gmai l.comschreef in bericht
>news:c71747b42 91e68c87997223d 6ae4@news.micro soft.com...
>>
>>Hello Cor Ligthert [MVP],
>>>
>>Well, when MS changes the way the .Fill method works so it doesn't
>>automatical ly open the connection.. you al will be cryin and I'll be
>>sittin in Maui sippin Rum n Coke.
>>>
>>-Boo
>>>
>>>GhostInAk
>>>>
>>>>3. You should always call connection.Open and .Close even if the
>>>>DataAdapt er 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
>>>>functionali ty for you.
>>>>>
>>>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
>>>>
>
>

>>
>>
>>

[Cor Ligthert [MVP]]

Re: check database connection/open

doh,

Sometimes my fingers are typing something else than I mean.

Read for Framework 2.0 -generated datasources in winform VS 2005

Cor

"Cor Ligthert [MVP]" <notmyfirstname @planet.nlschre ef in bericht
news:eaXQbmNrGH A.3908@TK2MSFTN GP05.phx.gbl...

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
>
>
"martin1" <martin1@discus sions.microsoft .comschreef in bericht
news:93680815-206E-4FE1-923B-E798975226CF@mi crosoft.com...

>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.ope n() 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?
>>
>>
>"Cor Ligthert [MVP]" wrote:
>>

>>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
>>>
>>>
>>"GhostInAK" <ghostinak@gmai l.comschreef in bericht
>>news:be1391bf fa8e8c87a1b7eb3 f9a6@news.micro soft.com...
>Hello Cor Ligthert [MVP],
>>
>You are most likely correct in that MS will not change the behaviour
>of
>the DataAdapter.Fil l 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
>>
>>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
>>>
>>"GhostInAK" <ghostinak@gmai l.comschreef in bericht
>>news:c71747b4 291e68c87997223 d6ae4@news.micr osoft.com...
>>>
>>>Hello Cor Ligthert [MVP],
>>>>
>>>Well, when MS changes the way the .Fill method works so it doesn't
>>>automaticall y open the connection.. you al will be cryin and I'll be
>>>sittin in Maui sippin Rum n Coke.
>>>>
>>>-Boo
>>>>
>>>>GhostInAk
>>>>>
>>>>>3. You should always call connection.Open and .Close even if the
>>>>>DataAdapte r 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
>>>>>functional ity for you.
>>>>>>
>>>>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
>>>>>
>>
>>
>>>
>>>
>>>

>
>