Command + Transaction Objects

Posted on 2011-10-27
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
    LVL 25

    Expert Comment

    by:Luis Pérez
    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

    Is the use of two command objects right?

    Author Comment

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

    Accepted Solution

    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    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…
    A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
    It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
    Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…

    779 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

    14 Experts available now in Live!

    Get 1:1 Help Now