0

I've already checked here.

I am looping an opening a connection each time and I'm not sure if ASP.NET handles it with a performance hit or that it recognizes this code and optimizes it automatically. What I have now:

For i As Integer = 0 To 100
    cmd = New SqlCommand("UPDATE <table> where id=@id", myConnection)
    cmd.Parameters.Add(New SqlParameter("@id", i))
    Try
        myConnection.Open()
        cmd.ExecuteNonQuery()
    Catch ex As Exception
    Finally
        myConnection.Close()
    End Try
Next i

How could I alter this code so that it does not open a connection each time? Bring the closing and opening outside the For loop? Do a check on the existence of an open connection within the loop? I'd love to see the code sample for the best practice.

Community
  • 1
  • 1
Adam
  • 6,041
  • 36
  • 120
  • 208

1 Answers1

0

First of all, opening and closing a connection in VB.NET doesn't actually open and close database connections. Instead, they will retrieve and return connections from and to the connection pool. So, while there is still some performance impact, it is pretty minimal.

That being said, you can still re-use a connection, perhaps with code like this:

myConnection.Open()
For i As Integer = 0 To 100
    cmd = New SqlCommand("UPDATE <table> where id=@id", myConnection)
    cmd.Parameters.Add(New SqlParameter("@id", i))
    cmd.ExecuteNonQuery()
Next i
myConnection.Close()

You should of course add try/catch/finally or some other mechanism to ensure that myConnection.Close() gets executed in all cases. This will ensure that the connection is returned to the pool. The connection won't actually close unless it times out.

John Wu
  • 50,556
  • 8
  • 44
  • 80
  • Thanks! To your point: to ensure that the connection is always closed, I'd have to add the entire `For` loop in the `Try` block right? And won't that affect performance even more than my current code? – Adam Jan 24 '17 at 03:24
  • 1
    Correct, you can add Try/Catch/Finally around the entire thing and it won't affect performance. – John Wu Jan 24 '17 at 03:27