To access the database, you need a DataContext instance. But how to effectively use it?

Make it global for the whole project? Then for simultaneous requests errors of the following type will be issued:

"Specified cast is not valid."

or

"InvalidCastException"

or

"DataReader is closed".

Make a single copy for one page? Then, too, you can get the same exceptions, if you run a lot of requests from the page.

Do each new instance for any request? Then this is quite a long time (as I understand it: the creation of an object + a connection to the database). But errors then do not appear.

What to do? Tell me! Maybe there are other solutions? It would be desirable that it was possible to send many requests simultaneously and the speed was as fast as possible :)

    3 answers 3

    Try using a separate DataContext for each stream. The easiest way is to declare it static with the ThreadLocal attribute. True, there is one drawback - it is not clear how to release it later. The solution depends on how parallel execution is implemented - a thread pool is used or a thread is created anew for each task.

      It is better to create a DataContext at the place of use, in the using block. The connection pool used there will automatically resolve all that is required for a quick connection to the database. ADO.NET connections are not thread-safe; you cannot use one global connection instance (which is used within a DataContext ) in a single multi-threaded application. Especially with transactions - in two accounts, you can run into a deadlock.

      In addition, you need to take care that the data context is not recreated too often. In other words, that your application addressed to a DB rather large blocks.

      • And if there are a lot of methods that need to connect to the database and 1 method calls method 2, which calls method 3, which calls method 4, then 4 instances will be created ???? Does not look very effective. It turns out you have chosen the worst performance option (3) of those that I suggested ... - megacoder
      • In this case, the methods must take an instance of the DataContext and access it. And already the code that calls them at the top level should create an instance of the DataContext and pass it. Besides. You looked, how many DataContext forms? Are you sure this will be a bottleneck, even if it is recreated every time? - Modus
      • Not sure, that's why I asked :) - megacoder

      See answers to similar questions: