blocks|key|2062631|text|ADO.NET+SQL+Server提供程序自己执行连接池。您可以通过连接字符串中的MinPoolSize和MaxPoolSize来控制池大小。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|2062632|entityMap^0|16|B|1I|B|0^^$0|@$1|2|3|4|5|6|7|H|8|@$9|I|A|J|B|C]|$9|K|A|L|B|C]]|D|@]|E|$]]|$1|F|3|-4|5|6|7|M|8|@]|D|@]|E|$]]]|G|$]]

ADO.NET SQL Server provider does the connection pooling itself. You can control the pool size by <code>MinPoolSize</code> and <code>MaxPoolSize</code> in the connection string.

blocks|key|2062634|text|你真的不应该自己处理这个问题，因为有无数的工具可以为你做这件事。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2062635|如果您真的想自己做这件事，那么可以研究一下Unit+of+Work模式，您可以在该模式中管理连接/事务生命周期。您肯定不想尝试在混乱的水域中导航，因为在不同的地方有打开/关闭的连接。|offset|length|2062636|如果您决定让您的组件直接打开数据库连接，那么连接生命周期很可能过于细粒度，从而导致单个用户操作有许多打开/关闭的连接。|2062637|entityMap|0|LINK|mutability|MUTABLE|url|http://martinfowler.com/eaaCatalog/unitOfWork.html^0|0|L|C|0|0|0^^$0|@$1|2|3|4|5|6|7|P|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|Q|8|@]|9|@$D|R|E|S|1|T]]|A|$]]|$1|F|3|G|5|6|7|U|8|@]|9|@]|A|$]]|$1|H|3|-4|5|6|7|V|8|@]|9|@]|A|$]]]|I|$J|$5|K|L|M|A|$N|O]]]]

You really shouldn't be handling this problem yourself, as there are countless tools out there that can do it for you.

If you really want to do it yourself, then look into the <a href="http://martinfowler.com/eaaCatalog/unitOfWork.html" rel="nofollow noreferrer">Unit of Work</a> pattern where you can manage connection/transaction lifecycle. You certainly don't want to try to navigate the messy waters where there are connections being opened/closed in different places.

If you decide to let your components directly open db connections then it is likely that the connection lifecycle will be too fine-grained and result in many open/closed connections for a single user operation.

