Solved

Execute Reader Command CLoseConnection

Posted on 2011-09-14
2
296 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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

For most people, the WrapPanel seems like a magic when they switch from WinForms to WPF. Most of us will think that the code that is used to write a control like that would be difficult. However, most of the work is done by the WPF engine, and the W…
More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
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…

816 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

9 Experts available now in Live!

Get 1:1 Help Now