Click here to Skip to main content
15,904,153 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
I am developing a WinForm Application which is heavily dependent on SQL Server Database. On a single form, the application required to connect to database several times. I want to ask that which is better practice and less performance issue between - opening SqlConnection single time on form_load event and closing connection on form_closing event OR opening and closing connection during each event (dropdown selection change, button click etc)? Please help.

What I have tried:

opening and closing connection during each event (dropdown selection change, button click etc)
Posted
Updated 21-Oct-19 22:16pm

As SQL Server uses connection pools, there is almost no performance hit when opening and closing connections, this is the recommended way by Microsoft.

If you are worried about performance you might be interested in PostgreSQL, see: relational-databases[^]
PostgreSQL is also much easier to distribute and deploy alongside your application.
 
Share this answer
 
v2
Open it each time: there are two simple reasons.
1) SQL connections are scarce resources - you shouldn't "hang onto them" any longer than you need to.
2) Some parts of SQL - such as a DataReader - need to be closed before any other action can continue on the same connection: if you rely on a single connection object, this can really mess up your code if you don't automatically close and Dispose of SqlCommand and suchlike objects, or if you call a method from within a DataReader loop.

The best practice is to create the connection (and all other SQL related objects) within a using block so they are automatically closed and Disposed when they go out of scope:
C#
using (SqlConnection con = new SqlConnection(strConnect))
    {
    con.Open();
    using (SqlCommand cmd = new SqlCommand("SELECT Id, description FROM myTable", con))
        {
        using (SqlDataReader reader = cmd.ExecuteReader())
            {
            while (reader.Read())
                {
                int id = (int) reader["Id"];
                string desc = (string) reader["description"];
                Console.WriteLine("ID: {0}\n    {1}", id, desc);
                }
            }
        }
    }
 
Share this answer
 
Comments
biny86 22-Oct-19 5:01am    
I am currently following this practice

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900