What are the implications of declaring a static, non-static, private or public database context?

Question

Hello,

What are the implications (in each case) of declaring a database context ex: mydbEntities as either:

1. public static mydbEntities db = new mydbEntities ();

2. public mydbEntities db = new mydbEntities ();

3. private static mydbEntities db = new mydbEntities ();

4. private mydbEntities db = new mydbEntities ();

I'm using ASP.NET MVC 2. Thank you!

Answer 1

static would be very very bad here. Data-context does not play nicely with threading, plis you want to throw it away each time to avoid overuse. You could use a static property that relates to the current http context. That might make sense. Then it is just a public static property.

Many people prefer things like IOC and instance-based contexts, however; or contexts that sit behind the storage interface. The choice is yours.

Note: another option is thread-static, but it should not be assumed that all of your request is serviced by the same thread. So don't do that either.

Answer 2

This is really a Ling 2 SQL question rather than an ASP.NET MVC question (I assume that's what you're using, or maybe it's Entity Framework).

A few people advocate the DataContext per web request pattern when using L2SQL in a web application. That is you create a single DataContext and keep it for the duration of the entire currently executing Web request. To do this you need to store it in the HttpContext.Items collection like so

HttpContext.Current.Items.Add("myKey", new MyDataContext());

...and retrieve it like so

MyDataContext context = HttpContext.Current.Items["myKey"] as MyDataContext;

You probably ought to create some helper class for easier access to your DataContext and hide this code away.