Understand using in Linq To SQL

Question

I am working with LINQ To SQL connection in my ASP.NET website but I have some strange problems. I created Singleton entity of DataContext for all of the using in the website but I don't really understand why the data not always updated and sometimes adding row to table failed because another entity with the same key already exists (I checked and that is no such entity).

What's the problem and there is another way to use this connection?

public class SQLConnection
{
    private LearningDBDataContext dbDataContext;

    public SQLConnection()
    {
        dbDataContext = new LearningDBDataContext();
    }

    public LearningDBDataContext GetDataContextInstance()
    {
        return dbDataContext;
    }

    public void reCreateDBConnection()
    {
        dbDataContext = new LearningDBDataContext();
    }
}

Don't create a singleton, the context should be short lived. You're also not disposing of the context anywhere and thus you are leaking connections. — Matthew, May 02 '14 at 20:33
Unlike forum sites, we don't use "Thanks", or "Any help appreciated", or signatures on [so]. See "[Should 'Hi', 'thanks,' taglines, and salutations be removed from posts?](http://meta.stackoverflow.com/questions/2950/should-hi-thanks-taglines-and-salutations-be-removed-from-posts). — John Saunders, May 02 '14 at 20:35
so every time that i need information from data-base in need to open new connection? — user1940350, May 02 '14 at 20:47

score 1 · Accepted Answer · edited May 23 '17 at 11:58

1

Definitely don't use a singleton. If you are using an IoC container then you can set the context to be shortlived - one instance of the context per request. Or in your data access layer, wrap any datacontext instantiations with a using statement to be sure that it is properly disposed of, i.e:

MyModel model;
using(var context = new LearningDBDataContext()){
     // fetch your data here
     model = ...
}
return model;

Check out this SO post and this MSDN site for more info on this subject.

edited May 23 '17 at 11:58

Community

1
1

answered May 02 '14 at 20:47

rwisch45

3,692
2
25
36

Now i have another problem. "Cannot access a disposed object. Object name: 'DataContext accessed after Dispose.'." ? – user1940350 May 03 '14 at 07:37
i created kinf od Model-View-Controller the controller returned list of Teachers table. In the view i iterate on the list and try to access to teacher.Schedule. (schedule is another related table) then i got this exception. – user1940350 May 03 '14 at 18:40

Understand using in Linq To SQL

1 Answers1