Hi all,
I have had a look around the forums but i could find anything, if someone could point me in the right direction for this.
What is the best way to handle an sql connection, in my program I have to get data from the server on a button click now my question is is it better to recreate the connection on every click and perform the query ie create a new SqlConnection every time, or is it better to create the connection once when the program starts up and just open and close it every time the push the button, ps the program has to run for a long time so I'm not so sure on the connection timing out.
Thanks

Even though you may open, close, and create new SQL connections, behind the scene the system is maintaining multiple connections to your database just waiting for you to need one. This helps optimize your database code.

So the answer is: Do what you feel makes your code more readable and maintainable.

Keep in mind that the SqlConnection object won't close itself; you'll always have to close it manually. I recommend you always do it with a using block, for example:

using(SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();
    // Do work here; connection closed on following line.
}

That, IMO, is the most readable and maintainable.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.