ASP.NET DataProvider Singleton Vs. Static Vs. Instantiate When Needed

I have seen all versions used a hundred times.  Which is the best way in a closed shop ASP.NET application using strictly SQL Server for data storage?
 
Do you instantiate a singleton copy of the dataprovider for the entire application to use?  Or just a bunch of static methods (yuch)? Or do you instantiate the dataprovider every time needed?  Are there threading issues involved  here?  What about transactions?
Published Thursday, August 11, 2005 11:28 AM by Travis
Filed under:

Comments

# re: ASP.NET DataProvider Singleton Vs. Static Vs. Instantiate When Needed

Thursday, August 11, 2005 2:21 PM by Dave
Well, if you use the sqlhelper class instead of writing your own custom library I think that is the best way to go.
When people quit, or if you bring on a contractor, they should be able to jump right in.

# re: ASP.NET DataProvider Singleton Vs. Static Vs. Instantiate When Needed

Thursday, August 11, 2005 3:42 PM by Richard
Why a singleton?? Only use singletons when you really need to. Possible examples are for queuing or synchronization. Singletons can cause concurrency/multithreading issues as well as performance bottlenecks.

I would say instantiate when needed. ADO.net leverages connection pooling in order to minimize the creation of new connections. Just try to minimize the duration of database connections, so that the connection is made available again in the pool for the next database transaction.

# re: ASP.NET DataProvider Singleton Vs. Static Vs. Instantiate When Needed

Thursday, August 11, 2005 4:06 PM by Jeff Gonzalez
We use static methods off the SqlHelper class. We hacked up SqlHelper pretty good, but the principal remains the same.

On our dataabstraction classes we have manager classes that expose a static property with the double check locking to avoid the problems that Richard mentioned.

You can find an implementation of this here http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnpatterns/html/ImpSingletonInCsharp.asp

The static singleton property allows us to reduce code and use inheritance (static methods can't be inherited) at the same time.

Leave a Comment

(required) 
(required) 
(optional)
(required)