none
Trying to understand the best coding practice for using ado in vb RRS feed

  • Question

  • Let's say I have the following two functions.  Is one any better than the other? What is the advantage of the Using command?  Will either approach prevent the use of connection pooling? Is one better to use for a parallel design app than another or is both approaches wrong?


    Thanks,

    Rut

     

     

    Public Sub ExecuteNonQuery(ByVal MyConnectionString As String, ByVal SqlQuery As String, ByVal cmdType As Data.CommandType)
                Using conn As New SqlConnection(MyConnectionString)
                    Using cmd As New SqlCommand(SqlQuery, conn)
                        cmd.CommandType = cmdType
                        conn.Open()
                        cmd.ExecuteNonQuery()
                    End Using
                End Using
            End Sub


            Public Sub ExecuteNonQuery2(ByVal MyConnectionString As String, ByVal SqlQuery As String, ByVal cmdType As Data.CommandType)
                Dim conn As SqlConnection = Nothing
                Dim cmd As SqlCommand = Nothing
                Try
                    conn = New SqlConnection(MyConnectionString)
                    cmd = New SqlCommand(SqlQuery, conn)
                    cmd.CommandType = cmdType

                    cmd.ExecuteNonQuery()
                Finally
                    conn.Close()
                    conn = Nothing
                    cmd = Nothing

                End Try
            End Sub

     


    Mike Rutledge
    Wednesday, July 27, 2011 5:25 PM

Answers

  • Hi Mike,
    Welcome!
    I'm not familiar with VB.net, but I think you should close the connection and command in using block.
    Public Sub ExecuteNonQuery(ByVal MyConnectionString As String, ByVal SqlQuery As String, ByVal cmdType As Data.CommandType)
           Using conn As New SqlConnection(MyConnectionString)
             Using cmd As New SqlCommand(SqlQuery, conn)
               cmd.CommandType = cmdType
               conn.Open()
               cmd.ExecuteNonQuery()
               cmd.Close()
               conn.Close()
             End Using
           End Using
         End Sub
     
    

    Ps: Connection.close() just to put connetion in Connetion Pool.
    Have a nice day.

    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by jmrut Tuesday, August 2, 2011 4:33 PM
    Thursday, July 28, 2011 6:52 AM
    Moderator

All replies

  • Hi Mike,
    Welcome!
    I'm not familiar with VB.net, but I think you should close the connection and command in using block.
    Public Sub ExecuteNonQuery(ByVal MyConnectionString As String, ByVal SqlQuery As String, ByVal cmdType As Data.CommandType)
           Using conn As New SqlConnection(MyConnectionString)
             Using cmd As New SqlCommand(SqlQuery, conn)
               cmd.CommandType = cmdType
               conn.Open()
               cmd.ExecuteNonQuery()
               cmd.Close()
               conn.Close()
             End Using
           End Using
         End Sub
     
    

    Ps: Connection.close() just to put connetion in Connetion Pool.
    Have a nice day.

    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by jmrut Tuesday, August 2, 2011 4:33 PM
    Thursday, July 28, 2011 6:52 AM
    Moderator
  • Hi,

    I am writing to check the status of the issue on your side.  Would you mind letting us know the result of the suggestions?

    If you need further assistance, please feel free to let me know.   I will be more than happy to be of assistance.

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Tuesday, August 2, 2011 3:57 PM
    Moderator
  • I went ahead and added the connection.close but from my testing it appears the Using clause automatically calls the dispose function of the object so I don't really think it is needed.

     

    Thanks,

    Rut


    Mike Rutledge
    Tuesday, August 2, 2011 4:33 PM