我的数据库连接关闭了吗? (Linq 到 Sql)
我正在使用 Linq to SQL 并阅读了有关尽快关闭数据库连接的博客文章。作为示例,他们展示了一个变量被转换为列表(使用 .ToList()),而不是实际返回 Linq 查询。我有以下代码:
public static bool HasPassword(string userId)
{
ProjDataContext db = new ProjDataContext();
bool hasPassword = (from p in db.tblSpecUser
where p.UserID == userId
select p.HasPassword).FirstOrDefault();
return hasPassword;
}
该查询可以吗?或者数据库连接保持打开状态的时间是否会超过必要的时间?
感谢您的任何建议
I'm using Linq to SQL and read in a blog post about closing database connections as soon as possible. As an example, they showed a variable being converted to a list (using .ToList()) instead of actually returning the Linq query. I have the below code:
public static bool HasPassword(string userId)
{
ProjDataContext db = new ProjDataContext();
bool hasPassword = (from p in db.tblSpecUser
where p.UserID == userId
select p.HasPassword).FirstOrDefault();
return hasPassword;
}
Is that query fine? Or will the database connection remain open for longer than necessary?
Thank you for any advice
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(5)
连接将被自动管理。然而,还有(或者至少可以像评论所建议的那样)与 DataContext 相关的其他资源。直到 DataContext 被垃圾收集器销毁后,这些资源才会被释放。因此,通常最好确保当您不再需要 DataContext 时调用 dispose。
这里确保在 using 块退出时调用 db.Dispose() ,从而显式关闭连接。
编辑:在讨论之后,我查看了自己的 DataContext 处置(也使用 Reflector)并发现了从
DataContext.Dispose
调用的以下代码(FW 3.5): 是被释放的资源:
DbConnection
、日志(TextWriter
)和<代码>DbTransaction。CommonDataServices
。LoadOptions
。提供者可能持有需要处置的资源(
DbConnection
和DbTransaction
)。此外,日志的TextWriter
可能必须被释放,具体取决于用户分配给DataContext
日志记录的TextWriter
实例机制,例如 FileWriter 然后自动关闭。据我了解,其他属性仅保留内存(无需过多查看细节),但这也可以通过 dispose 方法用于垃圾回收,但是,内存实际释放的时间并不确定。
所以,最后我完全同意casparOne的说法:
The connection will be managed automatically. However, there are (or at least can be as the comments suggest) additional resouces associated with the DataContext. These resources will not be released until the DataContext is destroyed by the garbage collector. So, it is usually better to make sure that dispose is called when you don't need the DataContext anymore.
Here it is ensured that
db.Dispose()
is called when the using block exits, thus closing the connection explicitly.Edit: Following the discussion I looked at the DataContext dispose myself (also using Reflector) and found the following code (FW 3.5) which gets called from
DataContext.Dispose
:So there are resources that gets freed:
DbConnection
, a log (TextWriter
) and aDbTransaction
.CommonDataServices
.LoadOptions
.The provider may hold resources that needs to be disposed (
DbConnection
andDbTransaction
). Also theTextWriter
for the log may have to be disposed, depending upon what instance of theTextWriter
the user has assigned to theDataContext
's logging mechanism, e.g. a FileWriter that then gets closed automatically.The other properties hold, as far as I understand them -without looking too much into detail - only memory, but this is also made available for garbage collection by the dispose method, however, the it is not determined when the memory actually gets freed.
So, finally I totally agree with casparOne's statement:
从实现的角度来看,不,你没有什么可担心的。但是,这不是由于查询,而是由于 DataContext 本身。
DataContext 类实现 IDisposable 接口,因此您应该调用每当您使用完 DataContext 实现后,就对其进行处置。
现在,众所周知的事实是,在 DataContext 实例上调用 Dispose 不会执行任何操作,因此不是 技术上需要。
不幸的是,这也是非常糟糕的做法。您应该始终根据合同而不是实现进行编码。因为 DataContext 实现了 IDisposable,所以您应该关闭它,即使您知道它什么都不做,因为这在未来的实现中绝对可能会发生变化。
另外,如果您切换到另一个 LINQ 提供程序(例如 LINQ-to-Entities),那么您必须在完成后调用 Dispose,因为数据库连接的生命周期在 ObjectContext 实例(也实现 IDisposable)非常不同,并且调用 Dispose对这些数据库连接有影响。
话虽如此,你还有一个更大的担忧。如果您共享一个 DataContext,则可能会面临跟踪过多对象的风险。除非您设置了 ObjectTrackingEnabled 属性 设置为 false 时,DataContext 正在跟踪通过它选择的每个对象。如果您在应用程序的生命周期内没有执行任何更新操作(或者即使您执行了更新操作),则专用于共享 DataContext 上的对象跟踪的资源数量可能会变得相当大。
使用其他数据库技术开发的规则(例如 System.Data.SqlClient 命名空间)仍然适用。
一般来说,像这样共享数据访问资源是一个坏主意。
您应该创建资源来访问数据库、执行操作,然后在完成后处置它们。
From an implementation point of view, no, you don't have anything to worry about. However, it's not due to the query, but to the management of the DataContext itself.
The DataContext class implements the IDisposable interface, so you should call Dispose on the DataContext implementation whenever you are done with it.
Now, it's a well known fact that calling Dispose on DataContext instances do nothing, and therefore are not technically required.
Unfortunately, it's also very bad practice. You should always code against the contract, not the implementation. Because DataContext implements IDisposable, you should close it, even if you know it does nothing because that could absolutely change in future implementations.
Also, if you switch to another LINQ provider, say LINQ-to-Entities, then you must call Dispose when you are done, because the lifetime of database connections in ObjectContext instances (which also implements IDisposable) is very different, and a call to Dispose has an impact on those database connections.
All that being said, you have a bigger concern. If you are sharing one DataContext, you run the risk of tracking too many objects. Unless you have set the ObjectTrackingEnabled property to false, the DataContext is tracking every object selected through it. If you are doing no update operations (or even if you are) over the lifetime of the app, the number of resources being dedicated for object tracking on a shared DataContext could become considerable.
The rules developing using for other database technologies (e.g. the classes in the System.Data.SqlClient namespace) still apply.
In general, sharing data-access resources like this is a bad idea.
You should create your resources to access the DB, perform your operations, and then dispose of them when done.
使用 Linq-To-SQL,您通常不需要关心具体打开和关闭作为上下文对象(示例中的 db)一部分的连接。大约唯一需要专门执行此操作的情况是,如果您通过上下文对象发送直接 SQL 调用,而不是使用 Linq。
使用 L2S,您通常希望创建上下文对象,执行工作单元,然后尽快处理该对象。你的代码示例对我来说看起来不错。
With Linq-To-SQL you generally don't need to be concerned about specifically opening and closing the connection that is part of the context object (db in your example). About the only time you would have to specifically do this is if you were sending direct SQL calls through the context object, instead of using Linq.
With L2S, you generally want to create your context object, do your unit of work, and then dispose of the object, as quickly as possible. Your code example looks fine to me.
我认为使用 using 语句是一个很好的做法。但我认为你的询问没有什么不好。
I think is a good practice to use the using statement. But I think there is nothing bad with your query.
在您的数据库对象不再存在(处置)或显式关闭后,数据库连接将立即关闭。
在您的示例中,它将(迟早)被垃圾收集。
The database connection will be closed immediately after your db object will no longer exist (disposed) or explicitly closed.
In your sample it will get (sooner or later) garbage collected.