[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


Command + Transaction Objects

Posted on 2011-10-27
Medium Priority
Last Modified: 2012-05-12
I have the following procedure, I use the first command object in a using block, I then need to use another, is this the correct way of doing it? Especially as it is in a transaction

Dim sConnectionString As String = ConfigurationManager.AppSettings("connectionString")
        Dim cn As New SqlConnection(sConnectionString)
        Dim trans As SqlTransaction
        Dim iTempId As Integer = iID

        Using cn

            Using cmd As New SqlCommand()

                With task
                    _taskID = .TaskID
                    TaskFolderKey = .TaskFolderKey
                    TaskName = .TaskName
                    TaskDescription = .TaskDescription
                    TaskXML = .TaskXML
                    TaskDisableTask = .TaskDisableTask
                    TaskCreated = .TaskCreated
                    TaskCreatedBy = .TaskCreatedBy
                    TaskLastModified = .TaskLastModified
                    TaskLastModifiedBy = .TaskLastModifiedBy
                End With

                _errors = Validation.Validate(Me)

                If _errors.Count = 0 Then

                    trans = cn.BeginTransaction

                    With cmd
                        .Connection = cn
                        .Transaction = trans
                        .CommandType = CommandType.StoredProcedure
                        .CommandText = "Task_Save"
                        .Parameters.AddWithValue("@ID", _taskID)
                        .Parameters.AddWithValue("@FolderKey", TaskFolderKey)
                        .Parameters.AddWithValue("@Name", TaskName)
                        .Parameters.AddWithValue("@Description", TaskDescription)
                        .Parameters.AddWithValue("@XML", TaskXML)
                        .Parameters.AddWithValue("@DisableTask", TaskDisableTask)
                        .Parameters.AddWithValue("@Created", TaskCreated)
                        .Parameters.AddWithValue("@CreatedBy", TaskCreatedBy)
                        .Parameters.AddWithValue("@LastModified", TaskLastModified)
                        .Parameters.AddWithValue("@LastModifiedBy", TaskLastModifiedBy)
                        .Parameters.AddWithValue("@NewID", SqlDbType.Int).Direction = ParameterDirection.Output
                        iID = DirectCast(.Parameters.Item("@NewID").Value, Integer)
                    End With

                    If iTempId > 0 Then
                        Using cmd1 = New SqlCommand
                            With cmd1
                                .Connection = cn
                                .Transaction = trans
                                .CommandType = CommandType.StoredProcedure
                                .CommandText = "TaskStep_Delete"
                                .Parameters.AddWithValue("@TaskID", _taskID)
                            End With
                        End Using
                    End If

                    trans.Commit()                    trans = Nothing

                End If

            End Using

        End Using

        Return _errors

Open in new window

Question by:rocky050371
  • 2
  • 2
LVL 25

Expert Comment

by:Luis Pérez
ID: 37037720
The way you're managing the transactions is right. Only one thing, you don't need the cn.Close() line, because the Using statement will manage itself the closing/disposing of the connection.

Author Comment

ID: 37037751
Is the use of two command objects right?

Author Comment

ID: 37037762
Is it not good practice to call close anyway?
LVL 25

Accepted Solution

Luis Pérez earned 2000 total points
ID: 37037879
The commands are used right.

You are, in fact, doing good practices... just with the Using block. With the Using statement you ensure that the connection object will be closed and disposed after you use it. That is the good practice. So you don't need to call the Close method because you are sure that the connection will be closed by the Using block.

Hope that helps.

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

In real business world data are crucial and sometimes data are shared among different information systems. Hence, an agreeable file transfer protocol need to be established.
Hello there! As a developer I have modified and refactored the unit tests which was written by fellow developers in the past. On the course, I have gone through various misconceptions and technical challenges when it comes to implementation. I would…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

873 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