blocks|key|1848617|text|我发现自己需要传递一个connection对象，以便允许多个业务对象在单个事务中将其自身保存到数据库中。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1848618|如果每个业务对象都必须为数据库创建自己的SQLConnection，那么事务将升级为分布式事务，我希望避免这种情况。|1848619|我不喜欢将SQLConnection对象作为参数传递来保存对象，所以我创建了一个ConnectionManager来处理创建SQLConnection对象、跟踪SQLConnection对象的使用以及在不使用时断开SQLConnection对象的工作。|1848620|以下是ConnectionManager的一些示例代码：|1848621|public+class+ConnectionManager:+IDisposable
{
++++private+ConnectionManager+instance;

++++[ThreadStatic]
++++private+static+object+lockObject;+
++++private+static+Object+LockObject
++++{
++++++++get
++++++++{
++++++++++++if+(lockObject+==+null)
++++++++++++++++lockObject+=+new+object();
++++++++++++return+lockObject;
++++++++}
++++}

++++[ThreadStatic]
++++private+static+Dictionary<string,+ConnectionManager>+managers;
++++private+static+Dictionary<string,+ConnectionManager>+Managers
++++{
++++++++get
++++++++{
++++++++++++if+(managers+==+null)
++++++++++++++++managers+=+new+Dictionary<string,+ConnectionManager>();
++++++++++++return+managers;
++++++++}
++++}

++++private+SqlConnection+connection+=+null;
++++private+int+referenceCount;
++++private+string+name;


++++public+static+ConnectionManager+GetManager(string+connectionName)
++++{
++++++++lock+(LockObject)
++++++++{
++++++++++++ConnectionManager+mgr;
++++++++++++if+(Managers.ContainsKey(connectionName))
++++++++++++{
++++++++++++++++mgr+=+Managers[connectionName];
++++++++++++}
++++++++++++else
++++++++++++{
++++++++++++++++mgr+=+new+ConnectionManager(connectionName);
++++++++++++++++Managers.Add(connectionName,+mgr);
++++++++++++}

++++++++++++mgr.AddRef();
++++++++++++return+mgr;
++++++++}
++++}

++++private+ConnectionManager(string+connectionName)
++++{
++++++++name+=+connectionName;
++++++++connection+=+new+SqlConnection(GetConnectionString(connectionName));
++++++++connection.Open();
++++}

++++private+string+GetConnectionString(string+connectionName)
++++{
++++++++string+conString+=+Configuration.ConnectionString;
++++++++return+conString;+
++++}

++++public+SqlConnection+Connection
++++{
++++++++get+{+return+connection;+}
++++}

++++private+void+AddRef()
++++{
++++++++referenceCount+%2B=+1;
++++}

++++private+void+DeRef()
++++{
++++++++lock+(LockObject)
++++++++{
++++++++++++referenceCount+-=+1;
++++++++++++if+(referenceCount+==+0)
++++++++++++{
++++++++++++++++connection.Dispose();
++++++++++++++++Managers.Remove(name);
++++++++++++}
++++++++}
++++}

#region+IDisposable+Members

++++public+void+Dispose()
++++{
++++++++Dispose(true);
++++}

++++protected+virtual+void+Dispose(bool+disposing)
++++{
++++++++if+(disposing)
++++++++{
++++++++++++DeRef();
++++++++}
++++}

++++~ConnectionManager()
++++{
++++++++Dispose(false);
++++}

#endregion

}|code-block|syntax|javascript|1848622|下面是我如何在业务对象中使用它：|1848623|public+void+Save()
{+++
++++using+(ConnectionManager+mrg+=+ConnectionManager.GetManager("SQLConnectionString")
++++{
++++++++using+(SQLCommand+cmd+=+new+SQLCommand)
++++++++{
++++++++++++cmd.connection+=+mgr.Connection
++++++++++++//+More+ADO+Code+Here
++++++++}

++++++++_childObject.Save();+//this+child+object+follows+the+same+pattern+with+a+using+ConnectionManager.
++++}
}|1848624|我保存了一个业务对象，它的所有子对象也使用相同的connection对象保存。当作用域远离原始父级时，using语句将关闭连接。|1848625|这是我从Rocky+Lhotka的CSLA框架中学到的模式。|1848626|基思|1848627|entityMap^0|0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Y|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|Z|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|10|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|11|8|@]|9|@]|A|$]]|$1|H|3|I|5|J|7|12|8|@]|9|@]|A|$K|L]]|$1|M|3|N|5|6|7|13|8|@]|9|@]|A|$]]|$1|O|3|P|5|J|7|14|8|@]|9|@]|A|$K|L]]|$1|Q|3|R|5|6|7|15|8|@]|9|@]|A|$]]|$1|S|3|T|5|6|7|16|8|@]|9|@]|A|$]]|$1|U|3|V|5|6|7|17|8|@]|9|@]|A|$]]|$1|W|3|-4|5|6|7|18|8|@]|9|@]|A|$]]]|X|$]]

I found myself needing to pass around a connection object so I could allow several business objects to save themselves to the database inside a single transaction.

If each business object had to create its own SQLConnection to the database, the transaction would escalate to a distributed transaction and I wanted to avoid that. 

I did not like having to pass the SQLConnection object as a parameter to save an object, so I created a ConnectionManager that handles creating the SQLConnection object for me, tracking the use of the SQLConnection object, and disconnecting the SQLConnection object when not in use.

Here is some code as an example of the ConnectionManager:

