[dba-VB] VB.Net - Data reader class

Hi John,

Your code looks really good.  The only thing you are missing is checking the
status of the connection before opening or closing it.

As for your questions about the data reader...The connection and command
object have to be open in order for the data reader to work.  I do not open
the connection or command until the data reader is needed so the flow you
have makes sense to me.

Doris Manning
mikedorism at verizon.net

-----Original Message-----
From: dba-vb-bounces at databaseadvisors.com
[mailto:dba-vb-bounces at databaseadvisors.com] On Behalf Of John W. Colby
Sent: Tuesday, August 23, 2005 11:43 AM
To: dba-vb at databaseadvisors.com
Subject: [dba-VB] VB.Net - Data reader class

As you might have guessed from my posts, I am a real nubee to vb.net.  As
such I am trying to make my life easier in getting real work done.  One
thing I need to do a lot is to open data readers.  While the absolute
minimum code required to do this is not overly complex, by the time you
capture errors and stuff it turns into a fair size chunk of code.  Having
done it once for a specific thing I was doing I pulled it out into a class
so that I could just instantiate the class every time I needed to do this.

Unfortunately I don't really understand the interactions between the pieces.
For example should I just store the provider and datasrc into the connection
string and wait.  Then when a reader is requested, call back up the chain -
the function requesting a reader object requests a command object, which
requests a connection.  Once the connection is valid the command object is
created, then the reader object is created and handed back.  Once all this
is done, can the connection be closed?  What about the command object?  Can
the reader be used if the connection and command object no longer exist?

It seems like that is what I should be doing, especially in light of the
"disconnected" thing so in vogue.  The code that follows does NOT do any of
that, rather creates a connection which it holds open, creates a command
object which it holds open, and finally creates a reader object on request.

Again, the objective of this whole exercise is to have a class that I just
call passing all the right stuff, which does the work, traps and logs
errors, and hands back a reader on request.

The class is as follows.  Please comment on it and let me know if I am
missing something, making my life harder etc.

Imports System.Data
Imports System.Data.OleDb
Public Class clsData
    Private mcnn As OleDbConnection
    Private mcmd As OleDbCommand
    Private mdr As OleDbDataReader
    Private mstrSQL As String
    Private mstrCnn As String
    Private mstrDataSrc As String
    Private mstrProvider As String
    '
    'Get the provider and data source strings
    'Store them locally "just because" and to assist debugging
    '
    Public Sub New(ByVal lstrProvider As String, ByVal lstrDataSrc As
String)
        mstrProvider = lstrProvider
        mstrDataSrc = lstrDataSrc
        mstrCnn = mstrProvider
        mstrCnn.Concat(mstrDataSrc)
        mGetToDataSrc()
    End Sub
    '
    'Use the connection string to open a connection
    'replace the messagebox with logfile
    '
    Private Function mGetToDataSrc()
        mstrCnn = mstrCnn
        mstrDataSrc = mstrDataSrc
        mstrCnn += mstrDataSrc
        mcnn = New OleDbConnection(mstrCnn)
        Try
            mcnn.Open()
            MessageBox.Show("Connection opened successfully")

        Catch e As Exception
            MessageBox.Show("Connection failed to open successfully:" +
e.ToString())
            Exit Function
        End Try
    End Function
    '
    'Pass in a SQL statement
    'Using the sql statement and the connection, get a command object
    '
    Property pSQL() As String
        Get
            pSQL = mstrSQL
        End Get
        Set(ByVal lstrsql As String)
            mstrSQL = lstrsql
            Try
                mcmd = New OleDbCommand(mstrSQL, mcnn)
            Catch e As Exception
                MessageBox.Show("Command failed to open successfully:" +
e.ToString())
                Exit Property
            End Try
        End Set
    End Property
    '
    'Using the command object, get a data reader
    '
    ReadOnly Property pDR() As OleDbDataReader
        Get
            Try
                mdr = mcmd.ExecuteReader
                pDR = mdr
            Catch e As Exception
                MessageBox.Show("Command failed to open successfully:" +
e.ToString())
                Exit Property
            End Try
        End Get
    End Property
    Protected Overrides Sub Finalize()
        MyBase.Finalize()
        mdr.Close()
        mcnn.Close()
        MessageBox.Show("Connection closed successfully")
    End Sub
End Class


John W. Colby
www.ColbyConsulting.com 

Contribute your unused CPU cycles to a good cause:
http://folding.stanford.edu/


_______________________________________________
dba-VB mailing list
dba-VB at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-vb
http://www.databaseadvisors.com