How to stay connected to database until screen close?

Question

I developed a C# program for Windows-CE platform. The program open and close the connection to the database for every single interaction. See the code below.

Button click:

private void btnStkIn_Click(object sender, EventArgs e)
{
    formStockIn = new frmStkIn();
    formStockIn.Show();
}

Select Data:

try
{
    using (SqlConnection sqlConn = new SqlConnection(<connection-string>))
    {
        sqlConn.Open();
        //Execute command
        SqlCommand sqlCmd = new SqlCommand(<Select Query>, sqlConn);
        SqlDataReader sqlReader = sqlCmd.ExecuteReader();

        while (sqlReader.Read())
        {
            <Statement here>
        }
    }
}
catch
{
    //SQL command error
    itemDT.ErrorMessage = "Select process is failed.Please contact system admin.";
    itemDT.Result = 12;
}

Update Data:

try
{
    using (SqlConnection sqlConn = new SqlConnection(<connection-string>))
    {
        sqlConn.Open();
        //Execute command
        SqlCommand sqlCmd = new SqlCommand(<Update Query>, sqlConn);
        if (sqlCmd.ExecuteNonQuery() <= 0)
        {
            //No row affect
            return -99;
        }
        else
        {
            //Completed
            return 0;
        }
    }
}
catch
{
    //Sql command error
    return 99;
}

I would like to connect to database once (when the form in shown) and then do select, Insert, Update the data using the same connection and close the connection when I close the screen. At run-time, some screen can select-update more than once.

What should I do?

Answer 1

What you are doing is fine. It is good practice to keep the connection open for the shortest time possible and then disposing it. This is what you are doing and that's good.

If you keep it open and the user goes off on lunch or vacation and clicks nothing else, you are keeping a connection for no good reason.

If you need to do multiple things at the same time, then open one connection and execute the queries and then close the connection right away.