when working with a repeater would you declare a new datacontext on every itemdatabound?

Question

this doesn't feel right

Private Sub drop_ItemDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.RepeaterItemEventArgs) Handles iqty.ItemDataBound
    If e.Item.ItemType = ListItemType.Item OrElse e.Item.ItemType = ListItemType.AlternatingItem Then
        Dim row As allowanceQtyQuery = CType(e.Item.DataItem, allowanceQtyQuery)

        Dim unit = CType(e.Item.FindControl("Unit"), DropDownList)
        Using db As New myContext
            Dim u = db.getAllUnit(True)
            unit.DataSource = u
            unit.DataTextField = "descen"
            unit.DataValueField = "unitid"
            unit.DataBind()
        End Using
        unit.Text = row.Unit
    End If
End Sub

because if I have let say 1000 items, it will declare 1000x the datacontext

how would you deal with this?

created this to be able to remove the "using db" part

Public ReadOnly Property listOfUnit() As List(Of Unit)
    Get
        If HttpContext.Current.Cache("unit") Is Nothing Then
            Using db As New ODSTS
                HttpContext.Current.Cache.Insert("unit", db.getAllUnit(True).ToList, Nothing, Cache.NoAbsoluteExpiration, TimeSpan.FromMinutes(5))
            End Using
        End If
        Return CType(HttpContext.Current.Cache("unit"), List(Of Unit))
    End Get
End Property

Answer 1

Cache the result set from the DB as a DataTable, List or whatever in a class member variable and bind each drop down from that, this way only hit DB the one time.

Answer 2

As I saw you aren't doing nothing especial with your datacontext, so why dont you cache it in a field and use into your method?

You could do something like:

Private Sub drop_ItemDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.RepeaterItemEventArgs) Handles iqty.ItemDataBound
    If e.Item.ItemType = ListItemType.Item OrElse e.Item.ItemType = ListItemType.AlternatingItem Then
        Dim row As allowanceQtyQuery = CType(e.Item.DataItem, allowanceQtyQuery)
        Dim unit = CType(e.Item.FindControl("Unit"), DropDownList)
            unit.DataSource = getDataContextPriv.getAllUnit(True)
            unit.DataTextField = "descen"
            unit.DataValueField = "unitid"
            unit.DataBind()
        unit.Text = row.Unit
    End If
End Sub

Protected Sub Page_Unload(ByVal Sender as Object, ByVal e as EventArgs)
    _myContext.Dispose()
End Sub

Private _myContext as DataContext
Private Property getDataContextPriv() As DataContext
     Get
      If(_myContext is Nothing) Then
       _myContext = new myContext()
      End If
      return _myContext
     End Get
End Property