Solved

Execute Reader Command CLoseConnection

Posted on 2011-09-14
2
293 Views
Last Modified: 2013-11-27
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
Comment
Question by:lrbrister
2 Comments
 
LVL 40

Accepted Solution

by:
Jacques Bourgeois (James Burger) earned 500 total points
ID: 36539901
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
 

Author Closing Comment

by:lrbrister
ID: 36542258
Great!  Thanks
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For a while now I'v been searching for a circular progress control, much like the one you get when first starting your Silverlight application. I found a couple that were written in WPF and there were a few written in Silverlight, but all appeared o…
A theme is a collection of property settings that allow you to define the look of pages and controls, and then apply the look consistently across pages in an application. Themes can be made up of a set of elements: skins, style sheets, images, and o…
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

919 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now