C# - 关闭 Sql 对象最佳实践
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/328005/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
C# - closing Sql objects best practice
提问by LeJeune
If you have a C# function with Sqlaccess, is it mandatory to close all objects/handles, or is everything cleaned up automatically once you exit the function
如果您有一个带有 Sqlaccess 的 C# 函数，是否必须关闭所有对象/句柄，或者退出函数后是否会自动清除所有内容
For example:
例如：
void DoSqlStuff()
{
SqlConnection sqlConn = new SqlConnection(...);
SqlCommand cmd = new SqlCommand(...);
SqlDataReader sqlData= null;
sqlConn,Open();
sqlData = cmd.ExecutReader();
while(sqlData.Read())
{
...
}
}
Is it optional, recommended or mandatory to close SqlConn and SqlData?
关闭 SqlConn 和 SqlData 是可选的、推荐的还是强制的？
Thanks.
谢谢。
采纳答案by Kevin Tighe
You should close the SqlConnection object as soon as you're done with it. If you don't then the connection will remain open, and will not be available to handle other requests.
您应该在完成 SqlConnection 对象后立即关闭它。如果不这样做，则连接将保持打开状态，并且无法处理其他请求。
The using statement is useful for this. It will call Dispose() on the object for you:
using 语句对此很有用。它将为您调用对象上的 Dispose()：
using (SqlConnection cn = new SqlConnection(connectionString))
{
SqlCommand cm = new SqlCommand(commandString, cn)
cn.Open();
cm.ExecuteNonQuery();
}
回答by Hans Passant
All three classes have a Dispose() method. Mandatory is too strong, but definitely highly recommended you use the using keyword so Dispose() is automatically called. Failing to do so makes your program run "heavy", using more system resources than necessary. And outright failure when you don't use the "new" keyword enough to trigger the garbage collector.
所有三个类都有一个 Dispose() 方法。强制性太强了，但绝对强烈建议您使用 using 关键字，以便自动调用 Dispose()。不这样做会使您的程序运行“繁重”，使用比必要更多的系统资源。当您没有使用足够多的“new”关键字来触发垃圾收集器时，就会彻底失败。
回答by Hans Passant
Any class handling SQL stuff like Connections should implement the IDisposable interface as stated by Microsoft .NET coding guidelines.
任何处理 SQL 内容（如 Connections）的类都应实现 Microsoft .NET 编码指南所述的 IDisposable 接口。
Thus, you should probably close and dispose your connection in your Dispose method.
因此，您可能应该在 Dispose 方法中关闭并处理您的连接。
回答by devio
You should close everything before returning from the function. Open datareaders mean open cursors on the database, resulting in increased memory usage. Same goes for database connections.
在从函数返回之前，您应该关闭所有内容。打开数据读取器意味着打开数据库上的游标，导致内存使用量增加。数据库连接也是如此。
Unused objects are not immediately freed in C#, but only when garbage collection is performed, which is not deterministic.
在 C# 中不会立即释放未使用的对象，而是仅在执行垃圾收集时才释放，这是不确定的。
回答by Paul
Calling Close on the SQL connection won't actually close it, but will return it to a connection pool to be reused, improving performance.
对 SQL 连接调用 Close 不会实际关闭它，但会将其返回到连接池以供重用，从而提高性能。
Additionally it is generally poor practice to not explicitly dispose of unmanaged resources when you are finished with them (asap).
此外，当您完成（尽快）非托管资源时，不明确处理它们通常是不好的做法。
回答by matt_dev
You don't need to have a separate using statement for the SqlDataReader (as well as one using statement for the connection) unless you plan do perform other operations with the connection after the SqlDataReader has fully read through the row set.
您不需要为 SqlDataReader 使用单独的 using 语句（以及用于连接的一个 using 语句），除非您计划在 SqlDataReader 完全读取行集后对连接执行其他操作。
If you are just opening a connection, reading some data using the reader, and then closing the connection, then one using statement for the entire block of code (surrounding the connection) will suffice as the garbage collector will clean up all resources tied to the connection that is disposed by the first using statement.
如果您只是打开一个连接，使用读取器读取一些数据，然后关闭连接，那么整个代码块（连接周围）的一个 using 语句就足够了，因为垃圾收集器将清理与连接相关的所有资源由第一个 using 语句处理的连接。
Anyway, here's a good articlethat describes it all...
回答by cweston
Explicit disposing in the finally statement is another approach, although the usingstatement is a much better solution. It produces a bit more code, but demonstrates the goal...
在 finally 语句中显式处理是另一种方法，尽管该using语句是一个更好的解决方案。它产生了更多的代码，但展示了目标......
SqlConnection conn = null;
try
{
//create connection
SqlCommand cmd = null;
try
{
//create command
SqlDataReader reader = null;
try
{
//create reader
}
finally
{
reader.Dispose();
}
}
finally
{
cmd.Dispose();
}
}
finally
{
conn.Dispose();
}
回答by David Lean
Be careful with absolutes here. A lot depends on what you are doing & where the inefficiencies may lie. In a Web Page where each user has a separate security context you may have no choice but to establish a new SQL connection with new security credentials with each page hit. Clearly nicer if you can use a pool of SQL connections with a shared security context & let the Web page filter the results but perhaps you can't.
这里要小心绝对值。很大程度上取决于您在做什么以及效率低下的地方。在每个用户都有单独的安全上下文的 Web 页面中，您可能别无选择，只能在每个页面点击时使用新的安全凭证建立新的 SQL 连接。如果您可以使用具有共享安全上下文的 SQL 连接池并让网页过滤结果，那显然会更好，但也许您不能。
In early versions of SQL Server ie (v6.5 or less) the Login Authentication was done by SQL Server. Also SQL was severely constrained by connection memory & the number of active connections it could handle. So it was a great idea to drop your connection when not in use. Post v6.5, most people use Windows Authentication to login to SQL. This causes a lot of network calls between servers & some latency. Kerberos Security is even more chatty, Thus establishing a SQL connection is expensive. For that reason you need to find a balance between Holding a connection open for the life of your WinForms application vs Opening & closing it within each method call.
在 SQL Server 的早期版本（即 v6.5 或更低版本）中，登录身份验证由 SQL Server 完成。SQL 也受到连接内存和它可以处理的活动连接数的严重限制。因此，在不使用时断开连接是个好主意。在 v6.5 之后，大多数人使用 Windows 身份验证来登录 SQL。这会导致服务器之间的大量网络调用和一些延迟。Kerberos 安全性更加健谈，因此建立 SQL 连接的成本很高。因此，您需要在 WinForms 应用程序的生命周期内保持连接打开与在每个方法调用中打开和关闭连接之间找到平衡。
As a rough guide, if you think your app is going to want to talk to SQL in the next, say 30 secs. Keep the established connection open. If they've minimised your app, not touched it within a timeout period, or you've got all the data in RAM & they are unlikely to need anything more from the SQL system. Close the connection.
作为一个粗略的指南，如果您认为您的应用程序将要在接下来的时间与 SQL 对话，请说 30 秒。保持已建立的连接打开。如果他们最小化了您的应用程序，在超时期限内未触及它，或者您已将所有数据保存在 RAM 中，并且他们不太可能需要来自 SQL 系统的更多信息。关闭连接。
Consider creating a Class with a System Timer to hold the connection. Your class will always provide a valid connection, but perhaps the class will choose to drop it & free the connection load on SQL when appropriate.
考虑创建一个带有系统定时器的类来保持连接。您的类将始终提供有效的连接，但该类可能会在适当的时候选择删除它并释放 SQL 上的连接负载。
Unless you are also writing Server based code, a small amount of memory inefficiency might not even be noticed. But 2-10,000 clients all poorly using your Security & Data Servers is likely to bring your Data Centre to its knees.
除非您也在编写基于服务器的代码，否则可能不会注意到少量内存效率低下。但是 2-10,000 个客户都没有很好地使用您的安全和数据服务器，很可能会让您的数据中心瘫痪。
