Different ways to make a connection to the database

Question

0.00/5 (No votes)

See more:

Hi,

C#

Dim conn As New SqlConnection(Acc.Common.Configuration.Instance.ConnectionString)

in vb.net while using the ado does and working with a connection, does it make any difference if for oppening a connection I use the code below:

VB

If conn.State = ConnectionState.Closed Then
conn.Open()
End If

or can I simply use the following code:

VB

conn.Open()

I guess that both ways it works, but in the logic does it make any different?

thanks,

What I have tried:

Both cases are working for me but I'm just asking which way is better.

Posted 20-Jun-16 23:15pm

m.r.m.40

Updated 20-Jun-16 23:32pm

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2016-06-20T23:32:00

Solution 1

Under normal circumstances, it shouldn't matter - because it's a very good idea to use a Using block around the creation of the instance anyway:

VB

Using con As New SqlConnection(Acc.Common.Configuration.Instance.ConnectionString)
	con.Open()
	Using com As New SqlCommand("SELECT iD, description FROM myTable", con)
                ...
	End Using
End Using

Because it ensures that the connection is always closed and Disposed when you are finished with it.
Because you create it, open it, and use it - then the system automatically closes and disposes of it there isn't any problem.
But...if the connection is open when you call Open on it, it will throw an InvalidOperationException

Posted 20-Jun-16 23:32pm

OriginalGriff

Comments

Maciej Los 21-Jun-16 5:51am

a5!

m.r.m.40 21-Jun-16 6:20am

Thanks for your explanation, but my question was about first checking if it is already open! if it is then nothing if not then open it.
Why should I do that?
When I can simply write conn.open()

OriginalGriff 21-Jun-16 6:32am

Read the last sentence again...

m.r.m.40 21-Jun-16 9:40am

Ow!
Sorry I was at work and pretty busy.
then, before calling Open on it, I must check if it is already open or not. right?

OriginalGriff 21-Jun-16 10:12am

Yes...but...they are scarce resources, so you shouldn't be "hanging on" to them - they should be created, opened, used, closed and disposed. If you hang on to them, you can cause worse problems than having it open when you try again - such as trying to create a DataReader on a connection that already has a DataReader open on it.
The easiest way to do that is with a Using block which removes it from scope so it can't be used when it's closed.
And if you do that, the question is moot, because it's only opened for a specific purpose and closed when it's been dealt with.

m.r.m.40 21-Jun-16 11:20am

Thanks a lot. that was a perfect explanation.

OriginalGriff 21-Jun-16 11:26am

You're welcome!