Annoying SQL exception, probably due to some code done wrong

Question

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:

The aspx.cs Call:

busProcesso openProcess = new busProcesso(pProcessoId);
try
{
    if (openProcess.GetDocument() == null)
    {
        //Irrelevant code.
    }
}
catch{ //... }

The Business class (relevant part):

 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:

if (ps.Servico.Document != null) from the GetDocument() method.

Opening SQL Server Activity Monitor, I see there is a process for my database (.Net SqlClient Data Provider)

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.

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. The error doesn't happen everytime. Sometimes it runs just perfectly. Then it starts to give the error. Then it stops. Sometimes it happens just once.. pretty weird.

Answer 1

Here's a couple of ideas to try.

1/ You can attach SQL server profiler to see the query that is being executed, which will allow you to copy and paste that query to see the data that is in the database. This might be help.

2/ You never check whether ps.Servico is null - you jump straight to ps.Servico.Document. If ps.Servico is null then you will get a null reference exception if you try to access any properties on that object.

Answer 2

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.

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.

But the context db is now out of scope, so it crashes, on the first line that tries to use the data.

There are 2 ways to get around this:

• return a list from ListaServicosProcesso, this will force the database call to execute
• move the for each into ListaServicosProcesso

Edit

Pharabus is correct db is not out of scope. The problem is here:

 db = new RENDBDataContext();

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.

Answer 3

I'm not sure of the exact cause of the error you're seeing (if you Google it, the references are all over the place...), but there are a few things you could improve in your code and I've found that just cleaning things up a bit often makes problems go away. Not always, but often.

I agree with the other answerers that it would help to keep better track of your DataContext(s). For example in you're creating it once in the constructor, then again in ListaServicosProcesso(). At that point vProcesso is on one DataContext and other entities will be on another, which gets messy.

I think you could simplify the whole thing a bit, for example you could combine GetDocument() and ListaServicosProcesso() like this:

public string GetDocument()
{
    try
    {
        // Are you sure vProcesso is not null?
        if (vProcesso == null)
            return null;

        // Only create the context if it wasn't already created,
        if (db == null)
            db = new RENDBDataContext();


        return db.Processo_has_Servicos
            .Where(ps => ps.Processo_Id == vProcesso.Id && ps.Servico.Document != null)
            .Select(ps => ps.Servico.Document) // use an implicit join
            .SingleOrDefault();

    }
    catch (Exception ex)
    {
        throw new Exception(ex.Message, ex);
    }
}