Advanced problem with function handling

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 :

VB Code:

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.

VB Code:

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) :(

VB Code:

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 !

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.

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:

VB Code:

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:

VB Code:

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.