none
SubmitChanges() doesn't work properly inside foreach RRS feed

  • Question

  • I am trying to call SubmitChanges() inside foreach statment that suppose to insert records into SQL server data base.Once SubmitChanges() throws exception (for example :Binary data would be trancated bla bla bla ... ) the next statements keeps to  throw same exception and the only solution that helps is to activate Dispose() function on my datacontext object and recreate it.Creating a new instance of the DataContext for each unit of work can lead to a performance degradation.So the question is how can I keep inserting data  without recreating data context.The following is my code:

    datacontext = new SwapDBDataContext();



    foreach
    (DataRow ro in ds.Tables[0].Rows){try{

    city = new sh_city();

    city.city_code = ro["CITY_CODE"].ToString();

    city.city_ename = ro["CITY_ENAME"].ToString();

    city.city_name = ro["CITY_NAME"].ToString();

    datacontext.sh_cities.InsertOnSubmit(city);

    datacontext.SubmitChanges();

    }

    }

    catch (Exception ex)

    {

    datacontext.Dispose();

    Logger.logError("", ex);

    datacontext = new SwapDBDataContext();

    }

    }




    Tuesday, December 30, 2008 8:21 AM

All replies

  • The best practice is to use a separate DataContext for each unit of work.  However, you ought to be able to consider the entire set of inserts as a single unit of work.  Can you call SubmitChanges only once outside the foreach loop after you've called InsertOnSubmit for each one?
    Wayward LINQ Lacky
    Wednesday, December 31, 2008 8:32 PM
    Moderator
  • I am really sorry that it takes to me so much time to answer ,but it doesn't work also.
    Tuesday, January 6, 2009 1:03 PM