ODBC Connection in Asp.Net (Connect and Dispose)

I Open and Close ODBC connection while the page is up and running. When and where should i put the Connection.Dispose method? I tried on the Page_Disposed but it never makes it there.

Public Class _WepPage1
Inherits System.Web.UI.Page

Dim MyConnection As New Odbc.OdbcConnection

Private Sub Page_Disposed(sender As Object, e As System.EventArgs) Handles Me.Disposed
    MyConnection.Dispose()
End Sub

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
    'String comes from Web.Config
    MyConnection.ConnectionString = ConfigurationManager.ConnectionStrings("MyConnection").ConnectionString
    If Not IsPostBack Then
        Call LoadSomeData()
    Else
        'Do some more stuff            
    End If
End Sub

Private Sub LoadSomeData()
    If MyConnection.State = ConnectionState.Closed Then MyConnection.O开发者_JAVA技巧pen()
    Dim MyCommand As New Odbc.OdbcCommand("select * from tablename", MyConnection)
    Try
        Dim dataR As Odbc.OdbcDataReader = MyCommand.ExecuteReader
        While dataR.Read
            DropDownList1.Items.Add(dataR("column1"))
        End While
    Catch ex As Exception
    End Try
    MyCommand.Dispose()
    If MyConnection.State = ConnectionState.Open Then MyConnection.Close()
End Sub

I would not use the Page_Load to start the connection. In .NET, the "real" connection object returns to the pool when you Dispose(), so it is there for other work. Even if you open, close and dispose numerous times, there is but a few microseconds of overhead max, so open a connection early and then keeping it open for the entire page duration does not really help you.

In addition, opening connection in Page_Load means you have tightly coupled your data access to your UI, leaving you a lot of rearchitecture and not just rearrangement to divide the work into proper tiers.

What this means is the connection should be created, used and disposed in one method (in your example). This means it is both logically separated and easy to separate into different classes and/or assemblies, should the application grow so you need a full DAL.

I would, at minimum, move all realting to connection to the LoadSomeData() method. If you want more proper separation of concerns, make a GetSomeData() routine and have the LoadSomeData() routine call the GetSomeData() routine and shape the data so it can be easily bound, rather than consume one row at a time (In your example, instead of binding a data object to the drop down, you are pushing one item at a time).

I hope this helps and I am sorry that many of the examples out there are really bad at best practices. And, yes, this includes many samples from people who should know better. ;-)