Opening a SQL connection on .NET windows application start and not closing

Question

I have a windows application with a start button on the main screen. Once I start the exe and hit the start button, a connection to the SQL database is opened. I am not explicitly closing the connection at any point of time unless it closes by itself, whereby I reestablish the connection to the db. My question is will this cause any performance issues? I doubt that, since only one connection at any point of time is open. Please advice. Thanks in advance.

The code is as below:

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

    Timer1.Interval = 30000
    fnDbConnect()
    lblMessage.Text = ""
End Sub

Private Sub btnStartSMS_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnStartSMS.Click

    btnStartSMS.BackColor = Color.Red
    If ObjCn.State = ConnectionState.Open Then
        Timer1.Enabled = True
    Else
        If fnDbConnect() Then
            Timer1.Enabled = True
        Else
            MsgBox("An error occured while connecting to database. Please try later", MsgBoxStyle.Critical)
        End If

    End If

End Sub

And ObjCn.State = ConnectionState.Closed is checked wherever application and it is True, then fnDbConnect() is called.

It will depend on how many client apps try to connect to your server at the same time. Further, if you do not USE the connection it will inevitably timeout so you will need to reconnect. You need to consider exactly WHY you are doing this. — zaitsman
unless it closes by itself - This can be tricky to detect, every call that interacts with the database will need handling. — Alex K.

NicoRiff NicoRiff · Accepted Answer · 2017-01-25T11:58:35

Since it does not include much more code writing, it does not make much sense you keep you connection open all the time. IMHO the best recomendation is to use connection pooling. It also will be a much better practice whenever your application starts to grow. For all that said, you can just do your sql connections this way:

using (SqlConnection connection = new SqlConnection("yourConnectionString"))
{
    connection.Open();
    // Do whatever you need to do
    connection.Close();
}

If you remain the connection open, that connection will not be available for some other thing that needs to comunicate to the database server. Also, remaining your connection open, will eventually lead you to have your connection wiped and disposed whenever the garbage collector runs. Basically, open and close your connection whenever you need to use it. Don´t leave your connections open. For this, the better usage is open as in the example with the using statement. That will ensure that your connection is disposed right when the operation finishes.

Opening a SQL connection on .NET windows application start and not closing

1 Answers