-2

I'm new in WinForms .Net, I'm using Dapper to deal with MySql DBs.

Should I start a new SqlConnexion for each query or only one SqlConnexion is enough for the whole application? (I start it in the beginning and dispose it by closing the app)

Which is the best way? And why?

Tim Diekmann
  • 7,755
  • 11
  • 41
  • 69
Abd13t
  • 1
  • 2
  • 1
    What do you mean by `SqlConnexion`? I'm not aware of any class of that name. If you mean `SqlConnection`, please correct yourself. But if you're using those, you're not talking to a [tag:mysql] database. Or that's the next problem you're going to encounter, since they only work with [tag:sql-server]. So, please decide *what you're actually working with* and edit your question appropriately. – Damien_The_Unbeliever Jul 25 '18 at 06:51
  • I disagreed with the close as duplicate since all we have in the question are "MySQL", "SqlConnexion" and [tag:mysql]. Since those things don't exist and/or are contradictory (as my previous comment already pointed out), I don't agree (yet) that it's a duplicate asking about `SqlConnection` specifically. – Damien_The_Unbeliever Jul 25 '18 at 08:24
  • @Damien_The_Unbeliever while the observations in your first comment are correct, I think it's safe to assume that the OP is using SQL server and SqlConnection, and just not a native English speaker and don't know how to spell connection (try to ignore your knowledge about the English language and say connection out loud - `x` instead of `t` seems like a reasonable choice under such circumstances). Further more - the correct answer clearly should not be effected by these details - *all* ado.net connection objects best practice is to be disposed as soon as done using, to return to the pool. – Zohar Peled Jul 26 '18 at 04:25

2 Answers2

2

As Damien wrote in his comment to the question, it's not really clear if you are using MySql (and therefor, MySqlConnection) or Sql Server (as implied by the misspelled SqlConnection in the question).

However, that detail is not relevant to the answer, since SqlConnection and MySqlConnection both implement connection pooling - so best practice is to close and dispose them as soon as possible.

The basic concept of connection pooling is that the ADO.Net provider creates a connection to the database when you first use it, but when you close the connection in your application, ADO.Net keeps the underlying connection alive, so that the next time you open the connection to the database, you don't need to go through all the overhead of creating the actual connection - ADO.Net simple re-use the existing one.

Microsoft's recommend closing and disposing SqlConnection after each use, since that's the only way the underlying connection can go back to the pool - from SQL Server Connection Pooling (ADO.NET) page:

Caution

We strongly recommend that you always close the connection when you are finished using it so that the connection will be returned to the pool. You can do this using either the Close or Dispose methods of the Connection object, or by opening all connections inside a using statement in C#, or a Using statement in Visual Basic. Connections that are not explicitly closed might not be added or returned to the pool. For more information, see using Statement or How to: Dispose of a System Resource for Visual Basic.

MySql documentation also recommends against using an application wide connection. In fact, they recommend not creating an instance of MySqlConnection at all, but instead let MySqlCommand manage the connection for you, by using overloads that takes the connection string as arguments:

To work as designed, it is best to let the connection pooling system manage all connections. Do not create a globally accessible instance of MySqlConnection and then manually open and close it. This interferes with the way the pooling works and can lead to unpredictable results or even exceptions.

One approach that simplifies things is to avoid manually creating a MySqlConnection object. Instead use the overloaded methods that take a connection string as an argument. Using this approach, Connector/NET will automatically create, open, close and destroy connections, using the connection pooling system for best performance.

Community
  • 1
  • 1
Zohar Peled
  • 79,642
  • 10
  • 69
  • 121
0

There is no need for you to start a new connection for each query because the connection is first built and maintained by the DBMS and your methods. all you have to do is to start it at first with a simple try catch syntax and use it in your program.

keivan shirkoubian
  • 75
  • 1
  • 1
  • 9