How to stay in touch with the database until the screen closes?

Question

I developed the C # program for the Windows-CE platform. The program opens and closes the database connection for each individual interaction. See code below.

Press the button:

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 the 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 the database once (when the form in the shown is displayed), and then select "Insert", "Update data" using the same connection and close the connection when the screen is closed. At run time, some screens may choose to refresh more than once.

What should I do?

Answer 1

What you do is beautiful. Good practice is to open the connection as soon as possible and then dispose of it. This is what you do, and that’s good.

If you keep it open, and the user goes out for lunch or vacation and clicks nothing else, you are in contact without a good reason.

If you need to do several actions at the same time, open one connection and execute the requests, and then immediately close the connection.