There is a database connection code and a request to it -

SqlConnection SqlConnection; public async void connectDB() { string ConnectionAdres = @"Путь"; SqlConnection = new SqlConnection(ConnectionAdres); await SqlConnection.OpenAsync(); SqlDataReader SQLDReader = null; SqlCommand GetAllEmployCommand = new SqlCommand("SELECT * FROM [employment]", SqlConnection); try { SQLDReader = await GetAllEmployCommand.ExecuteReaderAsync(); while (await SQLDReader.ReadAsync()) { string test = Convert.ToString(SQLDReader["ID"]) + Convert.ToString(SQLDReader["surname"]) + Convert.ToString(SQLDReader["name"]) + Convert.ToString(SQLDReader["patronymic"]); } } catch (Exception ex) { } finally { if (SQLDReader != null) { SQLDReader.Close(); } } public void closeConnect() { if (SqlConnection != null && SqlConnection.State != ConnectionState.Closed) { SqlConnection.Close(); } }

How can I separate the connection and requests? And is it normal if the connection will be open throughout the program? I click on the button-> from the beginning the connection method is called, then the request. Or to write each request in the code as I have is a normal practice?

  • one
    Create a connection every time you need to work with the database. There is no need to reinvent the connection pool, since it is already inside ADO.NET - tym32167

1 answer 1

In .NET, the Connection Pooling mechanism is implemented, which allows you to reuse the physical connection to the database. When you call SqlConnection.Open - you get a ready connection from the pool. When you call .Close , return it to the pool. Therefore, you need to keep the SqlConnection object open as little as possible - this allows you to effectively reuse it. And, at the same time, you get rid of problems with processing timeouts of inactivity of the connection.

According to your code - use using to work with SqlConnection and SqlDataReader - it will correctly cause Dispose ( Close ) upon completion of work - i.e. will do for you what you are doing in try / finally .

So if your code is shortened, you get something like this:

 public async string SelectTest() { string connectionString = @"Путь"; using (var sqlConnection = new SqlConnection(connectionString)) { await sqlConnection.OpenAsync(); SqlCommand getAllEmployCommand = new SqlCommand("SELECT * FROM [employment]", sqlConnection); using (var reader = await getAllEmployCommand.ExecuteReaderAsync()) { while (await reader.ReadAsync()) { string test = Convert.ToString(reader["ID"]) + Convert.ToString(reader["surname"]) + Convert.ToString(reader["name"]) + Convert.ToString(reader["patronymic"]); return test; // ? } return null; // ? } } }

But in general, it should be understood that manual work with ADO.NET is optional. It is worth using ORM:

  • If I understand correctly then return test; will return a sample, and return null if some kind of error? How in your variant to learn value ex (in my where try / catch)? How to understand what a mistake? - xomem
  • And using I connect at the beginning of the class, where all the libraries are. Is there any difference? - xomem
  • 2
    @xomem is different using, docs.microsoft.com/ru-ru/dotnet/csharp/language-reference/… - rdorn