I intend to create a .NET WinForms application (this will be a toy application) which connects to a SQL Server database backend. I have designed and implemented an object-oriented model in C# with classes corresponding to DB tables, as part of the
Model. After doing some research, I have decided to implement another layer of the
Model, designed specifically for DB connection. There will be a third layer, the service layer. In the database, there will be three or four tables at most.
Dealing with loss of connection to DB
My question is regarding the connection to the database. My vision is to establish a
System.Data.SqlClient SqlConnection when the application is run, and authenticate the user (salted hashed passwords are stored in the DB). If the authentication is successful, the connection is closed, and the main form is displayed. Whenever there is a need to update the database or retrieve data from it, another connection is established, appropriate queries executed, and then the connection closed.
A potential problem is if the ability to establish a connection is lost. What should be done to deal with this? Should the application terminate at that point? Should data be loaded from the db (held in instances of the classes) when the application starts, and then if anything needs to be written, it is written into the db when it terminates normally?