.NET Core EF,清理SqlConnection.CreateCommand [英] .NET Core EF, cleaning up SqlConnection.CreateCommand
问题描述
我正在使用.NET Core DI来获取 DbContext
,根据我的逻辑,我还需要在DB上执行原始SQL命令,因此,我创建 DbCommand
来执行这样的SQL(只是一个示例查询,实际的查询不太复杂,因此为了简单起见不在此处编写):
I am using .NET Core DI to get DbContext
and in my logic I need to execute raw SQL commands also on DB, so for that purpose I am creating DbCommand
to execute SQL like this(just an example query, actual one is little complex so not writing here for simplicity):
public string GetId()
{
var cmd = _context.Database.GetDbConnection().CreateCommand();
bool isOpen = cmd.Connection.State == ConnectionState.Open;
if (!isOpen)
{
cmd.Connection.Open();
}
cmd.CommandText = "Select TOP 1 ID from ABC;";
var result = (string)cmd.ExecuteScalar();
if (isOpen)
{
cmd.Connection.Close();
}
return result;
}
我的问题是,我正在使用 GetDbConnection ()
和 CreateCommand()
在DbContext上,所以我是否需要显式处置其中任何命令的结果(或将其包含在<$ c中$ c>使用语句)?
My question here is, that I am using GetDbConnection()
and CreateCommand()
on DbContext, so Do I need to explicitly dispose result of any of those commands(or enclose those in using
statement)?
还可以在 if
块中检查 cmd.Connection.State
是 ConnectionState.Open
必需的,如果DI提供了DbContext,该连接是否已经打开?
Also the if
block to check if cmd.Connection.State
is ConnectionState.Open
required, if DI is providing with DbContext, that connection will already be open?
顺便说一句,我们正在使用 AddDbContextPool
注册 DbContext
到如果重要的话,启用 DbContext
池。
BTW we are using AddDbContextPool
to register DbContext
to enable DbContext
pooling if that matters.
推荐答案
我的问题是,我在DbContext上使用
GetDbConnection()
和CreateCommand()
,所以我应该是否需要显式处理其中任何命令的结果(或将其包含在using语句中)?
My question here is, that I am using
GetDbConnection()
andCreateCommand()
on DbContext, so Do I need to explicitly dispose result of any of those commands(or enclose those in using statement)?
这些是不同的,并且
您需要做的就是遵循简单的原理-分配资源的代码负责清理它。
All you need is to follow to simple principle - the code which allocates resource is responsible for cleaning it up.
GetDbConnection
(如单词 Get
所示)可以不会创建 DbConnection
对象,而是返回 DbContext
实例在其生存期内创建和使用的对象。在这种情况下, DbContext
拥有 DbConnection
,因此您不应处置该对象(这样做可能会破坏所有者功能)。
GetDbConnection
(as indicated by the word Get
) does not create DbConnection
object, but returns the one created and used by the DbContext
instance during its lifetime. In this case the DbContext
owns the DbConnection
, so you shouldn't dispose that object (doing so could break the owner functionality).
从另一面来看, CreateCommand
确实创建新的 DbCommand
对象,所以现在您的代码拥有它并负责在不再需要它时对其进行处理。
From the other side, CreateCommand
does create new DbCommand
object, so now your code is owning it and is responsible for disposing it when not needed anymore.
同样的原理适用到打开
/ 关闭
。同样,您的代码不拥有 DbConnection
对象,因此您必须将其保留为与检索状态相同的状态。在处理需要打开连接的命令时,EF Core在内部执行此操作-首先将其打开,完成后将其关闭。除非它是从外部打开的,否则它们什么也不做。正是上述原则-如果您的代码执行打开
,那么它应该执行 Close
,否则不执行任何操作。
The same principle applies to Open
/ Close
. Again, your code is not owning the DbConnection
object, so you have to leave it in the same state as it was when you retrieved it. EF Core internally does that when processing commands which need open connection - open it at the beginning, close it when done. Except if it was opened externally, in which case they do nothing. Which is exactly the aforementioned principle - if your code does Open
, then it should do Close
, do nothing otherwise.
因此,所讨论的代码应类似于以下内容(请注意,代码的紧密逻辑中存在错误-调用 Close <的条件/ code>应该为
!isOpen
,与 Open
调用所使用的相同):
So the code in question should be something like this (note that there is a bug in close logic of your code - the condition for calling Close
should be !isOpen
, the same used for Open
call):
public string GetId()
{
using (var cmd = _context.Database.GetDbConnection().CreateCommand())
{
bool wasOpen = cmd.Connection.State == ConnectionState.Open;
if (!wasOpen) cmd.Connection.Open();
try
{
cmd.CommandText = "Select TOP 1 ID from ABC;";
var result = (string)cmd.ExecuteScalar();
return result;
}
finally
{
if (!wasOpen) cmd.Connection.Close();
}
}
}
这篇关于.NET Core EF,清理SqlConnection.CreateCommand的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!