Database Connection (SQL Server) for ASP.NET application

Question

I have an ASP.Net Application that uses a SQL Server database. I am also using ODBC to make the connection (see below). Then I load controls (many of them) with queries.

Is this the correct way to do this?

Also, I need to do most of these programatically, not at design time.

Sub Session_Start(ByVal sender As Object, ByVal e As EventArgs)
    ' Fires when the session is started

    Session("ConnString") = "DRIVER={SQL Server};SERVER=myserver;Trusted_Connection=True;DATABASE=mydatabase"
    Session("MyConnection") = New Odbc.OdbcConnection(Session("ConnString"))

End Sub

You can't (and shouldn't) store OdbcConnection in the session. It's a disposable object and needs to be disposed immediately after use to free resources, otherwise you'll end up with a resource leak and performance issues. — TheCodeKing
– TheCodeKing, Commented Aug 31, 2011 at 12:40
Actually you can by using a Static Class but I don't recommend that either. That was one of my first projects. — Troy Mitchel
– Troy Mitchel, Commented Aug 31, 2011 at 16:50
You can't store in session perhaps isn't entirely accurate, in your using InProc you could get away with it, but your application will just die under load. It's the same reason for not holding a static reference. Bad idea. Best practice is to dispose of disposable objects as soon as possible. — TheCodeKing
– TheCodeKing, Commented Aug 31, 2011 at 17:11

Bram Vandenbussche · Accepted Answer · 2011-08-31 12:02:44Z

2

I don't think that saving connection objects to session is very good practice (see below why)

Can't you just save the connectionstring in session and re-create the sql server connection on page_Load?

Sql connections should normally only live as long as your request lifetime (maximum), prefereably shorter. You should close a sql connection as soon as you don't need it anymore.

It's bad practice to keep one open during the entire session. As this will make your connection pool run out of available connections very fast.

Can you please explan your question a bit better?

edited Aug 31, 2011 at 12:02

answered Aug 31, 2011 at 11:57

Bram Vandenbussche

1,40111 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Troy Mitchel Over a year ago

I didn't open the connection in Session_Start, just created the variable to hold it. But I think I understand what your saying is to declare the connection variable in page load like [Dim MyConnection As Odbc.OdbcConnection = CType(Session("MyConnection"), Odbc.OdbcConnection)]. Then open and close it as needed in the page or control events. Is this correct?

Bram Vandenbussche Over a year ago

That is indeed what I'm saying. Just holding a variable declaration in Session has no real purpose. It would be easier to create a base class you can extend (inherit from) and do your connection handling in there, so you won't have duplicate code on each page.

Collectives™ on Stack Overflow

Database Connection (SQL Server) for ASP.NET application

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related