-1

I would like to know what is the best practice to work with C# and SQL Server (entity framework) between the two examples below :

1- open connection each time you want to work with SQL server and close it right after ? (I use entity framework using statement)

2- open connection once and keep it up as long as needed for all the tasks that need to be done even if there is code that doesn't need the SQL connection open to run ? ( I would control the context myself without the using statement in this scenario)

I know this question is not much complicated but I'm still a newbie in the domain of database and programming.

Many thanks for your answers!

Mr. Bone
  • 69
  • 7
  • Depends on what you mean by `"as long as needed for all the tasks that need to be done"`. What tasks? What are you actually doing? Can you provide examples? – David Apr 18 '17 at 13:51
  • 5
    This question is too broad AND opinion based - there isn't a single correct answer. – SQLMason Apr 18 '17 at 13:52
  • 1
    I disagree on the `too broad` claim. EF is known to slow down it's context when applying lots of changes (due to the change tracker). At best this would be a duplicate but this should be communicated to OP. – Stefan Apr 18 '17 at 13:54
  • Examples: http://stackoverflow.com/questions/5943394/why-is-inserting-entities-in-ef-4-1-so-slow-compared-to-objectcontext#5943699 , https://weblog.west-wind.com/posts/2014/Dec/21/Gotcha-Entity-Framework-gets-slow-in-long-Iteration-Loops , http://stackoverflow.com/questions/10103310/dbcontext-is-very-slow-when-adding-and-deleting – Stefan Apr 18 '17 at 13:55
  • For the record; this will argue that a short lived context is preferred. As applies to all memory management related questions by the way. Unless there is a justified reason (non given) all object must be disposed when dealt with. – Stefan Apr 18 '17 at 14:15
  • Possible duplicate of https://stackoverflow.com/questions/18947970/should-the-dbcontext-in-ef-have-a-short-life-span – Michael Freidgeim Apr 29 '20 at 22:33

2 Answers2

3

Open and close as quickly as possible. Let ADO.Net connection pooling take care of minimizing overhead of actually connecting to the database over and over. As long as you use the same connection string, closing a connection does not actually close it. It just releases it back to ADO.Net connection pool. The longer you keep it open, (and not in the pool), the more actual connections the pool may need to create to service the database requests.

Charles Bretana
  • 143,358
  • 22
  • 150
  • 216
0

Mainly depends on what kind of application you're designing, but it's great idea to close them as soon as possible (but not necessarily right after performing a single query, just to re-open it five lines of code later - reopening connection takes time too and on high-ping connections this might become an unbearable user experience).

Just be sure to close the connection whenever you end a task that requires an open connection (for example gathering data from DB) and start another task (for example processing data) or await user input. Remember you can pass the connection as parameter to methods you might be calling too, to conserve the connection pool.

schroedingersKat
  • 189
  • 8
  • Unless you explicitly code to manage connections yourself, ADO.Net handles this for you. "Closing" (or "Opening") a connection does not actually close or open it. It just sends a request to the ADO.Net framework connection pooling service, which (when closing), puts the connection back in the pool to be reused, or (Opening) fetches another open [available] connection from the pool. The pool only creates a new connection when it is empty and a new request comes in. – Charles Bretana Apr 19 '17 at 16:53