c# - How to create a scalable SQL connection that takes full advantage of connection pooling? -

Here's an existing repository connection pattern on an app that I'm working on.

  Public Class Repository: IDisposable {Public IDbConnection SiteConnection {Receive (if the connection to the site == blank) {siteConnection = New SqlConnection (DataBaseConfig.SiteConnectionString); SiteConnection.Open (); } Return site connection; }} Public Zero () {if (siteConnection! = Null) {siteConnection.Dispose (); SiteConnection = null; }}}   
 Then the repositories look like this:  
  Public class SomeRepository: repository {public IEnumerable & lt; Object & gt; GetSomeObjects () {SiteConnection.Query & lt; Object & gt; ("Select statement"); } Public IEnumerable & lt; Other Object & gt; GetSomeOtherObjects () {SiteConnection.Query & quot; Other Object & gt; ("Select statement"); }}   
 What I can tell, only the end of page load is settled at the very end. No transaction is being done In the past, I was under the assumption that you should always come late and should come out early in the beginning of SQL.    Public IEnumerable & lt; Object & gt; GetObjects () {Usage (sqlconnection conn = new sqlconnection (connString)) {conn.pen (); // do something with the database}}   
 Which method is more bulletproof and takes full advantage of connection pooling and uses at least database resources?   
 
 
 Take a look at connection pooling. Returns the pool as soon as possible by closing the connection. I think the best advice coming out of this kind of discussion is that what ado.net did best does the best and does not try to exclude its pooling logic if you use SqlConnection in a decompiler See, there is a lot going on under the cover. It is unbelievable that this is part of a larger transaction, while you can even do so (with SQL Server 2008).