MVC database connection .NET

Question

Introduction

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?

• πάντα ῥεῖ: For starters: martinfowler.com/eaaCatalog
• πάντα ῥεῖ: And only one question per question as usual for Q&A sites please.
• Robert Harvey: Most systems return a 500 error to the user if a database connection cannot be established. Thankfully, in a well-designed system, this happens very rarely.

Answer 1

Ultimately, it depends on your requirements, specificially

• how often and for what reasons do you expect the application to lose the ability to reconnect?

• is it reasonable to assume a reconnect will be possible some minutes later?

• how much data can get lost in case there is unsaved data, and how important could that data be?

Depending on the answers to these question, you can decide if

• the application should ask the user if it should terminate or try a reconnect later, or if it should not ask and terminate directly, or if it should try a reconnect after a certain time interval by itself

• the application requires a local "backup store" to make sure any "unsafed data" can be safed over the network at a later point in time, after a restart of the application (or if it is not worth the hassle to implement such a local store).

• Al2110: The database has inventory information (quantities of items) in one table. The purpose of the application should decrease the quantity of each, and create records of such transactions in a separate transactions table (foreign key item ID). Perhaps this information can be backed up if connection is lost, and the database updated once connection is back.
• Doc Brown: @Al2110: well, based on the information you presented, I can only tell you your options and the questions you have to ask yourself or your users. Doing the analysis and design is still up to you.