C# Data Connections Best Practice?

by

Connections are pooled by .NET, so re-creating them generally isn’t an expensive operation. Keeping connections open for long periods of time, however, can cause issues.

Most “best practices” tell us to open connections as late as possible (right before executing any SQL) and closing them as soon as possible (right after the last bit of data has been extracted).

An effective way of doing this automatically is with using statements:

using (SqlConnection conn = new SqlConnection(...))
{
    using(SqlCommand cmd = new SqlCommand(..., conn))
    {
        conn.Open();
        using(DataReader dr = cmd.ExecuteReader())  // or load a DataTable, ExecuteScalar, etc.    
        {
             ...
        {
    }
}

That way, the resources are closed and disposed of even if an exception is thrown.

In short, opening a connection when the app opens or when each form opens is probably not the best approach.

More Related Contents:

  1. Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints
  2. How do I connect to an MDF database file?
  3. SQL WHERE clause matching values with trailing spaces
  4. How to access ssis package variables inside script component
  5. SqlConnection.Close() inside using statement
  6. When executing a stored procedure, what is the benefit of using CommandType.StoredProcedure versus using CommandType.Text?
  7. How to use String property as primary key in Entity Framework
  8. Is SqlConnection.Close() need inside a Using statement?
  9. Update-Database command is not working in ASP.Net Core / Entity Framework Core because object in database already exists
  10. Search SQL by date ASP.NET
  11. I want to insert a record in DB and then need to return a row
  12. How can I add user-supplied input to an SQL statement?
  13. How to get last inserted id?
  14. What is the syntax for an inner join in LINQ to SQL?
  15. Entity Framework .Remove() vs. .DeleteObject()
  16. NHibernate QueryOver with Fetch resulting multiple sql queries and db hits
  17. What represents a double in sql server?
  18. SQL Insert Query Using C#
  19. Entity Framework error: Cannot insert explicit value for identity column in table
  20. Executing query with parameters
  21. How to disable cascade delete for link tables in EF code-first?
  22. Use the dynamic keyword/.NET 4.6 feature in Unity
  23. How to use TransactionScope in C#?
  24. insert datetime value in sql database with c#
  25. Getting the Last Insert ID with SQLite.NET in C#
  26. Join and Include in Entity Framework
  27. How to pass string array in SQL parameter to IN clause in SQL
  28. Check if a SQL table exists
  29. Mapping columns in a DataTable to a SQL table with SqlBulkCopy
  30. SQL connection string for microsoft access 2010 .accdb

Leave a Comment