• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 311
  • Last Modified:

Execute Reader Command CLoseConnection

In my code I've (always" had the command behavior in the execute reader.  Mainly because I guess I copied from somewhere at some point years ago.

My question is this...
Is it needed...and if so...is there a best practice on the Dispose() sections.

In other words...have I just gotten lazy and can I clean this up a little?
Dim objConn As SqlConnection = New SqlConnection(System.Configuration.ConfigurationManager.AppSettings("ConnPortal"))
        Dim oCom As New SqlCommand
            oCom.Connection = objConn
            oCom.CommandText = "sp_crmSS_transMasterUpdate"
            oCom.CommandType = CommandType.StoredProcedure
            oCom.Parameters.Add(New System.Data.SqlClient.SqlParameter("@transactionID", System.Data.SqlDbType.Int)).Value = CInt(_transactionID)
            oCom.Parameters.Add(New System.Data.SqlClient.SqlParameter("@tranAggregate", System.Data.SqlDbType.Money)).Value = CDbl(l_lead.tAggregate)
            oCom.Parameters.Add(New System.Data.SqlClient.SqlParameter("@loanDate", System.Data.SqlDbType.DateTime)).Value = l_lead.loanDate
            oCom.Parameters.Add(New System.Data.SqlClient.SqlParameter("@compoundingMethod", System.Data.SqlDbType.VarChar, 15)).Value = l_lead.Compounding
            oCom.Parameters.Add(New System.Data.SqlClient.SqlParameter("@modifiedBy", System.Data.SqlDbType.NVarChar, 30)).Value = Session("userName")

            Dim dr = oCom.ExecuteReader(CommandBehavior.CloseConnection)

            If dr.HasRows = False Then
                InjectScript.Text = "Update Failed!"
                InjectScript.ForeColor = Drawing.Color.Red
                Session("transactionID") = dr("transactionID")
                Session("parentTransactionID") = dr("parentTransactionID")
            End If

        Catch ex As Exception
            lblError.Text = ex.ToString
            lblError.ForeColor = Drawing.Color.Red
        End Try

Open in new window

1 Solution
Jacques Bourgeois (James Burger)PresidentCommented:
If you are careful about closing the connection, the CommandBehavior is not necessary.

It is safer to have it however... just in case you forget to close the connection or delete the Close line by mistake. These things happen, you know.

It also makes your life a little easier, you simply need one command to close both the DataReader and the Connection.

As for the Dispose, this is good practice. Most code analysis tools will flag you if you did not Dispose of an object that is disposable. But it is usually not necessary. I would say that more than 60% of programmer, and maybe more, never care about Dispose.

There is a discussion about what it does at the bottom of this thread.
lrbristerAuthor Commented:
Great!  Thanks
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now