0

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?

Improve this question
3
  • For starters: martinfowler.com/eaaCatalog
    – πάντα ῥεῖ
    Commented Jan 19, 2020 at 12:08
  • 1
    And only one question per question as usual for Q&A sites please.
    – πάντα ῥεῖ
    Commented Jan 19, 2020 at 12:13
  • 1
    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.
    – Robert Harvey
    Commented Jan 19, 2020 at 16:49

1 Answer 1

Reset to default
2

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).

Improve this answer
2
  • 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.
    – Al2110
    Commented Jan 20, 2020 at 1:46
  • @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.
    – Doc Brown
    Commented Jan 20, 2020 at 6:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.