Command + Transaction Objects

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

                    cn.Open()
                    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
                        .ExecuteNonQuery()
                        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)
                                .ExecuteNonQuery()
                            End With
                        End Using
                    End If

                    trans.Commit()                    trans = Nothing
          

                    cn.Close()
                End If

            End Using

        End Using

        Return _errors

Open in new window

rocky050371Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Luis PérezSoftware Architect in .NetCommented:
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.
0
rocky050371Author Commented:
Is the use of two command objects right?
0
rocky050371Author Commented:
Is it not good practice to call close anyway?
0
Luis PérezSoftware Architect in .NetCommented:
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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.