Advanced problem with function handling

[Borry]

Hello everybody,


I just wrote a datalayer with help from a wrox book. This layer works fine but I have a question about it.

This is my data layer :

Public Overloads Function ExecuteSql(ByVal SProcName As String, _
                                        ByRef myDataReader As SqlDataReader) As Boolean
 
        ' Create a new command object
        Dim myCommand As SqlCommand = CreateNoParamCommand(SProcName, CommandType.Text)
 
        ' Fill the DataReader object
        Try
            myDataReader = myCommand.ExecuteReader
            System.Diagnostics.EventLog.WriteEntry(MODULE_NAME, "hi", Diagnostics.EventLogEntryType.Error)
            Return True
        Catch e As Exception
            LogError(e)
            Return False
        End Try
 
    End Function

As you can see, I made a logging in the event log to see each time a dbase connection has been made.

I wrote following function to make a separate layer. This function returns me the datalayer.

Private Function LoadGridje() As SqlDataReader
 
        Dim strSQL As String
        Dim dr As SqlDataReader
 
        Dim DbAccess As New DataAccess()
 
        strSQL = "SELECT DISTINCT Afdeling FROM tblWerkposten"
 
        'Response.Write(DbAccess.ExecuteSql(strSQL, dr))
        Try
            If DbAccess.ExecuteSql(strSQL, dr) = True Then
                Return dr
            Else
                Return Nothing
            End If
        Catch
            Return Nothing
        End Try
 
    End Function

Right now I want to bind a combobox on my function. All this works perfect, except when I call the DataValueField, which needs to give me the values of the datareader, then my connection has been made twice (I can see this in my event log)

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
 
        With cboAfdeling
            .DataSource = LoadGridje()
            .DataValueField = LoadGridje.GetName(0)
            .DataBind()
        End With
 
    End Sub

I hope someone knows how I can work around this cause it's a pretty though one and I adore performance

Thanks in advance !

Bjorn

ps : if it isn't clear enough, please return a message !

[Cander]

set an object to the return value of the function first, then us that for your properties

Dim blah As DataReader

blah = loadGridje
.DataSource = blah
.DataValueField = blah.GetName(0)




see if that works.

[pvb]

Well, not sure why your code is opening a connection twice, i actually don't see where it opens it once but here's how's I'd bind a combobox:

DataAccess:

Public Class DataAccess : Implements IDisposable
 
    Private m_cn As SqlConnection
    Private m_connString As String = AppSettings.Item("DBConnString")
 
    Public Sub New()
        m_cn = New SqlConnection(m_connString)
    End Sub
 
    Public Overloads Function ExecuteSqlDataReader(ByVal cmdText As String, _
                                                   ByVal cmdType As CommandType) As SqlDataReader
        Dim cmd As New SqlCommand(cmdText)
        cmd.CommandType = cmdType
        Return ExecuteSqlDataReader(cmd)
    End Function
 
    Public Overloads Function ExecuteSqlDataReader(ByVal cmd As SqlCommand) As SqlDataReader
        cmd.Connection = m_cn
        cmd.Connection.Open()
        Return cmd.ExecuteReader()
    End Function
 
    Public Overloads Sub Dispose() Implements IDisposable.Dispose
        If Not m_cn Is Nothing Then
            If m_cn.State <> ConnectionState.Closed Then m_cn.Close()
            m_cn.Dispose()
        End If
    End Sub
 
End Class

Then, assuming theres an asp:DropDownList with the ID of "Authors" on the aspx page, the code behind would look like this:

Protected Authors As DropDownList
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    If Not Page.IsPostBack Then
        loadComboBox()
    End If
End Sub
 
Private Sub loadComboBox()
    Dim cmdText As String = "Select au_id, au_lname From Authors"
    Dim da As New DataAccess()
    Try
        Authors.DataSource = da.ExecuteSqlDataReader(cmdText, CommandType.Text)
        Authors.DataValueField = "au_id"
        Authors.DataTextField = "au_lname"
        Authors.DataBind()
    Finally
        da.Dispose()
    End Try
End Sub

There are probably a million different ways of doing this, just givin some options.