Connection Tips

When defining connections, here are a few simple guidelines that are useful to follow:

• Generally you will want to use a connection variable instead of a connection string as the

  ActiveConnection

  for commands. By using a connection variable, you're calling an existing connection. If you use a string, as we saw above, you are creating a new connection.
  
  
• Make sure you explicitly close a connection when it's no longer needed. Although a connection is closed when it goes out of scope, it's better to make sure it is closed when you think it is. You can proactively call

  Close

  and set the variable to

  Nothing

  when you know that you will no longer need a connection. This frees up resources before it actually goes out of scope.

When working with a SQL Server data source, consider these points:

• With forward-scrolling, read-only cursors (also known as firehose cursors) against SQL Server, you will not be able to start a new transaction on that connection. This is because the connection is dealing with the cursor and needs to complete what it's doing (i.e. get to the end of the cursor and close it) before continuing.
  
  
• In addition, it is a good idea to separate your forward-scrolling cursor code from your command execution code. You could get into a situation where the same SQL pages are used concurrently, for example a query such as

  SELECT

  *

  FROM

  TableX

  and an update to

  TableX

  . Without setting the

  CommandTimeout

  property, your could hang your system due to locking contention.

© 1997 by Wrox Press. All rights reserved.