• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 309
  • 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
        Try
            oCom.Connection = objConn
            oCom.CommandText = "sp_crmSS_transMasterUpdate"
            oCom.CommandType = CommandType.StoredProcedure
            oCom.Parameters.Clear()
            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")

            objConn.Open()
            Dim dr = oCom.ExecuteReader(CommandBehavior.CloseConnection)
            dr.Read()

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

            dr.Close()
        Catch ex As Exception
            lblError.Text = ex.ToString
            lblError.ForeColor = Drawing.Color.Red
        End Try
        objConn.Dispose()
        oCom.Dispose()

Open in new window

0
lrbrister
Asked:
lrbrister
1 Solution
 
Jacques Bourgeois (James Burger)Commented:
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.
0
 
lrbristerAuthor Commented:
Great!  Thanks
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

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