Last post Jan 12, 2010 09:06 AM by atconway
Jan 11, 2010 05:01 PM|SamCook1|LINK
So I started working on my first asp.net application that involves logging in and databases, and soon after i started messing around with a static class. I "discovered" that if you make a variable static, all sessions share that variable (I for some reason
was originally assuming that each session had its own copy of that "static" class). Anyway, after discovering this I thought to myself "how could this possibly be useful to me" and thought that it
might be a good idea to make a single static database connection for all of the sessions, rather than storing that as a session variable for each session. Does anybody know what would be the pros and cons of this approach? Are there any threading
issues that would pop up from doing this (my limited tests indicate that there isnt)?
Jan 11, 2010 05:47 PM|Mikesdotnetting|LINK
You should not be considering sharing database connections among users, or making them available in session or global variables in an ASP.NET application. Database connections should only be created when they are needed, and opened as late as possible in
your code, then closed as soon as possible.
The default ADO.NET setup allows for connection pooling. This leaves "closed" connections dormant in a pool so that they can be reused. That's how efficiencies are gained and your application can scale. You do not want the SQL Server sitting there managing
a whole load of resources (connections) that are not being used.
Jan 11, 2010 06:04 PM|Rick Matthys|LINK
When it comes to database functionality, just use SqlHelper from the Microsoft application data blocks. You won't need to worry about connections and all the gory details with the ado.net. It provides simple static methods that will cover 99.9% of your
data needs (executing sql, sprocs, returning dataset, data table, etc... It's just one file (SqlHelper.cs). Google it and you find a download from microsoft.
Here's an example calling a stored procedure which takes 1 parameter:
DataTable table = SqlHelper.ExecuteDataTable("someConnectionString", "someSProc", someParameter);
foreach (DataRow row in table.Rows)
string name = (string)row["Name"];
int age = (int)row["Age"];
Jan 11, 2010 11:22 PM|SamCook1|LINK
I didn't realize leaving connections open was that costly. So on a given page they should be opened and closed as soon as possible? I thought it might be more beneficial to keep a connection open for each session to avoid the overhead of connecting over
Jan 12, 2010 01:25 AM|Mikesdotnetting|LINK
I thought it might be more beneficial to keep a connection open for each session to avoid the overhead of connecting over and over.
No. Quite the opposite. Connection Pooling manages connections a lot more efficiently than you ever could through code. If a visitor lands on your site by mistake, they create a session. That will last for a minimum of 20 minutes. So will their opened connection,
even though it is never used for more than one page for a few seconds.
Jan 12, 2010 09:06 AM|atconway|LINK
To add on to what has already been stated, you want the connection only to persist for the absolute minimum scope needed for the calls you need to make. A great way to do this is by the 'Using' statement. Take a look at the code example below:
Using MyConn As New SqlConnection("Connection String Goes Here")
'Code needing to use the Sql Connection goes in here
'The Using Block will take care of closing the connection
Connections should only be open for as long as needed. The nice thing is ADO.NET handles Connection Pooling which will reduce the number of new connection that have to be opened each time it is requested. From MSDN:
"Connection pooling reduces the number of times that new connections must be opened. The pooler maintains ownership of the physical connection. It manages connections by keeping alive a set of active connections for each given connection configuration.
Whenever a user calls Open on a connection, the pooler looks for an available connection in the pool. If a pooled connection is available, it returns it to the caller instead of opening a new connection. When the application calls Close on the connection,
the pooler returns it to the pooled set of active connections instead of closing it. Once the connection is returned to the pool, it is ready to be reused on the next Open call."
SQL Server Connection Pooling (ADO.NET):