<pre><code>public class ConnectionManager: IDisposable
{
 private ConnectionManager instance;

 [ThreadStatic]
 private static object lockObject; 
 private static Object LockObject
 {
 get
 {
 if (lockObject == null)
 lockObject = new object();
 return lockObject;
 }
 }

 [ThreadStatic]
 private static Dictionary&lt;string, ConnectionManager&gt; managers;
 private static Dictionary&lt;string, ConnectionManager&gt; Managers
 {
 get
 {
 if (managers == null)
 managers = new Dictionary&lt;string, ConnectionManager&gt;();
 return managers;
 }
 }

 private SqlConnection connection = null;
 private int referenceCount;
 private string name;


 public static ConnectionManager GetManager(string connectionName)
 {
 lock (LockObject)
 {
 ConnectionManager mgr;
 if (Managers.ContainsKey(connectionName))
 {
 mgr = Managers[connectionName];
 }
 else
 {
 mgr = new ConnectionManager(connectionName);
 Managers.Add(connectionName, mgr);
 }

 mgr.AddRef();
 return mgr;
 }
 }

 private ConnectionManager(string connectionName)
 {
 name = connectionName;
 connection = new SqlConnection(GetConnectionString(connectionName));
 connection.Open();
 }

 private string GetConnectionString(string connectionName)
 {
 string conString = Configuration.ConnectionString;
 return conString; 
 }

 public SqlConnection Connection
 {
 get { return connection; }
 }

 private void AddRef()
 {
 referenceCount += 1;
 }

 private void DeRef()
 {
 lock (LockObject)
 {
 referenceCount -= 1;
 if (referenceCount == 0)
 {
 connection.Dispose();
 Managers.Remove(name);
 }
 }
 }

#region IDisposable Members

 public void Dispose()
 {
 Dispose(true);
 }

 protected virtual void Dispose(bool disposing)
 {
 if (disposing)
 {
 DeRef();
 }
 }

 ~ConnectionManager()
 {
 Dispose(false);
 }

#endregion

}
</code></pre>

Here is how I would use it from a business object:

<pre><code>public void Save()
{ 
 using (ConnectionManager mrg = ConnectionManager.GetManager("SQLConnectionString")
 {
 using (SQLCommand cmd = new SQLCommand)
 {
 cmd.connection = mgr.Connection
 // More ADO Code Here
 }

 _childObject.Save(); //this child object follows the same pattern with a using ConnectionManager.
 }
}
</code></pre>

I save a business object and all of its children are saved as well using the same connection object. When the scope falls away from original parent, the using statement closes the connection.

This is a pattern I learned from Rocky Lhotka in his CSLA framework. 

Keith

blocks|key|2062642|text|在您的示例中需要注意的一件事是，ASP.NET应用程序不应该使用ThreadStatic存储，因为线程可以重复使用，如果您不清理所有对象，最终会导致连接挂起。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2062643|在ASP.NET应用程序中，我会使用HttpContext.Items集合。您正在实现IDisposable，但我见过开发人员忘记调用Dispose或将代码放在using块中的情况。|2062644|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

One thing to be wary of in your example is that ASP.NET apps should not use ThreadStatic storage, as a thread can be re-used, and if you do not clean up all your objects you end up with a connection hanging around.

In an ASP.NET app I'd use the HttpContext.Items collection instead. You are implementing IDisposable, but I've seen scenarios where devs forget to call Dispose or place the code in a using block.

I'm wondering what would be the best prectice regarding mainataining connections to the database in .Net application (ADO.NET but I guess the practice should be the same for any data layer). Should I create a database connection and propagate it throughout my application, or would it be better to just pass connection strings/factories and create a connection ad-hoc, when it is needed. 

As I understand perfomance hit is not signifcant with pooling and it allows me to recover from broken connections quite easily (just a new connection will be created) but then again a connection object is a nice, relatively high-level abstraction and creating a new connection for every operation (not SQL command, but application operation) generates additional, duplicated code and feels like a waste of time/resources(?).

What do you think about these 2 cases, what are their cons/pros and which approach are you using in your real-life applications?

Thanks

Best practices re sharing IDbConnection or connection string/factory in your .Net code

SQL Server

数据库

我想知道在.Net应用程序(ADO.NET，但我想对任何数据层都应该是一样的)中维护数据库连接的最好方法是什么。我应该创建一个数据库连接并在我的应用程序中传播它，还是只传递连接字符串/工厂并在需要时即席创建一个连接更好？据我所知，性能命中对于池化并不重要，它允许我非常容易地从断开的连接中恢复(只是创建一个新连接)，但连...

问最佳实践是在.Net代码中共享IDbConnection或连接字符串/工厂
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问最佳实践是在.Net代码中共享IDbConnection或连接字符串/工厂EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问最佳实践是在.Net代码中共享IDbConnection或连接字符串/工厂
EN