views:

128

answers:

3

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.

+2  A: 

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.

Kirk Broadhurst
Tried opening the Server Profiler, the 2 queries (first for Processo_has_Servico, and second for Servico) are pretty normal.
Vitor Reis
+3  A: 

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.

Shiraz Bhaiji
What if the 2 methods are isolated form each other, and I create another datacontext on "GetDocument", and do not call "ListaServicosProcesso" from inside of it?
Vitor Reis
are you sure it is out of scope? it seems to be declared at the class level
Pharabus
@Pharabus, thanks for the comment, you are correct, I have updated my answer
Shiraz Bhaiji
+1  A: 

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);
    }
}
shaunmartin
That's exactly what I meant when I said I was accepting suggestions. Thank you for the contributin. I'll try all suggestions by morning and see if any solves the problem.
Vitor Reis
shaun, what if I need something similar (using the same methodology of your code) to update the table Processo_has_Servico? (right now i'm using a foreach then db.submitchanges()
Vitor Reis
Modifying your entity objects and calling db.SubmitChanges() is the most fundamental method for saving in LINQ to SQL and will work, assuming you're using the same DataContext object for both operations. However you may want to adopt an established pattern, especially if you're still early in the development process. A very good example of a popular (repository) pattern can be found here: http://nerddinnerbook.s3.amazonaws.com/Part3.htm (You may already know about that.) It has lots of example code and might be helpful to you.
shaunmartin