does my code work efficiently? & Q about New()?

[tradnbillies]

Is there a difference between:

Dim myCommand As SqlCommand = New SqlCommand("myStoredProc", myConnection)
Dim myCommand As New SqlCommand("myStoredProc", myConnection)

Is there anything wrong with this code:

Dim dbc As New dbConnect
Function doSomething(var As String) As String
   dbc.ConnectIt()
   Dim myCommand As New SqlCommand("myStoredProc", dbc.myConnection)
   Try
      myCommand.CommandType = CommandType.StoredProcedure
      [SIZE=2]myCommand.Parameters.Add("@user", SqlDbType.VarChar).Value = var
[/SIZE]    doSomething = myCommand.ExecuteScalar()
   Catch ex As Exception
      'myErrorHandlingCode
   Finally
      dbc.DisconnectIt()
      myCommand.Dispose()
   End Try
End Function

[SIZE=2][COLOR=black]'[/COLOR][/SIZE][SIZE=2][COLOR=black]dbConnect class[/COLOR]
[/SIZE][COLOR=black][SIZE=2]Public [/SIZE][SIZE=2]Shared[/SIZE][SIZE=2] myConnection [/SIZE][SIZE=2]As [/SIZE][SIZE=2]New[/SIZE][/COLOR][SIZE=2][COLOR=black] SqlConnection(ConfigurationSettings.AppSettings("connectionString"))[/COLOR]
[/SIZE][COLOR=black][SIZE=2]Public [/SIZE][SIZE=2]Shared [/SIZE][SIZE=2]Sub[/SIZE][/COLOR][SIZE=2][COLOR=black] ConnectIt()[/COLOR]
[/SIZE][COLOR=black][SIZE=2] myConnection = [/SIZE][SIZE=2]New[/SIZE][/COLOR][SIZE=2][COLOR=black] SqlConnection(ConfigurationSettings.AppSettings("connectionString"))[/COLOR]
[/SIZE][COLOR=black] [SIZE=2]If[/SIZE][SIZE=2] myConnection.State = ConnectionState.Closed [/SIZE][/COLOR][SIZE=2][COLOR=black]Then[/COLOR]
[/SIZE][SIZE=2][COLOR=black]    myConnection.Open()[/COLOR]
[/SIZE][SIZE=2][COLOR=black] End [/COLOR][/SIZE][SIZE=2][COLOR=black]If[/COLOR]
[/SIZE][COLOR=black][SIZE=2]End [/SIZE][SIZE=2]Sub[/SIZE][/COLOR]
[SIZE=2][COLOR=#0000ff][COLOR=black][SIZE=2]Public [/SIZE][SIZE=2]Shared [/SIZE][SIZE=2]Sub[/SIZE][/COLOR][SIZE=2][COLOR=black] DisconnectIt()[/COLOR]
[/SIZE][COLOR=black][SIZE=2] If[/SIZE][SIZE=2] myConnection.State = ConnectionState.Open [/SIZE][/COLOR][SIZE=2][COLOR=black]Then[/COLOR]
[/SIZE][SIZE=2][COLOR=black]    myConnection.Close()[/COLOR]
[/SIZE][COLOR=black][SIZE=2] End [/SIZE][SIZE=2]If[/SIZE][/COLOR]
[SIZE=2][COLOR=#0000ff][COLOR=black]End Sub[/COLOR]
[/COLOR][/SIZE][/COLOR][/SIZE]

So I basically have two questions. I know I should be aware of the difference between "Dim a As A = New A()" and "Dim a As New A()" if there is one, but I am sort of self taught, and am questioning whether there is a difference there. Secondly, based on the code provided here, is there anyway I would have a BUNCH of open SQL Connections that are not closing on the SQL Server? This is the problem I am running into with my code, and I cannot figure out why. I can provide more details if possible, but please help me if you can.

Thanks,
Dave

 

[RTaulbee]

Question One: I'd say no difference there.

Question Two: I can't remember correctly...does a "finally" execute whether there is an exception or not? In other words, does the code in the finally clause always fire, exception or not? Or does it only fire if there is no exception?

 

[cjard]

code in Finally blocks always runs, even if you say Exit Sub in the middle of the try.

Sub
  Try
    MsgBox "point 1"
    If something Then Exit Sub
    MsgBox "point 2"
  Catch 
  Finally
    MsgBox "point 3"
  End Try
  MsgBox "point 4"
End Sub

if something is false, we see every message box
if something is true, we see point 1 and 3

 

[cjard]

Is there a difference between:


So I basically have two questions. I know I should be aware of the difference between "Dim a As A = New A()" and "Dim a As New A()" if there is one,

there isnt. in C# and java we'd say

String s;
String s = new String();

the first one declares but doesnt initialise
the second one declares and inits

in vb we can (to declare, declare+init, declare+init respectively)

Dim s As String
Dim s As String = New String()
Dim s As New String

the third option is possibly to be avoided if only for human confusion reasons.. when you put the two statements together they are very similar:

Dim s As String
Dim s As New String

only one of these cases would let you say s.Length, the other case would crash with a null pointer exception (null reference)

i've answered a few questions from guys at work who havent understood this, and it did take me a while to spot that they hadnt made a new string.. so i'd say pick the longer option to make your code more readable

but I am sort of self taught, and am questioning whether there is a difference there. Secondly, based on the code provided here, is there anyway I would have a BUNCH of open SQL Connections that are not closing on the SQL Server? This is the problem I am running into with my code, and I cannot figure out why. I can provide more details if possible, but please help me if you can.

Thanks,
Dave

There is no way you CANNOT have a bunch of connections open, because the driver manager transparently performs connection pooling unless you set Pooling=False in the connection string. Doing so is usually a bad idea. I wouldnt call Dispose() on your connections though, because that may foul the pooling mechanism by making a connection unusable

See, what happens when you connect to a database, your computer makes a few connections, say 2 or 3.
When you say connection.Open (the second and subsequent times) your code receives an already-open connection that is good to use. It's fast because it is already open and the computer doesnt have the slow procedure of forging the connection. When you say Close, the connection goes back to the pool and lingers around waiting to be used again. The pool manager (you dont see) is responsible for keeping the connections alive and ensuring you dont get handed a dead one.

Thus, you should always open your connection, use it, and close it again. Microsoft chose to make the pooling transparent and just tell developers to operate like this, but some people dont know that Open doesnt actually forge a new link unless there are no more in the pool to be had (how the pool manager manages the number of pooled connections is not our concern) so they open it once and keep it open for the life of the app. Maybe microsoft should have made the pooling more visible, but it's abstraction - hide what you dont need to know kind of thing..

So, dont dispose your connections.. i'd hope that the dispose method was overridden to do nothing, but it might still be marking the connection unusable for an arbitrarily long amount of time, until the GC clears it up.. I suggest dont use it, and just run your DB as microsoft say (open use close) and accept that your SQL server enterprise manager thingie will show several open connections, because they actually do exist, are being managed by the pool and arent of concern to the developer (unless youre exhausting connections on the server, in which case you may way to alter the pool size limit)

 

[tradnbillies]

Thanks to everyone who responded. It sounds like they are the same thing, but it would be safer to go with the longer option for different reasons.
I just want to respond to what my problem was and how I solved it...
I was still getting literally dozens of connections that were open, and eventually I would have a timeout. I understand the concept of the pooled connections, but I still felt like there were too many that I didn't need. Even without the .dispose() in my disconnectIt() procedure, I was still getting open connections. I ended up discontinuing the use of the DB class that I made, and did all the ADO.NET right in the code. This fixed the problems, and now I can only find a handful (3-5 maybe) of connections open on the server. I still couldnt exactly put my finger on WHY this happened, but that's what went on.

Thanks,

Dave

 

[RTaulbee]

May be a bit late here. Could you make a global connection that is open on app open and closes on app close? All commands are used with that open connection and you'd never have to open/close, open/close. I guess the idea is, 1 always open connection vs connections that open/close constantly. I'm not sure what the best practice is there, but just a thought.

 

[cjard]

i dont think so.. the TAs create their own connections, and i dont have any control over that.. nor should i, because having one conenction open for the life of the app breaks multithreading and pooling mechanisms. MS say to open and close all your connections as near to needing them as possible. Pooling is done transparently, so there is seldom any actual tcp conenction being made when you Open, nor one being broken when you Close

 

[RTaulbee]

Interesting. I always wondered what the 'best practice' is for connection opening/closing. Thanks for the info Cjard.

 

[cjard]

I think microsoft already decided it for you ...

What's microsoft's address?

Microsoft, One Microsoft Way, Redmond...


On a side note, i'm looking at the auto generated code for table adapters and it's looking like you can actually tell a tableadapter what connection to use.. and if you tell it rather than it getting it itself, then it doesnt close it when it's done. I wonder if this is how we arrange transactions.. hmmm