?
Solved

Execute Reader Command CLoseConnection

Posted on 2011-09-14
2
Medium Priority
?
304 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 40

Accepted Solution

by:
Jacques Bourgeois (James Burger) earned 2000 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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
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…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

770 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