What if i don't close database connection in disconnected mode

Question

I am doing windows forms application with connection to SQL Server (disconnected mode). Every action I handle in my application needs the connection. So I don't know where to close the connection.

I am asking what if i don't close the connection. I know that there is no runtime error that appears. But is there any other error that could appear?

Answer 1

There is a risk that if you are using the same connection for each method, from a private field or GetConnection() method etc.; you could end up trying to read/write when the connection is still open.

This could then throw errors and you could end up in a whole heap of it, if using a live SQL database.

If you can, create a new instance of a connection in a using statement each time you want to access the DB.

Like this:

var connectionString = "YourConnectionStringToTheDB";
var queryString ="YourSQLQueryHere"; // FROM * IN tbl SELECT * etc...

using (SqlConnection connection = new SqlConnection(
           connectionString))
{
    using (SqlCommand command = new SqlCommand(queryString, connection))
    {
        command.Connection.Open();
        command.ExecuteNonQuery();
    }
 }

This way it is closed and disposed of when the code runs outside of the using statement's scope.

Notice that the SqlCommand is also in a using statement, as it too can be disposed of.

This is cleaner code and marks the objects for disposal by the Garbage Collector. (Google is your friend) - BIG topic; a lot of stuff to read.

Here is a similar question which gives you some more detail: The C# using statement, SQL, and SqlConnection

EDIT

Better still, you could wrap your SQL code in a try { } catch { } block to handle any errors at runtime.