c# - How can i reconnect an entity dataset inside using statement when a SQLException occurs? -

the situation have long running method looping through thousands of rows , doing processing , updates. loop nested inside using statement.

the problem when came work in morning see connection failed @ 8pm sqlexception , lost 12 hours of processing time. how can reconnect , continue processing loop programatically?

my idea this:

using (var _data = new my.ef.myentities()) {     (int = 1; < 17348; i++)     {         try         {           //do lots of stuff         }         catch (sqlexception ex)         {             writelogfile("error @ id" + i.tostring(), ex);             i--;             if (_data.database.connection.state != system.data.connectionstate.open)             {                 _data.  // what?  nothing looks useful here reconnect             }         }      } } 

this dev code, not production or user environment. i'm doing seeding database 10'000's of records , linked records idea load testing, i'm not programming possibilities, want able handle dropped connection , reconnect if possible.

thanks,

i moved comment answer. recommendation without knowing you're doing in do lots of stuff.

typically ef want spend little time possible inside using block. paraphrased this ef tutorial (among others):

1. get existing object db.
2. change object name out of context scope (disconnected mode)
3. pass modified entity method dbentityentry object , mark state modified
4. call savechanges() method update object information database.

spending 12+ hours inside using context sounds needs redesigned. said, here's recommendation if modifying existing entities (from memory, may need syntax tweaking):

ienumerable<entitytypeyouvedefined> listofentities;  using (var _data = new my.ef.myentities()) {     listofentities = _data.yourtablename.where(e => e.someconditionorpropertyorwhatever);     //or other search predicate pull want change. }  foreach(var entity in listofentities) {     entity.someproperty = "what want change"; }  using (var _data = new my.ef.myentities()) {     foreach(var entity in listofentities)     {         _data.entry(entity).state = entitystate.modified;     }     _data.savechanges(); } 

here's code if you're creating new entities:

var listofentities = new list<entitytypeyouvedefined>(); (int = 0; < 17348; i++) {     var newentity = new entitytypeyouvedefined();     //build newentity relationships here     listofentities.add(newentity); }  using (var _data = new my.ef.myentities()) {     foreach (var entity in listofentities)     {         _data.attach(newentity)         //you may have set entity state modified here. can't remember, i'm doing memory     }     _data.savechanges();  } 

this minimize time spend in loop , cleaner. also, if need repeatedly, use loop outside of using block. using block handles connection database you. don't need manage outside of connection string in app/web.config file.