CODEWITHSUNDEEP
c#sql-serverentity-framework-6dispose
Benny Ae
Benny Ae

Reputation: 2016

Dispose not working, many dead connections

I'm getting strange things since updated to EF6,no sure this is related or not, but used to be good

I'm doing a set of work, then save it to DB , then do another , save another.

after a while,i check SQL server by sp_who2 , i found many dead connections from my computer.

Job is huge then there goes to 700 connections, I have to kill them all manually in cycle.

program like:

while (jobDone == false)
{
     var returnData=doOneSetJob();
     myEntity dbconn= new myEntity;

     foreach( var one in retrunData) 
     {

        dbconn.targetTable.add(one );
        try
        {  
            dbconn.savechange();
            /// even i put a dispose() here , still lots of dead connections
        }
        catch
        {
           console.writeline("DB Insertion Fail.");
           dbconn.dispose();
           dbconn= new myEntity();
         }
     }

     dbconn.dispose()
}

Upvotes: 0

Views: 353

Answers (2)

Jhon
Jhon

Reputation: 582

In this case you should use a using statement. Taken from MSDN:

The using statement ensures that Dispose is called even if an exception occurs while you are calling methods on the object. You can achieve the same result by putting the object inside a try block and then calling Dispose in a finally block; in fact, this is how the using statement is translated by the compiler.

So, your code would look better like this:

using(var dbconn = new DbContext())
{
    while (!jobDone)
    {
        foreach(var one in retrunData)
        {
            try
            {
                targetTable row = new TargetTable();
                dbconn.TargetTable.add(row);

                dbconn.SaveChanges();
            }
            catch (Exception ex)
            {
                Console.WriteLine("DB Insertion Fail.");
            }
        }
    }
}

This way, even if your code fails at some point, the Context, resources and connections will be properly disposed.

Upvotes: 1

Mun
Mun

Reputation: 14308

You should consider refactoring your code so that your connection is cleaned up after your job is complete. For example:

using (var context = new DbContext())
{
        while (!jobDone)
        {
            // Execute job and get data
            var returnData = doOneSetJob();

            // Process job results
            foreach (var one in returnData)
            {
                try
                {
                    context.TargetTable.Add(one);
                    context.SaveChanges();
                }
                catch (Exception ex)
                {
                    // Log the error
                }
            }
        }
}

The using statement will guarantee that your context is cleaned up properly, even if an error occurs while you are looping through the results.

Upvotes: 2

Related Questions