0

I'm using db4o. My question is about best practices of how one should open and close the database in an ASP.NET web form?

I guess opening and closing the database before and after each save or update, is not the best practice because of the overhead this implies, lost of indexes, etc.

I have a "System" class that uses Singleton pattern, and it's the only class that connects to the database. On the constructor, I open the database, but I'm not sure when to close it, or even when to commit.

  1. Should I commit EVERY TIME after a save if success and rollback if error?

  2. When should I close or dispose the database? Should I implement IDisposable interface? Should I add a Destructor to my "System" class?

Gonzalo
  • 982
  • 6
  • 23
  • 39

2 Answers2

4

I guess opening and closing the database before and after each save or update, is not the best practice because of the overhead this implies, lost of indexes, etc.

It is the better practice. The performance issues are taken care of by Connection Pooling.

When you have a couple of Save and Updates in the same scope, use 1 connection.

But don't try to cache the connection. Correctly freeing it is much more important.

As a rule of thumb, only use connection variables as local vars, preferably in a using() {} statement. Don't make them fields/properties, that would indeed require IDisposable etc. Try to avoid it.

H H
  • 263,252
  • 30
  • 330
  • 514
  • Thanks Henk. I just found this question from the founder of db4o and he says opening and closing the database is not a good idea... I guess working with db4o is in fact different than other database engines. – Gonzalo Mar 06 '12 at 14:06
  • My answer does indeed apply to databases in general, I don't know db4o. – H H Mar 06 '12 at 14:09
  • Thanks Henk. Here's a link to the question: http://stackoverflow.com/questions/2998794/is-it-ok-to-open-a-db4o-file-for-query-insert-update-multiple-times – Gonzalo Mar 06 '12 at 14:13
0

When you commit is up to you, not the connector. I would argue closing is just as specific; if you're done with the database in the first 3 calls of the page why keep it open for the rest of the processing?

It seems to me these questions are more about best practices than database activity and design.

Brad Christie
  • 100,477
  • 16
  • 156
  • 200