How to work with the database in c# and other programming languages?

Question

SQL

Hello. I am new to programming languages ​​such as C#, C, C++, Java and others.
Here I am learning C#. There was a question, and whether correctly I work with a DB? I read 3 books about this, but everyone advises differently and I don’t know if I’m doing the right thing.
At the moment there is a login form for the program. There are three functions in total:
The first one starts working when the program is opened. It opens the connection and displays accounts from which you can choose your own. The connection is closed.
The second one works when the user has entered a password and tries to log in. The connection opens and we check the password.
The third one is for connection testing. The connection also opens and closes.
In fact, all functions are something like this:

private void SQLFunction(){
sqlConnection = new SqlConnection("Server=" + Properties.Settings.Default.SQLServer + ";Database=" + Properties.Settings.Default.SQLDateBase + ";User ID=" + Properties.Settings.Default.SQLLogin + ";Password=" + Properties.Settings.Default.SQLPass + ";");

 try {
sqlConnection.Open(); SqlCommand command = new SqlCommand("SELECT id, name, surname, patronymic FROM staff WHERE type = '1'", sqlConnection);  ТУТ ЧИТАЕМ ПОЛУЧЕННОЕ.
} 
catch{
 ТУТ ОШИБКА ЕСЛИ ПОДКЛЮЧЕНИЕ НЕ УДАЛОСЬ.
}

sqlConnection.Close(); // Закрываем соединение.
}

Is it correct that I open a connection every time? Maybe my choice is wrong?

Answer 1

https://github.com/aura-project/melia
C# example of working with the database and in general
See what kind of software.
desktop for the end user should not use the database at all (unless local)
if the server is the example above

Answer 2

Correct, at least if there is no high load, on Web sites it is the same, the protocol with the client is sessional, and with each request it is reopened/closed.

Answer 3

It's okay if you don't often open a connection, and if in the future the work becomes more frequent, then it's better to redo it. And I advise you to look towards ORM