Geek Answers Handbook

Linq2SQL, use dataContext after exception

Here is a small piece of code to test and explain the problem. I have a Person table with 3 fields:

  • Id
  • FirstName (cannot be null)
  • LastName (not NULL)

In the loop:

  • First: I insert the first line ... normal,
  • Secondly: I'm trying to insert the wrong element, Exception ... normal
  • Third: I try to insert the third row ... Exception (same as the second), but the values ​​are correct.

Is there anything to be done to use the same dataContext after the Exception ?

 public class MyTestClass { private readonly DataModelDataContext _dataContext; public MyTestClass() { _dataContext = new DataModelDataContext(); } public void InsertList() { List<Person> liste = new List<Person>(); liste.Add(new Person { FirstName = "AAA", LastName = "BBBB" }); liste.Add(new Person { FirstName = string.Empty, LastName = null }); liste.Add(new Person { FirstName = "CCC", LastName = "DDD" }); foreach (var item in liste) { try { _dataContext.Persons.InsertOnSubmit(item); _dataContext.SubmitChanges(); } catch (Exception ex) { Console.WriteLine(ex.Message); } } } }
+4
source share
1 answer

DataContext should always be short-lived. To achieve this, you must rethink your design.

You can consider these points:

  • Use context for each transaction. This way you can make context short-lived in a meaningful way.
  • Rollback on a failed transaction. This includes inserting the correct item with a new transaction. This means that you now have a new context.
  • Repeat the process until the transaction is completed.
  • Again, remember to end the context when done. This can be easily achieved using the using statement.

Note from MSDN :

The DataContext is the source of all objects mapped to the database connection. It keeps track of the changes you make to all received objects and maintains an "identity cache", which ensures that entities received more than once are represented using the same instance object.

In general, a DataContext instance is designed to last one “block of work,” however your application defines this term. A DataContext is lightweight and not expensive to create. A typical LINQ to SQL application creates DataContext instances in a method scope or as a member of short-lived classes. which are a logical set of related database operations.

If you need time to recalculate your design, temporarily you can do it as follows:

  public void InsertList(List<Person> people) { foreach (var person in people) { DoInsert(person); // You can use the returned flag and implement the logic if desired. // Or let the loop move on its ways. } } public bool DoInsert(Person person) { try { using (DataModelDataContext dataContext = new DataModelDataContext()) { dataContext.Persons.InsertOnSubmit(person); dataContext.SubmitChanges(); } return true; } catch (Exception ex) { Console.WriteLine(ex.Message); return false; } }

and you can call the InserList method:

  List<Person> liste = new List<Person>(); liste.Add(new Person { FirstName = "AAA", LastName = "BBBB" }); liste.Add(new Person { FirstName = string.Empty, LastName = null }); liste.Add(new Person { FirstName = "CCC", LastName = "DDD" }); InsertList(liste);
+3
source

More articles:


All Articles