Why always close Database connection?

by

You should not leave connections open.

You should:

  1. Open connections as late as possible
  2. Close connections as soon as possible

The connection itself is returned to the connection pool. Connections are a limited and relatively expensive resource. Any new connection you establish that has exactly the same connection string will be able to reuse the connection from the pool.

We strongly recommend that you always
close the connection when you are
finished using it so that the
connection will be returned to the
pool. You can do this using either the
Close or Dispose methods of the
Connection object, or by opening all
connections inside a using statement
in C#, or a Using statement in Visual
Basic. Connections that are not
explicitly closed might not be added
or returned to the pool. For more
information, see using Statement (C#
Reference) or How to: Dispose of a
System Resource for Visual Basic. Ref.

You should appropriately wrap anything that implements IDisposable in a using statement block:

 using (SqlConnection connection = new SqlConnection(connectionString))
 {
     connection.Open();

     ...

     command.ExecuteNonQuery();
 }

More Related Contents:

  1. Where is likely the performance bug here? [closed]
  2. ExecuteReader requires an open and available Connection. The connection’s current state is Connecting
  3. Getting all types that implement an interface
  4. MSSQL Error ‘The underlying provider failed on Open’
  5. Tips for optimizing C#/.NET programs [closed]
  6. Why doesn’t .NET/C# optimize for tail-call recursion?
  7. Is shifting bits faster than multiplying and dividing in Java? .NET? [closed]
  8. Generating permutations of a set (most efficiently)
  9. Does C# optimize the concatenation of string literals?
  10. File I/O with streams – best memory buffer size
  11. Reducing memory usage of .NET applications?
  12. Pass connection string to code-first DbContext
  13. Fastest method to remove Empty rows and Columns From Excel Files using Interop
  14. How to form a correct MySQL connection string? [closed]
  15. What is the real overhead of try/catch in C#?
  16. Does Interlocked.CompareExchange use a memory barrier?
  17. When is optimization premature? [closed]
  18. Fake-scrolling containers with very many controls
  19. How to fix “The ConnectionString property has not been initialized”
  20. Is the C# compiler smart enough to optimize this code?
  21. Using MySQLConnection in C# does not close properly
  22. Switch case on type c# [duplicate]
  23. Different ways of adding to Dictionary
  24. Writing FizzBuzz
  25. Micro Optimization of a 4-bucket histogram of a large array or list
  26. A better way to replace many strings – obfuscation in C#
  27. Testing an Entity Framework database connection
  28. using static Regex.IsMatch vs creating an instance of Regex
  29. C# Compiler optimization – Unused methods
  30. how to replace characters in a array quickly

Leave a Comment