Yeah you could do all that .. but I still feel like sharing a connection
object is not as good a solution as letting ADO.NET connection pool for
you. (Just my views before we start a war).
Here are my reasons --
a) You end up writing an application that cannot leverage connection
pools - thus locking yourself in an architecture where you will always be
restricted to a limited user scenario - this being much lesser than
hundereds of users atleast in the case of Sql Server.
b) The performance difference between connection pooled and connection
object shared is not that much. And you are writing extra code for that
and maintaining extra code and debugging extra code and checking in/out
extra code and .... .. ..
c) You are reinventing the wheel - if microsoft ever changes their
paradigm, you will be left in high water - just like I worked with this
company where they insisted on other threads twiddling with the form's
UI - the argument I heard back then was - this is non-blocking, the user
is much slower than the pc .. blah blah and they went ahead and put that
in their base classes - and now with .NET 2.0, they will get a nasty
exception - so essentially they will now not be able to upgrade to 2.0.
Not to mention, in the very same application they remote datasets a lot -
so they miss out on the performance gains via dataset.remotingformat.
d) MARS - what if you are sharing one object and using MARS - you will
hit the 10 command limit sooner than you can say whatchamacallit. Not to
mention - there is nothing you can now do to monitor this limit because
god knows who all is sharing your single object.
e) Blocking nature - pooling ensures that as your app needs grow, new
physical connections are created - one object means you are stuck
implementing all that.
I have tonnes of more reasons - and even for the simplest of
applications - say a desktop app touching an access db, I still recommend
connection pooling versus implementing your own hoopla.
Not to mention - if you indeed wanted to restrict the user to one single
physical connection as indeed your model would - you can specify that via
the connection string with ZERO code written. I think it makes sense to
leverage of all the code Microsoft has written and tested and debugged -
and that is running in thousands of applications worldwide. Why invent
your own wheel?? It might not be perfectly square .. uh huh I mean round
!!
Just my views
- Sahil Malik
http://dotnetjunkies.com/weblog/sahilmalik
David Browne said:
message
Sure, there is nothing to stop you from creating and opening a
persistent connection in a Windows Forms using ADO.NET. No, this won't
work with ASP.NET applications.
It does save time because even the connection pool Open takes time to
execute. It also prevents you from leveraging server-side
functionality that you CAN use in ADO.NET. The problem is, it limits
the number of users your server can handle--from several hundred to
several thousand or so.
--
And _how_ to do it is simple. Use a public variable in a module, just
like VB6. Or make it a "Shared" (static in C#) member of some class.
Another fun way to share a Connection, and one which works perfectly
well in ASP.NET as well as winforms apps, is to keep the connection in
thread-local storage. ASP.NET pools worker threads, so you won't get
too many connections open. Each worker thread will have its own
connection. The nice thing about this is that if you have a lot of
layers of business logic and MethodA calls MethodB calls MethodC, and
they all need the database, they will all share the same connection.
This can actually reduce the number of open connections.
class ThreadLocalConnection
{
[ThreadStatic]
static SqlConnection con;
[ThreadStatic]
static string lastConn;
public SqlConnection Connect(string ConnectionString)
{
if (con == null || ConnectionString != lastConn)
{
if (con != null)
{
con.Dispose();
}
con = new SqlConnection(ConnectionString);
con.Open();
lastConn = ConnectionString;
}
return con;
}
}
David
