Menu
I've come from Java experience and am trying to start with C#. I've read SqlConnection SqlCommand SqlDataReader IDisposable and I can understand that the best practice to connecting to a DB is wrapping SqlConnection, SqlCommand and SqlDataReader in their own using block.
But in Java we use to encapsulate the connection into a factory method, create it only once, and reuse it for all queries, even multithreaded ones. Only statements and result sets are created for each query and closed ASAP.
Isn't creating a new SqlConnection for each query kinda overkill? Can't it be reused?
575
To answer your specific question, you can reuse a SqlConnection for each query. Just make sure to close your current query ( SqlDataReader , etc.) before you run another one, ie. wrap them in their own using blocks.
Yes, you will have to dispose (of) the connection separately. You can also see this in the source code: SqlCommand. Dispose() does not close or dispose the connection it uses (by the way, that would be bad anyway as you could not execute multiple commands with the same connection).
If the SqlConnection goes out of scope, it won't be closed. Therefore, you must explicitly close the connection by calling Close or Dispose . Close and Dispose are functionally equivalent. If the connection pooling value Pooling is set to true or yes , the underlying connection is returned back to the connection pool.
The SqlConnection is opened and set as the Connection for the SqlCommand. The example then calls ExecuteNonQuery. To accomplish this, the ExecuteNonQuery is passed a connection string and a query string that is a Transact-SQL INSERT statement. The connection is closed automatically when the code exits the using block.
Creating a new instance of the class SqlConnection does not create a new network connection to SQL Server, but leases an existing connection (or creates a new one). .NET handles the physical connection pooling for you.
When you have finished with your connection (through which you can send multiple queries) just Close() or Dispose() (or use a using{} block preferably).
There is no need, and not good practise, to cache instances of the SqlConnection class.
104
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
