Automatically closed db connection within using statement

Question

I wrote this code to write my data into a database

Public Class SQL_Client

    Sub AddData(ByRef myGDI As GeneralInfo)
        Dim conStr As String = "Server=NB01009;Database=DEV_DW;Trusted_Connection=true"

        Dim query As String = String.Empty
        query = "INSERT INTO GeneralInfo(id, name, data) VALUES (@id, @name, @data)"

        Using con As SqlConnection = New SqlConnection(conStr)
            Using comm As New SqlCommand
                With comm
                    .Connection = con
                    .CommandType = CommandType.Text
                    .CommandText = query
                    .Parameters.AddWithValue("@id", myGDI.id)
                    .Parameters.AddWithValue("@name", myGDI.name)
                    .Parameters.AddWithValue("@data", "Hallo Data")
                End With
                Try
                    con.Open()
                    comm.ExecuteNonQuery()
                Catch ex As SqlException
                    Console.WriteLine(ex.Message.ToString(), "Error Message")
                End Try
            End Using

            Dim subquery As String = String.Empty
            query = "INSERT INTO FullData(GeneralInfoID, userRunID VALUES(@GeneralInfoID, @userRunID)"

            For Each myData As FullData In myGDI.data
                Using command As New SqlCommand
                    With command
                        .Connection = con
                        .CommandType = CommandType.Text
                        .CommandText = query
                        .Parameters.AddWithValue("@GeneralInfoID", myGDI.id)
                        .Parameters.AddWithValue("@userRunID", myData.userRunID)
                    End With
                    Try
                        con.Open()
                        command.ExecuteNonQuery()
                    Catch ex As SqlException
                        Console.WriteLine(ex.Message.ToString(), "Error Message")
                    End Try
                End Using
            Next

        End Using
    End Sub
End Class

Since I have a nested object I want to write into the database (see here) I use a loop and a seconed Using Statement. At the second command.ExecuteNonQuery() it get the undhandled Exception:

System.INvalidOperationException: 'The connection was not closed. The connection's current state is open.'

I actually thought that after leaving the Using statement the connection would be closed automatically. But it looks like this assumption was wrong. Could anyone tell me how to handle this correctly?

You are leaving the using around the SqlCommand not the using around the SqlConnection. The connection is still live and kicking, no need to reopen it — Steve
– Steve, Commented Mar 27, 2018 at 12:50
As a side note. Try to avoid AddWithValue. This shortcut has well known problems Can we stop using AddWithValue already? and speaking about performance How data access code affects database performances — Steve
– Steve, Commented Mar 27, 2018 at 12:54

MatSnow · Accepted Answer · 2018-03-27 12:51:50Z

2

You're opening the connection twice before the connection is closed. It will automatically be closed after the last End Using.

Remove the second con.Open() and it should be fine.

answered Mar 27, 2018 at 12:51

MatSnow

7,5573 gold badges22 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Automatically closed db connection within using statement

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related