恼人的SQL异常，可能是由于一些code做错了 [英] Annoying SQL exception, probably due to some code done wrong
问题描述
我开始对这个已经开始项目的工作，和我在尝试与执行一些交互时非常恼人的错误的SQL Server 2008：
I started working on this "already started" project, and I'm having a really annoying error when trying to execute some interactions with SQL Server 2008:
服务器无法恢复
交易。说明：
The server failed to resume the transaction. Desc.:
一这些错误我在这个特定的方法调用获得：
One of these errors I get in this specific method call:
的aspx.cs电话：
busProcesso openProcess = new busProcesso(pProcessoId);
try
{
if (openProcess.GetDocument() == null)
{
//Irrelevant code.
}
}
catch{ //... }
商业类（相关部分）：
public class busProcesso : IbusProcesso
{
public Processo vProcesso { get; set; }
RENDBDataContext db;
public busProcesso()
{
vProcesso = new Processo();
}
public busProcesso(decimal pProcessoId)
{
db = new RENDBDataContext();
try
{
vProcesso = db.Processos.SingleOrDefault(x => x.Id == pProcessoId);
}
catch (Exception ex)
{
throw new Exception(ex.Message, ex);
}
}
public string GetDocument()
{
try
{
string document = null;
foreach (Processo_has_Servico ps in ListaServicosProcesso())
{
if (ps.Servico.Document != null) //Get the error right at this line.
{
document = ps.Servico.Document;
}
}
return document ;
}
catch (Exception ex)
{
throw new Exception(ex.Message, ex);
}
}
public IQueryable<Processo_has_Servico> ListaServicosProcesso()
{
db = new RENDBDataContext();
try
{
return from ps in db.Processo_has_Servicos
join s in db.Servicos on ps.Servico_Id equals s.Id
where ps.Processo_Id == vProcesso.Id
select ps;
}
catch (Exception ex)
{
throw new Exception(ex.Message, ex);
}
}
}
正如我所说的，出现错误就在该行：
As I said, the error occurs right at the line:
如果（ps.Servico.Document！= NULL）从 GetDocument（）方法。
打开SQL Server的活动监视器，我看到有一个过程，我的数据库（.NET SqlClient数据提供程序）
Opening SQL Server Activity Monitor, I see there is a process for my database (.Net SqlClient Data Provider)
经过一段时间/使用（当我开始得到了服务器无法恢复交易的错误），我去到SQL Server活动监视器，并有大约5或6的未死亡，更多相同的过程（可能）应该已经。当我手动杀了他们，误差一段时间停止，直到它再次启动。
After some time/use (when I start to get the "server failed to resume the transaction" error), I go to the SQL Server Activity Monitor and there's around 5 or 6 more identical processes that weren't killed and (probably) should've been. When I manually kill them, the error stops for a while, until it starts again.
我不是在OO和所有工作真的很好，所以我可能失去了一些东西，也许一些方法来关闭这些连接之一。此外，关于此结构的任何帮助/提示将受到欢迎。
I'm not really good at working in OO and all, so I'm probably missing something, maybe some way to close one of these connections. Also, any help/tip about this structure will be welcome.
PS。该错误不会发生每次。有时，它运行得非常完美。然后开始，得到错误。然后停止。有时，它发生一次.. pretty怪异。
推荐答案
在ListaServicosProcesso的code为创建上下文分贝。然后，它返回一个IQueryable。
The code in ListaServicosProcesso is creating the context db. Then it is returning an IQueryable.
此时没有请求已被发送到数据库。
At this point no request has been sent to the database.
接下来就是为每个在code。此时EF说：我需要从数据库中获取数据。因此，它试图获取数据。
Then there is a for each in the code. At this point EF says "I need to get the data from the database". So it tries to get the data.
但背景DB现在出的范围，所以它崩溃，上试图使用数据的第一行。
But the context db is now out of scope, so it crashes, on the first line that tries to use the data.
有2种方式来解决这个问题：
There are 2 ways to get around this:
- 返回从ListaServicosProcesso名单，这将迫使数据库调用执行
- 将在每个进ListaServicosProcesso
修改
Pharabus是正确的分贝是不是超出范围。问题就在这里：
Pharabus is correct db is not out of scope. The problem is here:
db = new RENDBDataContext();
上下文的新实例时未经旧布置创建。尝试分贝的处置在ListaServicosProcesso的结束。更妙的地方分贝using语句。但随后的foreach必须在using语句内移动。
A new instance of the context is being created without the old one being disposed. Try Dispose of db at the end of ListaServicosProcesso. Even better place db in a using statement. But then the foreach must be moved inside the using statement.
这篇关于恼人的SQL异常，可能是由于一些code做错了的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
