Using statement in VB.NET 2.0

That's not elegance - that's an abomination. ;)

Ugh, that looks awful.

While we're on the subject. Why do you even need to call Dispose() on a Command object?

I do it because the Command objects inherit from System.ComponentModel.Component which implements a Finalize() method and I don't want my Command objects sticking around in memory for an extra garbage collection.

But, my point is that the Command object isn't holding onto an "unmanaged resource" like the Connection object is. Furthermore, the Command object doesn't override the Dispose() method inherited from System.ComponentModel.Component which seems to reinforce that it's not necessary to "dispose of" a Command object.

I could also say the same thing about the Transaction objects. Although of some value, the Transaction objects usually override Component.Dispose() and add a call to Transaction.Rollback().

I suppose beauty is in the eye of the beholder :)

About whether command objects should have their dispose method called: you're right, there's no real reason to call dispose on either the command object or the data reader. I've just gotten into the habit of calling dispose on anything that implements IDisposable - because you certainly can't go wrong calling it, and you could very well go wrong not calling it.

Yup - I call Dispose on everything IDisposable too at the advice of Joe Duffy. This includes DataSet. Laugh if you want. :-)

I like the crossing out of "been forced too" - classic. Don Box did a presentation where he used semicolons with his VB code using the prefix ' - another classic. :-)

while(rdr.Read()) {
...
}

is missing

Oops - good point. I'll update the post - I guess I was trying to too terse :)

From a completely unrelated Google search I came across this post by Fritz Onion over on pluralsight...

No one uses stored procs anymore? I find no examples on sqlcommand/datareaders using input/output parameters.

People still use stored procedures! They just don't put samples of using them on the web ;-)

this is the good practice to not use the dispose for each and every object, leave it to the gorbage collector to collect it.

I think that the using statement is really awesome in C#, and I'm glad to see it finally made it into VB.Net. As far as the first two posts, basically saying that it's ugly, well, you're right, but only because this is VB we're talking about here. I believe the semantically equivalent code _without_ the Using statement goes something like this:

Dim conn As SqlConnection = New SqlConnection(dsn)
Try
Dim cmd As SqlCommand = New SqlCommand("SELECT * FROM Employees", conn)
Try
conn.Open()
rdr = cmd.ExecuteReader()
While rdr.Read()
Console.WriteLine(rdr(0))
End While
Finally
If Not cmd is Nothing Then
cmd.Dispose()
End If
End Try
Finally
If Not conn is Nothing Then
conn.Dispose()
End If
End Try

I don't know if you can really argue that this code is somehow _less ugly_ than the one using Using. To me, it's longer and more confusing to not use Using.

Forgive my lack of indentation; it was indented when I typed it. I don't know if the comments support indentation or what.

The symetry is only as bad as you make it.

Come on if you TAB over it will be more than ONE SPACE that you display on this page.

With that more space... symetry is not an issue.

This is all really cool, but what if you want to catch any exceptions with the using block? Then you still need your try catch, yes?

Thank God you can still go to C++ if you want a modern .net language with destructors, instead of this nonsense of having the human do the error-prone task of being careful to dispose of each item at the correct time.

I'm still baffled that they took such a step backwards with C# -- what were they thinking, that assembler is their goal? :)

Example of using stored proc. Note some abstraction was doen to reduce repetition.
Friend Overloads Shared Function FillDataSet(ByRef sql As String, _
ByRef ReturnCode As Integer, _
ByRef ErrorMessage As String) As DataSet
Using con = CreateConnection()
Using dcdCmd = CreateCommand(sql, con)
Using da = New SqlDataAdapter(dcdCmd)
Return FillDataSet(CType(da, SqlDataAdapter), ReturnCode, ErrorMessage)
End Using
End Using
End Using
End Function

Friend Overloads Shared Function FillDataSet(ByRef da As SqlDataAdapter, _
ByRef ReturnCode As Integer, _
ByRef ErrorMessage As String) As DataSet

Dim ds As New DataSet
Try
da.Fill(ds)
Catch ex As Exception
ProcessError(ex, ReturnCode, ErrorMessage)
End Try

Return ds
End Function
---------------------------------------------
Public Function GetSomething(ByRef ReturnCode As Integer, _
ByRef ErrorMessage As String) As DataSet
Return Database.FillDataSet("GetSomething", ReturnCode, ErrorMessage)
End Function

Public Function GetUser(ByVal UserId As String, ByRef ReturnCode As Integer, ByRef ErrorMessage As String) As DataSet
Using con = Database.CreateConnection()
Using dcdCmd = Database.CreateCommand("GetUser", con)
Using da = New SqlDataAdapter(dcdCmd)
With dcdCmd.Parameters
.AddWithValue("@UserId", UserId)
End With
Return Database.FillDataSet(CType(da, SqlDataAdapter), ReturnCode, ErrorMessage)
End Using
End Using
End Using
End Function

I have been reading about Using statement in many places but it is not mentioned how to catch the exceptions within Using block.

We need to know somehow and log it. Can anyone give me a hint of where to look at or how to dot it.
Thanks much.

Pingback from  Aktuelle ID Auslesen | hilpers

Pingback from  Sql close connection issue.... | keyongtech

Why does the example show the connection being opened inside of the command using block?

Probably because that's the typical way to do it in C# -- stack the usings and open inside the last scope.

Ok, no answers so far... but why open connection inside the command using block? Why not have multiple commands be able to access the same connection? Does not the connection close when the connection using block is ended?

Any help?  Thanks!

Sorry Foo, missed your comment previously - didn't show up until after I reloaded the page.

However the question still remains - doesn't the connection using block serve to close the connection? Why not re-use when possible?

Thanks!